Database

Create Lab Studio uses PostgreSQL 16 as its database. The backend connects via the native pg driver — there is no ORM. All queries are raw parameterized SQL executed through the repository layer.

---

Overview

Concern	Location
Connection (singleton pool)	src/infrastructure/database/PostgresConnection.ts
Config (URL, schema)	src/config/database.config.ts
Migration runner	src/infrastructure/database/migrator.ts
Seed runner	src/infrastructure/database/seeder.ts
Migration files	src/infrastructure/database/migrations/
Seed files	src/infrastructure/database/seeds/
CLI entry points	src/cli/

The database schema is isolated via PostgreSQL's search_path. The schema name is read from the DATABASE_SCHEMA env variable.

---

Migrations

How migrations work

The DatabaseMigrator class manages schema evolution:

1. On first run it creates a migrations table inside the configured schema.
2. It reads all .sql files from migrations/ sorted alphabetically.
3. For each file, it checks whether a record already exists in the migrations table.
4. If not yet applied, it wraps the SQL in a BEGIN/COMMIT transaction and records the filename on success. On failure it rolls back and throws.

Because migrations are tracked by filename, a migration is never applied twice — even after a fresh deploy.

Naming convention

{number}_{description}.sql

• The number is zero-padded to three digits (e.g., 040, not 40).
• If you need to insert a migration between two existing numbers, append a letter suffix: 039a_..., 039b_....
• The description uses snake_case and should clearly state what the migration does.

Examples from the codebase:

000_enable_extensions.sql
000a_create_roles_table.sql
004_create_episodes_table.sql
039_create_episode_assignment_template_table.sql

Adding a new migration

1. Find the latest migration number in src/infrastructure/database/migrations/ (currently 039).
2. Create a new file with the next number:

src/infrastructure/database/migrations/040_add_published_at_to_episodes.sql

3. Write idempotent SQL. Use IF NOT EXISTS, IF EXISTS, or ON CONFLICT guards so that re-running does not break anything:

ALTER TABLE episodes
  ADD COLUMN IF NOT EXISTS published_at TIMESTAMPTZ;

4. Run it locally to verify:

npm run db:migrate

The migrator will skip all already-applied files and only execute the new one.

Never modify an already-applied migration. Once a file is recorded in the migrations table it will not run again. Instead, create a new migration that corrects the previous one.

---

Seeds

What seeds are and why we need them

Seeds are SQL files that populate the database with data required to use or develop the application. Unlike migrations (which define structure), seeds define content.

There are two categories:

Category	Purpose	Examples
Reference data	Core content the app cannot function without	Roles, permissions, role-permission mappings, emotions, templates
Development data	Test records that speed up local development	Test users with known passwords, sample projects, episodes, scenes, characters, comments, tasks

Reference data seeds are safe to run in any environment. Development data seeds should only be used locally or on staging.

How the seeder works

The DatabaseSeeder class reads all .sql files from seeds/ sorted alphabetically and runs each one in its own BEGIN/COMMIT transaction.

Important: unlike the migrator, the seeder has no tracking table. Running seeds multiple times will re-execute every file. All seed files must be idempotent — use ON CONFLICT ... DO NOTHING or ON CONFLICT ... DO UPDATE so repeated runs do not cause errors.

-- Good — safe to run multiple times
INSERT INTO roles (id, name, name_i18n)
VALUES ('00000000-0000-0000-0000-000000000001', 'admin', '{"en":"Admin"}')
ON CONFLICT (id) DO NOTHING;

Naming convention

{number}_{description}.sql

The same zero-padded prefix scheme as migrations. Files are executed in alphabetical order, so dependencies between seeds must be reflected in the filename ordering.

Examples:

000_seed_roles.sql              ← roles must exist before users
000a_seed_test_users.sql        ← depends on roles
001_seed_permissions.sql
002_seed_role_permissions.sql   ← depends on roles and permissions

Adding a new seed file

1. Create a file in src/infrastructure/database/seeds/:

src/infrastructure/database/seeds/012_seed_locations.sql

2. Write idempotent INSERT statements:

INSERT INTO locations (id, name, description) VALUES
  ('...uuid...', 'Forest', 'Dense forest')
ON CONFLICT (id) DO NOTHING;

3. Apply it locally:

npm run db:seed-file -- 012_seed_locations.sql

Or re-run all seeds from scratch:

npm run db:reset

Production seeds

runProductionSeeds() runs a curated subset of seeds that are safe for production — only reference data that the application requires to function. It does not run development test data.

Currently applied in production:

• 000_seed_roles.sql
• 000a_seed_test_users.sql
• 002_seed_emotions.sql

Run via:

npm run db:seed-prod

---

npm commands

All database commands are defined in be-create-lab-studio/package.json:

Command	What it does
npm run db:migrate	Applies all pending migrations
npm run db:seed	Runs all seed files (development)
npm run db:seed-prod	Runs production-safe seed subset only
npm run db:seed-file -- <filename>	Runs a single seed file by name
npm run db:delete	Drops all tables in the schema
npm run db:reset	db:delete → db:migrate → db:seed (full local rebuild)

Typical local dev workflow

Fresh setup from scratch:

cd be-create-lab-studio
npm install
npm run db:reset    # wipe → migrate → seed
npm run dev

After pulling a branch that adds a new migration:

npm run db:migrate

Resetting to a clean state mid-development:

npm run db:reset

---

CI/CD — automatic migrations

The backend has two GitHub Actions workflows, one per deployment environment.

Staging (stage branch → deploy-stage.yml)

- name: Build
  run: npm run build

- name: Run migrations
  run: npm run db:migrate # ← migrations run automatically

- name: Restart backend
  run: pm2 restart be-create-lab-studio-stage --update-env

Every push to stage automatically applies any pending migrations before the server restarts. This means:

• New migration files merged into stage are applied without manual intervention.
• The migrator's tracking table ensures each file runs exactly once.
• If a migration fails the deploy step fails, and the server is not restarted — protecting the staging database from a partially-applied migration.

Production (main branch → deploy.yml)

- name: Build
  run: npm run build

# no db:migrate step

- name: Restart backend
  run: pm2 restart be-create-lab-studio-prod --update-env

Migrations do not run automatically on production. When a release requires schema changes, run them manually over SSH before or after deploying:

ssh -p 2222 services@87.204.212.184
cd /opt/github/be-create-labs/prod/be-create-lab-studio
npm run db:migrate

This gives the team full control over when production schema changes are applied and allows for a coordinated rollout.

Summary

Environment	Branch	Migrations	Triggered by
Staging	stage	Automatic on deploy	Push to stage
Production	main	Manual via SSH	Push to main + manual step