Inflow.aiDocs

Optional technical reference. This material is for developers, integration specialists and platform administrators. For the consultant lesson, read the practical guide.

Preserved from source baseline d4587c7c0e073e58c2d430342f65ac55dd701bf4; source/static examples retain their stated scope. They are not new runtime verification or required consultant setup steps.

Current control reference — PostgreSQL search path

The platform pool pins every PostgreSQL connection to public, pg_temp (SCRUM-3170), so inflow_sync is not on the path and cannot shadow a public table. Role, database, $user, and URL currentSchema or options defaults are overridden. Startup refuses with DATABASE_SEARCH_PATH_UNPINNED when the pin is absent. Refer to any inflow_sync object by its schema-qualified name.

Current control reference — database schema readiness

As of source commit 69e7ac912e, DatabaseSchemaReadinessVerifier requires exactly one clean schema-state row at version 450 or later. A clean row below 450 produces DATABASE_SCHEMA_NOT_READY: MIGRATION_REQUIRED_450_CURRENT_<version>; a dirty row produces DATABASE_SCHEMA_NOT_READY: MIGRATION_DIRTY_AT_<version> because dirty-state rejection comes first. A missing or unreadable journal produces DATABASE_SCHEMA_NOT_READY: MIGRATION_STATE_UNAVAILABLE, and a result other than exactly one journal row produces DATABASE_SCHEMA_NOT_READY: MIGRATION_STATE_INVALID. The verifier reads public.inflow_schema_migration_state() (required floor and validation).

The current PostgreSQL directory ends at migration 000450, Home workspace configuration, matching the verifier floor. The migration-version check compares that highest migration with the Java constant (check). This number is specific to this source snapshot: inspect the verifier and active migration directory from the exact checkout rather than copying 450 forward. Current source also composes a Home configuration service, API and native authorization owner, but this migration reference does not establish that those paths are deployed or qualified, that the Home operator UI exists, or that a target database has been upgraded. This source reference does not prove that a particular database is ready.

A clean journal at or above a release's verified floor is not proof that every physical schema object is present; keep the migration journal and physical-schema checks separate, and never edit the journal to clear a readiness refusal.

Database Migrations Manual

Practical guide and worked examples

Identify the database before choosing a command

The canonical PostgreSQL migration source is migrations/postgres. The wrapper's default -Database postgres selects that directory, not a database name. The actual connection comes from -DatabaseUrl, then MIGRATION_DATABASE_URL, then DATABASE_URL, then the explicit local development default. Keep connection secrets out of command transcripts and reports. The same checkout can reach the wrong database if its lane/project/environment is wrong.

The wrapper uses docker compose exec ... java-dev, so the selected lane's java-dev service must already be running. A working PostgreSQL container does not imply that the migration client is available. Schema readiness, physical data-model publication and deployment-release admission are separate concerns; do not replace a migration receipt with an application page load.

Question Inspect Safe interpretation
Which source history? Actual migrations/<Database> and checkout Historical/archive directories are not the active source list.
Which target? Owned Compose project and privately selected URL Never infer target identity from a successful TCP connection.
Is history clean? Migration version and dirty state A journal number is metadata, not proof every required object exists.
Is anything pending? Current source history versus clean journal “No change” is accepted only at the expected clean latest source state.
Is rollback allowed? Exact down plan and forward-only markers A supported command name is not authorization to undo retained data.
Is API readiness satisfied? Current readiness verifier and complete migrations Do not copy an evergreen schema number into a guide or force it into the journal.

These procedures are for admitted isolated development or owner-approved deployment work. This documentation program does not start services, execute SQL, force migration metadata, drop a database or replay a held validation route.

The examples below use synthetic teaching data. They are grounded in the cited source contracts; this documentation change did not execute these operations against a running Inflow deployment. Use normal authenticated sessions and the actual returned identifiers, revisions and versions from an authorized environment.

Example ROOT-DB-01 — Inspect the current journal in an admitted lane

Scenario and inputs. Nadia owns a disposable development lane and has authority to inspect its database metadata. She has verified the Compose project and privately selected connection target. The immediate goal is to read applied version/dirty state, not to change schema or make the API appear ready by altering a version marker.

Procedure. Confirm the selected lane and running java-dev service, then use the repository wrapper's read command:

.\scripts\migrate.ps1 -Command version

Read the journal result and compare it with the canonical current migration files and the actual readiness verifier in the same checkout. Record only the nonsecret target identity, checkout, version and dirty status. If a URL is selected through the environment, inspect that selection privately rather than printing its password into a transcript.

Expected result. A readable clean journal establishes the reported migration metadata for that exact target. The result is not a schema-diff check, a full application smoke test or evidence that another database has the same state. The wrapper's version command does not perform an up migration.

Failure and recovery. An unreadable journal, missing client container or dirty state requires diagnosis at that boundary. Do not interpret it as an empty current database or run force to invent a clean result. Keep the target and original output identifiable and ask the legitimate database owner to resolve access or the failed migration before any write.

Sources. migrate.ps1:180, migrate.ps1:46, DatabaseSchemaReadinessVerifier.java:1.

Example ROOT-DB-02 — Prepare the migration client before applying the ordered history

Scenario and inputs. A fresh approved local development workspace is being initialized, and Nadia has already confirmed that it contains no data/evidence subject to a preservation hold. PostgreSQL alone is running. The migration wrapper will execute inside java-dev, so a client-service failure must be distinguished from a failed SQL migration.

Procedure. Follow the repository's lane-specific dependency startup, including java-dev, and inspect that service before invoking the wrapper. After privately confirming the intended database connection, the normal forward command is:

.\scripts\migrate.ps1 -Command up

Keep the ordered canonical source history intact. Retain the wrapper exit code, resulting clean journal and any original failure. Start or smoke-test the API only through the separately authorized full dependency boundary, with healthy plugin runner and required services.

Expected result. The wrapper checks the PostgreSQL journal before advancing and stops on dirty state. Successful application advances the intended target's migration history; an accepted clean “already newest” result means that target matched the newest active source file under the documented check. It does not certify runtime execution or a physical data-model deployment.

Failure and recovery. If exec java-dev fails, correct that owned client-service prerequisite instead of editing project versions or switching to another lane's container. If SQL fails, preserve the failure and current database state. Do not edit an applied file, drop a database with needed data or manufacture CI/fresh-database identities to reuse a restricted validation route.

Sources. migrate.ps1:67, migrate.ps1:98, AGENTS.md:133.

Example ROOT-DB-03 — Diagnose “no change” and a readiness mismatch

Scenario and inputs. The migration tool reported no pending change, but the API readiness check is refusing the schema. Nadia must determine whether source, target, journal or actual schema is mismatched. The version in a historical upgrade guide can be correct for that old release while being insufficient for the current checkout.

Procedure. Retain the exact API refusal and privately identify its database target. Use the same lane's migration journal read, then compare the result with the active *.up.sql history and DatabaseSchemaReadinessVerifier. Check dirty state and whether the checkout is the candidate whose API is actually served. The wrapper's no-change handling compares a clean reported version with the newest active source file; use that comparison rather than assuming any zero exit from an underlying client is sufficient.

Expected result. The reader can distinguish an already-current clean target from a stale/misrouted connection, dirty history or different served binary. The current schema threshold comes from the owning source and complete ordered history; a guide need not hardcode a permanently valid integer. Source inspection alone does not establish which binary a running API serves.

Failure and recovery. Never change the journal number just to satisfy readiness, suppress the verifier, or call an unrelated database a fresh target. If the intended migration is not represented, resolve source synchronization and the database owner's repair plan. Preserve the original refusal and avoid interpreting an infrastructure/database error as a valid empty result.

Sources. migrate.ps1:155, DatabaseSchemaReadinessVerifier.java:1, spec-077-upgrade.md:1.

Example ROOT-DB-04 — Plan a forward repair instead of casually reversing retained history

Scenario and inputs. A developer finds a schema issue after a migration has been applied in an environment with retained records. A down file exists, but one of the requested steps can be marked forward-only. The correct plan must respect actual data compatibility, the complete applied history and the authorized database owner's rollback decision.

Procedure. Inspect the exact applied migration and dependent objects, then the requested down sequence without executing it. The wrapper requires an explicit positive step count, a readable clean journal and a canonical source plan starting at the applied version. It refuses a plan containing -- migration-policy: forward-only. For a normal forward correction, add a new migration with the next legitimate source identity and validate version uniqueness through the repository checker; preserve the applied original file and its evidence.

Expected result. A forward-only refusal occurs before the wrapper changes the journal through that down operation. A compatible application-binary rollback may retain additive schema when the owner has admitted that plan. A new forward migration records the correction explicitly rather than rewriting what already ran. This is distinct from a generic command's existence in the wrapper's ValidateSet.

Failure and recovery. Do not remove the marker, change an applied SQL file, run force/drop or reuse a shared database to make a local test green. Those commands are consequential operations requiring their own exact authority and recovery plan. If compatibility is uncertain, retain the current evidence/data and investigate the distinct source/target/dependency hypotheses before choosing another write.

Sources. migrate.ps1:159, migrate.ps1:174, check-migration-versions.mjs:1.

Document status: Current migration operations reference. Verified against origin/master on 2026-08-14. Do not copy a numeric "latest migration" into prose; the ordered migrations/postgres/*.up.sql files are authoritative for each candidate.

Inflow Platform uses golang-migrate for database schema migrations.

The migration tool is installed in the Java development container, so developers do not need to install it directly on Windows.

Why golang-migrate

golang-migrate is a free, open-source migration runner. It works as a CLI and can be used from any application stack, including this Java platform.

We use it to:

  • Version database schema changes.
  • Apply migrations consistently in local, test, staging, and production environments.
  • Support explicit rollback files.
  • Avoid coupling the core platform to one database vendor.

Folder Layout

Migrations are grouped by database dialect:

migrations/
  postgres/
    000001_create_identity_tables.up.sql
    000001_create_identity_tables.down.sql

This is intentional. The service layer remains database-agnostic through repository ports, but real SQL differs between PostgreSQL, MySQL, SQL Server, Oracle, and other engines.

Future adapters should add their own migration folder:

migrations/mysql/
migrations/sqlserver/
migrations/oracle/

Local Development Database

The Docker Compose environment includes a PostgreSQL development database:

host: postgres-dev
port: 5432
database: inflow
migration owner: inflow / inflow
runtime login: inflow_api_dev / inflow-runtime-dev-only (local development only)

The migration-owner connection URL inside the container is:

postgres://inflow:inflow@postgres-dev:5432/inflow?sslmode=disable

The long-lived API does not use that owner credential. Compose provisions a non-login inflow_runtime capability role and the separate inflow_api_dev login. Migration 91 grants ordinary runtime DML while limiting audit_events and rule_execution_records to SELECT and INSERT. It also revokes the database-wide PostgreSQL PUBLIC default for temporary table creation. This affects every login on that database and prevents an API session from shadowing the unqualified evidence-table names with disposable temporary relations. The down migration deliberately restores PostgreSQL's default PUBLIC TEMPORARY grant and database CREATE for inflow_runtime, which keeps the pre-v89 replica manager's repeated CREATE SCHEMA IF NOT EXISTS call usable during an application rollback. Applying v89 again revokes that broad database capability. Migration 91 also creates the dedicated inflow_sync schema under the inflow_runtime capability role and grants DDL only there. Both replica managers assign replica and shadow tables to that stable role, so active-active nodes can create, swap, alter, and drop replicas without receiving CREATE on public or on the database. Identical DDL takes a transaction-scoped advisory lock and executes under SET LOCAL ROLE inflow_runtime; concurrent nodes therefore create the relation once with capability-role ownership and expose no login-owned crash window. Production must provision equivalent roles and passwords through its secret manager or IaC before applying migration 91; the checked-in runtime password is strictly a local-development value.

Running Migrations

Start or rebuild the containers:

docker compose up -d --build

Apply all pending migrations:

.\scripts\migrate.ps1 -Command up

up is idempotent: when golang-migrate reports no change, the wrapper checks migrate version and succeeds only if the database is clean and at the newest migrations/<db>/*.up.sql version; otherwise it refuses and names both versions. Callers that have just dropped/created the database (container-test.ps1, CI) pass -ExpectPending, which treats any no change as stale Docker state and fails (SCRUM-2387, SCRUM-2542).

migrate.ps1 reads MIGRATION_DATABASE_URL first, then DATABASE_URL, then the local owner default. The API reads DATABASE_URL. Keep those values different outside throwaway development: migrations need a stable owner, while API and worker processes must use the restricted runtime login.

Migration 93 adds the dedicated RUNTIME_INCIDENT_VIEW and RUNTIME_INCIDENT_RESOLVE permissions. Platform and application administrators receive both; read-only auditors receive view only. Its down migration removes only those five seeded rows, so rollback does not disturb unrelated role permissions.

Check current database version:

.\scripts\migrate.ps1 -Command version

The API verifies the current migration state while creating its datasource and fails startup with DATABASE_SCHEMA_NOT_READY when migration metadata is missing, dirty, or behind the version required by the running code. The runtime role retains no direct access to schema_migrations; migration 000204 exposes only its version and dirty flag through a restricted function. Apply migrations before starting or restarting an API node; do not route traffic to a node that failed this check. A repository agreement test keeps the runtime-required version synchronized with the highest shipped PostgreSQL migration.

Roll back one migration:

.\scripts\migrate.ps1 -Command down -Steps 1

Create a new migration pair:

.\scripts\migrate-create.ps1 -Name create_task_tables

Using Another Database

Pass the migration folder and database URL:

.\scripts\migrate.ps1 `
  -Database mysql `
  -DatabaseUrl "mysql://user:pass@tcp(host:3306)/inflow" `
  -Command up

The URL format depends on the golang-migrate database driver.

First Migration

The first PostgreSQL migration creates the identity persistence foundation:

  • identity_users
  • identity_user_actor_refs
  • identity_role_permissions
  • identity_api_keys
  • identity_user_profile_fields

These tables are used by the current JDBC identity adapters.

The second PostgreSQL migration creates the form persistence foundation:

  • form_definition_drafts
  • form_definition_versions
  • form_submissions

These tables are used by the current JDBC form adapters.

The third PostgreSQL migration creates the process definition persistence foundation:

  • process_definition_drafts
  • process_definition_versions

Process definitions are stored as standard BPMN 2.0 XML in bpmn_xml. This XML is the canonical process model. The internal engine ProcessModel is derived from the XML through BpmnModelMapper when the runtime needs to execute a process.

Migration 21 enforces BPM-style process definition versioning. Published process definitions are unique by (definition_key, version), so publishing another draft with the same key creates the next version for that key instead of another version 1.

Current Persistence Status

PostgreSQL is the durable application database in the reference runtime. JDBC adapters back identity, workspaces and grants, process definitions, workflow runtime state, timers and incidents, tasks and forms, rules, data models and records, Fabric, connectors and integrations, agents and memory, documents, messaging, operations, and API management.

In-memory repositories remain in the codebase for focused tests and explicit local compositions. Their presence does not mean the Spring application uses them for the corresponding production path. Verify assembly in platform-monolith/src/main/java/com/inflowai/platform/app/config and the owning module configuration when documenting a specific repository.

The migration directory, not a hand-maintained table list in this manual, is the source of truth for the current schema.

Migration Rules

Use one logical change per migration pair.

Always include both:

<version>_<name>.up.sql
<version>_<name>.down.sql

Do not edit a migration after it has been applied in a shared environment. Create a new migration instead.

Keep database-specific SQL in its own dialect folder.

Production Runtime Database Posture

Production verifies the actual PostgreSQL session while constructing the datasource and does not expose that datasource to any repository or startup bootstrap until the check succeeds. It refuses a switched role, any unexpected settable role membership, administrator option on inflow_runtime, superuser or other powerful role, missing PostgreSQL membership SET capability, temporary-table or schema-creation rights, evidence-table ownership, missing SELECT/INSERT, or effective UPDATE/DELETE/TRUNCATE on audit_events or rule_execution_records. It also requires inflow_runtime to own and have USAGE/CREATE on inflow_sync; startup fails before repositories are created if replica DDL would be unavailable. Unsupported database engines fail closed until an equivalent verifier exists for that dialect.

Document Generation and upstream live-group history reconciliation

Use the normal migration wrapper with the migration-owner connection. An already-advanced Document Generation database can have skipped the later-added upstream instance index. The additive reconciliation migration supplies that index; the matching API binary requires the new migration before startup. Applied and archived migrations are preserved.

The wrapper distinguishes the genuine upstream live-group history from incomplete historical Document Generation histories by checking physical catalog identity. It verifies the subscription table/columns/index and, for root-journal histories, the existing document, MCP and root catalogs before invoking the migrator. Missing or altered objects refuse while the journal is still unchanged. Investigate a refusal against the selected lane's known source and backup; do not force the journal or replace a conflicting index. The new reconciliation locks and rechecks the required schema and refuses incompatible objects. Its down path is forward-only; use a compatible binary rollback that retains the additive index.

The required backend CI job invokes scripts/ci/test-document-generation-migration-history.ps1. The helper runs both real PostgreSQL suites in the selected isolated Compose project:

  • Historical schema 315: 104 named checks preserve archived Document Generation, MCP and signal-channel catalogs and existing rows. Current-wrapper transitions stop at 315 through the existing -Steps option; repeated historical up uses the current physical gate and the frozen 315 source. This suite does not claim an index-free record-subscription schema.
  • Current schema 317: 67 named checks cover supported histories, normal repeated up, exact index identity, independently measured catalogs, corrupted schemas/journals, unsupported archived histories, forward-only down and concurrent schema drift. Future-schema probes apply actual migrations through 317 before an additive disposable 318 fixture.

The helper rejects incomplete, duplicated, failed or stale reports and binds both reports to their source bytes, required case manifests, independent fingerprints, Compose project and fresh database prefixes. The runners also verify that the migration files mounted in the Java container match the host checkout before and after execution. Report paths stay under repository reports/, reject symbolic links, and preserve existing evidence. Connection credentials use the existing secret environment; native migrator diagnostics are discarded.

Developer evidence and remaining validation: feature083 evidence/t102-upstream-integration. Synthetic migration fixtures establish schema/data behavior. Full platform tests, authenticated document-generation journeys and release gates remain separate requirements.

The developer candidate has also exercised a normal wrapper upgrade of its owned existing test database from316 to317. Existing rows and document/MCP/root catalogs were unchanged, and the target matched the independently measured indexed catalog. Evidence is specs/083-document-location-inputs/evidence/t102-upstream-integration/t103-owned-test-upgrade.json. This is an isolated development result; deployment and API startup require the matching current binary and their own validation.

Subsequent owned development runtime validation: the canonical inflow database upgraded normally315→317 after a private0600 logical backup. All249 existing application-table row-set digests and unrelated catalogs remained identical; only three empty root tables were added, four final catalogs matched independent indexed317 goldens, and the complete test317 snapshot stayed unchanged. The existing packaged loopback53980 launcher/HTTPS/trust/glyph overrides were preserved. Normal USER cookie login and workspace reads passed on the current API JAR. Main/public preview databases and APIs were untouched. Evidence: feature083 evidence/t102-upstream-integration/t103-owned-dev-upgrade.json, t103-current-runtime-startup.json, t103-normal-login-smoke.json, and both scoped reviews. This operational gate does not prove document release acceptance; the subsequent native-download keepalive defect is tracked by T105.

Try “document generation”, “integrations” or “first form”.