# Pages custom databases

2026-08-08. The unfinished half of Pages, and the piece every application after
it is a specialised version of.

The roadmap has carried this as `⏸ not built` since 2026-08-04, with an honest
reason: *"That is most of a CMS on its own and needs its own note about field
types, storage, indexing, and how per-field permissions interact with search. A
thin version bolted onto pages would be thrown away."* This is that note.

---

## The job

An operator defines a **database** — Recipes, Staff, Releases, Bug Reports —
gives it **fields**, and members or staff add **records**. Records get a list
view and a detail view, live at a URL, are searchable, and can be placed on
other pages as a widget.

That is the whole of it, and it is deliberately not "a spreadsheet" or "a form
builder". The test for every decision below is whether it serves *content with
structure*, which is what a CMS database is for.

## Storage: JSON with promoted columns

Three options, and the choice matters more than anything else here.

**Real columns per field** — a `CREATE TABLE` per database and an `ALTER` per
field. Fastest to query, and how the product we are matching does it. Rejected:
it means running DDL from a web form. A failed `ALTER` half way through leaves
a database in a state no migration knows about, and the extension system
already refuses to hand out that much rope.

**A row per value (EAV)** — one `record_values` row per field per record.
Flexible, and every list view becomes a join per column. Twenty records with
eight fields is 160 rows to assemble into a table, on the page people load
most.

**JSON, with the few columns everything needs promoted.** Chosen. Values live
in one `values` JSON column; `title` and `slug` are real columns because every
record has them, every URL needs them and every list sorts by them.

🚨 MySQL 8.4 — which is what Convoro requires — can index a JSON path with a
functional index. So a field that turns out to need filtering can be indexed
**later, by a migration, without changing how anything is stored**. That is the
property the other two options lack: EAV commits to the join up front, real
columns commit to runtime DDL up front, and this commits to neither.

The cost is honest: an unindexed JSON path filter is a scan. Acceptable at the
scale a custom database actually runs at (hundreds to low thousands of records,
not a forum's hundreds of thousands), and fixable per field when it is not.

## Field types

Kept deliberately short. Every type here is one somebody would put in a list
view or filter on:

| Type | Stored as | Notes |
|---|---|---|
| Text | string | single line |
| Textarea | string | plain, multi-line |
| Rich text | string | goes through the same editor and sanitiser as a post |
| Number | int/float | sortable, filterable |
| Date | ISO date string | sortable |
| Yes/no | bool | |
| Select | string | one of a fixed list the operator defines |
| Multi-select | list of strings | |
| Member | member id | rendered as a profile link |
| URL | string | validated |

🚨 **No file or image field in this version.** It needs an upload store, a
serving route, an nginx line (see the trap in HANDOFF-PC.md), quota handling
and a deletion story — that is Gallery's infrastructure, and Gallery is coming.
Building a second, weaker version here first is exactly the "thin version that
gets thrown away" the roadmap warned about.

## Per-field permissions, and what they do to search

The part most likely to be got wrong, so it is decided here rather than
discovered.

A field carries a **view group list**, empty meaning everybody. Two consequences
that are easy to miss:

- 🚨 **A hidden field must not leak through search.** If the search index is
  built from the whole record, a member searching for a word that only appears
  in a staff-only field gets a hit they cannot explain — and the snippet may
  show them the value.

  **Revised 2026-08-08, and revised the right way.** This note first settled for
  "only fields visible to everybody are indexed" — searchable by nobody rather
  than by the wrong people. Ernest's rule made that unnecessary as well as
  wrong: *"If someone shouldn't be able to see it then they don't see it."* The
  converse holds too — somebody who **should** see it should find it.

  Acting on that exposed why the blunt version had looked necessary:
  `ContentTypes::search()` **took no viewer**, so no application could filter by
  who was asking however much it wanted to. That was a live leak, not a future
  risk — a page restricted to administrators answered 404 to a signed-out
  visitor and handed them its title through quick search. The seam now carries
  the viewer and their groups, so a record filters its fields **per viewer**,
  which is what this should always have said.
- 🚨 **A hidden field must not leak through the API or the list view either.**
  The filter belongs in the service that reads records, not in each template —
  a template-level check is one forgotten `@if` away from a leak.

## Where records live

`/{database-slug}/{record-slug}`, alongside Pages' existing root-level slugs
and using the same fallback route. A database claims its slug at save time
against the registered routes, exactly as a page does, because a database
nothing can reach is worse than a refused slug.

## What it registers

This is the first feature built after the registries, and it uses all of them —
which is the point of having built them first:

- **Content type** per database, so records are searchable and appear in
  activity without search knowing what a Recipe is.
- **A widget kind**, "Records from a database", so a list of the newest recipes
  can go on the forum index. One kind with the database as a setting, not one
  kind per database — a dock listing forty kinds because somebody made forty
  databases is a worse dock.
- **Widget areas** on the list and detail views.
- **Personal data**, so records a member authored are covered by erasure and
  export. Easy to forget and legally the most expensive omission.

## What this note does not decide

- **Comments on records.** Wanted eventually, and it is the content-type
  registry's job rather than this feature's.
- **Revisions.** Pages have them; records do not yet.
- **Import/export of a database definition**, which is how somebody would share
  a Recipes template. Worth doing, its own change.
- **Cross-database relations** — a field pointing at a record in another
  database. The obvious next request, and it needs a story for what happens
  when the target is deleted before it is worth building.
