feat(db): land the Drizzle schema, config and conventions #217

New Issue

2026-08-28T15:17:15-05:00

bermudalamb commented

2026-08-28 15:17:15 -05:00

First step of adopting Drizzle, decided from the spike in #216. Infrastructure only — no route is converted here, so nothing changes at run time.

Scope

Promote drizzle.config.ts and the introspected src/db-drizzle/schema.ts from the spike branch (feature/216-drizzle-spike, eff11c6), dropping the throwaway src/db-tinqer/ and the experimental condition_note column.
Add tablesFilter to exclude pgmigrations. It is node-pg-migrate's bookkeeping table and has no business in a generated schema.
Decide where the schema lives and how it is refreshed. drizzle-kit pull writes to out; the spike copied the file into src/ by hand, which will drift the first time someone re-pulls.
Wire a db instance alongside the existing pool rather than replacing it. Both must work at once, because conversion is file-by-file.

Write down the array rule before anyone hits it

The spike's sharpest trap. In a Drizzle sql template, ${someArray} expands into a placeholder list, not a single array parameter:

sql`... WHERE id = ANY(${filters.categoryIds}::int[])`   // -> ANY(($1, $2)::int[])  INVALID
sql`... WHERE id = ANY(${sql.param(filters.categoryIds)}::int[])`  // -> ANY($1::int[])  correct

The wrong form type-checks, reads correctly, and fails at run time as invalid Postgres. Nothing warns. Across 187 call sites this is exactly the shape of defect that passes review and breaks in production, so it needs to be a documented convention here and ideally a lint rule.

Also record the good news, because it is load-bearing

${value} in a Drizzle sql template emits a bind parameter, not text. There is no way to spell "interpolate this value as SQL" by accident. That is the #202 invariant enforced structurally rather than by a comment and two mutation tests, and it is the main reason this adoption is worth doing — it should be stated where a future reader will find it, next to the invariant comment #202 added to buildItemFilterSql.

Not in scope

Converting any route. Removing node-pg-migrate. Generating any migration — the spike showed the first generated migration after a pull carries spurious drops and recreations of the three expression indexes, and that needs handling deliberately in its own issue, not as a side effect of landing config.

Done when

The schema and config are on main, the conventions are written down, the build is clean and both suites still pass, with no behaviour change.

Refs #216

First step of adopting Drizzle, decided from the spike in #216. Infrastructure only — no route is converted here, so nothing changes at run time. ## Scope - Promote `drizzle.config.ts` and the introspected `src/db-drizzle/schema.ts` from the spike branch (`feature/216-drizzle-spike`, `eff11c6`), dropping the throwaway `src/db-tinqer/` and the experimental `condition_note` column. - Add `tablesFilter` to exclude `pgmigrations`. It is `node-pg-migrate`'s bookkeeping table and has no business in a generated schema. - Decide where the schema lives and how it is refreshed. `drizzle-kit pull` writes to `out`; the spike copied the file into `src/` by hand, which will drift the first time someone re-pulls. - Wire a `db` instance alongside the existing `pool` rather than replacing it. Both must work at once, because conversion is file-by-file. ## Write down the array rule before anyone hits it The spike's sharpest trap. In a Drizzle `sql` template, `${someArray}` expands into a **placeholder list**, not a single array parameter: ```ts sql`... WHERE id = ANY(${filters.categoryIds}::int[])` // -> ANY(($1, $2)::int[]) INVALID sql`... WHERE id = ANY(${sql.param(filters.categoryIds)}::int[])` // -> ANY($1::int[]) correct ``` The wrong form type-checks, reads correctly, and fails at run time as invalid Postgres. Nothing warns. Across 187 call sites this is exactly the shape of defect that passes review and breaks in production, so it needs to be a documented convention here and ideally a lint rule. ## Also record the good news, because it is load-bearing `${value}` in a Drizzle `sql` template emits a bind parameter, not text. There is no way to spell "interpolate this value as SQL" by accident. That is the #202 invariant enforced structurally rather than by a comment and two mutation tests, and it is the main reason this adoption is worth doing — it should be stated where a future reader will find it, next to the invariant comment #202 added to `buildItemFilterSql`. ## Not in scope Converting any route. Removing `node-pg-migrate`. Generating any migration — the spike showed the first generated migration after a pull carries spurious drops and recreations of the three expression indexes, and that needs handling deliberately in its own issue, not as a side effect of landing config. ## Done when The schema and config are on `main`, the conventions are written down, the build is clean and both suites still pass, with no behaviour change. Refs #216

bermudalamb referenced this issue

2026-08-28 15:17:42 -05:00

decide: whether generated Drizzle migrations replace node-pg-migrate #219

bermudalamb referenced this issue

2026-08-28 15:17:48 -05:00

spike: evaluate Drizzle for type-safe queries and generated migrations #216

bermudalamb referenced this issue

2026-09-01 13:17:32 -05:00

SonarQube cleanup: remove the rejected Tinqer spike, clear the lint debt, dedupe the extension map, and report measures in CI #261

bermudalamb referenced this issue from a commit

2026-09-01 13:41:08 -05:00

chore(sonar): remove the rejected Tinqer spike, clear the lint debt, and report measures in CI (#261)