/
Database

Database

1. Database

Refreshed against dev-next. The primary application database is PostgreSQL.

1.1. Current data architecture

  • Default Laravel connection: pgsql

  • Primary schema/database for the application: PostgreSQL

  • Additional named connections exist for CCC and selected integrations

  • Some MariaDB/MySQL connections remain for legacy or peripheral systems; do not treat MariaDB as the primary app database

1.2. Coding guidelines

  1. Prefer Eloquent models and relationships

  2. Keep business logic in PHP, not in database-specific procedures or complex raw SQL

  3. Avoid driver-specific queries unless there is a clear reviewed need

  4. Never use obsolete PHP mysql APIs

  5. Use DB:: sparingly and only when Eloquent cannot express the query cleanly

1.3. Schema conventions

  • Table names are singular (contact, not contacts)

  • Foreign keys use tablename_id and are indexed

  • Default foreign-key delete behavior is restrict unless explicitly approved otherwise

  • Structured data uses $table->json(...) (JSONB on PostgreSQL)

  • Do not use DB ENUM types; prefer string columns with validation or check constraints

1.4. Migrations

New migrations should:

  • Live in global database/migrations/

  • Extend Database\Migrations\BaseMigration

  • Set $migrationScope to either database or system

  • Avoid mixing database changes and system/filesystem changes in one migration

  • Avoid re-declaring generic columns already provided by BaseMigration helpers such as id/timestamps/soft deletes when using the generic table helpers

See Migrations for details.

1.5. Models

  • Module models live in Modules/<Module>/Entities

  • Models used by Base MVC typically extend App\BaseModel

  • Relation select sources should expose html_list(): array returning id => label

1.6. Related pages