Storage Databases

Organization Storage databases are SQLite files managed in the `/databases` workspace. Use them for structured, changing data. See `storage.md` for general file and folder rules and `references/websites-forms-dashboards.md` for read-only website queries.

• `/databases` lists databases and offers `Add` to create one; a database has a display name, permanent identifier (alias), and optional description. Rename changes the display name, not the identifier. The database actions menu offers `Choose files folder` and, when set, `Clear files folder`: the selected existing Storage folder is the default destination for record attachments. Choosing it requires write access to the folder; clearing it does not move or delete previously attached files. `Delete permanently` removes the database and every table with no undo.
• Open a database to see its tables, row counts and size. Create, rename, and permanently delete tables. Creating a table adds `id` automatically; define at least one data column. A table also has an internal `_version` column for updates.
• A table has `Data` and `Schema` views. Data supports adding, editing, selecting and deleting rows, pagination, sorting, and `Typed filters`. Filters can match `All filters` or `Any filter`, are applied to the full table rather than only the visible page, and can be cleared. Schema allows adding and renaming columns; column types cannot be changed in place. Deleting a column permanently removes its values from all rows.
• Supported data column types: `TEXT`, `INTEGER`, `REAL`, `BOOLEAN`, `TIMESTAMP`. A row editor offers `Empty` for nullable values. Changes to databases and tables update open views through realtime events. When the realtime connection returns after an interruption, database lists, tables, saved views, and trigger data refresh in the background so missed changes appear; the first connection does not force a redundant refresh.
• Inside a database, the `Tables`, `Triggers`, and `Views` tabs show their respective counts. The lists have separate paths: `/databases/{alias}/tables`, `/databases/{alias}/triggers`, and `/databases/{alias}/views`. The `Triggers` tab lists triggers across tables with server-side pagination, sorting, search, and table/workflow/enabled filters. Its workflow filter groups structured workflows by AI teammate, with organization workflows last. `New trigger` opens a form to select a table, row event, optional conditions and filters, a workflow, a parallel-run limit, and an enabled switch; the picker groups eligible published structured teammate workflows by AI teammate. A trigger's history page shows its definition and switch, paged tasks with a status filter, captured row/changes, failures, and links to runs.

• The `Views` tab lists saved views over a database. A table view displays a paged, filterable, sortable query as rows. A board displays up to 20 columns, each with its own query, filters, and paged cards; moving cards between columns is not supported. A card reads one record by `:id`, displays its fields, and can edit configured source columns; lists can open or create records through an associated card. Cards can include up to 10 configured tabs: paged `table` tabs of related records (optionally with per-row deletion) or editable `form` tabs laid out in groups, plus a built-in `History` tab for changes to the record's underlying rows and links. Existing card records and their `form` tabs edit each configured writable field inline and save it independently. New record creation remains a single transactional form save. List filters and sorting are viewer controls, not saved in the view definition.
• A top sidebar search can find card records by a configured title or subtitle when a table or board view opens that card. Selecting a numeric-ID result opens the card record; unconfigured or broken views are not searchable this way. See `organization-search.md`.
• Anyone with database read access can see and open its views; record changes additionally require database write access. Only organization owners and admins can author view definitions in the UI: `New view`, `Edit view`, and `Delete view` operate on a name and JSON definition. A view can be deleted without deleting its database rows; a view referenced by another view cannot be deleted until the reference is removed. These organization view routes are not exposed through `system.do_organization_action` to AI teammates; the separate `database.*_view` tools described below use the teammate's database shares.
• A card view or individual board column can configure an `upload` with `enabled`, `record_table`, target files `table`, record-link `row_column`, Storage `path_column`, optional `name_column`, and an optional overriding `folder`. Board columns also identify the record with `id_column`; cards use their `:id`. With uploads enabled and a usable files folder, users can drag files onto an open card or board record or use `Attach files`; without a folder, the UI prompts them to set one. A file is stored under `{folder}/{record_table}/{record_id}/{filename}` and linked by a new row in the configured table. Repeated filenames receive a numbered name rather than overwriting existing files. Uploads require an active organization, Storage folder write permission, an existing folder, and a record displayed by the view; if row insertion fails, the newly saved file is removed. A view-specific folder takes precedence over the database's default.
• A definition has `type` (`table`, `board`, or `card`) and read-only SQL queries plus display `fields`. Table fields, board columns and `table` tab fields show values only; card and `form` tab fields can be editable when they name a valid `source` table, column, and record-id result column. Types include text, long text, number, money, boolean, date, datetime, email, phone, URL, image, select, multiselect, HTML, map, Storage file, and read-only plain-text `label` (no `source`). A field's optional `size` sets its value text to `x-small`, `small`, `medium`, `large`, or `x-large`; it does not change the field width. Optional `display_format` controls presentation, not stored values: string `prefix` and `suffix` apply to text, long text, number, money, date, datetime, email, phone, URL, select and label; `fraction_digits` (integer 0–20) and `use_grouping` (boolean) apply only to number and money; `date_style` (`short`, `medium`, `long`, `full`) applies to date and datetime; `time_style` (`short`, `medium`, `long`) applies only to datetime. Unknown or incompatible options fail validation. Formatting follows the viewer's locale; number grouping defaults to off and rounding is applied only when `fraction_digits` is set. Neither changes the saved value. A percent suffix does not rescale the number, and money keeps its configured currency. Selects can use fixed choices or a dictionary table; multiselects use a dictionary and link table. For a dictionary, set `table`, `value_column`, and `label_column`; optional `order_by` names a column of that dictionary table (default `label_column`) and `direction` is `ASC` (default) or `DESC`. Options load in the configured order from up to 1,000 dictionary rows, and invalid sort-column or direction references are rejected when validating the view. HTML is isolated from application scripts; `storage: true` reads markup from a Storage file path. Map fields display an address. A file field uses an organization Storage path and the viewer's own file access for preview, Docs, or download. Creation, deletion, and opening records must be explicitly configured in the view, and edits save atomically with row-version conflict checks.
• The backend checks definitions and referenced tables, columns, queries, and related card views before saving; create a card before a list that names it. Invalid definitions return reasons. When a database schema change invalidates a view, it is marked broken and cannot read or write until its definition is fixed. A list or `table` tab page defaults to 50 rows and cannot exceed 200; a view query supports up to 100 fields, a board up to 20 columns, and a card up to 10 configured tabs. Each tab needs a unique `id` (not `history`), a `type` (`table` or `form`), a title, and a query by the card record's `:id`. A `table` tab uses `fields`, optional `page_size`, and optional `delete` with its target table and row-id result column. A `form` tab uses `form`, a layout of 1–20 rows with 1–4 side-by-side groups per row; each group needs at least one field and may have a title. A form tab shows the first row its query returns and takes no top-level `fields`, `page_size`, or `delete`. The built-in `History` tab is reserved and paged independently. Database change logs record who changed a relevant row (user, AI teammate, workflow or API), what changed, and when; this is a per-card-record history, not a general change-history view of every database event.

• In the saved-view UI, a table view puts its name in the table header and groups filters, display controls, and the `New` record action at its right (when creation is configured and allowed); unlike board and card views, its page title has no view-type badge or database breadcrumb. An open card displays record details beside its tabs on wider screens; drag the `Resize record details` divider or use its keyboard controls to change the details width (330–512 px, remembered per view in that browser). On existing records, editable fields open inline controls and save individually; boolean fields use a toggle. Edits across the main card and form tabs are serialized, then refreshed from the server. A conflicting edit stays available for `Retry` or `Discard`; if a save succeeded but the subsequent refresh failed, use `Retry refresh` rather than assuming the write failed. Pending edits and saves guard navigation away from the card, and active edits are preserved while switching form tabs. `Ctrl+S` or `⌘+S` saves the focused inline field on an existing record. New records still use a form-wide Save action, which that shortcut can also submit. Table-tab deletion asks for confirmation. A table view keeps one loading indicator visible until its initial rows are ready.
• For an editable `file` field or Storage-backed `html` field on a saved card, select the existing Storage file from the picker; it opens near the current file's folder. Select a file and choose `Choose`, or double-click it. `Clear` removes the field's reference without deleting the file, and `Cancel` leaves the value unchanged. The HTML picker accepts `.html` and `.htm` files; unsupported files cannot be chosen. The picker uses the Docs Storage tree, so visible files and actions follow the viewer's Storage access. A failed save leaves the picker open with an error. File fields show a type icon and file name, and provide `Preview` for supported PDF, media, and HTML files, `Open in Docs` for Docs-viewable files, and `Download`; image files can display inline. Storage-backed HTML renders in a sandboxed preview. Read-only HTML and image previews expose their source path through a `Source` tooltip, while the file-name path is available on hover or focus. A file's availability still depends on the viewer's own Storage read access.

• A table trigger starts a workflow after a managed row is created or updated, not when a row is deleted. An update trigger may require a named column to change from and/or to specified values; omitted `from`/`to` accepts any value. Optional typed row filters apply to the resulting row and match `all` or `any` conditions. The trigger must target a published structured workflow attached to an AI teammate with read access to the database. Create, edit, and re-enable validate the table, columns, condition/filter types, workflow, and access.
• Each matching event queues a task and starts an asynchronous workflow run when a slot is available. The run input includes `database_trigger_id`, `event`, `database`, `table`, `row_id`, `row`, and `changes`. The per-trigger `Parallel runs` limit defaults to 5 (maximum 50); further tasks wait. A disabled or deleted trigger stops accepting work and fails queued tasks, while a run already underway is allowed to finish. A trigger is disabled if its workflow is removed or unpublished or its teammate loses database read access when a task is processed. Tasks show `pending`, `starting`, `running`, `done`, or `failed`; finished task history is retained for 30 days.
• Up to 20 triggers may be attached to each table; a trigger accepts up to 20 filters. Changes to managed databases, tables, columns and rows are recorded with operation type, actor source (`user`, `workflow`, `ai`, or `api`), relevant before/after values, and time. Database imports record the imported database and tables, not an entry for every imported row. These records support trigger processing and the scoped History tab of a saved card view; there is no general history tab for the whole database.

• Database aliases are 1 to 63 lowercase letters, digits, `_`, or `-`, starting with a letter or digit. Table and column names are 1 to 63 letters, digits, or `_`, starting with a letter or `_`.
• Limits: 20 managed databases per organization, 512 MiB per managed database, 100 tables per database, 120 columns per table, and at most 1,000 inserted or returned rows per operation. List rows with `limit` (default and maximum 1,000) and `offset`; use a read-only query to aggregate, count, group, or join instead of downloading all rows.
• Raw queries accept one `SELECT` or `WITH ... SELECT` statement and positional `?` parameters. Writes, `PRAGMA`, `ATTACH`, and multiple statements are not available through the query surface. In raw SQL, `BOOLEAN` values are stored as 0/1; managed tables expose `id` and `_version`.
• Managed databases live under `.databases/{alias}` in organization Storage and use `.sqlite` files; `.db3` files are also recognized. Uploading a `.sqlite` or `.db3` file elsewhere in Storage does not create a managed alias. AI tools `database.schema` and `database.query` may read a permitted Storage file by `storage_path` instead of alias, but do not modify it.
• When Storage Access Group enforcement is enabled, organization users need a readable grant to see a managed database and a manageable grant to change it; owners and admins retain organization-wide access. AI teammate database operations must also pass the teammate's shared-folder access checks. A folder on the way to an accessible database can be traversed without exposing sibling files.

Authenticated organization routes use `/api/organizations/{organization_id}/storage/databases` as their base. `GET` lists and `POST` creates databases; `PATCH /{alias}` accepts `display_name` and/or `storage_path` (an existing writable folder; `null` clears it), and `DELETE /{alias}` permanently removes one. `POST /{alias}/query` takes `{ "sql": "SELECT ...", "params": [] }` for read-only SQL. `GET` and `POST /{alias}/tables` list and create tables. `GET`, `PATCH`, and `DELETE /{alias}/tables/{table}` read the schema, rename, and permanently delete a table. `POST /{alias}/tables/{table}/columns` adds a column; `PATCH` and `DELETE /{alias}/tables/{table}/columns/{column}` rename and permanently remove one. `GET`, `POST`, `PATCH`, and `DELETE /{alias}/tables/{table}/rows` list, insert, batch-update, and batch-delete rows; `GET` and `PATCH /{alias}/tables/{table}/rows/{row_id}` get and update one row. Row-list query parameters include `limit`, `offset`, `order_by`, `direction`, `match`, and a JSON-encoded `filters` array. Organization access is required; mutations require an active organization. These are organization routes, not the `/api/private/storage` file API.

Trigger routes

Relative to `/api/organizations/{organization_id}/storage/databases`: `GET /{alias}/triggers` lists triggers across tables (with `limit`, `offset`, `order_by`, `direction`, `table`, `workflow_id`, `is_enabled`, and `search` filters); `GET /{alias}/triggers/{trigger_id}` reads one. `GET` and `POST /{alias}/tables/{table}/triggers` list and create triggers for a table. `PATCH` and `DELETE /{alias}/tables/{table}/triggers/{trigger_id}` edit/enable/disable and delete one. `GET /{alias}/tables/{table}/triggers/{trigger_id}/tasks` lists tasks with optional `status`, `limit`, and `offset`. List responses include a total; task history is newest first. Create requires `event` (`row_created` or `row_updated`) and `workflow_id`; optional `change_condition` applies only to updates, `filters` and `filter_match` constrain the resulting row, `max_concurrent_runs` sets capacity, and `is_enabled` controls activation. A newly created trigger is enabled by default. Trigger creation/editing requires an active organization; deleting triggers is not AI-exposed.

Saved-view routes

Relative to `/api/organizations/{organization_id}/storage/databases`: `GET`/`POST /{alias}/views` list/create definitions; `GET`/`PATCH`/`DELETE /{alias}/views/{view_id}` read/edit/delete one. Only owners and admins may create, edit, or delete definitions, and writes require an active organization. `GET /{alias}/views/{view_id}/rows` returns paged table rows or board-column rows (`column_id`) with optional `limit`, `offset`, `order_by`, `direction`, `match`, and JSON `filters`. `GET`/`PATCH`/`POST`/`DELETE /{alias}/views/{view_id}/record` read/save/create/delete a record, with `id` for a record read or update and a `column_id` for a board-column creation or deletion where applicable; `GET`/`PATCH` on a card record also accept `tab_id` to read or save one `form` tab's row. A save sends `values` and row `versions` to reject conflicting changes. `GET /{alias}/views/{view_id}/record/tabs/{tab_id}` returns a `table` tab's related-record page by `id`; `DELETE` on that route accepts `id` and `row_id` to delete a row only when the tab has an enabled `delete` configuration. `GET /{alias}/views/{view_id}/record/history` returns the record's changes by `id`, paged with `before` and `limit`. These routes enforce the viewer's database permissions, and mutations require an active organization. For enabled card or board uploads, `POST /api/organizations/{organization_id}/storage/databases/{alias}/views/{view_id}/record/files?id={record_id}` accepts one multipart `file`; a board additionally supplies `column_id`. It returns the saved Storage path, filename, and linking row ID. The upload route checks folder write access and the view's configured record and folder rules.

`database.list`, `database.schema`, `database.create`, `database.create_table`, `database.alter_table`, `database.insert_rows`, `database.upsert_rows`, `database.list_rows`, `database.update_rows`, `database.delete_rows`, and `database.query` are normally loaded on demand when lazy tools are enabled. View tools are `database.list_views`, `database.get_view`, `database.validate_view`, `database.create_view`, and `database.update_view`. The definition input to the last three is JSON text, not a JSON object; validate before saving. Listing and reading need database read access; creating/updating need write access. There is no view-delete AI tool. Read database schema before changing rows or defining views. Batched row updates accept `expected_version` to detect concurrent changes; row deletion is permanent. Database, table, and column deletion are not exposed as AI tools.

A regular AI teammate needs readable or writable Storage sharing for `.databases/{alias}` (or an inherited grant on `.databases`); creating a database requires write access to `.databases`. An unshared database is not returned to that teammate. System AI teammates operate under organization-level Storage access. `database.schema` and `database.query` take exactly one of `alias` or `storage_path`; a storage path is limited to files that the teammate can read.

Publishing a workflow that uses a database tool checks literal database aliases, table/column references, query SQL, and whether the workflow's AI teammate has the needed read or write share. Workflows with database tool steps must be attached to an AI teammate. Values filled by placeholders cannot be checked until the run executes. This catches many invalid steps before a workflow runs but does not replace runtime checks.

A Storage website can query a managed database read-only through `window.alloy.databases.query(alias, sql, params)` after declaring its alias in `.alloy.json` beside the site's entry page. `window.alloy.databases.subscribe(alias, callback)` supplies change notifications while the preview is open. Websites cannot write database rows. To let a person open a saved view in the app, use `window.alloy.appUrl('/databases/{alias}/views/{view_id}')` for the link's `href`. To open one record directly, link to its card view with `window.alloy.appUrl('/databases/{alias}/views/{card_view_id}?record_id={id}')`. An older list-view link using `?record={id}` still redirects to its associated card; for a board list's older link, `&column={column_id}` selects the column's card. The app opens supported database or Docs paths on a trusted click, in place or with `target="_blank"`. See `references/websites-forms-dashboards.md` for examples and constraints.