lcbp3/.agents/workflows/schema-change.md

description

description
Manage database schema changes following ADR-009 (no migrations, modify SQL directly)

Schema Change Workflow

Use this workflow when modifying database schema for LCBP3-DMS. Follows specs/06-Decision-Records/ADR-009-database-strategy.md — NO TypeORM migrations.

Pre-Change Checklist

• Change is required by a spec in specs/01-Requirements/
• Existing data impact has been assessed
• No SQL triggers are being added (business logic in NestJS only)

Steps

1. Read current schema — load the full schema file:

specs/03-Data-and-Storage/lcbp3-v1.7.0-schema.sql

2. Read data dictionary — understand current field definitions:

specs/03-Data-and-Storage/03-01-data-dictionary.md

// turbo 3. Identify impact scope — determine which tables, columns, indexes, or constraints are affected. List:

• Tables being modified/created
• Columns being added/renamed/dropped
• Foreign key relationships affected
• Indexes being added/modified
• Seed data impact (if any)

4. Modify schema SQL — edit specs/03-Data-and-Storage/lcbp3-v1.7.0-schema.sql:
   • Add/modify table definitions
   • Maintain consistent formatting (uppercase SQL keywords, lowercase identifiers)
   • Add inline comments for new columns explaining purpose
   • Ensure DEFAULT values and NOT NULL constraints are correct
   • Add version column with @VersionColumn() marker comment if optimistic locking is needed

Caution

NEVER use SQL Triggers. All business logic must live in NestJS services.

5. Update data dictionary — edit specs/03-Data-and-Storage/03-01-data-dictionary.md:

   • Add new tables/columns with descriptions
   • Update data types and constraints
   • Document business rules for new fields
   • Add enum value definitions if applicable

6. Update seed data (if applicable):

   • specs/03-Data-and-Storage/lcbp3-v1.7.0-seed-basic.sql — for reference/lookup data
   • specs/03-Data-and-Storage/lcbp3-v1.7.0-seed-permissions.sql — for new CASL permissions

7. Update TypeORM entity — modify corresponding backend/src/modules/<module>/entities/*.entity.ts:

   • Map ONLY columns defined in schema SQL
   • Use correct TypeORM decorators (@Column, @PrimaryGeneratedColumn, @ManyToOne, etc.)
   • Add @VersionColumn() if optimistic locking is needed

8. Update DTOs — if new columns are exposed via API:

   • Add fields to create-*.dto.ts and/or update-*.dto.ts
   • Add class-validator decorators for all new fields
   • Never use any type

// turbo 9. Run type check — verify no TypeScript errors:

cd backend && npx tsc --noEmit

10. Generate SQL diff — create a summary of changes for the user to apply manually:

-- Schema Change Summary
-- Date: <current date>
-- Feature: <feature name>
-- Tables affected: <list>
--
-- ⚠️ Apply this SQL to the live database manually:

ALTER TABLE ...;
-- or
CREATE TABLE ...;

11. Notify user — present the SQL diff and remind them:
    • Apply the SQL change to the live database manually
    • Verify the change doesn't break existing data
    • Run pnpm test after applying to confirm entity mappings work

Common Patterns

Change Type	Template
Add column	ALTER TABLE \table` ADD COLUMN `col` TYPE DEFAULT value;`
Add table	Full CREATE TABLE with constraints and indexes
Add index	CREATE INDEX \idx_table_col` ON `table` (`col`);`
Add FK	ALTER TABLE \child` ADD CONSTRAINT ... FOREIGN KEY ...`
Add enum	Add to data dictionary + ENUM('val1','val2') in column def

On Error

• If schema SQL has syntax errors → fix and re-validate with tsc --noEmit
• If entity mapping doesn't match schema → compare column-by-column against SQL
• If seed data conflicts → check unique constraints and foreign keys