BuilderIO/storing-data

Storing Data — SQL is the Source of Truth

Rule

All application data lives in SQL (SQLite locally, cloud database in production). The agent and UI share the same database. There is no filesystem dependency for data.

How It Works

Agent-native apps use SQLite via Drizzle ORM + @libsql/client. This works locally out of the box and upgrades seamlessly to cloud databases (Turso, Neon, Supabase, D1) by setting DATABASE_URL. Local and production behave identically.

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() / setSetting() | | oauth_tokens | OAuth credentials | @agent-native/core/oauth-tokens | | sessions | Auth sessions | @agent-native/core/server |

Domain Data (per-template)

Define schema with Drizzle ORM in server/db/schema.ts. Get a database instance with const db = getDb() from server/db/index.ts. All queries are async.

| Template | Tables | | ------------ | --------------------------------------------- | | Mail | emails, labels (+ Gmail API when connected) | | Calendar | events, bookings | | Forms | forms, responses | | Content | documents | | Slides | decks (JSON stored in SQL) | | Videos | compositions in registry + localStorage |

Agent Access

The agent uses actions to read/write the database:

• pnpm action db-schema — Show all tables, columns, types
• pnpm action db-query --sql "SELECT * FROM forms" — Run SELECT queries
• pnpm action db-exec --sql "INSERT INTO ..." — Run INSERT / UPDATE / DELETE. Use for short columns, multi-column writes, computed updates.
• 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.
• App-specific actions for domain operations (auto-exposed as HTTP endpoints) — always prefer these over raw SQL when one exists. They encode business rules, and for editor-backed tables (documents, slides) they also push live Yjs updates to open collaborative editors. db-patch is the generic fallback for tables without a dedicated edit action.

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

Frontend Access

The frontend calls actions via their auto-mounted HTTP endpoints using React Query hooks:

import { useActionQuery, useActionMutation } from "@agent-native/core/client";

// Read data (calls GET /_agent-native/actions/list-meals)
const { data } = useActionQuery<Meal[]>("list-meals", { date: "2025-01-01" });

// Write data (calls POST /_agent-native/actions/log-meal)
const { mutate } = useActionMutation<Meal>("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.

Cloud Deployment

Local SQLite works out of the box. To deploy to production with a cloud database:

1. Set DATABASE_URL (e.g. libsql://your-db.turso.io)
2. Set DATABASE_AUTH_TOKEN for auth
3. No code changes needed — @libsql/client handles both local and remote

Real-time Sync

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.

Do

• Use Drizzle ORM for structured domain data (forms, bookings, documents)
• Use the settings store for app configuration and user preferences
• Use application-state for ephemeral UI state that the agent and UI share
• Use oauth-tokens for OAuth credentials
• Use core DB scripts (db-schema, db-query, db-exec, db-patch) for ad-hoc database operations
• Reach for db-patch instead of db-exec UPDATE whenever you're making a small change to a large text/JSON column — it's much cheaper on tokens

Don't

• Don't store structured app data as JSON files
• Don't store app state in localStorage, sessionStorage, or cookies (except for UI-only preferences like sidebar width)
• Don't keep state only in memory (server variables, global stores)
• Don't use Redis or any external state store for app data
• Don't interpolate user input directly into SQL queries — use Drizzle ORM's query builder

Security

• SQL injection — Use Drizzle ORM's query builder, never raw string interpolation for SQL queries
• Validate before writing — Check data shape before writing, especially for user-submitted data

Application State and Context Awareness

When 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 every route change. The agent reads this before acting.
• navigate key — the agent writes one-shot commands to navigate the UI. The UI processes and deletes them.
• Domain-specific keys (e.g., 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.

Related Skills

• context-awareness — How to expose navigation and selection state via application-state
• real-time-sync — Set up polling so the UI updates when the database changes
• actions — Create actions with defineAction to query the database (auto-exposed as HTTP endpoints)
• self-modifying-code — The agent can also modify the app's source code