Totlob Docs

PostgreSQL 16 with 154 tables, built only from dbmate migrations. Queries are hand-written SQL, with types generated against the live schema by PgTyped.

Layout

Path	What
`db/migrations/`	dbmate migrations (73). The only way to build a database.
`db/schema.sql`	Committed `pg_dump` of the schema, produced by `scripts/dump-schema.mjs` inside the container. For review only.
`db/mysql-source/`	Original MySQL DDL and queries from the Go service, read-only reference
`src/modules/<m>/<m>.sql`	Hand-written queries with PgTyped annotations
`src/modules/<m>/<m>.queries.ts`	Generated output. Never edit by hand.
`pgtyped.config.json`	`camelCaseColumnNames: true`

Migration workflow

Follow these steps strictly in order:

npx dbmate new add_widget_table    # 1. write the migration
npm run db:migrate                 # 2. apply locally
npm run db:generate                # 3. PgTyped introspects the live DB
npm run db:dump                    # 4. refresh db/schema.sql
npm run typecheck && npx vitest run <feature>   # 5. verify
git add db/ src/modules/**/*.queries.ts         # 6. commit together

Rules for new tables

Integer identity primary keys. Use uuidv7 only for genuinely new concepts.
Every table with updated_at needs a set_updated_at() trigger.
Claim the table in src/modules/ownership.ts, or the boundaries test fails.
Money columns are numeric(20,6); rates are numeric(20,10).
Shop-scoped rows must be filtered by shop_id in SQL, never only in application code.

Seeds inside migrations

Migrations seed reference data:

languages: en, ar, fr
14 currencies
weight and dimension units
book attribute definitions
all lookup tables (order sources, financial statuses, cancel reasons…)
the app catalogue: reports, jarde, shelves

Gotchas

Never load schema.sql into a real database

db/schema.sql includes the schema_migrations ledger. Loading it makes dbmate think every migration ran, so future migrations are skipped forever. The test database "rots" the same way: if it drifts, run npm run db:test-reset.

db:generate exits 0 even when a SQL file fails to parse. PgTyped skips the file rather than deleting the old output, so check the log and the typecheck.
scripts/ is not typechecked.
Production migrations run in Heroku's release phase, before new dynos serve traffic. A failing migration blocks the deploy.
Production Postgres is on Stackhero, so heroku pg:psql doesn't work. Connect with the URL directly.

Search projection

Search reads from search_document, search_attribute and search_product_stock, a projection of the catalogue maintained by search.* events. It can be rebuilt with scripts/backfill-search.ts. See Deployment for why that matters.