Docs
BlogHomeStart building

Database

Read and write your app's data, in development or in production.


Every project has its own SQL database (SQLite, on Cloudflare D1 — or Turso after a move) in two environments: dev, used by the agent and the development preview, and live, used by the published site. Every request names the environment in env. The app reaches the same database through Drizzle ORM in its own code (Totalum SDK — database).

The schema belongs to the app's code (src/db/schema.ts): it changes through migrations the agent writes, which apply to dev at once and to live when you publish. These endpoints never change the schema. They are free to call; the rows they read and write count as the project's database usage.

Backups, restore points, point-in-time restore and the move to Turso are on Database Backups & Restore.

#Get Schema

GET/api/v1/vcaas/projects/:projectId/database/schemaapi-keyFree

The tables, columns, foreign keys and relations of one environment's database, read from the database itself, plus the display settings shown in the Totalum database explorer.

Path parameters

ParameterTypeDescription
projectIdstringThe project ID

Query parameters

FieldTypeRequiredDescription
envstringYes"live" or "dev"

Response fields

FieldTypeDescription
data.envstringThe environment read
data.tables[].namestringTable name
data.tables[].labelstringDisplay label
data.tables[].kindstring"table", "junction" (a many-to-many link table) or "system" (auth and migrations: read-only here)
data.tables[].primaryKeystring[]The primary key's columns in key order; [] when the table has none (its rows are addressed by rowid)
data.tables[].columns[].namestringColumn name
data.tables[].columns[].sqlTypestringSQLite type, e.g. TEXT, INTEGER
data.tables[].columns[].kindstringValue kind, e.g. text, number, datetime, options, relation
data.tables[].columns[].labelstringDisplay label
data.tables[].columns[].notNullbooleanNOT NULL
data.tables[].columns[].uniquebooleanUnique on its own: a one-column primary key or a one-column UNIQUE index. A column of a composite primary key is not unique on its own
data.tables[].columns[].defaultstring | nullDefault value expression
data.tables[].columns[].optionsstring[] | undefinedAllowed values (a CHECK … IN (…) list)
data.tables[].columns[].relationobject | undefined{ table, fkColumn, onDelete } the column references
data.tables[].indexes[]object{ name, columns, unique }
data.tables[].foreignKeys[]object{ column, table, toColumn, onDelete }
data.tables[].rowCountEstimatenumberRows in the table (0 unless counted)
data.ledgerobject{ count, lastCreatedAt, lastHash } of the applied migrations
data.ddlHashstringHash of the whole schema: changes whenever a migration changes it

Example request

curl -H "api-key: tlm_sk_your_key" \
  "https://builder-api.totalum.app/api/v1/vcaas/projects/barber-bookings/database/schema?env=live"
Success · 200 OK
json
{
  "errors": null,
  "data": {
    "env": "live",
    "tables": [
      {
        "name": "bookings",
        "label": "Bookings",
        "kind": "table",
        "primaryKey": ["id"],
        "columns": [
          { "name": "id", "sqlType": "TEXT", "kind": "text", "label": "Id", "notNull": true, "unique": true, "default": null },
          { "name": "status", "sqlType": "TEXT", "kind": "options", "label": "Status", "notNull": true, "unique": false, "default": "'pending'", "options": ["pending", "paid", "cancelled"] },
          { "name": "user_id", "sqlType": "TEXT", "kind": "relation", "label": "User", "notNull": true, "unique": false, "default": null, "relation": { "table": "users", "fkColumn": "id", "onDelete": "cascade" } }
        ],
        "indexes": [{ "name": "bookings_user_id", "columns": ["user_id"], "unique": false }],
        "foreignKeys": [{ "column": "user_id", "table": "users", "toColumn": "id", "onDelete": "cascade" }],
        "rowCountEstimate": 0
      }
    ],
    "ledger": { "count": 4, "lastCreatedAt": "2026-10-08T11:15:00.000Z", "lastHash": "a41f9c" },
    "ddlHash": "9c0e2b"
  }
}

Error codes

CodeHTTPMeaning
DEV_DATABASE_NOT_FOUND404env is "dev" and the project has no development database
PROJECT_NOT_FOUND404Project does not exist or you don't own it

#Query Rows

POST/api/v1/vcaas/projects/:projectId/database/rowsapi-keyFree

Rows of one table, filtered, searched, sorted and paginated. Filters compile to parameterised SQL on the server; values are never interpolated.

Path parameters

ParameterTypeDescription
projectIdstringThe project ID

Body parameters

FieldTypeRequiredDescription
envstringYes"live" or "dev"
tablestringYesTable name
filterobjectNo{ and: [...] } or { or: [...] } of conditions { column, op, value } and nested groups. op: eq, ne, lt, lte, gt, gte, contains, startsWith, in, isNull, notNull. At most 4 levels, 50 conditions and 1,000 in values
searchstringNoText search, at most 200 characters
sortarrayNoUp to 3 { column, dir: "asc" | "desc" }
pageobjectYes{ limit, after? , offset? } — limit 1–200; after is the previous page's nextAfter (keyset, the default way to page); offset (≤ 10,000) is the alternative. Send after or offset, not both
expandstring[]NoUp to 5 foreign-key columns of table; each row gets the referenced row as <column>__expanded (null when missing)
countbooleanNoAlso return total, the number of matching rows

Response fields

FieldTypeDescription
data.columnsarray{ name, sqlType?, kind? } of each column
data.rowsobject[]The rows, one object per row
data.nextAfterstring | undefinedCursor of the next page; absent on the last page
data.totalnumber | undefinedMatching rows, when count was true

Example request

curl -X POST \
  -H "api-key: tlm_sk_your_key" \
  -H "Content-Type: application/json" \
  -d '{"env":"live","table":"orders","filter":{"and":[{"column":"status","op":"eq","value":"paid"},{"column":"total","op":"gte","value":100}]},"sort":[{"column":"created_at","dir":"desc"}],"page":{"limit":20}}' \
  https://builder-api.totalum.app/api/v1/vcaas/projects/my-app/database/rows
Success · 200 OK
json
{
  "errors": null,
  "data": {
    "columns": [
      { "name": "id", "sqlType": "TEXT" },
      { "name": "status", "sqlType": "TEXT" },
      { "name": "total", "sqlType": "REAL" },
      { "name": "created_at", "sqlType": "TEXT" }
    ],
    "rows": [
      { "id": "01J8ZK3W6Q2Y4N7B9C1D3F5G7H", "status": "paid", "total": 120, "created_at": "2026-09-20T10:04:11.000Z" }
    ],
    "nextAfter": "WyIyMDI2LTA5LTIwVDEwOjA0OjExLjAwMFoiXQ"
  }
}

Error codes

CodeHTTPMeaning
TABLE_NOT_FOUND404No such table; errorDetails.suggestions lists close names
COLUMN_NOT_FOUND400A filter, sort or expand column does not exist (or is not a foreign key, for expand)
FILTER_TOO_COMPLEX400Filter deeper than 4 levels, more than 50 conditions or more than 1,000 in values
VALIDATION_ERROR400Invalid body, or page.after is not a cursor of this table and sort
DEV_DATABASE_NOT_FOUND404env is "dev" and the project has no development database

#Insert Record

POST/api/v1/vcaas/projects/:projectId/database/recordsapi-keyFree

Insert one row and return it as stored.

Path parameters

ParameterTypeDescription
projectIdstringThe project ID

Body parameters

FieldTypeRequiredDescription
envstringYes"live" or "dev"
tablestringYesTable name
dataobjectYesColumn → value. Columns with a default may be omitted

Response fields

FieldTypeDescription
data.rowobjectThe inserted row, every column

Example request

curl -X POST \
  -H "api-key: tlm_sk_your_key" \
  -H "Content-Type: application/json" \
  -d '{"env":"live","table":"bookings","data":{"id":"bk_1001","status":"paid","user_id":"usr_42"}}' \
  https://builder-api.totalum.app/api/v1/vcaas/projects/barber-bookings/database/records
Success · 200 OK
json
{ "errors": null, "data": { "row": { "id": "bk_1001", "status": "paid", "user_id": "usr_42", "created_at": "2026-10-08T12:00:00.000Z" } } }

Error codes

CodeHTTPMeaning
TABLE_NOT_FOUND404No such table
COLUMN_NOT_FOUND400data names a column the table does not have
COLUMN_REQUIRED400A NOT NULL column without a default is missing
VALUE_NOT_ALLOWED400The value is not one of the column's allowed values
SYSTEM_TABLE_READ_ONLY403The table is written by Totalum only (e.g. _migrations)

#Record ids

The :recordId of the single-record routes and the id of a bulk item address exactly one row by the table's key (see primaryKey and unique in Get Schema):

  • the value of id when that column is unique, else of the table's one unique column — e.g. 42 or 01J8ZK3W6Q2Y4N7B9C1D3F5G7H;
  • for a composite primary key, an object naming every key column and nothing else — e.g. {"post_id":"p1","user_id":"u1"}. In the URL it is that JSON, URL-encoded: …/database/records/%7B%22post_id%22%3A%22p1%22%2C%22user_id%22%3A%22u1%22%7D; in a bulk item it may be the object itself;
  • for a table with no key, the row's rowid (returned by Query Rows as the rowid column).

An id that does not name every key column (e.g. p1 alone on a (post_id, user_id) key) is refused with 400 RECORD_KEY_REQUIRED — it is never matched against part of the key. If an id would match more than one row (a key column whose collation lets two values compare equal), the write is refused with 400 AMBIGUOUS_RECORD_ID and nothing is changed.

#Update Record

PATCH/api/v1/vcaas/projects/:projectId/database/records/:recordIdapi-keyFree

Update the given columns of one row, found by its record id, and return the full row.

Path parameters

ParameterTypeDescription
projectIdstringThe project ID
recordIdstringThe row's record id: its key value, or for a composite primary key the URL-encoded JSON object of every key column

Body parameters

FieldTypeRequiredDescription
envstringYes"live" or "dev"
tablestringYesTable name
dataobjectYesColumn → new value; at least one column

Response fields

FieldTypeDescription
data.rowobjectThe updated row, every column

Example request

curl -X PATCH \
  -H "api-key: tlm_sk_your_key" \
  -H "Content-Type: application/json" \
  -d '{"env":"live","table":"bookings","data":{"status":"cancelled"}}' \
  https://builder-api.totalum.app/api/v1/vcaas/projects/barber-bookings/database/records/bk_1001
Success · 200 OK
json
{ "errors": null, "data": { "row": { "id": "bk_1001", "status": "cancelled", "user_id": "usr_42", "created_at": "2026-10-08T12:00:00.000Z" } } }

Error codes

CodeHTTPMeaning
RECORD_NOT_FOUND404No row has that id
RECORD_KEY_REQUIRED400The id does not name every column of the table's key; errorDetails.keyColumns lists them
AMBIGUOUS_RECORD_ID400The id matches more than one row; nothing was changed
TABLE_NOT_FOUND404No such table
COLUMN_NOT_FOUND400data names a column the table does not have
VALUE_NOT_ALLOWED400The value is not one of the column's allowed values
SYSTEM_TABLE_READ_ONLY403The table is written by Totalum only

#Delete Record

DELETE/api/v1/vcaas/projects/:projectId/database/records/:recordIdapi-keyFree

Delete one row by its record id. There is no trash: a deleted row comes back only from a backup.

Path parameters

ParameterTypeDescription
projectIdstringThe project ID
recordIdstringThe row's record id: its key value, or for a composite primary key the URL-encoded JSON object of every key column

Body parameters

FieldTypeRequiredDescription
envstringYes"live" or "dev"
tablestringYesTable name

Response fields

FieldTypeDescription
data.deletedbooleanAlways true
data.idstring | number | objectThe deleted row's id, as sent

Example request

curl -X DELETE \
  -H "api-key: tlm_sk_your_key" \
  -H "Content-Type: application/json" \
  -d '{"env":"live","table":"bookings"}' \
  https://builder-api.totalum.app/api/v1/vcaas/projects/barber-bookings/database/records/bk_1001
Success · 200 OK
json
{ "errors": null, "data": { "deleted": true, "id": "bk_1001" } }

Error codes

CodeHTTPMeaning
RECORD_NOT_FOUND404No row has that id
RECORD_KEY_REQUIRED400The id does not name every column of the table's key; errorDetails.keyColumns lists them
AMBIGUOUS_RECORD_ID400The id matches more than one row; nothing was changed
TABLE_NOT_FOUND404No such table
SYSTEM_TABLE_READ_ONLY403The table is written by Totalum only
End of Delete RecordNext endpointPOSTBulk Write

#Bulk Write

POST/api/v1/vcaas/projects/:projectId/database/records/bulkapi-keyFree

Insert, update or delete up to 500 rows of one table in one atomic batch: if one item fails, none is applied.

Path parameters

ParameterTypeDescription
projectIdstringThe project ID

Body parameters

FieldTypeRequiredDescription
envstringYes"live" or "dev"
tablestringYesTable name
opstringYes"insert", "update" or "delete"
itemsarrayYes1–500 items: row objects for insert, { id, data } for update, { id } for delete — each id a record id

Response fields

FieldTypeDescription
data.opstringThe operation
data.countnumberRows inserted, updated or deleted
data.rowsobject[] | undefinedThe inserted or updated rows (insert, update)
data.idsarray | undefinedThe ids actually deleted (delete)

Example request

curl -X POST \
  -H "api-key: tlm_sk_your_key" \
  -H "Content-Type: application/json" \
  -d '{"env":"dev","table":"services","op":"insert","items":[{"id":"cut","name":"Haircut","price":20},{"id":"beard","name":"Beard trim","price":12}]}' \
  https://builder-api.totalum.app/api/v1/vcaas/projects/barber-bookings/database/records/bulk
Success · 200 OK
json
{ "errors": null, "data": { "op": "insert", "count": 2, "rows": [{ "id": "cut", "name": "Haircut", "price": 20 }, { "id": "beard", "name": "Beard trim", "price": 12 }] } }

Error codes

CodeHTTPMeaning
BULK_TOO_LARGE400More than 500 items
RECORD_KEY_REQUIRED400An item's id does not name every column of the table's key; errorDetails.keyColumns lists them
AMBIGUOUS_RECORD_ID400An item's id matches more than one row; nothing was changed
SQL_ERROR400An item failed; nothing was applied
TABLE_NOT_FOUND404No such table
COLUMN_NOT_FOUND400An item names a column the table does not have
SYSTEM_TABLE_READ_ONLY403The table is written by Totalum only
POST/api/v1/vcaas/projects/:projectId/database/relationsapi-keyFree

Add or remove the junction-table row that links two rows in a many-to-many relation.

Path parameters

ParameterTypeDescription
projectIdstringThe project ID

Body parameters

FieldTypeRequiredDescription
envstringYes"live" or "dev"
aobjectYes{ table, id } — one side
bobjectYes{ table, id } — the other side
opstringYes"link" or "unlink"
junctionTablestringNoThe junction table to use; required when more than one connects the two tables

Response fields

FieldTypeDescription
data.opstringThe operation
data.junctionTablestringThe junction table used
data.changednumber1 when a row was added or removed, 0 when it already was (or was not) linked

Example request

curl -X POST \
  -H "api-key: tlm_sk_your_key" \
  -H "Content-Type: application/json" \
  -d '{"env":"live","op":"link","a":{"table":"bookings","id":"bk_1001"},"b":{"table":"services","id":"cut"}}' \
  https://builder-api.totalum.app/api/v1/vcaas/projects/barber-bookings/database/relations
Success · 200 OK
json
{ "errors": null, "data": { "op": "link", "junctionTable": "booking_services", "changed": 1 } }

Error codes

CodeHTTPMeaning
AMBIGUOUS_RELATION400Zero or several junction tables connect the two tables; errorDetails.candidates lists them — send one as junctionTable
NOT_A_JUNCTION_TABLE400junctionTable does not reference both tables
RECORD_NOT_FOUND404a or b does not exist
TABLE_NOT_FOUND404No such table
End of Link or Unlink RecordsNext endpointPOSTRun SQL

#Run SQL

POST/api/v1/vcaas/projects/:projectId/database/sqlapi-keyFree

Run one SQL statement with bound parameters. Reads return at most 1,000 rows. Writes run only when asked for. Schema changes (DDL) are refused: they are migrations in the project's code.

Path parameters

ParameterTypeDescription
projectIdstringThe project ID

Body parameters

FieldTypeRequiredDescription
envstringYes"live" or "dev"
sqlstringYesOne statement, at most 100 KB, with ? placeholders
paramsarrayNoUp to 100 values bound to the placeholders
allowWritesbooleanNoRequired for INSERT, UPDATE and DELETE
confirmLivebooleanNoRequired for a write on live (the published site's data)

Response fields

FieldTypeDescription
data.columnsarray{ name } of each result column
data.rowsobject[]Result rows (at most 1,000)
data.truncatedbooleantrue when the result had more than 1,000 rows
data.metaobjectThe database's statement metadata (rows_read, rows_written, changes, duration, …)

Example request

curl -X POST \
  -H "api-key: tlm_sk_your_key" \
  -H "Content-Type: application/json" \
  -d '{"env":"live","sql":"SELECT status, SUM(total) AS total FROM orders GROUP BY status"}' \
  https://builder-api.totalum.app/api/v1/vcaas/projects/my-app/database/sql
Success · 200 OK
json
{ "errors": null, "data": { "columns": [{ "name": "status" }, { "name": "total" }], "rows": [{ "status": "paid", "total": 4810 }, { "status": "refunded", "total": 120 }], "truncated": false, "meta": { "rows_read": 312, "rows_written": 0, "changes": 0, "duration": 0.4 } } }

Error codes

CodeHTTPMeaning
DDL_NOT_ALLOWED400The statement changes the schema — change src/db/schema.ts, then npm run db:generate and npm run db:migrate
SQL_READ_ONLY403The statement writes and allowWrites is not true
LIVE_WRITE_CONFIRMATION_REQUIRED409A write on live without confirmLive: true
SYSTEM_TABLE_READ_ONLY403The statement writes _migrations
SQL_MULTI_STATEMENT400More than one statement
SQL_ERROR400The database rejected the statement

#Ask Database

POST/api/v1/vcaas/projects/:projectId/database/askapi-keyMetered (one AI call)

Turn a plain-language question into one SQL statement for this database. The statement is shown, never run: run it with Run SQL.

Path parameters

ParameterTypeDescription
projectIdstringThe project id

Body parameters

FieldTypeRequiredDescription
questionstringYesWhat you want from the data
questionBase64stringNoThe question as base64 (UTF-8), instead of question
envstringNo"live" or "dev". Default: live once the project has been published, else dev

Response fields

FieldTypeDescription
data.sqlstringThe statement
data.explanationstringWhat it does
data.isReadOnlybooleantrue when the statement only reads
data.creditsChargednumberWhat the question cost

Example request

curl -X POST \
  -H "api-key: tlm_sk_your_key" \
  -H "Content-Type: application/json" \
  -d '{"question":"How many bookings were paid last week?","env":"live"}' \
  https://builder-api.totalum.app/api/v1/vcaas/projects/barber-bookings/database/ask
Success · 200 OK
json
{ "errors": null, "data": { "sql": "SELECT COUNT(*) AS paid FROM bookings WHERE status = 'paid' AND created_at >= datetime('now', '-7 days')", "explanation": "Counts the bookings paid in the last 7 days.", "isReadOnly": true, "creditsCharged": 0.02 } }

Error codes

CodeHTTPMeaning
NL_QUERY_UNANSWERABLE422The database's tables cannot answer the question (errorMessage says why)
DEV_DATABASE_NOT_FOUND404env is "dev" and the project has no development database

#Get Database Usage

GET/api/v1/vcaas/projects/:projectId/database/usageapi-keyFree

The database's usage and health: rows read and written and storage, in credits (today, 7 and 30 days), size, tables and row counts, the slowest queries, tips (missing indexes, large tables, backups) and whether moving to Turso is worth it.

Path parameters

ParameterTypeDescription
projectIdstringThe project id

Query parameters

FieldTypeRequiredDescription
envstringNo"all" (default), "live" or "dev"

Response fields

FieldTypeDescription
data.generatedAtstringWhen this was computed
data.envs[].envstring"live" or "dev"
data.envs[].providerstring"d1" or "turso"
data.envs[].regionstring | nullWhere the primary copy runs
data.envs[].readReplicasbooleanWhether reads are served from copies near visitors
data.envs[].sizeBytesnumber | nullSize of the database
data.envs[].lastBackupAtstring | nullLast manual backup
data.envs[].usageobject{ today, last7Days, last30Days }, each { rowsRead, rowsWritten, credits: { rowsRead, rowsWritten, storage, total } }
data.envs[].dailyobject[]One row per day: { date, rowsRead, rowsWritten, requests, credits, sizeBytes }
data.envs[].tablesobject | null{ count, rows, list: [{ name, rows, estimated? }] }
data.envs[].queriesobject[] | nullThe heaviest query shapes of the last 7 days: { sql, count, totalMs, rowsRead, rowsReturned, rowsWritten }
data.tursoobject{ verdict, reasons[], caveats[], location, envs, estimateSec, databaseBytes, credits, paidPlanRequired }; verdict: "recommended", "consider", "not_needed" or "on_turso"
data.tipsobject[]{ env, severity: "warn" | "info", code, message, params? }
data.notesobject{ liveTrafficSettledNightly, queriesSince }

Example request

curl -H "api-key: tlm_sk_your_key" \
  "https://builder-api.totalum.app/api/v1/vcaas/projects/barber-bookings/database/usage?env=live"
Success · 200 OK
json
{
  "errors": null,
  "data": {
    "generatedAt": "2026-10-08T12:00:00.000Z",
    "envs": [
      {
        "env": "live",
        "provider": "d1",
        "region": "WEUR",
        "readReplicas": true,
        "sizeBytes": 2457600,
        "lastBackupAt": null,
        "usage": {
          "today": { "rowsRead": 4120, "rowsWritten": 36, "credits": { "rowsRead": 0.0002, "rowsWritten": 0.0021, "storage": 0.0001, "total": 0.0024 } },
          "last7Days": { "rowsRead": 30211, "rowsWritten": 240, "credits": { "rowsRead": 0.0017, "rowsWritten": 0.0137, "storage": 0.0007, "total": 0.0161 } },
          "last30Days": { "rowsRead": 30211, "rowsWritten": 240, "credits": { "rowsRead": 0.0017, "rowsWritten": 0.0137, "storage": 0.0007, "total": 0.0161 } }
        },
        "tables": { "count": 7, "rows": 412, "list": [{ "name": "bookings", "rows": 240 }] },
        "queries": []
      }
    ],
    "turso": { "verdict": "not_needed", "reasons": [{ "code": "fits_d1", "message": "The database is small (2.5 MB of 10 GB) and its traffic fits D1 well. There is no need to move." }], "caveats": [] },
    "tips": [],
    "notes": { "liveTrafficSettledNightly": true, "queriesSince": "2026-10-01T12:00:00.000Z" }
  }
}

Error codes

CodeHTTPMeaning
VALIDATION_ERROR400env is not all, live or dev

#Undo Agent Run Database Change

POST/api/v1/vcaas/projects/:projectId/database/undo-runapi-keyFree

Undo the latest agent run's database change: the database goes back to the restore point taken at the start of that run (a backup is taken first), then the code goes back to before the run. Only the latest run can be undone. Follow it on Get Project State.

Path parameters

ParameterTypeDescription
projectIdstringThe project id

Body parameters

FieldTypeRequiredDescription
runIdstringYesThe run to undo
confirmstringNoThe project id, typed to confirm

Response fields

FieldTypeDescription
data.statusstring"restoring"
data.envstringThe database restored
data.runIdstringThe run undone
data.restoredTostringThe restore point used
data.previousBookmarkstringThe point taken just before (restoring to it undoes the undo)
data.preRestoreBackupIdstringThe backup taken first
data.codeVersionIdstring | nullThe version the code goes back to; null when the run changed no code
data.operationobject{ kind, id }

Example request

curl -X POST \
  -H "api-key: tlm_sk_your_key" \
  -H "Content-Type: application/json" \
  -d '{"runId":"run_01k6xc4n7d","confirm":"barber-bookings"}' \
  https://builder-api.totalum.app/api/v1/vcaas/projects/barber-bookings/database/undo-run
Success · 202 Accepted
json
{ "errors": null, "data": { "env": "dev", "status": "restoring", "restoredTo": "00000085-00000001-…", "previousBookmark": "00000091-00000002-…", "preRestoreBackupId": "bkp_01k6xg", "operation": { "kind": "backupRestore", "id": "op_01k6xh" }, "runId": "run_01k6xc4n7d", "codeVersionId": "ver_01k6xh9q" } }

Error codes

CodeHTTPMeaning
AGENT_RUNNING409A prompt is running
OPERATION_IN_PROGRESS409Another restore or long operation is running
End of Undo Agent Run Database Change Back to top