Database Conventions

General

• PostgreSQL 16+
• One schema prefix per module (e.g., people_, groups_, events_)
• UUIDs (v7) for all primary keys
• created_at and updated_at timestamps on every table
• Soft deletes only when business requires it (prefer hard delete)

Naming

• Tables: {module}_{entity} in snake_case plural (people_members, groups_groups)
• Columns: snake_case (first_name, created_at)
• Foreign keys: {referenced_table_singular}_id (group_id, member_id)
• Indexes: idx_{table}_{columns} (idx_people_members_email)
• Unique constraints: uniq_{table}_{columns}

Types

• IDs: uuid (UUID v7 for time-ordering)
• Timestamps: timestamptz (always with timezone, stored as UTC)
• Timezones: varchar(64) for IANA timezone identifiers (e.g., Europe/Zurich)
• Money: bigint (store in cents/smallest unit)
• Enums: varchar with CHECK constraint (not PostgreSQL enum type)
• JSON: jsonb for flexible structured data
• Hierarchies: ltree for tree structures (org chart, group hierarchy)

Multi-tenancy

• Every table has a tenant_id column
• Composite indexes always include tenant_id first
• Row-level security (RLS) as defense-in-depth
• Application-level tenant scoping is the primary mechanism

Migrations

• Doctrine Migrations for schema changes
• One migration per logical change
• Migrations must be reversible (include down())
• Never modify a migration that’s been deployed
• Test migrations on a clean database

Indexes

• Always index foreign keys
• Always index tenant_id (usually composite with other columns)
• Use partial indexes where appropriate (WHERE deleted_at IS NULL)
• Use GIN indexes for JSONB columns that are queried
• Profile before adding indexes — don’t over-index

Cross-module

• No foreign keys between modules
• Use UUID references (eventual consistency)
• Cross-module data access via contract interfaces or events