Database and migration changes

Flyway owns the production schema under engine/src/main/resources/db/migration/. Hibernate validates the result; it does not generate production tables.

Migration workflow

1. Add the next numbered migration. Never edit a migration included in a released version.
2. Make additive changes where possible and preserve existing rows.
3. Add indexes for new acquisition, lock or query paths.
4. Update JPA entities and repositories to match the migrated schema.
5. Prove a fresh PostgreSQL database reaches the new version.
6. Prove upgrades from every supported prior schema version.
7. Document backup, ordering, rollback and downgrade limitations.

Choose concurrency deliberately

Mechanism	Use it for	Example
Pessimistic row lock	One resource must have a single transition winner	Task completion or subscription consumption
Optimistic version	Detect a stale aggregate write	Process-instance updates outside a fully locked path
FOR UPDATE SKIP LOCKED	Replicas claim independent available work	Timers, external tasks and outbox rows
Unique constraint/upsert	Reserve a durable identity	Idempotency keys

Keep lock ordering stable and transactions short. A query that works under H2 is not evidence that it behaves correctly under PostgreSQL contention.

flowchart TD
    Migration[New Flyway migration]
    Fresh[Fresh PostgreSQL test]
    Upgrade[Prior schema upgrade tests]
    Concurrent[Two-context concurrency test]
    Recovery[Rollback and restart test]

    Migration --> Fresh
    Migration --> Upgrade
    Migration --> Concurrent
    Migration --> Recovery

Schemas that grew with the agentic track

Newer capabilities are ordinary additive migrations with the same rules:

Capability	Migration surface	Note
Schema dispatch	process_definitions.schema_type (BPMN_XML/APL_NATIVE, V10)	Every published schema version v1–v9 upgrades cleanly; the column is NOT NULL and CHECK-constrained
Project file tree	project_folders/project_resources + folder links (V13)	Project-scoped documents with optimistic revisions; deleting a folder archives contents
Insight Loop	insight_execution_facts, insight_observation_windows, insight_findings, insight_proposals, insight_approval_policies, insight_proposal_reviews, insight_worker_lease	Facts commit with their runtime command; uniqueness is per visit_id; the analyzer lease must stay cluster-safe
Agent attempt metadata	agent_metadata JSON on the external-task row	Same JSON travels in history event details; never prompts, tokens or PII

Fact rows are small, indexed, bounded writes inside terminal commands — do not make analyzer aggregation part of the command transaction, and do not widen facts with process variables or prompts.

Destructive migrations need an operational contract

Do not delete or rewrite production data without explicit backup, restore, rollback and upgrade guidance. A downgrade normally requires restoring the pre-upgrade backup.

Minimum verification

Persistence changes require PostgreSQL Testcontainers, fresh migration, supported upgrade paths, rollback behavior and restart recovery. Acquisition or locking changes also require at least two concurrent engine contexts and a single-winner/lossless-progress assertion.