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

Add the next numbered migration. Never edit a migration included in a released version.
Make additive changes where possible and preserve existing rows.
Add indexes for new acquisition, lock or query paths.
Update JPA entities and repositories to match the migrated schema.
Prove a fresh PostgreSQL database reaches the new version.
Prove upgrades from every supported prior schema version.
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 added since 1.0

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, credentials or PII
Project task groups	`project_member_task_groups` (V19)	Candidate groups a member holds in one project
AI providers	`ai_providers` (V22)	Provider keys encrypted with `ABADA_ENCRYPTION_KEY`; served to worker principals only
Provider models, price catalog	`ai_provider_models`, `model_catalog_prices`, `ai_refresh_state` (V39)	Model lists read from each provider; insert-only catalog prices; a lease per background refresh, claimed and finished in their own transactions with the remote call between them
Execution tokens	`process_tokens` and `token_id` on tasks, external tasks, jobs and subscriptions (V23); per-token `loop_counts` (V25)	Joins count token ids; loop counters survive restarts
Incidents	`incidents` (V24)	One open incident stops one token; operators retry or cancel
Boundaries and service levels	`jobs.job_kind`/`boundary_id`, `tasks.due_at`/`escalated_at`, `external_tasks.model_override`/`deferrals` (V26)	Timeout and SLA jobs are acquired with `SKIP LOCKED` like timers

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.

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.