PostgreSQL Strategy

Purpose

PostgreSQL is the primary operational database for Algosure. It stores module-owned source facts, workflow state, tenant context, audit references, outbox records, read models, and operational configuration.

Responsibilities

Responsibility	Standard
Source facts	Stored in module-owned tables.
Workflow state	Stored by the Domain that owns the workflow.
Tenant context	Stored on tenant-scoped records.
Audit references	Stored on material tables and in audit data structures.
Outbox records	Stored transactionally with source changes where reliable event delivery is required.
Read models	Stored as projections where PostgreSQL is appropriate.

Positioning

flowchart TB
    Backend[Spring Boot Modulith]
    Postgres[(PostgreSQL)]
    Documents[(Object Storage)]
    Search[(Search Index)]
    Vector[(Vector Store)]
    Analytics[(Analytics Projections)]

    Backend --> Postgres
    Backend --> Documents
    Backend --> Search
    Backend --> Vector
    Postgres --> Analytics

Rules

• PostgreSQL is the default operational source of truth.
• Alternative stores are projections, document stores, search stores, vector memory, or analytics stores unless an architecture decision says otherwise.
• PostgreSQL structures must preserve DDD ownership and module boundaries.
• Customer data must be tenant-scoped and access-controlled at repository and API boundaries.
• Database design must support future module extraction by avoiding shared table ownership.

Non-Goals

This document does not choose hosting, extensions, sizing, replication, backup settings, connection pools, or physical deployment topology.