49.9k
A feature request board gives users somewhere to suggest improvements and support ideas they care about. A request has an author, a status, and a vote count. Behind that small interface sits an important promise: one account gets one vote per request, even when two requests arrive together.
Let's build that with Next.js, Prisma ORM, Clerk, and ClickHouse Managed Postgres. We'll create requests, open their detail pages, vote, and filter the board by status. Postgres stores the relationships and enforces vote uniqueness; the server checks who may change each record.
The full runnable application includes the schema, SQL migrations, sample data, and configuration templates. Keep its README open for the complete setup, connection, and cleanup commands. Here we'll follow the operations that make the board work.
The architecture
The browser talks to a Next.js application running on Node.js. Clerk supplies sign-in and verified session identity. Server-side code uses Prisma through its node-postgres adapter to connect to ClickHouse Managed Postgres over TLS.
| User action | Responsibility |
|---|---|
| Read the board or open a request | Next.js loads records and vote counts from Postgres |
| Submit or edit a request | The server derives identity from Clerk and checks the allowed fields |
| Add or remove a vote | Postgres stores a relationship between the account and the request |
| Filter by status | A database query selects matching requests |
ClickHouse Cloud is the platform that offers both managed ClickHouse for analytics and ClickHouse Managed Postgres for transactional workloads. ClickHouse Managed Postgres provides PostgreSQL with local NVMe storage and managed database operations. These services are connected through a unified data stack: ClickPipes can replicate Postgres data into ClickHouse, and pg_clickhouse can query ClickHouse from Postgres.
This board uses the Postgres service on its own. Its current records, relationships, and vote totals fit ordinary transactional queries. A ClickHouse service and a ClickPipe aren't prerequisites for running it.
1. Create the database and connect the application
Start with a ClickHouse Cloud account and $300 in trial credits, plus a Clerk application. Follow the README to install the pinned dependencies and create a Managed Postgres service with clickhousectl. The instructions show each infrastructure command and SQL file explicitly, so you can inspect what changes before running it.
Download the service's CA certificate, confirm the direct connection endpoint, and configure separate migration and runtime credentials. Migrations need to change the schema; the application needs access to its records. Keep those responsibilities separate when deploying.
The runtime configuration passes the downloaded CA to node-postgres and requires certificate verification:
ssl: {
ca: readFileSync(env.DATABASE_CA_PATH, "utf8"),
rejectUnauthorized: true,
},The Prisma client uses that configuration through PrismaPg, selecting the application's feature_board schema. TLS authenticates the database server as well as encrypting the connection.
One detail matters when combining a connection URL with an explicit TLS configuration: node-postgres warns that URL parameters such as sslmode or sslrootcert can replace the ssl object. Use the example's documented connection format so the intended CA and verification settings survive parsing. See the node-postgres TLS documentation.
2. Read the schema before applying the migration
There are two tables: feature requests and votes. Requests store the author's Clerk ID and a display-name snapshot. Votes store a Clerk user ID and a request ID. Identity remains in Clerk; there is no separate users table to synchronize.
In the Prisma schema, the vote's relationship and primary key are:
request FeatureRequest @relation(fields: [requestId], references: [id], onDelete: Cascade)
@@id([requestId, userId])The SQL migration expresses the same composite key:
CONSTRAINT "votes_pkey" PRIMARY KEY ("request_id", "user_id")The two columns are unique together. A user can support many requests, and a request can collect votes from many users. What cannot exist is a second vote by the same user for the same request. PostgreSQL's constraint documentation explains how uniqueness applies across a group of columns.
The request foreign key prevents orphan votes; deleting a request removes its votes. The example pins Prisma ORM 7 and keeps its migration SQL in the repository. Review that SQL, then apply it and load the sample data using the README. Another person should be able to create the same tables and constraints from a clean checkout.
3. Make repeated votes safe
Imagine opening the same request in two tabs and voting in both. Disabling a button prevents some accidental clicks, but it cannot coordinate two browser tabs or two application processes.
The vote operation asks Postgres to insert the relationship, skipping an existing vote:
await db.vote.createMany({
data: [{ requestId: id, userId: actor.userId }],
skipDuplicates: true,
});This avoids a separate “does this vote exist?” check followed by an unprotected insert. Those two operations could race: both requests could observe no vote before either writes. The database constraint remains authoritative regardless of which application process receives the request.
Removing a vote targets the same user-request pair. Repeating that removal leaves it absent. Adding and removing are explicit actions, so retrying an add does not accidentally become an unvote.
The displayed count comes from the stored vote rows. We don't maintain an independent counter that could drift when an insert is skipped or a removal is retried.
4. Keep ownership checks on the server
Clerk answers who is signed in. The application decides what that person may do. It gets the user ID from Clerk's server-side auth() helper, rather than accepting an author or voter ID from the browser.
Editing a request uses one conditional write in the application service:
const result = await db.featureRequest.updateMany({
where: { id: parseRequestId(requestId), authorId: actor.userId },
data: parseContent(input),
});A zero-row result means the request doesn't exist or doesn't belong to that user. Authors can edit or delete their requests. Only users listed in the server's MAINTAINER_USER_IDS configuration can change status to Open, Planned, In progress, or Shipped.
Showing an edit button only to its owner helps the interface, but the write itself must include the ownership condition. A crafted request should have the same permissions as a click in the browser. The example also validates submitted text and status values before using them in database operations.
5. Try the behavior that matters
Start the app, sign in, and submit a request such as “Export the roadmap as CSV.” Open its detail page, vote, and reload. The stored count should still include exactly one vote from your account.
Then exercise the boundaries:
- Open the request in two tabs and add your vote from both. Expect one stored vote.
- Remove your vote and repeat the removal. Expect no vote from your account.
- Sign in as a second user. They can add their own vote, but cannot edit your protected records.
- Change a request's status using an authorized account and check the board's status filter.
During verification against PostgreSQL 18.6 on ClickHouse Managed Postgres, 24 simultaneous voting attempts by one user produced exactly one vote. Browser tests with real Clerk sessions confirmed the application flows, including rejection of signed-out and cross-user writes. Runtime and migration connections accepted the trusted CA and rejected an unrelated CA; the migration connection also rejected a hostname mismatch.
Use the README for deployment configuration, service costs, and removal of the resources you created. A small example still needs an explicit end to its Cloud lifecycle.
Adapt it to your product
The useful pattern is a relationship with an enforceable rule: a person supports a request once. The same design works for saved items, event interest, or following a project. Change the product vocabulary while keeping identity on the server and uniqueness in the database.
Start with the complete example and ClickHouse Managed Postgres. You'll have a small application to extend, with its data model and deployment steps available to read alongside the code.
