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.
81%
Does it follow best practices?
Run evals on this skill
Adds up to 20 points to the overall score
Passed
No findings from the security scan
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, except a value that is not a ref at all or is missing (malformed with unrecognized_scheme or empty), which is an ordinary action error the model corrects by passing the listed ref; 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.
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.
When Drizzle cannot express a PostgreSQL query, import sql from
@agent-native/core/db and pass its fragment directly to
getDbExec().execute(). Use sql.value(value) for bound values,
sql.ident(name) for validated lowercase identifiers, sql.in(values) for a
nonempty list (it includes parentheses), and sql.join(fragments, separator)
or nested fragments for composition. The tag rejects plain string interpolation
at compile time; do not build { sql: ...${value}... } or pass interpolated
templates directly to execute(). The diff-scoped guard checks new
interpolated SQL reaching getDbExec().execute() through the helper and wrapper
paths it analyzes. A reviewed exception needs // raw-sql-ok: <reason> as
a standalone comment immediately before the sink statement or after it on the
same line. Markers inside SQL text, callbacks, and object properties do not opt
out. The guard does not audit Drizzle sql.raw() or raw adapter query() calls,
so use bound Drizzle fragments or the typed kernel instead of interpolating
dynamic values through those APIs.
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 applies the
framework tables and then that plugin (runAppReleaseMigrations), because a
release-owned deploy skips it at boot; a project with a Drizzle config is left to
db:migrate, and 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.
| Store | Purpose | Access |
|---|---|---|
application_state | Ephemeral UI state (compose windows, navigation) | readAppState() / writeAppState() |
settings | Persistent KV config (preferences, app settings) | getSetting() / putSetting() |
oauth_tokens | OAuth credentials | @agent-native/core/oauth-tokens |
sessions | Auth sessions | @agent-native/core/server |
For Chat and templates using the framework migration plugin, define the
PostgreSQL schema in server/db/schema.ts; getDb() comes from the local
server/db/index.ts. Some templates have their own Drizzle Kit layout and
local instructions. All queries are async.
import { eq, sql } from "drizzle-orm";
import { boolean, pgTable, text } from "drizzle-orm/pg-core";
export const tasks = pgTable("tasks", {
id: text("id").primaryKey(),
title: text("title").notNull(),
completed: boolean("completed").notNull().default(false),
createdAt: text("created_at").notNull().default(sql`now()`),
});
const rows = await db.select().from(tasks).where(eq(tasks.id, taskId));Use drizzle-orm/pg-core so app schemas state their PostgreSQL types directly.
Member offboarding and email changes refuse to run while any column named
email, *_email, *scope_id, created_by, updated_by, invited_by,
owner, principal_id, session_id, or user_id has no policy.
createSharesTable() tables are handled for you, and undeclared owner_email
columns transfer to the successor, which is right only for owned content.
Declare every other one, including columns that are not member identities, from
the app's database plugin graph (Clips does it in server/db/index.ts):
import { registerIdentityColumns } from "@agent-native/core/org";
registerIdentityColumns([
// Access grant: follows an email change, ends with the membership.
{ table: "space_members", column: "email", emailChange: "rekey", offboard: "delete", orgScope: { column: "space_id", references: { table: "spaces", column: "id", orgColumn: "org_id" } }, reason: "Space membership grants access." },
// Someone else's address: never rewritten.
{ table: "meeting_participants", column: "email", emailChange: "retain", offboard: "retain", reason: "Attendee address from the calendar provider." },
// A credential in an owner_email table: never handed to the successor.
{ table: "api_keys", column: "owner_email", emailChange: "rekey", offboard: "revoke", reason: "Bearer keys act as their owner." },
]);Choose delete for grants, credentials, and pending tokens, or revoke (sets
revoked_at) when a reader treats a missing row as "not revoked"; retain for
attribution, history, and third-party addresses; transfer only for owned
data. Declare owner_email tables that hold credentials or grants, or
offboarding hands them to the successor. A table without org_id needs
orgScope or an organization-scoped removal leaves its rows alone.
| Template | Tables |
|---|---|
| emails, labels | |
| Calendar | booking links and bookings; events stay in Google Calendar |
| Forms | forms, responses |
| Content | documents in SQL; connected local-folder sources sync separately |
| Slides | decks (JSON stored in SQL) |
| Clips | recording metadata in SQL; video and image files use blob storage |
A status column with more than two values gets a defineMachine from
@agent-native/core/state-machine beside its store; the SQL derives from it.
machine.parseState(row.status), never as Status.
An unknown value throws instead of flowing on as a plausible state.WHERE status IN (${machine.sqlStateList("claim")}), or
inArray(table.status, machine.fromStates("claim")) in Drizzle — and treat
zero updated rows as a lost race or an illegal transition, never success.Core's A2A task store (a2a/task-lifecycle in @agent-native/core) is the reference.
The agent uses app-specific actions to read/write the database. Core DB scripts are for inspection and maintenance, not for implementing normal product behavior:
pnpm action db-schema — Show all tables, columns, typespnpm action db-query --sql "SELECT * FROM forms" — Run SELECT queriespnpm action db-exec --sql "UPDATE ..." — Last-resort ad-hoc maintenance for short columns, multi-column writes, or computed updates when no domain action exists. For several related writes, prefer --statements '[{"sql":"...","args":[...]}]' so they run sequentially in one transaction. Schema changes are blocked; use a reviewed additive migration instead.pnpm action db-patch --table <t> --column <c> --where "<clause>" --find "<old>" --replace "<new>" — Surgical search/replace on a large text column. Sends the diff instead of re-transmitting the whole value, so it's dramatically more token-efficient than db-exec UPDATE when editing multi-kilobyte documents, slide HTML, dashboard/form JSON, etc. Targets exactly one row per call — narrow --where by primary key. Supports --edits '[{find,replace},...]' for batch edits and --all to replace every occurrence.db-patch is the generic fallback for tables without a dedicated edit action.For one-off maintenance, how to choose between db-exec UPDATE and db-patch:
| Scenario | Use |
|---|---|
SET status = 'published' on one row | db-exec |
SET calories = calories + 50 | db-exec |
| Updating several columns at once | db-exec |
| Inserting/updating several rows as one logical operation | db-exec --statements |
Fixing a typo in a 50KB markdown document's content column | db-patch |
| Changing a single key in a dashboard's JSON blob | db-patch |
Tweaking one paragraph of slide HTML stored in decks.data | db-patch |
| Any edit where you'd otherwise re-send thousands of characters | db-patch |
All of these honor the per-user / per-org data scoping — you can't read or write rows outside the current user's data, regardless of which tool you choose.
The frontend calls actions using React Query hooks from the client API. The framework owns the HTTP transport behind these hooks, so components should not call action routes with raw fetch.
import { useActionQuery, useActionMutation } from "@agent-native/core/client/hooks";
// Read data
const { data } = useActionQuery("list-meals", { date: "2025-01-01" });
// Write data
const { mutate } = useActionMutation("log-meal");Actions are the preferred way for the frontend to access data. You rarely need custom /api/ routes — only for file uploads, streaming, webhooks, or OAuth callbacks.
Local PGlite works out of the box for development. To deploy to production or any environment where data must survive restarts:
DATABASE_URL to a persistent hosted PostgreSQL database.Polling streams database changes to the UI. When the agent writes to the database via scripts, the UI updates automatically via useDbSync() which invalidates React Query caches.
select, insert, update, delete) and standard operators from drizzle-orm (eq, and, or, inArray, desc, etc.) for app reads/writesdrizzle-orm/pg-core for PostgreSQL table and column builders; use @agent-native/core/db/schema for framework-owned sharing helpers onlysettings store for app configuration and user preferencesapplication-state for ephemeral UI state that the agent and UI shareoauth-tokens for OAuth credentialsuploadFile() or putPrivateBlob() for large files/blob data and store only URLs, ids, or handles in SQLdb-schema, db-query, db-exec, db-patch) for ad-hoc database operationsdb-exec --statements instead of several separate db-exec calls for related writes; it is faster and rolls back the whole batch if one statement failsdb-patch instead of db-exec UPDATE whenever you're making a small change to a large text/JSON column — it's much cheaper on tokensdata: URLs, screenshots, videos, audio, PDFs, ZIPs, or session replay chunks directly in SQL rows, application_state, settings, or resourcesgetDbExec() when Drizzle can express the queryWhen storing app-state, include navigation state — the agent needs to know what the user is looking at. The application_state table holds ephemeral UI state that both the agent and UI share. Key patterns:
navigation key — the UI writes current view and selection on route changes; state may be scoped to the current browser tab.navigate command — write it with writeAppStateForCurrentTab("navigate", value) so the command reaches the current tab; the UI processes and deletes it.compose-{id}) — bidirectional state for features like email drafts.When adding a new data model or feature, also consider what navigation and selection state needs to be exposed via application-state. See the context-awareness skill for the full pattern.
defineAction to query the databaseBuilderIO/agent-native
cf9ff10
If you maintain this skill, you can claim it as your own. Once claimed, you can manage eval scenarios, bundle related skills, attach documentation or rules, and ensure cross-agent compatibility.