Create Flyway Migration

Create the file at: backend/src/main/resources/db/migration/V{N}__{description}.sql

3. Use the Standard Table Template

Every new table MUST include:

-- V{N}: {Description}
-- Tables: {list of tables created/modified}
-- RLS: yes
-- Author: @database agent

CREATE TABLE {table_name} (
    id BIGSERIAL PRIMARY KEY,
    tenant_id VARCHAR(255) NOT NULL,
    -- domain-specific columns here
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Multi-tenant isolation (REQUIRED for every tenant-scoped table)
ALTER TABLE {table_name} ENABLE ROW LEVEL SECURITY;
CREATE POLICY {table_name}_tenant_isolation ON {table_name}
    USING (tenant_id = current_setting('app.tenant_id', true));

-- Indexes
CREATE INDEX idx_{table_name}_tenant ON {table_name}(tenant_id);

4. Column Type Rules

5. Validate

• Verify RLS policy exists for every tenant-scoped table
• Verify NUMERIC(19,4) for monetary columns
• Verify TIMESTAMPTZ for all timestamp columns
• Verify naming follows snake_case convention

References

• Existing migrations: backend/src/main/resources/db/migration/
• Database agent: .github/agents/database.agent.md
• Schema docs: docs/technical/sql-schema.md