Copy page

Developer Guide

Partitioning & retention

Primebrick partitions append-only tables with pg_partman so retention becomes "drop a partition" instead of DELETE — no table lock on the hot path, and old data can live on slower storage.

What is partitioned today

Every {table}_audit audit-trail table is a native RANGE partition parent:

Code
PRIMARY KEY ("id", "changed_at")
) PARTITION BY RANGE ("changed_at");
SELECT partman.create_parent('schema.mytable_audit', 'changed_at', 'native', 'monthly');

The DDL generator (database-patch-to-sql.ts) emits this for every @AuditTrail() entity — premake = 4 keeps four future partitions ready. Reads always go through the parent table: all partitions are visible as one logical table, so audit-query.ts (and any findAll/findByPage) needs zero partition awareness.

Entity model

The entity declares a single logical @Key() — the composite DDL primary key (id, changed_at) is a DDL detail only:

Code
@Key()
id!: bigint;              // DDL PK is actually (id, changed_at)

@Column()
changed_at!: Date;        // partition key — normal @Column (Date → timestamptz)

See src/audit/audit-log-entity.ts for the canonical shape.

Partitioning a main table — rules

1. All UNIQUE/PK constraints must include the partition key (PG rule): UNIQUE (uuid) → UNIQUE (uuid, created_at); PRIMARY KEY (id) → PRIMARY KEY (id, created_at). The entity keeps @Key() id + @Unique() uuid — the generated constraint just widens.
2. Prefer an immutable key (created_at, not updated_at): updates that cross a partition boundary physically move the row. A mutable watermark key (last_seen_at) is allowed only when that movement IS the retention semantic — see "activity watermark" below.
3. Child tables cannot FK to a partitioned parent. Because the unique constraint must contain the partition key, child.parent_uuid → parent.uuid has no matching constraint. Integrity becomes application-level: the repository performs the cascade in one transaction (hardDelete per child + parent). This is the deliberate trade-off — document it in the entity header.
4. Non-key filters don't prune — acceptable for append-only logs at normal volume.
5. Write the PARTITION BY RANGE + create_parent DDL in the table's patch — there are no entity decorators for partitioning; it is a pure DDL concern.

First partitioned domain table: ai.ai_conversations

ai_conversations is partitioned RANGE (last_seen_at) — the activity watermark, not created_at. Semantics: "a conversation is dead N months after its last event", not "N months after creation".

• last_seen_at = max(created_at) of every child event (message, feedback). Child writes stamp it via a versioned update (the entity is auditable — the optimistic lock applies to bookkeeping writes too; Repository.touch was removed because it bypassed the version contract) inside the same transaction as the child insert — the clock can never drift from the rows that justify it.

• Invariant: conv.last_seen_at >= every child's updated_at. So a partition about to expire contains only conversations whose ENTIRE subtree is expired — retention removes each family's data in the same window ("quasi-atomic").

• ai_messages / ai_feedback are NOT partitioned — they die with their conversation, never independently.

• Retention job order matters: partition drop is DDL — it does NOT fire FK cascades and leaves no parent rows to JOIN. The maintenance runner must purge children FIRST, set-based on the expiring window:

  Code
  DELETE FROM ai.ai_feedback f
  USING ai.ai_conversations c
  WHERE f.conversation_uuid = c.uuid AND c.last_seen_at < $1;
  DELETE FROM ai.ai_messages m
  USING ai.ai_conversations c
  WHERE m.conversation_uuid = c.uuid AND c.last_seen_at < $1;
  -- then partman.run_maintenance_proc() drops the now-safe partition

  Do NOT purge children by bare created_at < cutoff — children of a still-active conversation can predate the cutoff; key the purge on parent membership (last_seen_at), never on child time alone.

Retention

Retention is configured on partman.part_config, per parent:

Code
UPDATE partman.part_config
SET retention = '6 months', retention_keep_table = false
WHERE parent_table = 'ai.ai_conversations';

Partitions older than the window are dropped at the next partman.run_maintenance_proc() run (retention_keep_table = false; true detaches instead, keeping them as plain tables for archival).

Scheduler: nothing in the platform calls run_maintenance_proc() today — pg_cron is not installed. The sanctioned path is a service-side scheduled job calling dal.partitionMaintenance(entities) — it owns the dedicated client internally (consumers never touch PoolClient).