Proposal 0005

Proposal 0005 — Database organization

Status	Draft — for team review
Author	Fabian Kloosterman
Date	2026-08-07
Repository	ridescoredc-models
Decides	how the database is divided, who owns each part, and what migrations govern
Depends on	proposal 0001 (load), proposal 0002 (dataset descriptions)

---

1. Summary

The database is divided into three schemas by who writes them: data for pipeline output, app for what users create, serving for views and tile functions. Each has its own role and grants. Migrations govern app only, because data and serving are rebuilt from external data packages and app is not.

2. Context

Everything is in public today. live/schema/0001_baseline.sql, a pg_dump of the deployed database, shows ridescoredc and crashes_dc (pipeline output) beside the update_score tile function. The survey tables' DDL sits separately in live/schema/wip-patch.sql.

live/README.md states that live/ owns the model-produced tables and the tile functions; the survey tables hold user data written by ridescoredc-website.

Migrations run with yoyo, when the feedback service starts.

3. Problem

The database holds several kinds of data that differ in who writes them, how they are updated, and whether they can be recreated:

	Written by	Updated by	Replaceable
score and network tables	the pipeline	full replacement	yes — rebuilt from a run
feedback and survey tables	the website, at runtime	rows appended by users	no
tile functions and views	whoever last edited one	redefinition	yes — regenerated

All of the tables sit in public, under one role, with no permissions distinguishing one kind from another. This means that nothing prevents the different ridescore components (frontend, models) to modify tables thatthey do not own.

4. Proposal

4.1 Three schemas, by who writes them

We propose to partition the database into three schemas:

Schema	Holds	Written by	Rebuilt from
data	one table per dataset in the loaded package, and the load record	ridescore load	the package
app	feedback, survey submissions	the website	nothing — it is the original
serving	views, tile functions, query endpoints	a deployment	generated artifacts (proposal 0006)

4.2 No migrations for data tables in ridescoredc-models

Both the data and serving database schemas are owned by the data pipeline in ridescoredc-models and do need migrations. For the model data, updates are "full-replace" and the table's shape comes from the dataset description in the data package, and the loader builds the tables. The views and tile functions in serving are auto-generated.

Migrations do make sense for the application tables (e.g., user feedback) that are owned by the frontend. These migrations live in the ridescoredc-website repository and only apply to the app schema,

4.3 Permissions

Role	data	app	serving
loader	own	—	—
deployer	read	—	own
application	read	write	read
tile server	read	—	read

4.4 How app refers to data

Application rows (e.g., user feedback) identify a feature by the geometry dataset's key, which proposal 0001 §4.3 requires to be stable across republication of its source. Because tables in data are dropped and rebuilt when new data is loaded, user feedback references geometry "by value" without the use of a foreign key. A feedback row that points at geometry that no longer exist needs to be handled by the web application. As a check, the data loader reports how many user feedback rows point at nothing.

4.5 This repository stops carrying migrations

live/ holds migrations, the SQL they apply, and yoyo's configuration. Under this proposal, the live folder is removed:

In live/ today	Where it goes
survey table DDL (schema/wip-patch.sql)	ridescoredc-website, with the code that writes those rows
the tile function (schema/wip-update_score_v2.sql, functions/)	generated by a deployment (proposal 0006)
the baseline dump and the migration that replays it	removed
yoyo config and requirements	move with the migrations to ridescoredc-website

5. Alternatives considered

Option	Cost	Proposal
everything in public, as today	nothing to attach permission to, and a full-replace load sits next to user data	three schemas
separate databases rather than schemas	joining feedback to the network becomes a cross-database problem	schemas give the boundary and keep the join
migrations for every table	a table's shape declared in both a description and a migration	data description is authorative
foreign keys from app into data	data could not be rebuilt, and a republished city dataset would violate the constraint	reference by value, with a report of what dangles

6. Open questions

7. Comments