Migration / Seed / Dump Tooling

Overview

The database-2026 repo doesn't just hold the schema — it's a full toolkit for managing the database's lifecycle: creating migrations, seeding initial data, generating column comments, and capturing snapshots of the real structure from a live server.

The one thing to understand before changing anything here is that the schema is managed through three overlapping channels:

1. Prisma (prisma/schema.prisma and prisma/migrations/) covers only the public schema, and only some of its tables
2. Manual SQL (manual-sql/) for tables, columns, and triggers Prisma doesn't know about — these must be applied by hand with psql
3. App migrations (apps/<app-name>/migrations/) for separate schemas like appointment, bulletin, and loyalty, which Prisma never touches at all

Core Data Structure

package.json scripts

Script	Actual command	What it does
npm run init	prisma migrate reset followed by up --file=init	Creates the schema from scratch
npm run status	prisma migrate status	Checks which migrations haven't been applied
npm run apply	prisma migrate deploy	Applies migrations to the database
npm run up --file=<name>	migrate diff writes a down file, then migrate dev --create-only	Creates a matching up/down file pair
npm run down --file=<name>	prisma db execute against a file in prisma/down/	Rolls back a migration
npx ts-node seed.ts	Reads the file named by the $FILE_SEED variable in seed-data/	Seeds a single file
npx ts-node seed-all.ts	Walks every file in seed-data/	Seeds the entire set

The mechanism behind seed.ts and seed-all.ts is very simple: it reads a SQL file, splits it on split(';'), and sends each statement through $executeRawUnsafe one at a time. The consequence is that a seed file can never contain a ; character inside a string literal.

Seed order

Order matters here because tables reference each other via foreign keys. Files seed-data/01 through 16 run in this order:

system_module → system_role → system_role_module → organization → user → line_oa → line_user → audience → rich_menu → password_history → rich_message → campaign → api_client → api_key → tracking_line_users → form_builder_rule

src/generate-comment.ts

A custom Prisma generator that converts /// comments in the schema into COMMENT statements and automatically produces a migration for them. It uses a lock file with a sha256 hash to detect whether comments have changed. This tool is a workaround for Prisma issue #8703, must always be applied manually, and has a limitation with field names that aren't in snake_case.

schema-dumps/2026-07-24/

A structural snapshot of preprod captured with pg_dump --schema-only on PostgreSQL 17.9.

• schema.sql — the full structure
• triggers-summary.txt — one line per trigger
• notify-functions.txt — every function that calls pg_notify

This dump only covers the public and appointment schemas as of that date — bulletin and loyalty are not included.

Drift-checking tooling

prisma-example-script/check-diff-schema-db.txt holds example prisma migrate diff commands for comparing schema.prisma against the real database in each environment (local, UAT, production). This is the main tool for catching drift caused by applying manual SQL.

prisma/ERD.md is a mermaid ERD that is out of date — it still uses names like Organize and includes Pdpa and AudienceMember, neither of which exist in the current schema. Treat schema.prisma as the source of truth instead.

Related Files

• package.json, seed.ts, seed-all.ts, README.md (a Thai-language guide)
• src/generate-comment.ts, src/dist/generate-comment.js
• prisma/schema.prisma, prisma/migrations/, prisma/ERD.md
• manual-sql/0.select.sql through manual-sql/9.import_mapping.sql
• apps/{appointment,bulletin,loyalty}/migrations/
• schema-dumps/2026-07-24/, prisma-example-script/check-diff-schema-db.txt
• .env.example — the DATABASE_URL and DATABASE_NAME variables; the example values are still written for MySQL even though the real system runs PostgreSQL
• .gitignore — ignores prisma/migrations and prisma/down, so any migrations that are committed got there via a forced add of specific files

Connections to Other Services

• Every service uses the schema this repo defines, but no service uses Prisma at runtime — cms-api-go and client-api-go use raw SQL with sqlx or GORM, and worker-go and webhook-go follow the same approach. The consequence is that renaming a column is never caught at compile time; every service's queries have to be fixed by hand
• Caution: prisma migrate doesn't know about the manual SQL or the app schemas. Running prisma migrate reset only wipes public and drops every trigger with it, so the files in manual-sql/ must be reapplied every time (the README has a "How to Reinit" section covering this)