# Managed databases

Source: /docs/reference/python/databases.md

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](/docs/building/store-app-data). This page defines the exact database source and downstream SDK contracts.

## Settings contract [#settings-contract]

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

| Field | Type and default |
| --- | --- |
| storage_gb | Positive integer, model default 10 GB |
| backups.enabled | Boolean, default true |
| backups.cron | Nonempty cron string or null, default null |
| migration_approval_required | Boolean, default true |

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.

```yaml
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.

## Migrations [#migrations]

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.

```sql
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:

```sql
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 execution [#environments-and-execution]

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 access [#downstream-python-access]

Downstream APIs/transforms import these declarations from vortexify:

```python
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 inspection [#cli-inspection]

```sh
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 platform [#inspect-database-data-in-the-platform]

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](/docs/reference/cli/configuration), [transform inputs](/docs/reference/python/transforms#inputs-and-runtime-values), and [backend operations](/docs/building/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.