Database Conventions

Naming Standards

• Schema names: lowercase, no underscores (processing, not processing_pipeline)
• Table names: lowercase with underscores (processing_jobs, not ProcessingJobs)
• Foreign keys: Always explicit (entity_id, not entity)
• View names: {purpose}_{entity}_{suffix} format
  • Purpose: what the view is used for (e.g., search, report, summary, detail)
  • Entity: main entity/table the view is based on (e.g., entities, searches, users)
  • Suffix: additional context (e.g., with_data, by_status, recent)
  • Examples: search_entities_with_data, report_entities_by_status, summary_searches_recent

Row Level Security Requirements

• Public Schema: All tables must have RLS enabled
• Content Schema: Entity-level access control
• Processing/System Schemas: RLS disabled, schema-level access control

Cross-Schema Relationships

Cross-schema foreign keys are required and encouraged. Reference authoritative sources rather than duplicating data.