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.

Next steps