Database Specification Foundation

Purpose

This section defines implementation-level database specification standards for Algosure. The standards will guide future schema specifications, migrations, table designs, indexing, audit, tenant isolation, outbox records, and read models.

This is not a table catalogue. It does not create final SQL, schemas, tables, columns, migrations, or entity mappings.

Scope

Standard	Purpose
Database Specification Standard	Required contents of future database specs.
PostgreSQL Strategy	PostgreSQL position as the primary operational database.
Schema Ownership Standard	Domain and module ownership of persistence areas.
Table Design Standard	Rules for operational table design.
Primary Key Standard	Identifier and primary key guidance.
Foreign Key Standard	Reference and relationship guidance.
Indexing Standard	Index design and review rules.
Audit Column Standard	Common audit and traceability columns.
Tenant Column Standard	TenantId and OrganizationId rules.
Migration Strategy	Change management and release rules.
Liquibase Strategy	Liquibase use and ownership model.
Outbox Table Standard	Future-safe event reliability table rules.
Read Model Standard	Query projection and read model rules.
Database Testing Standard	Verification expectations.

Architecture Alignment

flowchart TB
    Module[Spring Boot Modulith Module]
    Schema[Module-Owned Schema or Table Set]
    Tables[Domain-Owned Source Tables]
    Outbox[Outbox Tables]
    ReadModels[Read Models]
    Postgres[(PostgreSQL)]

    Module --> Schema
    Schema --> Tables
    Schema --> Outbox
    Schema --> ReadModels
    Tables --> Postgres
    Outbox --> Postgres
    ReadModels --> Postgres

Core Rules

• PostgreSQL is the primary operational database.
• Domains own source facts and persistence ownership.
• Modules do not read or write another module's owned tables directly.
• Every tenant-scoped table includes tenant and organization context where applicable.
• Material tables include audit columns and optimistic locking where mutable.
• Soft delete is explicit and governed; it is not a substitute for retention policy.
• Migrations follow module ownership.
• Liquibase is the preferred migration strategy for future implementation.
• Outbox tables support reliable future event delivery.
• Read models are projections and must not become source-of-truth owners.