Skip to content

ADR 0002: PostgreSQL Is the Primary Company Data Store

Context

BooksPlus 3 is a desktop-first .NET and Avalonia application, but it still requires a durable, transactional data store for accounting records, source-document references, close history, audit events, configuration, and reporting.

Accounting data needs stronger guarantees than casual local files can provide. The data store must support reliable transactions, integrity constraints, backup and restore, auditability, and future reporting needs.

Decision

BooksPlus 3 will use PostgreSQL as the primary company data store.

Desktop packaging, installation, local service management, backup, restore, and encryption details may vary by deployment, but the default data model and development path optimize for PostgreSQL.

Rationale

PostgreSQL is selected because it provides:

  • strong transactional behavior;
  • rich integrity constraints;
  • expressive SQL for accounting and reporting;
  • mature indexing and query capabilities;
  • long-term headroom for audit-oriented and reporting-oriented workloads.

Using PostgreSQL also avoids designing around a weaker local-file storage model and then having to unwind those assumptions later.

Consequences

Positive consequences:

  • Accounting-critical data can rely on relational constraints and transactions.
  • Reporting and audit queries have a strong SQL foundation.
  • Future multi-user, support, or service-backed deployments have a clearer migration path.

Negative consequences:

  • Desktop installation and support are more complex than a single embedded file.
  • Backup, restore, and local service health need explicit product and sysadmin guidance.
  • Packaging must account for operating-system differences.

Implementation guidance

  • Treat PostgreSQL persistence models as representations of canonical BooksPlus truth, not as the truth itself.
  • Keep domain and application logic independent of PostgreSQL-specific APIs.
  • Use migrations deliberately and do not assume database neutrality.
  • Define backup, restore, encryption, and service-health behavior before production use.
  • Use PostgreSQL constraints to support, not replace, domain and application validation.
  • ../../contracts/kernel.contract.md
  • 0013-encrypted-company-data.md
  • ../../contracts/security/backup-security-contract.md