# Store app records

Source: /docs/building/store-app-data.md

Use a managed PostgreSQL database for records your app owns and changes: approval decisions, work orders, user-entered notes, or planning overrides. Synced tables and transform outputs are useful analytical inputs; a database gives your backend a place to perform operational reads and writes.

Your coding agent defines the database in YAML and SQL. APIs and transforms access it through SDK input declarations. The React app calls those APIs, keeping database credentials in the backend.

## Configure storage and migration behavior [#configure-storage-and-migration-behavior]

Create a database resource such as `operations`. Its source has two parts:

```text
databases/operations/
  vortexify.database.yaml
  migrations/
    001_initial.sql
```

The exact parent directory comes from your project configuration. The database has no Python entrypoint. Put its settings in `vortexify.database.yaml`:

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

| Setting | Decision it controls |
| --- | --- |
| `storage_gb` | Requested database storage in GB; must be a positive integer |
| `backups.enabled` | Whether backups are enabled; defaults to `true` |
| `backups.cron` | Backup schedule expression; defaults to `null` |
| `migration_approval_required` | Whether new origin migrations require approval; defaults to `true` |

The model default for storage is 10 GB. Inspect the generated file because a starter can choose explicit values. Only these settings are accepted; unknown fields are rejected. Keep connection strings and physical database identifiers out of this file. Saved settings describe the requested configuration; inspect the environment and job results to confirm what has been applied.

## Evolve the schema with ordered SQL files [#evolve-the-schema-with-ordered-sql-files]

Start with a migration such as `migrations/001_initial.sql`:

```sql
CREATE TABLE public.work_orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    asset_id TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'open',
    notes TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
```

For a later change, add `002_add_priority.sql` rather than rewriting the applied file. Applied migrations are checked against their saved contents. Keep ordering consistent and let the worker own transactions; omit `BEGIN`, `COMMIT`, and `ROLLBACK` from migration files.

A commit can submit migration work when the approval policy permits. With approval enabled, origin waits for a decision; a provisioned preview environment is ungated. Review existing rows before introducing constraints or changing types, and plan any required backfill.

## Saved source and applied schema are different states [#saved-source-and-applied-schema-are-different-states]

Origin and preview, when provisioned, are separate physical databases. A migration applied to one is not automatically applied to the other. You can commit dependent API/transform source once the database source is committed, before migration completes. Inspect the target environment and its applied version before running that code.

Your coding agent uses the CLI to save source and inspect migration status:

```sh
vortexify commit --type database --resource operations
vortexify database operations --json
```

Migration approval requires project owner or admin access. If origin requires approval, review the pending SQL and approve the actual saved version with `vortexify database operations --approve VERSION --environment origin`. Approval starts migration work; it does not mean the schema is already ready. Wait on the returned migration job with `vortexify job JOB_ID --wait`, then inspect the live schema and applied version.

Use `--environment preview` for a preview approval decision when applicable. The CLI's environment flag selects the approval/rejection target; inspect the database's environments before assuming a preview exists. A backup and a physical restore are separate operations from applying schema migrations.

In the platform, open the database's **Tables** view and select a table to inspect **Data** and **Schema**. Its **Code** and **Versions** views show the source and saved history; **Runs & logs** shows execution history. The table list includes environment labels. The current resource workspace does not yet expose a dedicated migration approval or environment-management panel.

> **Platform screenshot placeholder:** The mounted database Tables view with an environment-labeled table selected and its Schema visible. A future migration approval panel should be illustrated separately as a proposed UI.

## Connect backend code to the tables [#connect-backend-code-to-the-tables]

Use `Database("operations", tables=["work_orders"], mode="read_write")` in an API's inputs when it needs to create or update records. The runtime injects a scoped Postgres connection; use parameterized SQL for values. Schema changes still belong in migrations.

Use `DatabaseTable("operations.work_orders")` when a transform or API needs a read-only tabular snapshot. That resolves to a DataFrame or a DuckDB relation according to the function engine. Both `DatabaseTable` and `Database` follow execution environment selection. Production uses origin. The current direct API preview path prefers a provisioned preview database and otherwise falls back to origin; watcher-executed preview jobs require a preview environment. Confirm which environment exists before running code that reads or writes app records.

Before testing a consumer, confirm that its declared tables and columns exist in the environment it will access. See [database contracts](/docs/reference/python/databases) for scoping and migration details, then [build a backend operation](/docs/building/backend-operations) for the interface-facing logic.