Managed databases

A Vortexify database is managed PostgreSQL storage with versioned settings and SQL migrations. Its source contains vortexify.database.yaml and migrations/*.sql; it does not define a Python SDK class. The SDK references below describe how APIs and transforms consume it.

For the workflow and configuration choices, start with Store app records. This page defines the exact database source and downstream SDK contracts.

Settings contractCopy Anchor Link

Root vortexify.database.yaml must contain a YAML mapping. Supported keys are:

Unknown top-level fields or unknown backup fields are rejected. The starter may choose explicit settings different from model defaults: inspect existing source. The settings describe the saved database configuration. Environment status reports applied schema versions, backup status, and approval decisions; those operational records tell you which work has completed.

storage_gb: 10
backups:
  enabled: true
  cron: "0 * * * *"
migration_approval_required: true

Keep customer credentials, physical database identifiers and private connection settings outside repository source and user-facing results.

MigrationsCopy Anchor Link

At least one SQL file under migrations/ is required. Start with migrations/001_initial.sql; add ordered files such as migrations/002_add_order_status.sql for later changes. Keep names consistently zero-padded so source ordering is unambiguous.

CREATE TABLE public.orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id TEXT NOT NULL,
    amount NUMERIC(12, 2) NOT NULL CHECK (amount >= 0),
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX orders_customer_id_idx ON public.orders (customer_id);

A later migration can add a column without rewriting the initial migration:

ALTER TABLE public.orders
    ADD COLUMN status TEXT NOT NULL DEFAULT 'pending';

These are separate files, applied in order. Do not edit, rename or delete migrations already applied in any environment. The worker checks applied file contents and tracks its ledger; source history is not a scratch schema. Add a new migration to correct or evolve an applied schema. Do not put BEGIN/COMMIT/ROLLBACK transaction control in migration files: the worker owns transactions and ledger updates.

Inspect real schema, constraints, keys and relevant data before adding uniqueness, NOT NULL, foreign keys or type conversions. A small preview does not establish that all existing rows satisfy a new constraint. Keep changes focused and use a deliberate backfill strategy where needed. Ordinary updated_at columns do not update themselves; write updated_at = now() in updates or define an intentional trigger via a migration.

The _vortexify_worker schema is reserved for runtime migration bookkeeping. Do not inspect or modify it through customer migration work or application code. User tables normally belong to public; qualify names when ambiguity is possible.

Environments and executionCopy Anchor Link

Production Database and DatabaseTable inputs use origin. During direct API preview execution, the resolver prefers a provisioned preview database and falls back to origin if preview is absent. Watcher-executed preview jobs explicitly require the preview environment. Preview is therefore not a guarantee of an isolated database on every execution path. Check environment availability before a live write; source-controlled mocks can test logic without live database access.

Origin and preview, when provisioned, are separate physical databases, not two Postgres schemas. Inspect available environments; choose the intended target explicitly. The CLI approval command defaults to origin; use --environment preview when that is the intended target. Do not assume changes to one are applied to the other. Saved source can differ from either environment's live state.

Committing or saving source automatically submits new SQL to the selected environment when its approval policy permits. Origin waits for approval when required; approving the saved version starts the worker. A provisioned preview is ungated. Review pending migrations against the selected environment's applied version. Parsing alone does not execute SQL; a commit/save can launch an asynchronous worker. Migration approval/rejection requires project owner or admin access. Follow the returned job to completion and check the environment's applied version and live schema.

Use migration history and job IDs to inspect failed or interrupted work before submitting again. Stopping a wait does not cancel the underlying operation. Backups are separate worker operations; verify their results. A backup is not an automatic rollback, and physical restore is a separate platform operation.

Downstream Python accessCopy Anchor Link

Downstream APIs/transforms import these declarations from vortexify:

from vortexify import Database, DatabaseTable

orders_input = DatabaseTable("operations.orders")
operations_connection = Database("operations", tables=["orders"], mode="read_write")

These are input declaration examples, not database source files. Put declarations in an API/transform inputs={...} mapping under local argument names.

DatabaseTable takes one reference string and is read-only. Pandas receives a DataFrame, PySpark a Spark DataFrame, and DuckDB registers a relation in conn. Environment selection applies to DatabaseTable as well as Database. Database(name, *, tables, mode="read_write") resolves to a scoped Postgres connection: mode is read or read_write and tables is an explicit list of accessible table names. Use parameterized operational SQL; schema management still belongs to migrations. Do not promise DDL access through a read_write declaration or embed private connection strings in app code.

React applications access database-backed behavior through declared APIs and the frontend SDK, not direct database credentials. Verify the actual table and API contracts before planning these dependencies.

CLI inspectionCopy Anchor Link

vortexify create database operations
vortexify database operations --json

Creation provisions origin storage; commits can initiate migration work under the saved policy. Structured status currently exposes environment applied versions, backups, and approval decisions. Pending migrations are listed by the separate database migration service. vortexify database operations --approve VERSION --environment origin approves and applies a saved version; --reject VERSION rejects it without applying. These are state-changing decisions, not inspection commands. Use the actual saved version and intended environment.

Inspect database data in the platformCopy Anchor Link

Open the resource's Tables view, select a table, then inspect Data, Schema, or Relationships. Database tables are live environment reads rather than file-backed producer-run snapshots. The table list includes the environment label where available.

The mounted resource workspace currently exposes database source, versions, tables, connections, and run history. Dedicated migration approval and environment-management controls are not mounted there yet. Use the database services/CLI approval workflow for those operations; a screenshot of a migration editor would be a proposed screen.

See CLI configuration, transform inputs, and backend operations.

Committing a DatabaseTable("operations.orders") input requires the committed database producer, not an already materialized SQL table. Running the consumer still requires that table in the selected environment. After a failed migration, approving the same saved version again submits a new job; a recorded approval or an empty pending-approval list does not establish that the schema was applied.