DIT Digital Services Guidelines

Appearance

Database Decisions ​

Database design must prioritize data integrity, consistency, performance, and correctness under concurrency.
The database is the final authority on truth. Application logic must reinforce, not replace, database guarantees.

Uniqueness ​

Columns intended to store unique values must always be protected by a database-level unique index.

Application-side uniqueness checks such as “check-then-act” are not sufficient and are prone to race conditions.
Without a unique index, duplicate or invalid data may enter the system even if application logic attempts to prevent it.

Always enforce uniqueness at the database layer.

Versioning (Optimistic Concurrency Support) ​

Versioning records is strongly recommended to support Optimistic Concurrency Control (OCC).
A version column allows the system to detect conflicting updates without locking resources.

Requirements ​

  • The version column must be atomically incremented with each update.
  • Prefer ORM-native versioning when available (e.g., lock_version).
  • Use versioning for workflow transitions, multi-step updates, and high-concurrency areas.

Foreign Keys ​

Foreign key constraints must be enabled for all relational references unless there is a clearly documented architectural reason not to.

Foreign keys ensure:

  • Referential integrity
  • Prevention of orphaned records
  • Predictable cascaded behavior

If foreign keys are intentionally omitted, the decision must be formally justified.

Not Null Constraints ​

Columns that must always contain values should be explicitly marked as NOT NULL.

Relylying solely on application logic to enforce required data fields is insufficient and leads to subtle data integrity issues.

Primary Keys ​

  • Use surrogate primary keys unless a natural key is absolutely stable and immutable.
  • If UUIDs are used for primary keys, they must be UUID v7.
    • UUIDv7 provides strong ordering characteristics, making indexing and ordering efficient.
  • Primary keys must never be modified after creation.

Indexing ​

Indexes must be created thoughtfully.

Guidelines ​

  • Index columns frequently used in WHERE, JOIN, or ORDER BY clauses.
  • Avoid indexing low-cardinality columns unless needed.
  • Composite index order matters — place the most selective column first.
  • Ensure indexes match real query access patterns, not assumed ones.

Transactions ​

All multi-step or multi-record operations must be wrapped in atomic database transactions.

Transactions guarantee:

  • All-or-nothing behavior
  • Correct sequencing under concurrency
  • Prevention of partial updates

Never perform grouped record updates without transactional protection.

Soft Deletes (Use With Caution) ​

Soft deletes (deleted_at) introduce additional complexity:

  • Queries must filter out soft-deleted records
  • Unique constraints must account for soft-deleted rows
  • Relationships become harder to validate

Use soft deletes only when audit or retention requirements make them necessary.

Migrations and Schema Evolution ​

Requirements ​

  • Schema changes must be applied using version-controlled migrations.
  • Migrations must be backward-compatible when using rolling deployments.
  • Avoid destructive changes without careful planning.

Zero-Downtime Migration Principles ​

  • Add new columns before writing to them
  • Deploy application changes before enforcing constraints
  • Remove unused columns only after the application no longer references them

Data Types ​

Choose the most appropriate and explicit data type:

  • Use integer or bigint for counters and IDs
  • Use decimal for monetary amounts
  • Use timestamp with time zone for dates
  • Use jsonb sparingly and only when flexibility is required

Avoid ambiguous types such as text for structured fields unless justified.

Auditing ​

For traceability, implement:

  • created_at / updated_at timestamps
  • created_by / updated_by when applicable
  • audit logs for critical changes

Auditing improves accountability and simplifies debugging.

Summary ​

Database correctness and reliability depend on:

  • Unique constraints
  • Foreign keys
  • Not-null constraints
  • Proper indexing
  • Strong, explicit data types
  • Atomic transactions
  • Record versioning for concurrency
  • UUIDv7 for primary-key UUIDs
  • Careful schema evolution and migration discipline

Strong database decisions ensure long-term maintainability and resilience across DIT systems.