# Data Center How-To — browsing and querying your data (read-only)

The Data Center is the platform's read-only console over every registered
SQLite database. In the shipped product it has **four tabs — Schema, Table
browser, SQL editor, Integrity** — under a store-health summary and the
registered-database table. The console can never modify your data: read-only
is enforced at four independent layers (§2).

What it does **not** have, so you are not hunting for them: no ER diagram and
no foreign-key rendering (§6), no Export tab, and no XLSX/Parquet button — the
UI's export is CSV, and §8 says exactly which rows each button gives you.

Companion guides: [Data Guide](DATA_GUIDE.md) (what the tables mean) and
[Reading the Stats](READING_THE_STATS.md) (how to interpret the numbers).

---

## 1. Opening the Data Center

**Packaged install (the .exe from the wizard) — the web terminal.** The Data
Center ships inside the **web terminal**, which is what the installer launches
by default. Open the sidebar → **Data Center** group → **Data Center**. The
terminal and the API are served single-origin on `http://127.0.0.1:8000`.

**Source checkout.** `python scripts/serve_web.py` builds the terminal if
needed and serves the same screen on `http://127.0.0.1:8000` (`--port` to move
it). If you are already running the main backend
(`python -m uvicorn server.main:app`), the same read-only API is mounted at
`http://127.0.0.1:8000/api/dc/...`.

> **Port 8001 is a developer server, not a product surface.**
> `scripts\setup\START_DATACENTER.bat` builds a *separate* front-end out of the
> `ui_next/` development tree (needs Node/npm) and serves it on
> `http://127.0.0.1:8001`. That front-end is **not** in the installer. So on a
> packaged install the desktop app's *Settings → Data Center → OPEN DATA
> CENTER* button does start the :8001 server, but the page you land on says the
> front-end "has not been built" — the read-only API is live there, the UI is
> not. Use the web terminal instead. Either way everything binds to
> `127.0.0.1`; nothing is reachable from the network.

The console's tabs are **Schema · Table browser · SQL editor · Integrity**,
with a database selector above them. The rest of the same screen carries the
store-health tiles, the registered-database table (§3), provider health and a
knowledge-store rollup; the sidebar's Data Center group adds **Knowledge
Store**, **LLM Monitor** and **Provider Health** as screens of their own.

**There is no live freshness ticker in this build.** Each tab fetches when you
open it and caches briefly (schema 60s, table rows 15s, integrity 30s); the
console does not watch the files and does not refresh itself when a training
run or a trade lands. Switch tabs or reload to re-read.
(`GET /api/dc/refresh-signal` still returns a freshness fingerprint if you want
to poll it yourself.)

## 2. Why it cannot write to your data

1. Every connection opens the file with SQLite's `mode=ro` URI flag (the VFS
   itself refuses writes).
2. `PRAGMA query_only=ON` is set on every connection.
3. The SQL editor accepts only `SELECT` / `WITH` / `EXPLAIN` statements —
   DDL/DML (`INSERT`, `UPDATE`, `DROP`, …), `ATTACH`, writable `PRAGMA`s,
   `load_extension`, and multi-statement input are all rejected before
   execution.
4. A path allow-list: only databases registered in the Data Center registry
   can be opened at all.

Those four layers cover everything the console does — schema, browsing, SQL,
integrity and export are all reads. **The console's UI has no write control of
any kind.**

For completeness, the `/api/dc` API does carry three POST families that no
console button calls, each behind a permission gate:

* **Backups** (`/backup-all`, `/{db}/backup`) — copy out; the source file is
  not changed.
* **Compact** (`/{db}/compact`) — this one really does write: it checkpoints
  the WAL and runs `VACUUM` on the file.
* **Supervisor** approve/dismiss and undo (`/supervisor/…`, permission
  `risk.limit.write`) — these route through the bounded, audited supervisor
  apply path (every change clamped, reversible, logged). The web terminal's
  Slide Doctrine screen renders the supervisor's recommendations and audit log
  read-only and deliberately exposes **no** apply or rollback control.

## 3. The database list — what the columns mean

The **Databases** table above the console shows each key, file size, table
count, integrity verdict, open issues, WAL size, **cadence**, last-modified
age, and whether the file exists on disk yet. (The console's own selector is
the short version: key — size · table count.) Cadence explains *why* a
database sits at its mtime:

* `live` / `daily-pipeline` — should look fresh; staleness here is meaningful.
* `event-driven` — written only on real activity (orders, bot runs). Quiet =
  correct.
* `on-demand` — caches/registries populated only when something asks.
* `weekly-manual` — TNN retrain output.
* `deprecated` — intentionally frozen; never pipelined "to look fresh".

See [Data Guide §1](DATA_GUIDE.md) for the full registry table.

## 4. Browsing tables

Pick a database above the tabs, then the **Table browser** tab → pick a table.
You get paginated rows, the total row count ("1–100 of 5,412"), and a CSV
button for the page you are looking at (§8). Page size is 50 / 100 / 250 / 500;
the API's own hard cap is 1,000 rows per request and 10 seconds per query, so
use pagination or a narrower query for big tables.

**This build has no click-to-sort in the grid** — rows arrive in SQLite's
natural order. Sorting is one tab away rather than one click: write an
`ORDER BY` in the SQL editor, or pass `order_by=`/`desc=` to the table endpoint
(§9). Column types, PKs and defaults live in the **Schema** tab (§6).

**Remember the two row-counting caveats** (details in
[Data Guide §2.6–2.7](DATA_GUIDE.md)):

* Raw `brain_signals` / `options_recommendations` rows include per-tick
  re-records and `superseded` tombstones. Row count ≠ number of predictions.
* Only `status='evaluated'` rows have graded outcomes; `open` rows are not
  wrong, just not graded yet.

## 5. The SQL editor — rules and tested examples

The **SQL editor** tab gives you a text box (Ctrl+Enter runs it), a chip per
table that drafts a `SELECT * FROM <table> LIMIT 100;` for you, a local history
of your last 25 queries for this database, and a CSV button on the result. When
the guard rejects a statement you see the server's own rejection text verbatim,
not a generic error.

Rules enforced by the guard:

* One statement per run; `SELECT` / `WITH` / `EXPLAIN` only.
* Results are capped at 1,000 rows (a `LIMIT` is appended automatically if you
  didn't write one; a "truncated" flag tells you when the cap hit).
* 10-second execution timeout.
* `LIMIT` placement is free-form — same line or its own line both work.
  (An older build's auto-LIMIT guard missed a `LIMIT` that started its own
  line and appended a second one, erroring the query; fixed 2026-07-02.)

Every query below was executed against the real databases on a live install
(2026-07-02) through the exact guard the console uses. Where the output is
thin, that is the honest state of a young install — noted inline.

### 5.1 Resolved signal performance by source (dedup-aware)

The `ROW_NUMBER` window collapses per-tick re-records to one observation per
standing prediction per day — the same collapse the platform's own statistics
apply. Database: `feedback_loop_ledger`.

```sql
SELECT source,
       COUNT(*)                      AS n_resolved,
       ROUND(AVG(was_correct), 3)    AS hit_rate,
       ROUND(AVG(outcome_return), 3) AS avg_fwd_return_pct
FROM (
    SELECT source, was_correct, outcome_return,
           ROW_NUMBER() OVER (
               PARTITION BY symbol, source, action, horizon,
                            SUBSTR(created_at, 1, 10)
               ORDER BY id
           ) AS rn
    FROM brain_signals
    WHERE status = 'evaluated' AND was_correct IS NOT NULL
)
WHERE rn = 1
GROUP BY source
ORDER BY n_resolved DESC
```

Example output (dev machine): `llm` n=93 hit 32.3%, `algo_bot` n=15 hit
26.7%, `neural` n=3. Small N — which is exactly why the platform stamps these
scopes `insufficient`/`luck` rather than quoting the hit rates as skill.

### 5.2 How much of the table is tombstones?

```sql
SELECT status, COUNT(*) AS n
FROM brain_signals
GROUP BY status
ORDER BY n DESC
```

Example output: 165 `superseded`, 116 `evaluated`, 37 `open` — over half the
raw rows are de-duplication tombstones kept for audit. This is why `COUNT(*)`
is never a prediction count.

### 5.3 Options hit-rate and risk-normalized P&L by strategy

Database: `feedback_loop_ledger`. Dedup window mirrors the platform's options
key (symbol, strategy, expiry, day).

```sql
SELECT strategy,
       COUNT(*)                       AS n_evaluated,
       ROUND(AVG(holds_true), 3)      AS hit_rate,
       ROUND(AVG(outcome_pnl_pct), 2) AS avg_pnl_pct_of_risk
FROM (
    SELECT strategy, holds_true, outcome_pnl_pct,
           ROW_NUMBER() OVER (
               PARTITION BY symbol, strategy, SUBSTR(expiry, 1, 10),
                            SUBSTR(created_at, 1, 10)
               ORDER BY id
           ) AS rn
    FROM options_recommendations
    WHERE status = 'evaluated' AND holds_true IS NOT NULL
)
WHERE rn = 1
GROUP BY strategy
ORDER BY n_evaluated DESC
```

Example output: `bull_call_debit` n=18 hit 0% avg −99.3% of risk;
`bear_call_credit` n=5 hit 100% +24.2%; five more strategies at n≤3. Read
with [Reading the Stats §4](READING_THE_STATS.md): every per-strategy scope
here is below the 20-observation verdict floor.

### 5.4 The latest edge verdict per scope

```sql
SELECT scope, ts, n, ROUND(hit_rate, 3) AS hit_rate,
       ROUND(t_stat, 2) AS t_stat, ROUND(p_value, 4) AS p_value,
       ROUND(sharpe, 2) AS sr, ROUND(psr, 3) AS psr, ROUND(dsr, 3) AS dsr,
       verdict
FROM edge_metrics e
WHERE ts = (SELECT MAX(ts) FROM edge_metrics WHERE scope = e.scope)
ORDER BY scope
```

`edge_metrics` rows are snapshots — this pattern picks the latest per scope.
Check `ts`: an old snapshot can predate methodology fixes. Scopes with n < 20
honestly carry `verdict='insufficient'` and NULL triplet columns.

### 5.5 POP calibration, coarse (do 80% calls hit 80%?)

```sql
SELECT ROUND(predicted_pop, 1)   AS pop_bucket,
       COUNT(*)                  AS n,
       ROUND(AVG(holds_true), 3) AS realized_rate
FROM options_recommendations
WHERE status = 'evaluated' AND holds_true IS NOT NULL
  AND predicted_pop IS NOT NULL
GROUP BY pop_bucket
ORDER BY pop_bucket
```

(The **Options Lab** screen's reliability curve — sidebar → Options → Options
Lab, fed by `/api/options/calibration` — is the polished version of this, and
it collapses duplicates first.) On the dev install the 0.8 bucket shows
n=8 realized 0% — a badly calibrated early recommender, shown honestly rather
than hidden. Note this coarse query does not dedup; expect it to differ
slightly from the dashboard.

### 5.6 Watch one options position evolve day by day

```sql
SELECT eval_date, spot, ROUND(est_value, 2) AS est_value,
       ROUND(pnl, 2) AS pnl, days_to_expiry, holds_true, final
FROM options_eval_log
WHERE rec_id = 42
ORDER BY eval_date
```

One row per day per recommendation (a UNIQUE index makes same-day re-runs
idempotent). `est_value` is a model re-price (Black–Scholes on spot history),
not a live chain quote.

### 5.7 Trial Ledger — the N behind every DSR

Database: `trial_ledger`.

```sql
SELECT strategy_family, source, COUNT(*) AS n_trials,
       MAX(SUBSTR(created_at, 1, 10)) AS last_trial
FROM trials
GROUP BY strategy_family, source
ORDER BY n_trials DESC LIMIT 20
```

Example output: `algo_bot:SWING` 416 backtests, `tnn_retrain` 25 retrains,
horizon families ~20 each — and `SELECT COUNT(*) FROM trials` gives the global
N (592 here) that any family without recorded history inherits. This is the
selection pressure your DSR is deflated against.

### 5.8 Why didn't the system act? (risk decisions)

Database: `brain_knowledge`.

```sql
SELECT allowed, reason, COUNT(*) AS n
FROM risk_decisions
GROUP BY allowed, reason
ORDER BY n DESC LIMIT 10
```

Example output: 900 blocks for `rate_limit:10>=10/10s`, 619 `ok` — the risk
layer's paper trail of every allow/block.

### 5.9 Resolved brain observations by action

Database: `brain_knowledge`. Always filter `resolved = 1` — unresolved rows
have NULL grades.

```sql
SELECT action, COUNT(*) AS n_resolved,
       ROUND(AVG(was_correct), 3) AS hit_rate,
       ROUND(AVG(fwd_return), 3)  AS avg_fwd_return
FROM observations
WHERE resolved = 1 AND was_correct IS NOT NULL
GROUP BY action
```

### 5.10 Audit the de-duplication yourself

```sql
SELECT symbol, source, action, SUBSTR(created_at, 1, 10) AS day,
       COUNT(*) AS raw_rows
FROM brain_signals
GROUP BY symbol, source, action, day
HAVING COUNT(*) > 1
ORDER BY raw_rows DESC LIMIT 10
```

Example output: `WMT algo_bot BUY 2026-06-17` = 18 raw rows; several 8–9×
clusters. Each cluster is ONE standing prediction that the statistics count
once. If you compute your own stats with raw SQL, group the same way.

### 5.11 Closed algo-bot trades by strategy

Database: `algo_trades`.

```sql
SELECT strategy, COUNT(*) AS n_closed,
       ROUND(AVG(CASE WHEN pnl > 0 THEN 1.0 ELSE 0.0 END), 3) AS win_rate,
       ROUND(SUM(pnl), 2) AS total_pnl
FROM trades
WHERE is_closed = 1
GROUP BY strategy
ORDER BY n_closed DESC
```

Honest note: on the dev install this returns **zero rows** — the bot has not
closed any trades there. Empty is the correct answer, not an error.

### Where data is thin (as of this writing)

* `flow_ratings`: thousands of `open` rows, zero `evaluated` — ratings only
  resolve when the daily evaluation pipeline runs and horizons elapse. An
  all-open backlog means "not graded yet", not "all correct".
* `llm_vetoes`: single-digit rows; veto-quality stats need ≥ 20 resolved.
* Most options strategy scopes: n ≤ 5. Expect `insufficient` verdicts.

## 6. Schema tab — and the one thing it does not draw

The **Schema** tab lists every table in the selected database, ordered by row
count. Pick one and you get its columns — name, declared type, PK flag, NOT
NULL, default value — plus the column count, row count, primary-key list and
index count.

**There is no ER diagram, and this build does not display foreign keys at
all.** Say it plainly rather than have you hunt for a tab: the relationship
view is a feature of the developer-only standalone front-end (§1), not of the
product. The data is not missing, only unrendered — `GET /api/dc/{db}/schema`
returns `declared_fks` and `inferred_fks` (name-inferred links such as
`options_eval_log.rec_id → options_recommendations.id`) and the console throws
those two arrays away. If you want them before writing a join, read the
endpoint directly (§9), or ask the SQL editor for the `CREATE TABLE` text,
whose `REFERENCES` clauses are the declared FKs:

```sql
SELECT name, sql FROM sqlite_master WHERE type = 'table' ORDER BY name
```

Run against `feedback_loop_ledger` this returns 9 tables, and exactly one —
`options_eval_log` — carries a `REFERENCES` clause: the declared FK in the
example above.

(`PRAGMA foreign_key_list(...)` will *not* work in the editor — the guard
accepts SELECT/WITH/EXPLAIN only and rejects PRAGMA by design.)

Declared FKs are not merely decorative elsewhere in the product: the
**Integrity** tab's foreign-key orphan check (`PRAGMA foreign_key_check`) is
what acts on them.

## 7. Integrity checks

The **Integrity** tab runs a read-only check catalog per database and reports
each check as pass/warn/fail with a severity (P0–P3), a one-line detail, and —
where the check found something — an expandable sample of offending rows with
its own CSV button:

* Generic SQLite health: `PRAGMA integrity_check`, foreign-key orphans,
  duplicate primary keys, NULLs in NOT NULL columns, empty tables, WAL file
  growth, timestamp sanity (e.g. future timestamps).
* Platform-specific checks, e.g. orphaned open positions and
  performance-vs-history reconciliation on the trade ledger.

A `warn` on an empty table in a young install is normal. Failures worth
attention are the P0/P1 ones — corruption, FK orphans, duplicate PKs.

## 8. Getting data out

**There is no Export tab in this build, and no XLSX or Parquet button.** What
the console gives you is three CSV downloads, each written from rows that are
already on your screen — so what you get is exactly what you can see, no more:

* **Table browser → CSV** — the page you are looking at (up to your page-size
  setting), *not* the whole table. Page through, or use one of the two routes
  below for a bigger pull.
* **SQL editor → CSV** — the rows the query returned, so at most the 1,000-row
  cap; the "truncated" badge tells you when the cap bit.
* **Integrity → expand a check → CSV** — that check's offenders *sample*, which
  is a sample and not the full population.

For a whole table, or for XLSX/Parquet, use the export endpoint. It runs
through the same read-only guard and is capped at 100,000 rows. Paste it into
the browser tab you already have the terminal open in:

```
http://127.0.0.1:8000/api/dc/export?db=feedback_loop_ledger&table=edge_metrics&fmt=csv
http://127.0.0.1:8000/api/dc/export?db=trial_ledger&sql=SELECT%20*%20FROM%20trials&fmt=csv
```

(Port 8001 instead if you are on the developer standalone server of §1.)

`fmt=csv` needs nothing extra. `fmt=xlsx` is written with the **`xlsxwriter`**
package and `fmt=parquet` with **`pyarrow`**; both are optional dependencies,
and if one is not installed the endpoint returns an error naming the missing
package rather than a file. CSV is the format that always works.

## 9. API quick reference (power users)

Every endpoint below is on the same origin as the web terminal —
`http://127.0.0.1:8000/api/dc/...` — and on `:8001` if you are running the
developer standalone server. Local only.

| Endpoint | What it returns |
|---|---|
| `GET /api/dc/databases` | Registered DBs with size, table count, cadence, mtime |
| `GET /api/dc/health` | Per-DB rollup: integrity verdict, issue count, WAL size, last backup |
| `GET /api/dc/{db}/schema` | Tables, columns, PKs — **plus `declared_fks` and `inferred_fks`, which the console does not render (§6)** |
| `GET /api/dc/{db}/table/{name}?limit=&offset=&order_by=&desc=` | Paginated rows — this is where the sorting the grid lacks lives |
| `POST /api/dc/{db}/query` body `{"sql": "SELECT ..."}` | Guarded query result |
| `GET /api/dc/{db}/integrity` | Integrity check results |
| `GET /api/dc/export?db=&table=\|sql=&fmt=csv\|xlsx\|parquet` | File download (§8) |
| `GET /api/dc/refresh-signal` | Freshness fingerprint — available, but nothing in this build polls it |
| `GET /api/dc/knowledge/* /algo/* /options/* /llm/* /portfolio/* /supervisor/*` | The rollups behind the Knowledge Store, LLM Monitor and Slide Doctrine screens |

The write-side POSTs (`/backup-all`, `/{db}/backup`, `/{db}/compact`,
`/supervisor/…`) are listed in §2. They are permission-gated and no console
button calls them.

## 10. Opening the files with other tools

The databases are plain SQLite files — any SQLite tool can read them. If you
do, open them **read-only** (`file:...?mode=ro` URI, or your tool's read-only
switch): the platform writes to these files in WAL mode while running, and an
external tool holding a write lock can stall it. The Data Center exists so you
never have to take that risk.