26 — Storage capacity and transition sizing

This doc describes the method: what the store's capacity inventory measures, how its numbers size the Postgres and Parquet/DuckDB design, and which numbers come from the live readback. The numbers themselves are dated evidence and are recorded in the installation's own task database, not here. The target data model the sizing applies to is db/postgres/.

Why measure first

NoticeOS did not record how big its store is or how fast it grows, and an estimate from the code alone is wrong in both directions:

• Raw provider pages are not store growth. They are gzip objects in the raw archive (R2); the store keeps one manifest row per attempt (archive_runs), with each stored object named once in archive_objects.
• Normalized metrics are stored as changes, not copies.signal_observations appends a value only when a (date, metric) is new or revised (workers/ingest/src/signal-store.ts), so its growth is far below "sites × metrics × days × runs".
• Insight snapshots are the opposite. Their content hash covers generatedAt, so every newly generated report is a new row of up to 1 MB (workers/ingest/src/insight-snapshots.ts), and only the two newest rows per site are ever read: the site page reads the newest, and the Wall's feed compares it with the one before (apps/tower/worker/wall-feed.ts).

The inventory measures the Postgres operational store. The inventory does not change retention or migrate anything.

What the inventory measures

pnpm os:capacity runs inside the app container and sends GET /api/capacity to the running OS over the loopback-only ingest endpoint, with the operator bearer token the other script reads use. The ingest runtime, which holds the store connection, returns the result; the script opens no database connection (scripts/ingest-door.mjs). pnpm os:capacity -- --json prints the raw response.

The response is metadata only — table and column names, counts, byte totals and first/last arrival timestamps. No stored value leaves the store. Every statement is a SELECT, inside an application-role read-only transaction, run one table at a time. Row security scopes every count and column-byte sum to the request's workspace. Physical database, relation and bucket sizes are reported only when that workspace is the store's sole workspace; they are unavailable on a shared store, where physical sizes would reveal other workspaces' activity. Raw bucket objects are never downloaded.

Figure	How it is measured
Database size	pg_database_size(current_database()); unavailable on a shared store
Physical bytes per table	pg_total_relation_size, including heap, indexes and TOAST; unavailable on a shared store
Stored bytes per table	Sum of pg_column_size() over every column in the workspace's rows, including stored compression. Excludes indexes and page overhead, which are reported together as unattributed; on a shared store the difference is unavailable
Largest column	The column holding most of a table's bytes — where compression and TOAST matter
Rows added per day	Rows whose arrival stamp falls in the last 30 days, divided by the days actually covered (a table created ten days ago grew for ten days)
Bytes added per day	The same window, summed row bytes
Arrival stamp	The column named in db/postgres/tables.json; signal_observations joins its run by (workspace_id, run_seq) and uses that run's finished_at
Table kind	history keeps every row; state holds one row per thing; cache deletes old rows as it adds new ones. Store growth adds history tables only
Missing tables	Catalogued tables the store does not have: migrations not applied
Insight snapshots	Per site: rows, uncompressed payload bytes, largest payload, rows and publish days in 30 days; the two newest rows per site (read) against every older row still present (kept, unread)
Raw archive	From the manifests: runs by outcome, and distinct objects and bytes per integration (an unchanged run reuses an object, which is counted once). From listing the bucket: objects and bytes per producer prefix, stopping after 100,000 objects
Job durations (lanes)	p50, p95 and max of each scheduled job's runs over 30 days (job_runs) — one scheduled operation end to end
Full-scan time	Elapsed time of each table's read transaction, including its connection and query, and their sum
Newest backup	File count and bytes of the newest dated backup folder, from stat alone

A table added by a migration without a db/postgres/tables.json row is still measured as undeclared, with no growth rate; scripts/postgres-model.test.mjs fails until the row is added.

The catalog treats retained insight snapshots as cache: each site's two newest stay and exported older rows may be removed from Postgres under the approved retention rule. Export acknowledgements, numbering counters, measurement-series keys, capability targets and item dispositions are state. History growth therefore excludes those bounded state tables and caches.

From the inventory to a sizing

For each table, with r = rows, b = stored bytes, Δr/Δb = rows and bytes added per day:

1. Projected size at a horizon of h days (1 and 3 years):
   • history: b + Δb × h.
   • cache: steady state, Δb × retention days (the oldest row's age shows the retention actually applied).
   • state: b, scaled by the number of sites if it is per site.
2. Postgres physical storage: compare each table's measured relationBytes with its scoped valueBytes. Physical bytes already include indexes, page overhead and TOAST; column bytes already reflect stored compression. For projection, scale the measured physical-to-value ratio, measured on a representative populated copy, rather than adding a second workspace-id or compression allowance.
3. Shared-store sizing: collect physical sizes from an isolated copy or an explicitly authorized host administration read, then combine them with the scoped per-workspace growth figures. Request-scoped capacity never exposes another workspace's physical totals.
4. Parquet: rows of daily metrics and manifests compress to a small fraction of their stored bytes; the ratio is measured, not assumed, by writing one month of each dataset from an isolated backup copy with DuckDB and dividing.
5. Per site and per workspace: divide each table's Δr by the number of sites that produce it. A self-hosted install is one workspace; a hosted database is the sum over workspaces, each sized by its sites.
6. Change detection: signal_observations rows per day over signal_runs rows per day is the observations a run adds. Compared with the full-series figure, it is the storage that change detection saves, and that saving must continue after the move.
7. Snapshot retention: kept but unread bytes are what a snapshot retention rule would reclaim. Measured only — changing retention is an operator decision. The Postgres rule is that each site's two newest stay and older ones move to the analytical store (db/postgres/).

The capacity envelope is then, per store: the projected 3-year size, the largest payload, the slowest scheduled job's p95, and the newest backup's size and restore time — each with the headroom the chosen hosting leaves.

The live readback

The inventory has been tested against a synthetic store (workers/ingest/test/capacity.test.ts, scripts/os-capacity.test.mjs). The live numbers come from one run on the installation after the change is deployed:

pnpm os:capacity -- --json

The readback records these figures, with the date, in the installation's own task database:

• database size, stored bytes and unattributed bytes; tables and missing tables;
• per table: kind, rows, bytes, rows and bytes per day, largest column;
• insight snapshots: rows, bytes, per day, read against kept-but-unread;
• raw archive: objects and bytes per integration and per day, and whether the bucket listing agrees with the manifests;
• p50/p95/max per scheduled job, and the full-scan time;
• the newest backup's size.

Two figures need a step that the inventory does not perform and are recorded with the same readback: the restore time (time restoring the newest backup into an isolated scratch folder, the drill in scripts/README.md), and the Parquet compression ratios behind step 4 (measured on that same isolated copy).