--- title: "Build a habit tracker with Next.js, Drizzle ORM, and ClickHouse Managed Postgres" date: "2026-10-27" author: "Al Brown" category: "Engineering" excerpt: "Build a Next.js 16 habit tracker with Drizzle ORM and node-postgres on ClickHouse Managed Postgres. A composite primary key keeps one check-in per day, and a window query computes streaks." --- # Build a habit tracker with Next.js, Drizzle ORM, and ClickHouse Managed Postgres A habit tracker looks like a CRUD app, but it makes one promise that is easy to break: **one check-in per habit per day**. Double-click a checkbox, retry a request on a flaky connection, or open the app in two tabs, and a naive implementation stores two rows for the same day or undoes the check it just made. The streak counter then shows the wrong number. This article walks through a small Next.js app that keeps that promise in Postgres. It uses Next.js 16 with Server Components and Server Actions, Drizzle ORM with node-postgres, and ClickHouse Managed Postgres, provisioned with `clickhousectl`. The full code, with every setup command, is in the [Habit Tracker example](https://github.com/ClickHouse/examples/tree/main/applications/habit-tracker). ## What you'll build ![Habit Tracker: a week of check-ins and a streak per habit](https://clickhouse.com/uploads/build_habit_tracker_nextjs_drizzle_postgres_screenshot_37ae6ff356.png) One screen lists your habits, each with a checkbox for each of the last seven days and the current streak. A second screen shows one habit's last 91 days as a heatmap, with its current and longest streak. ## The promise: one check-in per day, whatever the client does The user-facing rule is simple. If I ticked Tuesday, Tuesday is ticked once. That has to hold when: - the user double-clicks; - the browser retries a Server Action; - two tabs tick the same day at the same moment. An application-level check ("does a row exist? if not, insert") can't hold this rule. Two requests can both see "no row" before either inserts. The database has to enforce it. ## Architecture and data model The browser talks to a Next.js app whose Server Components read and whose Server Actions write through Drizzle and `pg`, over verified TLS, to ClickHouse Managed Postgres. There are three tables in a `habit_tracker` schema: `users`, `habits` (with a nullable `archived_at`) and `check_ins`. The invariant lives in the Drizzle definition of `check_ins`. ```ts export const checkIns = habitTracker.table( "check_ins", { habitId: integer("habit_id") .notNull() .references(() => habits.id, { onDelete: "cascade" }), day: date({ mode: "string" }).notNull(), createdAt: timestamp("created_at", { withTimezone: true }) .notNull() .defaultNow(), }, // One check-in per habit per day. The primary key is the UNIQUE (habit_id, day) // constraint that makes double clicks, retries and races harmless. (t) => [primaryKey({ columns: [t.habitId, t.day] })], ); ``` `day` is a Postgres `date`, not a timestamp. "Today" is computed once, in a configured time zone, and the row stores the calendar day it belongs to. The composite primary key `(habit_id, day)` is the whole invariant, so Postgres needs no help from a queue or a lock service. ## The operations that keep the promise ### Send the desired state, not "toggle" A toggle endpoint can't be retried safely. Send it twice and the second request undoes the first. The checkbox instead sends the state it should end up in, `done: true` or `done: false`. Repeating that request changes nothing, so double clicks and retries are harmless. ### Check in with INSERT … ON CONFLICT DO NOTHING Checking a day is a single statement. `INSERT … SELECT` only produces a row when the habit is active and belongs to the user, and `ON CONFLICT DO NOTHING` turns a duplicate into a no-op. ```ts await db .insert(checkIns) .select( db .select({ habitId: habits.id, day: sql`${day}::date`.as("day"), createdAt: sql`now()`.as("created_at"), }) .from(habits) .where(ownedActiveHabit(userId, habitId)), ) .onConflictDoNothing(); ``` Unchecking is a `DELETE` with the same ownership condition, which is idempotent too. The Server Action validates the habit ID, the date and the seven-day window with Zod before calling this function. ### Compute streaks in SQL with gaps and islands A streak is a run of consecutive days. In a run, `day - row_number()` is the same for every row, because both increase by one each day. Grouping by that value gives one group per run. ```ts sql` with islands as ( select c.habit_id, c.day, c.day - (row_number() over (partition by c.habit_id order by c.day))::int as island from ${checkIns} c join ${habits} h on h.id = c.habit_id where h.user_id = ${userId} and h.archived_at is null ), runs as ( select habit_id, max(day) as last_day, count(*)::int as length from islands group by habit_id, island ) select habit_id, coalesce(max(length) filter (where last_day >= ${today}::date - 1), 0)::int as current, max(length)::int as longest from runs group by habit_id ` ``` A run is current if it ends today or yesterday, since today's check-in may still be to come. Because the primary key guarantees one row per day, `count(*)` is the streak length. Duplicate rows would break this query without anyone noticing. ## Connect Next.js and Drizzle to ClickHouse Managed Postgres Create the service and download its CA certificate with `clickhousectl`: ```bash clickhousectl cloud postgres create \ --org-id "$CH_ORG_ID" --name habit-tracker \ --provider aws --region "$PG_REGION" --size "$PG_SIZE" \ --pg-version 18 --ha-type none --tag project=habit-tracker \ --json > .deployment/postgres-create.json clickhousectl cloud postgres certs get "$PG_SERVICE_ID" --org-id "$CH_ORG_ID" --output .deployment/ca.pem ``` `src/db.ts` creates the only connection pool. It passes the CA with `rejectUnauthorized: true`, which verifies both the certificate and the hostname (the `verify-full` behaviour). It refuses a remote host without a CA, so a missing variable can't quietly fall back to an unencrypted connection. ```ts export function tlsOptions( connectionString: string, caPath: string | undefined, ) { if (caPath) return { ca: readFileSync(caPath, "utf8"), rejectUnauthorized: true }; // Local Postgres from `clickhousectl local postgres` has no TLS. Anything // else must have a CA, so a missing variable can't silently disable TLS. if (LOCAL_HOSTS.has(new URL(connectionString).hostname)) return false; throw new Error("Set PGSSLROOTCERT to the CA file for a remote database."); } ``` The pool has five connections, a 5-second statement timeout, and a `globalThis` singleton so `next dev` hot reloads don't leak pools. Migrations run with drizzle-kit as a migrator role, and the app connects as a role that has only `SELECT`, `INSERT`, `DELETE` and one `UPDATE` column. The [README](https://github.com/ClickHouse/examples/tree/main/applications/habit-tracker#set-up-clickhouse-managed-postgres-with-clickhousectl) has the full sequence: roles, migrations, grants, seed data and the local Postgres loop. ## Prove it The integration suite runs as the app role against ClickHouse Managed Postgres. The core test fires 20 identical check-ins at once. ```ts test("20 concurrent check-ins for the same day leave exactly one row", async () => { const id = await newHabit(); await Promise.all( Array.from({ length: 20 }, () => setCheckIn(db, DEMO_USER_ID, id, today, true), ), ); assert.equal(await rowsFor(id, today), 1); }); ``` ```text ✔ 20 concurrent check-ins for the same day leave exactly one row ✔ the database rejects a duplicate even without ON CONFLICT ✔ unchecking is idempotent too ✔ streaks are computed in SQL with gaps and islands ✔ list, history and archive ✔ another user's habit cannot be checked in ✔ the app role cannot run DDL ✔ managed Postgres connections use verified TLS ``` In the second test, a plain `INSERT` of a duplicate fails with `23505`. The guarantee comes from the constraint, so it holds even for code that forgets `ON CONFLICT`. ## Where to go next Add real users by swapping the fixed demo user for your auth library's user ID. Store a time zone per user if your users span regions. If you later want product analytics across many users, such as completion rates by weekday or retention cohorts, replicate `check_ins` to ClickHouse with ClickPipes and keep Postgres for the writes. **[Start a ClickHouse Cloud free trial with $300 in credits](https://clickhouse.com/cloud)** and run the [Habit Tracker example](https://github.com/ClickHouse/examples/tree/main/applications/habit-tracker) on ClickHouse Managed Postgres. ## Frequently asked questions ### Question 1: How do I connect Drizzle ORM to a managed Postgres with TLS? Create a `pg` `Pool` with `ssl: { ca, rejectUnauthorized: true }`, using the provider's CA file, and pass it to `drizzle({ client: pool })`. Keep `sslmode` out of the connection URL, because `pg` lets URL parameters override the `ssl` object. drizzle-kit opens its own connection, so `drizzle.config.ts` needs the same `ssl` object.