Data tables

A data table is an Airtable-style table you create in the Data app. Each table has typed columns and rows stored as JSONB. They're the structured-data surface of Project88 — anywhere an agent needs a list of contacts, products, leads, tickets, anything tabular. Tables are scoped to an org.

Two storage strategies

There are two physical patterns under the hood:

1. User tables (default). All user-created tables share a single user_table_rows table. Each row is one Postgres row with a JSONB data column. This is the Notion / Airtable pattern — schemas live as metadata in user_tables.columns, not as Postgres DDL.
2. People (native). The people table is a real Postgres table with typed columns, B-tree and GIN indexes, and a generated tsvector for full-text search. Surfaced in the Data mode as a virtual user table via an addon_slug = '_system' marker row. DataProvider detects system tables and routes CRUD through api.people.* instead of api.userTableRows.*.

The split exists because People can grow to 100k+ rows per org; the JSONB pattern doesn't scale to that with per-column indexes.

Column types

The single source of truth lives in src/modals/recordEditor/colTypes.jsx. There are 15 column types, each with a type-aware cell renderer and editor:

You define columns via the Add column sheet in the Data mode. New column types register automatically once they're added to COL_TYPES — every picker in the app picks them up without further wiring.

CRUD

CRUD happens inline. The Data app's DataWorkspace provides:

• Inline cell editing
• Add row / add column / delete table toolbar
• Filters and sorting (planned UI parity across all types)
• Drag-to-reorder rows
• Optimistic updates with rollback on error

Behind the scenes, DataProvider watches the current org and re-fetches on org change. Row CRUD routes through api.userTableRows.* (or api.people.* for system tables). Operations are optimistic with rollback-on-error.

The People table

Created automatically the first time you enter an org via ensure_people_user_table(). Native columns include:

• first_name, last_name, email, phone, company, job_title
• tags (text array)
• source, status, opted_in_sms, opted_in_email
• city, state, country
• last_contacted_at, conversation_count
• custom_fields (JSONB for user-defined columns)

upsert_person() lets you insert-or-merge by email — the SMS and Campaigns apps use this to ingest contacts without creating duplicates.

Relations

User tables can declare relations in user_table_relations:

• from_table + from_column → to_table + to_column
• relation_type: one-to-one, one-to-many, many-to-many

These are user-defined and currently inform UI hints; the storage is still JSONB-on-rows, not real Postgres foreign keys.

Formula columns

Formula columns evaluate an expression against the row's data and render the result. The engine lives in src/lib/formula/rowFormula.js with helpers shared across the app via SHARED_FORMULA_HELPERS (src/lib/formula/helpers.js).

Syntax:

• Same-table refs — {columnLabel} reads another column's value from the current row.
• One-hop traversal — {linkLabel.target} follows a link column and reads a column from the linked row.
• Operators — and, or, not, +, -, *, /, mod, comparison.
• Helpers — count(), filter(), round(), if(), switch(), concat() (null-tolerant), filterEmpty(), filterPresent().

Compilation is cached per formula source (256-entry LRU).

Child-link columns and rollups

A child-link column stores a foreign-key relationship to rows in another table. The column spec includes:

Aggregators are NaN-safe (non-numeric children are skipped). Formula mode runs a per-parent expression against {children} — useful for things like "average price of completed child invoices."

ChildLinkCell.jsx batches rollup fetches via RPC to avoid N+1 reads; sorting and filtering work on rollup output too.

Inline child creation — clicking + Add child opens an inline form that creates the child row and links it in one step.

Value sets — shared option sets

Reusable option sets for select / multi-select columns. Stored in the user_value_sets table; managed from Settings → Values.

• Each value set is org-scoped.
• Items have label, color, sort order.
• Columns can reference a value set instead of defining their own options inline — change the set once and every column updates.
• On record conversion (ConvertRecordsModal), inherit_tags opt-in carries tag assignments from source rows to destination rows.

The settings pane is a three-column editor on the shared MasterDetailLayout primitive: sets list on the left, the selected set's values in the middle (with the set header pinned on top), and a single-value editor on the right. The disposition-category and compliance selects that used to live as cramped inline FancySelects now sit in the right-pane editor with room to breathe; category dots read from the --positive / --destructive design tokens so they re-theme with the rest of the app. System sets (e.g. Call dispositions) hide their Rename / Delete buttons and surface a Reset to defaults action instead. New set and New value both create-with-defaults and land you in the editor — no separate "add row" mode.

Filtering: trees, presets, inline chips

Data-table filters are trees, not flat lists. Schema:

• Rule node — { kind: 'rule', field, operator, value }
• Group node — { kind: 'group', conjunction: 'and' | 'or', negate, children }

Groups can nest. The UI for the tree lives in src/components/canvas-page/widgets/shared/filters/. Legacy flat-array filters are normalised on read for backward compatibility.

Filter presets — save a filter tree to the filter_presets table and share it across widgets. Presets are scoped per (org, entity_type_key) so the right ones surface for each widget type. Presets are lazy-loaded on first reference and cached. Presets also carry sort priority (migration 115 added a sorts column), so a saved preset restores filters and multi-sort together. In the data-table widget, presets double as the user-facing Saved views picker — see Data app → Saved views.

Range operator on date columns — date and datetime columns support an op: 'range' rule with a structured value: { period: 'this_week' | 'last_week' | 'this_month' | 'last_month' } for named periods, or { period: 'custom', start, end } (YYYY-MM-DD, end day inclusive). Boundary math (src/lib/dateRanges.js) follows the user's week-start preference, uses local-TZ Date constructors so DST transitions resolve correctly, and runs once per filter pass via precomputePeriodRanges so the resolved window is stable across all rows. The data-table widget, dial bucket builder, and pipeline stage widget all wire into the same primitive. presetSchema validates the structured shape on save.

Inline filter chips — a compact chip row above the table that mirrors active filters; click any chip to edit or remove.

Multi-sort — sort by multiple columns at once. Sort state persists on the widget.

Frozen and resizable columns

• Frozen — set per-column via the column-header menu. Frozen columns pin to the left with a divider shadow and a stacked z-index.
• Resizable — drag the column edge. Final width persists onto the column metadata via updateTable. Live drag uses an in-memory widthOverrides map so the resize is smooth.

Per-table settings

Open a table's gear to expose the per-table settings sheet (EditTableSheet), laid out as collapsible Section blocks:

• Enable tags (use_tags, migration 124) — toggle the synthetic _tags column on or off. Off hides the column in the data-table viewer but preserves existing tag assignments, so flipping back on restores them untouched. Useful for tables where tags aren't useful (e.g. clients).
• Default formats (column_type_defaults, migration 125) — per-type presets (date, datetime, time, number, price, percent, phone, email) used as a fallback when a column's own format is unset. Resolution order is col.format → table default → hardcoded default, so per-column customizations still win. The resolver (src/lib/columnFormats.js) is threaded through every render path — the data-table viewer, RecordDetailSheet (view + edit), record editor, child-link rollups (so a sum/avg of a child price column inherits the child table's price default), and the shared field pickers — so the edit-mode date / phone picker uses the same format the cell renders with. Formula columns short-circuit (their format is an object, not a preset string).

Both columns default to behavior-preserving values, so existing tables are unaffected on rollout.

Archive vs. delete

Bulk Delete on user-table rows is now Archive — rows get an archived_at timestamp instead of going away. Schema (migration 123):

• archived_at timestamptz on user_table_rows.
• Partial index (table_id) WHERE archived_at IS NULL covers the hot path; the Archived view does its own scan.
• Three SECURITY INVOKER RPCs taking uuid[]: archive, unarchive, permanently_delete. RLS continues to enforce org membership; bulk operations travel in the request body, not the URL.

The store keeps parallel caches for active vs. archived rows with optimistic moves and rollback on API error; rowCounts excludes archived. Per-row affordances (the trash icon on RecordDetailSheet, DataPage, DataWorkspace) now read Archive with the same two-click confirm. The Archived view exposes Export / Restore / Permanently delete instead, the last behind a confirm dialog. See Data app → Archived view for the UI surface.

Sensitive columns — column-level encryption

Any value-storing column type (text, number, email, phone, etc.) can be marked sensitive. The column stores sensitive: true in its schema and the database tier handles encryption at write time. Reading a sensitive value requires a reveal RPC that writes an audit log row.

Formula, child-link, and link columns can't be marked sensitive — they don't store the value directly.

This complements Supabase Vault (used for provider keys and integration tokens) — Vault is for application secrets, column encryption is for end-user data inside tables.

Where to go next

• Records — the rich detail UI on top of rows
• Tags — universal tagging across rows
• Canvases and widgets — pipeline, table, record, stats widgets
• Data app
• Pages — embed a table block in a page
• Tools — the search_user_tables built-in