Mannu
edd239fa69
drizzle-kit generate against the now-prod-aligned schema produces a single baseline migration covering all 35 tables. VERIFIED: 0000_baseline_prod_2026_05_19.sql was compared column-for-column and type-for-type against the drizzle-kit pull introspection of tia_prod. Table sets identical, all columns and types match. The baseline is a faithful representation of production. This baseline will be marked as already-applied in prod's __drizzle_migrations table (done out-of-band, not in git), so the migrator runs nothing on the next deploy. It exists purely as the reference point for future schema diffs. Adds drizzle/README.md documenting the baseline reset and the migration workflow going forward.
55 lines
2.3 KiB
Markdown
55 lines
2.3 KiB
Markdown
# Tia — Database Migrations
|
||
|
||
This folder is **source code** and is committed to git. It is consumed by the
|
||
deploy pipeline (`pnpm db:migrate`, run on container start — see `Dockerfile`).
|
||
|
||
## Baseline reset — 2026-05-19
|
||
|
||
The project's first 16 migrations (`0000`–`0015`) plus a `manual/` folder were
|
||
hand-rolled SQL applied directly via the Dokploy database terminal. They were
|
||
**never** run through Drizzle's migrator, so:
|
||
|
||
- prod had no `__drizzle_migrations` tracking table;
|
||
- the `drizzle/` folder was gitignored, so migration SQL never reached the server;
|
||
- `schema.ts` had drifted well behind the real production schema.
|
||
|
||
To fix this we performed a **Path A baseline reset**:
|
||
|
||
1. `pg_dump` backup of prod taken and stored off-server.
|
||
2. `drizzle-kit pull` introspected the live prod schema (35 tables).
|
||
3. `src/db/schema/*.ts` was rewritten to match prod exactly.
|
||
4. Legacy migrations were archived to `_archived_pre_baseline_2026-05-19/`
|
||
(also retained in git history).
|
||
5. A single fresh baseline — `0000_baseline_prod_2026_05_19.sql` — was generated
|
||
and **verified column-for-column against the introspected prod schema**.
|
||
6. Prod's `drizzle.__drizzle_migrations` table was created and seeded with one
|
||
row marking `0000_baseline_prod_2026_05_19` as already applied, so the
|
||
migrator treats prod as up-to-date and runs nothing on the next deploy.
|
||
|
||
## Normal workflow from here
|
||
|
||
```bash
|
||
# 1. Edit src/db/schema/*.ts
|
||
# 2. Generate a migration from the diff:
|
||
pnpm db:generate # writes drizzle/000N_<name>.sql
|
||
# 3. Review the generated SQL by eye.
|
||
# 4. Apply locally against the dev DB:
|
||
pnpm db:migrate
|
||
# 5. Commit schema + migration together, then push.
|
||
# Dokploy redeploys; the migrator applies it in prod on container start.
|
||
```
|
||
|
||
## Hard rules
|
||
|
||
- **Never** edit a migration file after it has been pushed. Fix-forward with a
|
||
new migration instead.
|
||
- **Never** run schema-changing SQL directly against prod. It becomes drift.
|
||
- The `drizzle/` folder must stay **out** of `.gitignore`.
|
||
|
||
## RLS policies
|
||
|
||
Five log tables (`feeds`, `diapers_logs`, `sleeps`, `vaccinations`, `growth`)
|
||
plus `children` / `family_members` carry row-level-security policies in prod.
|
||
These are **not** modelled in the `pgTable` definitions and are managed
|
||
separately in the database. Drizzle migrations will not recreate them — keep
|
||
that in mind if you ever rebuild the DB from scratch.
|