Databases Next Generation (SP Note 3.0, productivity workstream)
Branch next-p-db. This file says what exists after this work, what changed in the data and in the API, what was measured, and
where the limits are. Routes that existed in 2.x keep their paths and their answer shapes; every change below is additive unless it is
listed under "Behaviour changes".
1. What exists
| Area | State |
|---|---|
| Query engine | Filters, sorts, search, grouping, aggregates and pagination run in PostgreSQL (src/lib/databases/engine.ts) for every stored type: title, text, URL, number, date, checkbox, select, status, multi-select, AI text, file (empty / not empty), created time, last-edited time. Only the requested window is hydrated (relations, rollups, formulas). |
| JavaScript fallback | A rule or a sort on a relation, rollup or formula, a date operator (before / after / on) on a non-date property, a text operator on a number / date / checkbox, and a search in a database that has a rollup or a formula use the 2.x path (queryRowsJs: load, hydrate, filter in memory). For a top-level AND, the rules that can run in SQL still do. The answer says so: engine: "js", fallback: ["filter:formula", …]; the table footer shows "computed". |
| Windows | The UI loads pages of 100 rows by offset (window.ts); the 500-row cap is gone. The table is virtualised (36 px rows, only the rows on screen + 8 of overscan are in the DOM). Phone cards, list, gallery and board columns load 100 (board: 50) more on demand — they are not virtualised: their DOM grows with what was loaded. |
| Grouping | Table, board, list and phone cards, by a select, status or checkbox property. Counts, offsets and per-number-property sum / average / min / max of each group are computed by the server on the whole result. Groups collapse; the collapsed keys are saved with the view (config.collapsed). |
| Rollups | 19 aggregates: count, count values, sum, average, min, max, unique, show original + count unique, empty, not empty, percent empty / not empty, checked, unchecked, percent checked / unchecked, median, range. Rollups over a relation, a rollup or a formula of the target rows are computed (audit D9), two levels deep at most. |
| Formulas | Errors are per cell with their message; an error in a formula that another formula reads is reported (Error in "X": …) instead of being read as empty; a dependency cycle is detected, named (A → B → A), refused when the property is saved (400 formula_cycle) and shown in the property editor; test(s, regex) refuses catastrophic patterns (D24). |
| Calendar | Loads the six weeks on screen from the server (not a slice of the database). Drag a row to another day (the time of day is kept), drag a row without a date onto a day, undo. "+N more" opens the day (U6); rows without a date are counted on the whole database and listed (first 50). |
| Timeline | Loads the dated rows (first 500 in the view's order, with a notice beyond). Drag a bar to move it, drag an end to stretch it, Alt+←/→ (Shift = end only) from the keyboard; undo. |
| Gallery | Cover = a file property, the page cover, or none; card size (small / medium / large); image fill or fit; properties shown on cards — saved with the view. |
| Filter builder | AND / OR groups nested up to three levels; a local draft saved 400 ms after the last change (U4); incomplete rules are marked and not applied; the server validates (D10). |
| Table editing | Keyboard: arrows, Home / End, PageUp / PageDown, Enter or F2 (edit; toggles a checkbox), Tab / Shift+Tab (move, and commit + move while editing), type-to-edit, Delete / Backspace (clear), Escape (cancel; clears the selection), Space (toggle / open the row). A non-number typed in a number cell is refused and the value kept (U1). Enter on a control inside a cell goes to that control (U5). |
| Selection, bulk | Row checkboxes, shift-range (fetches the ids of windows that are not loaded), select-all on every row of the view + search (not only the loaded ones). Bulk: edit a property, move to a group, duplicate, delete. |
| Undo / redo | ⌘Z / ⇧⌘Z (Ctrl+Z / Ctrl+Y) and buttons: cell edits, new row, duplicate, delete, calendar / timeline drags, board moves, every bulk action. The client stack holds 50 entries for the time the database is open; each entry calls the server (re-sent values, rows/restore, or the bulk journal rows/undo). |
| Columns | Width, order (drag a header, or Move left / right) and visibility are saved per view (config.widths, config.order, config.hidden). Footer per number column: sum, average, min, max of the whole result (config.footers). |
| Row templates | Presets of values + page body per database; "Save as template" from a row, New ▾ creates from a template, one template can be the default of the New button. |
| CSV | Export streams every row of the view (D11; the search box is passed too). Import: no header / empty / not multipart → 400 (D4). |
| Smart (AI) properties | Configuration UI (task, prompt, choices, model through ModelPicker, auto-run, AI-managed), generate / regenerate per cell, retry on a failed cell (D8), "Fill empty cells" and "Recompute all" for the rows of the view (1 000 per click, with a confirmation that states the count), runs log drawer (status, trigger, model, provider, tokens, cost, error, retry). AI text carries the AI mark. A value typed by hand is protected by the API (user_edited); the UI offers "Replace with AI", which sends force. |
| Phones | 360 / 375 / 430 px: every view fits; cards, list and gallery load by pages; the grid is the same virtualised grid; bulk bar, filter builder (in the options sheet), New ▾ are touch-sized. |
2. Data changes — migration 0005_next_productivity
Additive only; rollback in drizzle/0005_next_productivity.down.sql; journal entry idx 5 (when 1795305600000).
| Object | Columns / definition | Use |
|---|---|---|
table database_row_templates |
id, workspace_id → workspaces, database_id → databases (cascade), name, icon, values jsonb, content jsonb, position, is_default, created_at, updated_at; index (database_id, position) |
row templates |
table database_row_ops |
id, workspace_id → workspaces, database_id → databases (cascade), kind (update / delete / duplicate), data jsonb, row_count, created_at, undone_at; index (database_id, created_at) |
undo / redo journal of bulk actions; entries older than 7 days are deleted when a new one is written |
index db_rows_db_pos_idx |
database_rows (database_id, position, created_at) |
default order read window by window |
Neither table is in the sync feed (/api/sync): native clients do not read them. src/db/schema.ts: databaseRowTemplates,
databaseRowOps, and the index on databaseRows.
View configuration (JSON, no migration) gains: collapsed: string[], order: string[] (now read), footers: {propertyId: "sum" | "avg" | "min" | "max"}, cover: "property" | "page" | "none", cardSize, coverFit, cardProps: string[].
3. API changes
3.1 POST /api/databases/<id>/query (existing; answer still {rows, total, limit, offset})
New optional inputs: extraFilter (group ANDed with the filter), groupBy (id of a select / status / checkbox property, or null),
skipGroups (keep the group-first order, skip the group counts), aggregates (bool), rowIds (≤ 500 uuids: only those rows),
limit: 0 (counts only; was min 1). New answer fields: engine ("sql" | "js"), fallback (reasons, JavaScript path only),
hasMore, groups: [{key, count, offset, aggregates}] (with groupBy; key = option id, "true" / "false", or "__none";
offset = index of the group's first row in the returned order), aggregates: {propertyId: {sum, avg, min, max, count}}. With
groupBy, rows are ordered by group (option order, "no value" last), then by the sorts. Rows gain cover when the row page has one.
Errors: an explicit filter / extraFilter / sorts naming an unknown property, an unknown operator, a group with both and and
or, or nested deeper than 4 → 400 bad_filter / 400 bad_sort. groupBy on another type → 400 bad_request.
3.2 New routes
| Route | Body → answer |
|---|---|
PATCH /api/databases/<id>/rows {move: {id, after? | before?}} |
→ {id, position}. One row written (fractional position; renormalised when no room is left). Replaces the full order list for a single move (D7); order still works. |
POST /api/databases/<id>/rows/duplicate {id} | {ids ≤ 200} |
→ 201 the new row / rows. Copies stored values, forward relations, body, icon, cover; title "<title> (copy)"; placed after its source. |
POST /api/databases/<id>/rows/bulk {action: "update" | "delete" | "duplicate", values?, ids? | all?: {viewId?, filter?, extraFilter?, search?, except?}} |
→ {ok, action, affected, opId, rows?}. Limits: update 50 000 rows, delete 5 000, duplicate 200 (400 too_many). update accepts text, number, date, checkbox, status, select, multi-select, URL. |
POST /api/databases/<id>/rows/undo {opId, redo?} |
→ {ok, kind, affected, undone}; 404 when the journal entry is gone, 409 when already in that state. |
GET / POST / PATCH / DELETE /api/databases/<id>/row-templates |
list · create {id?, name, icon?, values?, content?, fromRowId?, isDefault?} → 201 · patch {id, …} · delete ?id= |
POST /api/databases/<id>/rows (existing) |
new optional templateId: template values are the defaults (explicit values win), its body and icon fill what is not sent. |
3.3 Behaviour changes (all fixes of audited defects)
- D4 CSV import without header / empty file / non-multipart body: 400 (
bad_csv,bad_request) instead of 500. - D7 the web client moves one row with
{move}; drag-reorder is disabled while a filter is active. - D9 rollups over relation / rollup / formula targets compute. A rollup whose relation has no linked row now answers
0/nullinstead of{error: "No target property"}. - D10 view
POST/PATCHvalidateconfig.filterandconfig.sorts(400). Deleting a property removes its rules, sorts and settings from every view of the database, so a stored view never holds a dead reference. - D11
GET …/query?format=csvandGET /api/databases/<id>?format=csvstream every row (was 1 000). An empty database exports the header line without a trailing line break. - D20 relation writes keep only rows of the target database. D21 deleting the reverse side of a two-way relation clears
twoWayon the forward property. - D23
GET …/rows?limit=&offset=: not a whole number ≥ 1 / ≥ 0 → 400. - D24
test(s, regex): pattern > 300 characters or with nested quantifiers →{error}in the cell; tested string capped at 20 000 characters. - Formulas: a formula reading a formula in error now shows
Error in "X": …(it used to compute with an empty value). - Filters on AI cells: a pending or failed AI cell is empty for
empty/not_emptyin every path (the 2.x SQL pushdown called it filled, the JavaScript path empty). - Search (SQL path) also matches a date by its ISO form (
2026-11); the displayed form (Nov 14) still matches. Checkbox "Yes / No" and created / last-edited times are not searched in SQL (they were in 2.x, through their display text). - Text sorts use the ICU root collation (
und-x-icu) when the server has it (PostgreSQL 17 here), else the database collation; the 2.x path usedlocaleCompare. GET …/rows?q=(relation picker) now applies its limit in SQL.- Bulk edits through
rows/bulktrigger AI auto-run only for ≤ 25 rows sent asids.
4. Measurements
Machine: M4M64c (Mac Studio M4 Max, 64 GB), PostgreSQL 17.11 shared with other databases, Node v26.10.0. Database "BENCH 20k rows":
20 000 rows, 12 properties (status, select, multi-select, 2 numbers, date, checkbox, text, URL, created time, 1 formula), seeded by
scripts/db-seed-bench.ts (deterministic). Nothing below is estimated.
4.1 Server query time, in process (scripts/db-bench.ts, 9 runs after 1 warm-up, median / p95 in ms)
"2.x" = queryRowsJs, the previous engine, kept in the code as the fallback; "3.0" = queryRows. Both hydrate what they return
(2.x: 500 rows, the old UI window; 3.0: 100 rows, the new window — the second line uses 500 for both).
| Scenario | 2.x median (p95) | 3.0 median (p95) | engine | × | rows matched |
|---|---|---|---|---|---|
| first page, default order | 105.5 (107.4) | 6 (6.2) | sql | 17.6 | 20,000 |
| first page, same window as 2.x (500 rows) | 105.2 (109.3) | 8.1 (8.6) | sql | 13 | 20,000 |
| filter: Status = doing AND Estimate > 100 | 106 (110) | 13.8 (15) | sql | 7.7 | 3,280 |
| filter: Owner contains "mat" (pushdown existed in 2.x) | 18.6 (19.8) | 9.6 (9.7) | sql | 1.9 | 2,480 |
| sort: Cost descending | 111.4 (113.2) | 15.4 (17.1) | sql | 7.2 | 20,000 |
| sort: Due ascending, then title | 124.7 (127.8) | 19.7 (23.5) | sql | 6.3 | 20,000 |
| filter + sort (Tags contains research, Due before 2027, by Estimate desc) | 110.8 (112.1) | 17.2 (19.4) | sql | 6.4 | 2,240 |
| search "zephyr 12" | 157.3 (161.7) | 157.4 (160.9) | js | 1 | 38 |
| deep window (offset 19 900) | 105.3 (106.6) | 17.6 (24.5) | sql | 6 | 20,000 |
| grouped by Status: window + whole-database counts and sums | 104.2 (106.6) | 38.9 (40.6) | sql | 2.7 | 20,000 |
| sort on a formula (JavaScript fallback in 3.0) | 106.4 (113.1) | 106.6 (112.9) | js | 1 | 20,000 |
Note on the search line: the 2.x number above was measured after the Intl formatters were cached in values.ts (a change of this
branch that also speeds up the old path). Before that change the same 2.x search took 1 658–1 925 ms (measured on this database,
same script). The benchmark database has a formula, so 3.0 searches it through the JavaScript path; on the same rows without the
formula property the SQL search took 17–59 ms warm over six search terms (measured once with an ad-hoc script that is not kept in the repository).
4.2 HTTP (running server: auth, JSON), from the Playwright run — db-metrics.json
| Request | median ms | p95 ms | engine | rows matched |
|---|---|---|---|---|
| first window (100 rows, default order) | 64.3 | 66.2 | sql | 20,000 |
| filtered (Status = doing AND Estimate > 100), sorted by Due desc | 35.4 | 37 | sql | 3,280 |
| sorted by Cost desc | 37.3 | 39.5 | sql | 20,000 |
| grouped by Status (window + counts + sums) | 75.1 | 76.7 | sql | 20,000 |
| deep window (offset 19 900) | 42.2 | 42.8 | sql | 20,000 |
| search “zephyr 12” (database has a formula → JavaScript path) | 166.9 | 184.5 | js | 38 |
| sorted by a formula (JavaScript path) | 104.9 | 114.6 | js | 20,000 |
These HTTP times were taken with the web server limited to one connection per pool (the scratch database accepts 6 connections in all); an earlier run of the same test with pools of two measured medians of 27.8 / 31.3 / 24.3 / 40.3 ms for the first four lines.
CSV export of the 20 000 rows: 2,742,907 characters in 422 ms (one GET, whole body read by the test).
4.3 Browser scroll (headless Chromium via Playwright, 1280×800, production build)
Frame-to-frame time measured with requestAnimationFrame while scrollTop advances every frame. The first rows were visible
383 ms after navigation started; 24 rows in the DOM at rest.
| Pattern | frames | median ms | p95 ms | p99 ms | max ms | frames > 16.7 ms | > 33 ms | > 50 ms | max rows in DOM |
|---|---|---|---|---|---|---|---|---|---|
| wheel-like (36 px / frame ≈ 60 rows / s) | 299 | 16.7 | 16.8 | 16.8 | 16.8 | 0 | 0 | 0 | 33 |
| fast (360 px / frame ≈ 600 rows / s) | 299 | 16.7 | 16.7 | 16.8 | 16.8 | 0 | 0 | 0 | 33 |
| scrollbar sweep (3 600 px / frame, whole 20 000 rows in ≈ 3.3 s) | 199 | 16.7 | 16.8 | 16.8 | 16.8 | 0 | 0 | 0 | 32 |
Windows fetched during the three scroll patterns: 77 of 200 (requests are throttled to one per 120 ms while scrolling).
Headless Chromium is not a display: these are main-thread frame times, not presented frames on a screen. No "before" was measured for scrolling: the 2.x grid could not show more than 500 rows.
4.4 Smart property with a real provider (one run)
One cell classified by claude-haiku-5-5 (anthropic): 538 input + 13 output tokens, 0.0000603 USD, answer Urgent — read from ai_property_runs by the opt-in e2e test (E2E_REAL_AI=1). Every other AI test uses the local fake model.
5. Limits (known, not hidden)
- Virtualisation is the table only. Cards, list, gallery and board grow in the DOM as more pages are loaded (100 per step).
- JavaScript path (see §1) still loads and hydrates the whole (pre-filtered) database per query: a sort on a formula over 20 000 rows costs what 2.x cost (§4.1, last line). CSV export and bulk-by-query of such a view load it once and slice.
- Timeline shows at most 500 dated rows; calendar at most 1 000 rows per six-week window; undated lists show 50.
- Grouping only by select, status, checkbox (as in 2.x). No sub-groups, no manual order inside a board column.
- Row height is fixed (36 px): a cell with many chips is clipped in the grid (the row panel shows everything).
- Bulk limits: 50 000 rows per edit, 5 000 per delete, 200 per duplicate. A delete of more than 100 rows uses a batch path that
writes no per-page
page.trashedjournal event (the rows and pages are trashed and restorable the same way). - Undo lives in the open page (not across reloads); bulk undo entries are kept 7 days on the server but the client forgets its stack on reload. Property and view changes are not on the undo stack (hiding a column offers its own Undo toast).
- Shift-range selects at most 5 000 rows; "Select all" covers more.
- Column fill of an AI property runs on the rows of the view, 1 000 per click, sequential batches of 200 (the API limit).
- Rollup depth: a rollup of a rollup of a rollup answers
{error: "Rollup nested too deep"}. - Regex guard is a heuristic (nested quantifiers, length); it does not prove a pattern safe.
- Search parity: see §3.3. A number stored as text with separators other than a comma is searched as stored.
- Sync / natives: row templates and the undo journal are web-only for now (not in
/api/sync). - Dragging a calendar chip or a column header uses HTML5 drag and drop: mouse / trackpad only. On phones the date is changed from the row sheet and columns are moved with "Move left / right".
6. Files
Server: src/lib/databases/engine.ts (SQL engine), query.ts (hydration, rollups, JS reference), rows.ts (move, duplicate, bulk,
undo journal, templates), group.ts, formula.ts, csv.ts, service.ts; routes under src/app/api/databases/[id]/.
Client: src/components/database/ — window.ts (windowed loader), undo.ts, views/TableView.tsx (virtual grid), BulkBar.tsx,
FilterBuilder.tsx + filter-logic.ts, view-logic.ts, growing.tsx, dates.ts, SmartProperty.tsx, RowTemplates.tsx, and
the six views. Scripts: scripts/db-seed-bench.ts, scripts/db-bench.ts, scripts/db-one-connection.ts.
Tests: tests/unit/next-p-db-engine.test.ts, next-p-db-rows.test.ts, next-p-db-client.test.ts;
tests/e2e/next-p-db.spec.ts, next-p-db-phones.spec.ts (Playwright projects next-p-db, next-p-db-phone-360/375/430).
7. Verification (2026-11-19, M4M64c, scratch database note_next_pdb_dev)
| Command | Result |
|---|---|
pnpm typecheck |
exit 0 |
pnpm test |
286 tests: 284 pass, 0 fail, 2 skipped (26 of them are the three next-p-db-* files: 26 / 26) |
pnpm test:http |
9 tests: 8 pass, 1 fail — GET /api/pages/<id>/zoom?level=1 is expected to answer 501 by tests/http/next-api.test.ts, and answers 200 since the AI workstream wired it (that test and that route are not part of this work) |
pnpm test:e2e |
102 tests: 97 pass, 4 fail, 1 skipped (the opt-in real-provider test, run once apart: pass). Projects of this work: next-p-db 13 / 13 (+1 opt-in), next-p-db-phone-360 / 375 / 430 2 / 2 each |
pnpm build |
exit 0 |
| migration 0005 | down, then up again on the scratch database: tables and index removed, then back |
The 4 e2e failures are the tests that need the worker process (mobile.spec.ts "search: filters…" on the three phone projects —
"worker indexed the page" — and desktop.spec.ts "desktop layout…" — "Worker running" in Settings → Jobs). On this node the scratch
database is capped at 6 connections and the production build opens one pool per server bundle: the web server, the worker and the
tests do not fit together (the worker exits with "too many connections"). They were therefore not verified here; nothing they
exercise (search index, jobs panel) is touched by this branch. For the same reason the AI e2e test runs the worker's real handler
through scripts/e2e-ai-runner.ts (one connection) instead of the worker process: the pg-boss dispatch is not exercised by it.
Two suites assume a small scratch database and fail while the 20 000-row benchmark database (or the tombstones left by deleting it)
is present: next-sync ("without kinds": the page must be in the first 500 of /api/sync) and next-knowledge (health lists are
capped at 200). tsx scripts/db-seed-bench.ts --drop plus delete from sync_tombstones restores the state they expect — that is
how the 284 / 0 above was obtained; the Playwright suites re-seed the benchmark themselves.
Screenshots of the runs: ~/dev/note-next-p-db-data/shots/ (table at 20 000 rows top / bottom, collapsed groups, board, keyboard,
bulk bar, filter builder, calendar, timeline, gallery, formula cycle, AI configuration / error / runs log / real run, phones).