Skip to content

URL: https://mkdocs.justinsforge.com/memory/general/reference_suite_database_engine/

CANON UPDATE (2026-06-18) , renamed to CreatorTrack. This is the CreatorTrack collection/database engine (product formerly "The Suite"/"Workspace"), built and previewed at dev.creatortrack.ai. It is the foundation apps plug into: most apps (tasks, habits, CRM, content calendar) are just a collection (schema + view) on this engine, i.e. configuration, not new code. Build order: reusable UI components first, then this Notion-class page framework + database engine as the foundation, then apps on top; iterate back-and-forth until features work. The data layer formerly "lifeos" is now the core database (core). Full canon (with tech-stack overview): creator-suite-vision-strategy.

CANON UPDATE (2026-06-18) , Property Type Registry. A property type's knowledge used to be smeared across six parallel structures (PropType union, COMPUTED_TYPES, ADDABLE_TYPES, TYPE_LABEL, PROP_GLYPH, TYPE_HINT) plus inline switches in Cell/PropertyMenu/FilterSort. It is now consolidated into one canonical registry lib/prop-types.ts: PROP_TYPES: Record<PropType, PropTypeSpec> where a single object fully describes each type (label/glyph/hint, addable/computed, zod configSchema/valueSchema, parse/serialize, isEmpty, compare, filterOps, formatText, calcKinds). The free-text type + jsonb config/value are interpreted, validated, sorted, filtered and rendered THROUGH this registry. Adding a property type = adding one PROP_TYPES entry (+ a Python migration only if it needs net-new storage) + one entry in the UI dispatch maps (CELL_BY_TYPE in Cell.tsx, CONFIG_EDITORS in PropertyMenu.tsx). The old constants now re-export registry-derived from collection.ts. Boundary validation: /api/collection/value and /prop validate value/config against the spec's zod schemas before any jsonb write (malformed → 400). Filters are per-type operators (is/contains/before/after/is_empty/checked/…) from each spec's filterOps; the AND/OR group builder that composes them is a Tier 2 db-views task. Currency is NOT a separate type: it is the number number_format value "dollar" (USD via Intl), per the locked decision , no stray "currency" literal exists.

CreatorTrack , Collection / Database Engine

Built 2026-06-17 in /home/justinwieb/creatortrack (Next.js 16 App Router, React 19, TS, Drizzle as a typed client over lifeos Postgres; Python migrations own DDL). This is foundation item #26: the apps-as-building-blocks core. Most apps (tasks, habits, CRM, projects, content-calendar) are just a collection with a schema + a view, so the engine is ONE strong collection surface and those apps become CONFIGS, not code. Ported faithfully from the canonical Flask source scripts/forge_workspace_ui_database.py.

Data model (already in lifeos, unchanged)

app.nodes (a database/collection node + its database_row children), plus app.collection_props (schema), app.collection_values (cells, jsonb, PK rowId+propId), app.collection_views (saved views, jsonb config), app.collection_relations (link rows). Title lives on nodes.title, not in collection_values.

Write path (never raw SQL from client)

client → /api/collection/* route handler → lib/mutations.ts → asUser() (RLS) → Postgres. Same pattern as nodes/blocks. Idempotent: setCellValue upserts on (rowId, propId) and deletes on empty; setRelation ON CONFLICT DO NOTHING; ensureCollection seeds Name + a table view only if missing. Verified end-to-end 2026-06-17 (prop add → row add → value set → DB confirm → delete, all 200).

Files delivered

  • lib/prop-types.ts , the Property Type Registry (single source of truth). Defines PropTypeSpec, PROP_TYPES, the FilterOp vocabulary + matchesFilter evaluator, the per-type config zod schemas + inferred types (PropConfigByType), and the registry-derived TYPE_LABEL/PROP_GLYPH/TYPE_HINT/ADDABLE_TYPES/COMPUTED_TYPES. Boundary helpers validateConfig/validateValue. Pure module; cycle with collection.ts is safe (neither reads the other's bindings at top-level eval, only inside functions).
  • lib/collection.ts , pure engine: PropType/ViewType, option color palette (optionColor hashes a string to one of 8 Notion colors), applyView (hidden/filter/sort, filter now per-type operators via matchesFilter), evalFormula (guarded Function stub, denylist), rollup, calcColumn (Calculate footer, isEmpty via the registry), formatNumber. The six scattered type constants now re-export registry-derived from prop-types.ts.
  • lib/data.ts , collectionData (props/views/rows/cells + resolved relation chips + computed rollups & formulas), listDatabases, databaseRows, databaseProps.
  • lib/mutations.ts , addProp/updateProp/deleteProp/reorderProps/duplicateProp, setCellValue, setRelation, createRow/deleteRow, addView/updateView/deleteView, ensureCollection.
  • app/api/collection/* , prop, prop/[id], prop/[id]/duplicate, prop/reorder, value, relation, row, row/[id], view, view/[id], databases, [id]/rows, [id]/props.
  • components/workspace/database/ , context.tsx (client store + actions, optimistic + router.refresh reconcile), Cell.tsx (per-type editors), PropertyMenu.tsx (add + column menu + per-type config), FilterSort.tsx, TableView.tsx, Views.tsx (board/gallery/list/ calendar), DatabaseClient.tsx (view tabs + toolbar).
  • components/workspace/DatabasePage.tsx , server loader, flattens Maps to a snapshot.
  • Tokens in app/theme.css (8-color option palette --opt-*, db metrics) and CSS in app/(suite)/workspace.css (.db*).

Property types (14)

title, text, number (plain/comma/dollar/percent format), select, multiselect, status, date, checkbox, url, email, phone, relation (target_db + chips), rollup (count/sum/avg/ min/max over a relation), formula (prop("Name"), arithmetic, round/concat/now/ dateBetween/contains). select/multiselect/status options are stored as plain strings; chips get a stable hashed color. relation/rollup/formula are computed read-only.

Views (5 native)

table (complete: inline edit per type, column resize via header right-edge drag saved to view.config.widths, column reorder via header drag saved to collection_props positions, add/hide/duplicate/delete + change-type via column menu, row open /n/[id] + delete, New page row, Calculate footer), board (group by select/status), gallery (cards), list (compact), calendar (month grid by a date prop). Filters + sorts are per-view, saved to collection_views.config; quick search is ephemeral client-side title filter.

Deliberately deferred (with why)

  • Formula engine is a stub (guarded new Function + token denylist), not the full Flask AST whitelist. Owner-only, server-side; safe enough, swap to a real parser later.
  • Rollups computed server-side; after a relation/rollup change the client calls router.refresh() to recompute (formulas recompute locally for live typing).
  • No drag-reorder of ROWS, no group-by on non-select props, no conditional formatting, no per-option manual color override, no timeline/chart/form view layouts (Flask listed them as non-native fallbacks; we render the 5 native and fall back to table).
  • Calculate-footer selection is per-session (not persisted to the view config).

Boundaries honored

Did not touch finance (Flask app on 127.0.0.1:8096, still 200), clips/media, or the UI kit components (consumed only). schema.ts ids marked .generatedByDefaultAsIdentity() to mirror the live sequence so drizzle inserts typecheck (TS-only, no DDL pushed). tsc clean except the stale Next validator.ts. /kit, /n/136, /, /finances all 200.

[Claude Code]