SPEC-STV-05-Databases

📜

SPEC-STV-05 · Spec header. Spec ID: SPEC-STV-05 · Title: Databases / Tables System · Version: 1.0.0 · Status: Planned · Authority: Specification · Priority: P1 · Owner role: Backend architect · Reviewers: Frontend lead, Database architect · Last reviewed: 2026-05-11 · Sync targets: app/Services/Databases/**, docs/DATABASE_SCHEMA.md (db tables section) · Depends on: SPEC-STV-HUB, SPEC-STV-02, SPEC-STV-04 · Consumed by: SPEC-STV-03, SPEC-STV-09 · Conflict rule: Hub wins. · Change policy: Backend architect + Frontend lead; Registry bump.

1 · Concept

A documentation database is a structured collection of rows hosted on a page. It is conceptually a Notion-style database (not a MySQL database). Each database has:

a schema of properties (columns),

many rows with cell values keyed by property,

many views that render the same rows differently.

Databases live in documentation_databases. They can be embedded into another page via the database_view block.

2 · Property types (15)

type	config	cell value shape
`text`	`{ multiline?: bool }`	`{ rich: RichText[] }`
`number`	`{ format: "plain\|percent\|currency", currency?, decimals? }`	`{ n: number }`
`select`	`{ options: [{ id, label, color }] }`	`{ option_id }`
`multi_select`	`{ options: [...] }`	`{ option_ids: [] }`
`date`	`{ include_time: bool, range: bool }`	`{ start: iso, end?: iso, time?: bool }`
`checkbox`	`{}`	`{ checked: bool }`
`user`	`{ multi: bool }`	`{ user_uuids: [] }`
`file`	`{ multi: bool }`	`{ file_uuids: [] }`
`url`	`{}`	`{ url: string }`
`email`	`{}`	`{ email: string }`
`phone`	`{}`	`{ phone: string }`
`status`	`{ groups: [{ name, options: [...] }] }`	`{ option_id }`
`relation`	`{ target_database_id, multi: bool }`	`{ row_ids: [] }`
`formula`	`{ expression: string }`	`{ result: number\|string\|bool\|date }` (computed)
`rollup`	`{ relation_property_id, target_property_id, aggregation: "sum\|count\|min\|max\|avg\|earliest\|latest\|any\|every" }`	`{ result }` (computed)

relation, formula, rollup ship in P2.1 (after the v1 set is stable).

3 · Denormalization for search/filter

database_cell_values carries value_text, value_number, value_date populated by DatabaseService::writeCells(). Filters and sorts in views use these columns (with indexes) instead of JSON path queries.

4 · View types (6)

type	config keys	Notes
`table`	`{ visible_properties: [], frozen_property_id?, sort: [...], filter: ..., group?: property_id }`	Default.
`board`	`{ group_by: property_id (select\|status), card_properties: [...] }`	Kanban.
`list`	`{ title_property_id, subtitle_properties: [...] }`	Compact.
`gallery`	`{ cover_property_id (file\|image), card_properties: [...] }`	Image cards.
`calendar`	`{ date_property_id, end_date_property_id? }`	Month/week view.
`timeline`	`{ start_property_id, end_property_id, scale: "day\|week\|month\|quarter" }`	P5+.

5 · Filter / sort / group spec

Filter expression:

{
  "op": "and",
  "conditions": [
    { "property_id": 12, "op": "equals", "value": "opt_open" },
    { "op": "or", "conditions": [
      { "property_id": 9, "op": "contains", "value": "docs" },
      { "property_id": 9, "op": "is_empty" }
    ]}
  ]
}

Sort: [{ property_id, direction: "asc|desc" }]. Group: a single property_id.

All evaluated server-side using the denormalized columns; never read JSON for filter.

6 · Inline as block

The database_view block embeds a database. embed: "inline" renders the rows inline on the page; embed: "linked" renders a link card. Permissions on the host page do not widen access to the database — the database's own visibility wins.

7 · API

See SPEC-STV-03 §7. All filter/sort/group config is server-applied; the client must not paginate without honoring the view config.

8 · Migration / refactor rules

A property type change requires a migration job that rewrites cell values + denorm columns. Disallowed if data would be lost; offer dry-run.

Deleting a property deletes all cell values for it (cascade).

A relation property delete also clears rollups that consume it.