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.

Formula-column rules — a rule on a formula column evaluates the formula the same way the sort path does, then compares the result. equals / not_equals compare as text (matching the existing child-link-formula branch); range operators (>, <, between) coerce numerically. Numeric-formula equality (expected_commission equals 100 matching 100.00) is a deliberate follow-up.

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.

Phone columns: canonical search key

Every phone value carries a canonical digit key so lookup no longer depends on typed formatting matching stored formatting. Normalization is NFKC → strip extension → ASCII digits → drop NANP country code and is enforced on both sides as JS/SQL twins pinned by a shared fixture file:

Storage: migration 173 adds a search_phones text[] column on user_table_rows with a partial GIN index, populated by the existing handle_user_table_updated_at trigger extension. The backfill runs under a transaction-local GUC so updated_at is not stamped — that column drives both the grid's default sort and global_search's recency tiebreak, and a bulk re-stamp would scramble both.

Phone columns are detected by name as well as type. Numbers imported into text columns are searchable — phone/mobile as substrings; tel/cell word-bounded so hotel/excellent don't match. NFKC is required, not cosmetic: JS \D deletes full-width digits ('１２３' → '') while Postgres \D keeps them, and production has rows carrying invisible LTR-embedding marks from iOS pastes.

Deliberate carve-outs.

• A formatted query under 4 digits (e.g. (710)) does not digit-normalize — it can only match literally, so a stray parenthesis in a text search doesn't sweep hundreds of rows.
• Phone keys stay out of search_vector — digit tokens would distort ts_rank_cd and leak digit-soup into the palette's ts_headline snippets.

Totals row — per-column summaries

Every view of a data table carries a per-column summary footer. Each column exposes a type-aware aggregation picker (Sum, Avg, Min, Max, Median, Range for numerics; Earliest / Latest / Range for dates; Checked / % Checked for booleans; Most common for select columns; Filled / Empty / Unique / % Filled / % Empty on every type). Percent columns list Avg first — summing a rate column is almost always a mistake.

• Scoped to the filtered rows, never the page. Paging can't change a total; filtering always does.
• Stored per view in filter_presets.summaries (migration 159) — a jsonb map of { [columnKey]: { agg } } alongside the view's filters and sorts.
• Results are typed { value, state, reason }. ok is exact for the rows we have; partial shows with a marker when the row fetch was truncated (comparing rows returned against an exact count in the same round-trip catches clipping from ROW_FETCH_LIMIT or PostgREST's db-max-rows); uncomputable renders — for formula / child-link columns above the existing 5,000-row rollup cap.

Aggregation implementations live in src/lib/dataTable/aggregations.js and the pipeline lives in src/lib/dataTable/summaries.js.

Row-fetch ceiling and sort_order allocation

userTableRows.list walks the table with .range() up to ROW_FETCH_LIMIT, with an id tiebreaker so page boundaries stay stable when sort_order values collide. A single un-ranged select would be clipped at PostgREST's db-max-rows (1000 on Supabase) regardless of the .limit() requested — so the client always pages and the footer flags a clipped set as partial.

sort_order allocation lives in a BEFORE INSERT trigger (migration 160), guarded by a per-table advisory lock, so every insert sees the true max — not the browser cache's prefix. The column's DEFAULT 0 is dropped (migration 161); Postgres applies column defaults before BEFORE INSERT triggers, so a default would silently pin every new row to 0 before the trigger could fire. create_record_with_children and convert_rows defer to the trigger; convert_rows no longer copies the source table's sort_order into the target's numbering space (the original value is recorded in record_conversions.source_sort_order for undo_conversion). No unique index on (table_id, sort_order) — undo_conversion deliberately restores a row to a slot another row may since have taken.

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 launch the per-table settings modal (EditTableSheet) — a centered modal on the shared ModalShell (same chrome as CreateViewModal and ViewSettingsSheet), with a Fields and Settings tab bar pinned above the scrolling body. The Settings tab is 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