Databases & SQL

Confluye databases are structured tables you can populate by hand or from a CSV, query with read-only SQL, and read or write from workflow blocks. Every table lives inside a workspace and is isolated from other workspaces.

Tables and columns

A table is a set of typed columns and rows. Columns use one of five types:

A json cell keeps its structure: the grid and CSV preview show arrays and objects as expandable JSON. Expand nested entries to inspect them. In the grid, open Edit JSON to edit the full value in a multiline field, then use Save row to save it (invalid JSON is kept as a plain string instead of being dropped). JSON export returns the array or object as-is; CSV export writes it as a JSON string. In SQL queries a json column materializes as TEXT holding that JSON, so json_extract(skills, '$[0]') works.

Table search and Command Center contains filters inspect the compact JSON text, including object keys and nested values. Command Center eq/neq filters accept JSON values and compare them structurally: object key order does not matter, while array order does. Numeric ordering operators remain scalar-only, and arrays or objects are not sorted.

Empty cells in text, number, status, and date columns are stored as absent (they render blank) rather than as empty strings. An empty cell in a json column is stored as JSON null, both when a CSV import creates the table and when an append adds rows.

When you edit rows in the grid or through the API, each row's keys must name declared columns. Scalar columns accept strings or finite numbers. A partial edit updates only the fields you supply and leaves every other cell untouched, including cells stored as absent or JSON null. Incoming scalar null is not generally accepted outside json columns.

Importing from CSV

Open a database and use Import CSV to create a new table from a .csv file. The dialog parses the full file in the browser once per file, header toggle, or delimiter change, infers column types from every accepted non-empty value up to the file and row limits, and shows a live preview of the first ~100 rows. Type overrides reuse that parsed data without re-reading the file. On confirm, the server re-parses the full file and creates the table in one bulk write.

1. 1

   Upload a file

   Pick a .csv file. The delimiter is auto-detected (you can override it), and a UTF-8 BOM is stripped so the first header is clean.

2. 2

   Review the preview

   Confirm the header row toggle (turn it off to generate column_1, column_2, … names), check the inferred column types, and adjust any type that guessed wrong. JSON columns show expandable arrays and objects so you can inspect their values before importing.

3. 3

   Name the table

   The table name is pre-filled from the filename. If a table with that name already exists in the database, a _2 suffix is applied.

4. 4

   Import

   On success the dialog closes, navigates to the new table, and shows a toast with the imported and skipped row counts.

How columns and types are inferred

• Headers are trimmed; empty headers become column_N; duplicate headers are disambiguated with a numeric suffix (name, name_2, name_3, …).
• Types are inferred per column from every non-empty value in the parsed file (up to the import row cap), not only the ~100 rows shown in the preview grid: if every such value is a JSON array or object the column is json; if every one is numeric the column is number; otherwise a name heuristic applies (status/stage → status; date/updated/names ending in at → date); everything else is text. Mixed values fall back to text. You can override any type in the preview, including switching a column to json so its cells are parsed as arrays or objects.
• All imported columns are optional (required = false).

Limits and validation

The import endpoint is POST /api/databases/import (mirrored at POST /api/v1/databases/import). It accepts multipart/form-data and requires Member, Admin, or Owner role.

stats reports totalRows, importedRows, skippedRows, and malformedRows.

Appending CSV rows

Use Import CSV → Append to existing to add rows to an existing table without changing its schema. Append mode maps CSV columns to destination columns by exact name first, then by case-insensitive trimmed name. You can override the mapping manually or set a CSV column to ignored before submitting.

The preview uses the same parser and mapping rules as the final append, but only tokenizes enough rows for the dialog preview. Missing destination columns are filled with null; ignored CSV columns are reported as warnings. Rows with too many cells, invalid number values, or invalid date values are omitted and counted in the skipped/malformed totals.

The same POST /api/databases/import endpoint handles append when mode=append, tableId, and optional columnMappings are included in the multipart form. The /api/v1/databases/import mirror returns the same append stats and also includes a rowsUrl for the target table.

Querying with SQL

Each database has a SQL panel with an editor and a Run button. Queries execute against the tables of the current database only — the SQL never touches Postgres. On each run, the selected tables are materialized into a fresh in-memory SQLite database, so queries are fully isolated by construction.

SELECT "name", "stage", "amount"
FROM "deals"
WHERE "amount" > 1000
ORDER BY "amount" DESC
LIMIT 100

Reference tables and columns by their exact name, quoted with double quotes when they contain spaces. Joins across tables of the same database are supported.

Safety constraints

• Results are capped at 1,000 rows; when there are more, the response is flagged truncated.
• Materialization is capped at 50,000 rows per table, with a warning when a table is truncated for querying.
• A query against a missing table returns the list of available tables so you can correct it.

The query endpoint is POST /api/databases/query (mirrored at POST /api/v1/databases/query) and returns { columns, rows, truncated, durationMs, warnings }. Any workspace member — including viewers — can run read-only queries. A database from another workspace simply returns 404.

Query results can be exported to CSV, the same way a full table can.

Using databases in workflows

Workflow blocks read and write these tables directly. Table Write row keys you configure must match declared columns; undeclared keys are validation errors and are never silently discarded. When the block omits values for an existing table, generated defaults apply only to declared columns that match; if none match, you must supply explicit values. When the target table is missing, the block still auto-creates with its full generated defaults unchanged.

Managed configuration

Managed configuration is an explicit contract for a small class of non-sensitive tables whose rows must move between workspaces in the same installation. It is separate from ordinary table data: a table participates only after an Admin or Owner declares it and acknowledges the writers that will be blocked. A table name does not opt a table in, and business rows are never exported implicitly.

Declaration cannot be undone, and ordinary writers cannot be restored. Review the affected writers before accepting the permanent classification. Lifecycle values, like row values, must not contain recognized credentials or tokens.

The contract fixes a textual key column, a textual lifecycle column with active and retired values, and the allowed columns and types. Text columns may declare references to another table in the same package. Package references use logical table keys; workspace, database, table, and row IDs from the source are not portable identifiers. Retiring a row keeps its key and changes its lifecycle state. The first delivery does not delete rows, change keys or column types, or provide a drift override.

Managed row edits validate table references against the current workspace and package ownership. If preview or export reports an invalid reference, use its table and row keys to find and correct the row, or include the referenced declared table in the export selection when it belongs to the same package.

The managed configuration panel in Tables supports four stages:

1. Declare a non-sensitive table, review the listed workflow writers, and edit rows by stable key and expected revision. Background refreshes keep an existing draft pinned to the revision it started from; a failed new-row save keeps the entered values visible. Use Reload rows and discard edits when you want to drop local drafts and fetch the current rows. JSON cells keep your text while you edit; correct any JSON validation error before saving the row. Clearing a numeric cell leaves it empty and blocks saving until you enter a finite number; enter 0 explicitly when zero is intended.
2. Export only selected declared tables into a portable JSON package. Downloads use compact JSON so whitespace does not push a valid package beyond the import size limit. All selected tables are read from one consistent snapshot, including when concurrent writers commit changes.
3. Upload a package, choose every destination explicitly, and run a preview. The preview compares the package with the observed destination and the last successful application for that package.
4. Review the actions and blockers, then confirm and apply from the authenticated human session.

A row already equal to the desired package is converged and remains unchanged, even if its earlier baseline differs. Schema, ownership, classification, missing, unexpected, or changed-row drift blocks an application; there is no ignore-drift action. A stale preview must be recalculated. Applying all tables, rows, the new baseline, and the receipt is one transaction. A retry with the same request identity returns the existing receipt after authorization checks, so a lost response can be retried without duplicating effects. This recovery remains available if new applications are disabled after the commit: use Recover application to retry the existing confirmed request. A definitive conflict or expired confirmation disables Confirm and apply until you choose Recalculate preview and review the new result. History is workspace-scoped and shows the actor, package key, action list, the applied status, receipt, and creation time without copying row values into the receipt.

Packages are limited to 20 tables, 500 rows per table, 1,000 rows total, and 1 MiB of JSON. Export also rejects selections whose stored JSON text exceeds 32 MiB before loading row values into the application. This separate read bound allows for database formatting and physical references; the downloaded portable package still must fit within 1 MiB. Only non-sensitive configuration is supported; credentials, tokens, secrets, and business data do not belong in a package. Existing CSV append and workflow writers are blocked once a table is declared managed. There is no SQL override or delete operation for managed rows.

Application is disabled until deployment certification enables it with MANAGED_CONFIGURATION_APPLY_ENABLED=true and durable DATABASE_URL storage. When it is disabled, ordinary tables keep their existing behavior and the catalog, managed-row reads, and application history remain separate from ordinary table writes. An installation without durable PostgreSQL does not advertise apply availability. Agents and API clients can validate, preview, and prepare a proposal link, but only the authenticated human panel issues the confirmation used by apply.

See docs/operations/managed-configuration.md in the repository for recovery guidance and the Databases API reference for endpoint contracts.

Next steps