12-database-sql

Database and SQL Rules

These rules apply when working on database schemas, queries, and migrations.

Technology

• Database: PostgreSQL (production), SQLite (development)
• ORM: None -- direct pg queries
• Migrations: Raw SQL files in src/db/migrations/
• RLS: Row Level Security (MANDATORY for target architecture)

Row Level Security (RLS)

CRITICAL: RLS is mandatory for ALL tables containing user data.

RLS Context Helpers

Every database operation MUST use the appropriate context helper:

Helper	Use Case
withUserContext	User-scoped operations
withAdminContext	Admin-scoped operations
withSystemContext	Background/webhook operations

Zero Tolerance

• No direct query calls without RLS context wrappers
• No bypassing RLS policies for convenience
• No using superuser credentials in application code

RLS Validation

After any schema change, validate RLS:

• User isolation: User A cannot see User B's data
• Admin access: Admin can see data within policy scope
• System context: Background tasks use system context

Migration Workflow

# 1. Create a new migration file
# Use the next sequential number: XXX_description.sql
touch src/db/migrations/008_add_new_table.sql

# 2. Write the migration SQL (CREATE TABLE, ALTER TABLE, etc.)
# 3. Add RLS policies if new table contains user data
# 4. Register the migration in src/db/migrations/index.ts
# 5. Test locally
npm run dev

# 6. Commit
git add src/db/migrations/
git commit -m "feat(db): description [ASP-XXX]"

Migration Rules

• Never use DROP TABLE without a backup strategy
• Never apply manual DDL in production outside of migrations
• Always add proper indexes for query patterns
• Add RLS policies for every new table with user data
• Schema changes REQUIRE System Architect approval
• Follow naming convention: XXX_description.sql (e.g., 008_add_audit_log.sql)