peter
85679b5e78
For the coverage-aware holiday-calendar API in TSAPI core (db_base holiday_calendar__get / __set_coverage / __set_metadata / __upsert). - meta.holiday_calendars (name, description, covered_from, covered_to, source, updated_at): a calendar exists when it has a row here, not when it has holidays (retires the 9999-12-31 sentinel row). covered_from/to: the range over which the closure list is complete; both set or both NULL, from <= to (holiday_calendars_coverage_check). - Calendar names are citext in every table (registry, holidays.calendar_name, exchanges.default_holiday_calendar, instruments.holiday_calendar): stored as given, unique and matched case-insensitively -- also in the foreign keys -- as the native TSAPI backend does with COLLATE NOCASE. New extension citext (00_extensions.sql). Name check: non-empty, trimmed, <= 50 characters. - FKs: holidays -> registry ON DELETE CASCADE; exchanges/instruments -> registry ON DELETE RESTRICT (a calendar in use cannot be dropped); ON UPDATE CASCADE everywhere. - meta.series.num_records dropped. Only some write paths maintained it (never C++), it sometimes held an estimate, and it was NULL for a series never written to. Clients ask the table: count(*), or SELECT EXISTS (SELECT 1 FROM <table>) for "is it empty?". - find_schema_problems() covers the new table, columns, constraints and extension, and runs with search_path = pg_catalog so format_type() reports 'public.citext' whatever the caller's search_path. Verified on a fresh database (PG 17): run_setup.py installs it and find_schema_problems() returns zero rows, also for a caller without public on the search_path; constraint behaviour checked by hand (case-insensitive duplicate refused, FK matches any spelling, RESTRICT and CASCADE, coverage and name checks). Re-provision databases (no migration). Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
146 lines
6.5 KiB
Markdown
146 lines
6.5 KiB
Markdown
# TSAPI_SERIESDB_SCHEMA — canonical timeseries DB schema
|
||
|
||
**Single source of truth** for the PostgreSQL schema used by the TSAPI
|
||
timeseries database. Both client libraries target the schema defined here:
|
||
|
||
- **Python** — `TSAPI_SERIESDB_PG_PY`
|
||
- **C++** — `TSAPI_POSTGRES_CPP`
|
||
|
||
Neither project should keep its own copy of the DDL. When the schema changes,
|
||
it changes **here**, and both projects re-provision / re-test against it.
|
||
|
||
Current schema version: **1.5.0** (see [`VERSION`](VERSION); recorded at runtime
|
||
in the `meta.schema_version` table).
|
||
|
||
---
|
||
|
||
## Layout
|
||
|
||
```
|
||
TSAPI_SERIESDB_SCHEMA/
|
||
├── VERSION # canonical schema version string
|
||
├── run_setup.py # provisioner — runs the numbered files in order
|
||
├── db_config.json.example # copy to db_config.json and edit (gitignored)
|
||
├── README.md
|
||
└── sql/
|
||
├── 00_extensions.sql # pg_trgm, citext
|
||
├── 01_schemas.sql # meta / series / log schemas
|
||
├── 02_sequences.sql # id sequences
|
||
├── 03_tables.sql # meta.* and log.* tables
|
||
├── 04_indexes.sql # indexes + registry constraints
|
||
├── 05_procedures.sql # meta.* / log.* stored procedures
|
||
├── 06_triggers.sql # prevent_series_drops event trigger
|
||
├── 07_reference_data.sql # timezones + currencies + security types
|
||
├── 08_script_log.sql # log.script_log table + view
|
||
├── 09_schema_check.sql # meta.find_schema_problems()
|
||
└── 10_schema_version.sql # records VERSION in meta.schema_version
|
||
```
|
||
|
||
Every fresh install runs `00`–`10` in order (`run_setup.py`, and the Python
|
||
client's `init_postgres_db()`). There are no migration scripts: a database
|
||
from an older schema is re-provisioned, not upgraded.
|
||
|
||
---
|
||
|
||
## Provisioning a database
|
||
|
||
```bash
|
||
# 1. configure
|
||
cp db_config.json.example db_config.json # then edit host/user/password
|
||
# (or pass connection flags on the command line instead)
|
||
|
||
# 2. create the schema
|
||
python run_setup.py # uses db_config.json / defaults
|
||
python run_setup.py --host 192.168.1.7 --user claude --password claudepwd
|
||
python run_setup.py --drop-existing # drop the existing installation first
|
||
# (ALL series data); user schemas untouched
|
||
```
|
||
|
||
The database may hold the user's own schemas and tables; provisioning never
|
||
touches them. It **refuses** a database that already holds the library's
|
||
objects -- `meta`, `log`, a non-empty `series` schema, or the
|
||
`prevent_series_drops` event trigger -- and reports the installed version.
|
||
There are no migrations: re-provision with `--drop-existing` (deletes all
|
||
series) or use another database. All files run in one transaction, so a
|
||
failure leaves the database untouched.
|
||
|
||
Requires `psycopg2` and a PostgreSQL user with CREATE privileges on the target
|
||
database.
|
||
|
||
---
|
||
|
||
## What gets created
|
||
|
||
- **Schemas:** `meta` (registry + reference data), `series` (timeseries data
|
||
tables live here), `log` (event/audit trail).
|
||
- **Registry:** `meta.series` — one row per series table, PK `table_id`,
|
||
`UNIQUE (db_schema, table_name)`.
|
||
- **Reference tables:** `meta.currencies`, `meta.timezones`, `meta.exchanges`,
|
||
`meta.instruments`, `meta.sessions`, `meta.holidays`, `meta.security_types`,
|
||
`meta.schema_version`.
|
||
- **Holiday calendars (1.5.0):** `meta.holiday_calendars` is the registry -- a
|
||
calendar exists when it has a row there. It records the coverage range
|
||
(`covered_from`/`covered_to`: the closure list is complete over it),
|
||
`description` and `source`. Calendar names are `citext` in every table:
|
||
stored as given, unique and matched case-insensitively. Foreign keys from
|
||
`meta.holidays` (cascade on delete), `meta.exchanges` and `meta.instruments`
|
||
(a calendar still in use cannot be dropped). Clients pass names as untyped
|
||
parameters (never `::text`) and keep `public` on the `search_path`, so
|
||
comparisons use the case-insensitive `citext` operators.
|
||
- **Procedures:** `meta.drop_series` / `_if_exists` / `drop_all_series`,
|
||
`meta.update_series_last_timestamp`, `meta.refresh_series_timestamps`,
|
||
`meta.read_series_batch[_cursor|_ohlc]`, `log.notify_event_log`,
|
||
`log.flush_pending_to_event_log`.
|
||
- **Event trigger:** `prevent_series_drops` — blocks direct `DROP TABLE` on a
|
||
registered series (drops must go through `meta.drop_series*`).
|
||
- **Self-check:** `meta.find_schema_problems()` — see below.
|
||
|
||
Series data tables have **no `UNIQUE` constraint on `timestamp`** — the index is
|
||
BRIN. Concurrent writers must serialise per-series with the advisory-lock
|
||
protocol (`pg_advisory_lock(hashtext('tsapi_pgdb.write:{schema}.{table}'))`); see
|
||
the C++ project's `planning/2026-09-02_cpp_locking_interop_plan.md`.
|
||
|
||
---
|
||
|
||
## Checking a database
|
||
|
||
```sql
|
||
SELECT * FROM meta.find_schema_problems(); -- zero rows = complete and usable
|
||
```
|
||
|
||
One row per problem, `(object, problem)`, for example
|
||
`('table meta.instruments', 'missing columns: sector')`. Read-only. It checks
|
||
schemas, the extension, sequences, every table column's type and NOT NULL,
|
||
constraints, indexes, views, stored-function bodies (whitespace-insensitive
|
||
fingerprint), the event trigger, reference-data row counts and that a version
|
||
row exists. If the function itself does not exist, the database is not
|
||
initialized or predates it -- re-provision.
|
||
|
||
`09_schema_check.sql` mirrors `00`–`08`: **change it in the same commit as any
|
||
of them**. The release test -- a freshly provisioned database must return
|
||
zero rows -- fails until you do. The file's header shows how to refresh the
|
||
function-body fingerprints.
|
||
|
||
---
|
||
|
||
## Versioning contract
|
||
|
||
Bump [`VERSION`](VERSION) -- and the string in `10_schema_version.sql` -- whenever
|
||
`sql/` changes in a way clients must know about. Each client compares
|
||
`meta.schema_version` with the version it was built for on connect, and calls
|
||
`meta.find_schema_problems()` for everything else.
|
||
|
||
---
|
||
|
||
## Provenance
|
||
|
||
The `sql/` files were seeded (byte-for-byte, 2026-09-02) from the then-current
|
||
authoritative copy in `TSAPI_SERIESDB_PG_PY/tsapi_pgdb/sql/`. From this point on,
|
||
**this** directory is authoritative and the Python project should consume it
|
||
rather than keep its own copy — see the migration note in the C++ project's
|
||
`planning/` folder.
|
||
|
||
> **Known drift caught during setup:** on 2026-09-02 the remote instance
|
||
> `192.168.1.7` had `meta.sessions.exchange_id` as nullable, while the canonical
|
||
> DDL (`03_tables.sql`) defines it `NOT NULL`. Re-provision drifted databases
|
||
> from these files.
|