scoobydont-666/shared-claude-skills/database-design

□ Every table has a clear, singular purpose
□ Primary key defined (prefer INTEGER AUTOINCREMENT or UUID, not natural keys)
□ Foreign keys declared with ON DELETE behavior (CASCADE, SET NULL, RESTRICT)
□ NOT NULL on every column that should never be empty
□ DEFAULT values for columns with sensible defaults
□ No VARCHAR without length limit on user-facing input
□ Created_at and updated_at timestamps on every mutable table
□ No redundant data that can be derived from joins

□ Tables: plural snake_case (users, audit_events, context_chunks)
□ Columns: singular snake_case (user_id, created_at, token_count)
□ Foreign keys: {referenced_table_singular}_id (user_id, blob_hash)
□ Indexes: idx_{table}_{columns} (idx_users_email, idx_chunks_hash_index)
□ Constraints: {type}_{table}_{columns} (uq_aliases_alias_namespace, fk_chunks_hash)

□ Primary keys are automatically indexed
□ Foreign keys need explicit indexes (PostgreSQL does NOT auto-index FK columns)
□ Add indexes for WHERE, JOIN, ORDER BY columns in hot queries
□ Composite indexes: leftmost column must be the most selective
□ Covering indexes for read-heavy queries (include all SELECT columns)
□ Partial indexes for queries that filter on a constant (WHERE status = 'active')
□ Do NOT index columns with low cardinality (boolean, enum with 3 values)
□ Do NOT index columns that are rarely queried

□ Tenant ID column on every tenant-scoped table
□ Every query includes WHERE tenant_id = ? (no exceptions)
□ Composite unique constraints include tenant_id: UNIQUE(alias, tenant_id)
□ Row-level security (PostgreSQL) or application-level enforcement
□ Test: Tenant A cannot read Tenant B's data (explicit cross-tenant test)
□ Indexes include tenant_id as prefix for tenant-scoped queries

□ PII columns identified and documented
□ Encryption-at-rest for SSN, EIN, account numbers (application-level Fernet or PG pgcrypto)
□ No PII in indexes (encrypted columns cannot be indexed — use blind index pattern)
□ Retention policy defined: how long, auto-delete mechanism, audit trail for deletions
□ Audit log table: who accessed what, when, from where (append-only, no DELETE permission)
□ Backup encryption for database dumps containing PII

# Initialize
alembic init alembic
 
# Create migration from model changes
alembic revision --autogenerate -m "add user_roles table"
 
# Apply
alembic upgrade head
 
# Rollback
alembic downgrade -1

□ Never DROP COLUMN in production without a deprecation period
□ Add columns as NULLABLE first, backfill, then add NOT NULL constraint
□ Rename via: add new column → copy data → drop old column (not ALTER RENAME)
□ Large data migrations: batch in chunks (1000-10000 rows) to avoid lock contention
□ Always test migration AND rollback on a copy of production data
□ Migrations must be idempotent (safe to run twice)
□ No data-dependent migrations in the same transaction as schema changes