Storing DataCAUTION

A framework for building agentic apps

The instruction file, verbatim from the audited commit — this is the text the model reads, and the surface the audit's instruction layer examines. Quoted here so you can judge it without cloning anything.

---
name: storing-data
description: >-
  How to store application data in agent-native apps. All data lives in SQL.
  Use when adding data models, deciding where to store data, or reading/writing
  application data.
scope: dev
metadata:
  internal: true
---

# Storing Data — SQL is the Source of Truth

## Rule

All application data lives in **SQL** (local PGlite, hosted Postgres in production). The agent and UI share the same database. SQL stores structured records, metadata, references, and searchable text — not large raw file payloads. Do not store durable app data in the filesystem unless the app is explicitly running a Local File Mode artifact flow described below.

Large binary or file-like payloads (images, video/audio, PDFs, ZIPs, screenshots, session replay chunks, thumbnails, generated assets, `data:` URLs, and base64 file bodies) must go through configured file/blob storage such as `uploadFile()` or `putPrivateBlob()`. Persist only the returned URL, asset id, or opaque blob handle in SQL. If storage is unavailable in hosted or persistent-database mode, fail closed with setup guidance instead of falling back to base64 in `application_state`, `settings`, `resources`, or app tables.

**Uploaded files an action opens later** (chat attachments, import controls, chunked commits) are one core `AttachmentRef`: mint with `mintAttachmentRef`, open with `resolveAttachment` and `unwrapAttachment` from `@agent-native/core/private-blob`. Never write a template descriptor, never branch on error text, and never truncate a ref (`ATTACHMENT_REF_MAX_CHARS` is the floor for any prompt or field that carries one). Each failure is typed: `notFound`, `forbiddenScope`, `expired` and `malformed` are definitive and stop the turn; only `storageUnavailable` is retryable, and the same ref works once storage is back, so never ask the user to attach the file again for it.

**Local File Mode exception:** some artifact apps (Content, Plans, Slides, Dashboards, Designs, etc.) can intentionally use repo files as the source of truth for the artifact itself. This must be explicit via `agent-native.json`, `AGENT_NATIVE_MODE=local-files`, or an app-owned local-file action helper. In that mode, the UI and agent still go through app actions, but those actions read/write scoped files through `@agent-native/core/local-artifacts` instead of SQL rows. App state, auth, settings, credentials, collaboration metadata, and hosted database mode remain SQL. File-to-database or file-to-provider synchronization is an explicit sync step, not an implicit side effect of editing.

When you add a data model, a list, or a read path, also follow the `performance` skill: project only the columns a list renders, index the columns hot queries filter/sort on, and avoid query waterfalls — so apps stay fast as data grows.

## How It Works

Agent-Native apps use Drizzle ORM over PostgreSQL. Local development uses PGlite at `data/pglite`; production and shared preview deploys need a persistent hosted PostgreSQL `DATABASE_URL`.

For app code, use Drizzle's schema/query DSL by default. Raw SQL is an escape hatch for additive migrations, health checks, or one-off maintenance, not the normal way to build features.

### Schema and migration ownership

Define PostgreSQL tables with Drizzle's `drizzle-orm/pg-core` exports and use
`drizzle-orm` for query operators. Import only framework-owned sharing helpers,
such as `ownableColumns()` and `createSharesTable()`, from
`@agent-native/core/db/schema`. Do not import table builders through the core
helper module.

If the scaffold already has `drizzle.config.ts`, `drizzle/schema.ts`, and a
`db:generate` script, generate reviewed SQL with Drizzle Kit and load it through
`runDrizzleMigrations`. Do not assume every starter has that setup or add a
second migration owner beside it. The Chat and default starters do not ship that
Drizzle Kit setup; follow their local instructions and the Database docs for
the migration path. In Chat, add `server/db/schema.ts`, `server/db/index.ts`
with `createGetDb(schema)`, and `server/plugins/db.ts` with
`runMigrations([...], { table })`. `scripts/migrate-production.ts` is framework-only;
do not create a parallel `runMigrations([...])` list beside a generated Drizzle
migration owner. Give each handwritten migration a unique, stable `name`
alongside its `version`; append changes instead of renumbering, reusing, or
editing an applied entry.

Why: version numbers alone are not a safe identity. Two branches that each independently extend the same migration list can ship different DDL under the same version numbers — whichever branch deploys first "claims" those version numbers in the bookkeeping table, and the other branch's DDL is silently treated as already applied even though it never ran. A `name:` slug is tracked independently of version numbers, so it applies exactly once per database regardless of what any other branch already recorded.

Existing unnamed migrations don't need to be renamed retroactively (the two gating strategies coexist), but any new entry should always carry a name.

Every migration must be additive and backward compatible with code that may
still be running. A new `ADD COLUMN ... NOT NULL` with no `DEFAULT` breaks on
existing rows and inserts from older code. Make it nullable, give it a safe
`DEFAULT`, or use a self-filling type (`SERIAL`, `GENERATED ... AS IDENTITY`);
backfill separately when it needs a real value.

### Core SQL Stores (auto-created, available in all templates)

| Store               | Purpose                                              | Access                                     |
| ------------------- | ---------------------------------------------------- | ------------------------------------------ |
| `application_state` | Ephemeral UI state (compose windows, navigation)     | `readAppState()` / `writeAppState()`       |
| `settings`          | Persistent KV config (preferences, app settings)     | `getSetting()` / `putSetting()`      

CAUTIONgrade B · trust 89/100 Install with care. The audit found things worth knowing before you trust its output.

What the source does

Filesystem

none-observed

Network

none-observed

Shell

none-observed

Dependencies

pinned

Secrets in source

none-found

Findings (5)

.agents/skills/an

.agents/skills/content-product-development

.agents/skills/design-exploration

.agents/skills/turn-into-app

.agents/skills/visual-edit

Gates applied: no_behavioural_pass.

[![ModelsAtlas audit: Storing Data](https://modelsatlas.com/skills/builderio/storing-data/badge.svg)](https://modelsatlas.com/skills/builderio/storing-data/)

Not your listing, or something out of date? Claim or update your listing.