Persistence Architecture¶

Status: ACCEPTED — I1-B3 Technology Decision Gate
Accepted: 2026-09-21
Depends on: Foundation v1.0, I1-B1, I1-B2

Baseline¶

Touriffique initially uses one PostgreSQL 18 database/cluster for authoritative relational platform state.

The accepted stack is:

PostgreSQL 18
├── PostGIS 3.6.x
├── TypeORM 1.x
├── @nestjs/typeorm
└── pg

TypeORM uses Data Mapper/repository style. Active Record/BaseEntity is not the platform persistence pattern.

Engine schema ownership¶

Each engine owns a logical PostgreSQL schema:

Engine	Schema
Platform	`platform`
Capability	`capability`
Commerce	`commerce`
Site	`site`
CRM	`crm`
Marketing	`marketing`
Channel & Advertising	`channel`
Finance	`finance`
Integration	`integration`
Operations	`operations`
Internationalization	`i18n`

This is an ownership mechanism, not a microservice/database-per-engine architecture.

An engine's application layer does not directly adopt another engine's repositories because the tables are physically reachable. Cross-engine application access follows owned contracts.

Deliberate cross-schema database constraints are allowed for foundational integrity. The canonical example is tenant-owned rows referencing the Platform-owned Tenant identifier.

Tenant row model¶

Tenant-owned authoritative rows carry an explicit tenant_id.

Illustrative shape:

tenant_id uuid NOT NULL

The presence of tenant_id is not itself authorization. It participates in both application scoping and RLS policy enforcement.

Platform-global/reference data is not forced to carry a fake tenant identifier.

Isolation layers¶

Tenant isolation has two independent layers.

Application layer¶

The server establishes a trusted Tenant Context from authenticated membership, hostname/site resolution, API client or another approved authority.

Repositories/query services receive tenant context through trusted application infrastructure rather than arbitrary client input.

PostgreSQL RLS¶

RLS provides defense in depth for tenant-owned tables.

A tenant-scoped unit of work establishes transaction-local tenant context before accessing RLS-protected state.

Conceptually:

SELECT set_config('app.current_tenant_id', :tenant_id, true);

or the equivalent SET LOCAL behavior.

RLS policies then compare the trusted context to row tenant_id.

The exact session-variable key/policy helper implementation may be refined during implementation, but the following are frozen:

context is trusted/server-established;
tenant-scoped runtime access is RLS-constrained;
pooled connections must not leak tenant context between requests;
transaction-local context is preferred for that reason;
RLS never substitutes for application permission/entitlement checks.

Privilege separation¶

Normal tenant API/worker workloads must not use a database superuser or migration credential.

At minimum, persistence access distinguishes:

tenant/runtime access — normal application traffic, RLS enforced;
privileged administrative/platform access — explicit cross-tenant/system operations;
migration/DDL access — schema/extension/policy changes.

Implementation may use separate roles for (2) and (3), and should do so where operationally practical. They must not collapse into the ordinary tenant runtime credential.

Transactions¶

Transaction-critical operations use an explicit TypeORM transaction boundary and the transaction-bound EntityManager/query runner.

Code inside a transaction does not silently fall back to a global/non-transactional manager for writes that belong to that unit of work.

Tenant DB context required by RLS is established in the same transaction/unit of work.

Migrations¶

Versioned migrations are the sole production schema-change authority.

Required rules¶

migrations are committed with source code;
synchronize: true is forbidden in production/staging and any persistent shared environment;
PostgreSQL-specific migration SQL is allowed and expected where abstractions are insufficient;
migrations own RLS policies, PostGIS extension setup, indexes, constraints and required database functions;
destructive changes require explicit expand/backfill/contract planning when live data is involved;
deployments must be able to determine the exact applied migration version.

Disposable developer/test databases may use convenience synchronization only when explicitly isolated from persistent/shared data, but migration-based test setup remains preferred for parity.

IDs¶

Major platform/domain aggregates use UUIDv7.

Database migrations should support UUIDv7 defaults where appropriate. Application code may create UUIDv7 identifiers before persistence when domain workflows require an identifier earlier.

Identifiers remain opaque public references; knowing an ID never grants authorization.

JSONB¶

JSONB supports controlled extensibility, not arbitrary persistence.

Appropriate examples include:

versioned vertical-specific offering/resource attributes;
validated tenant custom-field values;
provider payload snapshots/metadata where the original structured document matters.

Each authoritative JSONB shape requires an owning schema/version/validation contract.

Canonical transaction state such as payment amount, booking status, tenant identity, inventory quantity or authoritative timestamps remains strongly modeled.

Indexes target demonstrated JSONB query paths; broad GIN indexing is not automatic.

PostGIS¶

PostGIS 3.6.x is part of the baseline PostgreSQL deployment.

Coordinate baseline¶

WGS84 / SRID 4326 is the default interchange coordinate system.

Point locations¶

For real-world point locations and distance/proximity search, the default canonical type is:

geography(Point, 4326)

Representative uses:

hotels/resorts/villas;
restaurants/beach clubs;
marinas;
vehicle pickup/return points;
excursion meeting/departure points;
operational locations/resources with a physical position.

The API may expose latitude/longitude and/or GeoJSON representations, but standalone latitude/longitude columns are not the canonical persistence representation when a spatial column exists.

Areas and boundaries¶

Service areas, geofences, operational regions and physical boundaries use an appropriate PostGIS spatial type.

geometry(..., 4326) is the default starting point for topology/boundary operations; a projected SRID or geography type may be selected when the operation and accuracy requirements justify it.

Query/index policy¶

Use PostGIS spatial predicates/functions for proximity/intersection/containment behavior instead of duplicating geodesic formulas in application code.

GiST indexes are added where the workload/query plan benefits from them.

Touriffique does not enable PostGIS Raster, Topology, Tiger Geocoder or other optional extensions until a concrete requirement exists.

Geocoding/address lookup is an Integration concern and is not provided merely by enabling PostGIS.

Environment parity¶

Development, CI, staging and production must target PostgreSQL/PostGIS versions compatible with this baseline.

CI/integration tests exercising migrations, RLS or spatial behavior must use a real PostgreSQL + PostGIS instance rather than replacing those semantics with SQLite/in-memory substitutes.

Deferred decisions¶

I1-B3 does not select:

managed PostgreSQL hosting/provider;
connection-pool/proxy provider;
backup/PITR vendor/configuration;
full-text/external search implementation beyond PostgreSQL capabilities;
geocoding/maps provider;
database observability provider.

Those are handled by later gates.