# Oracle 26ai schema — TuneVault

Generated from the **live Neon schema** (PostgreSQL 16.14, 107 tables, 1,300 columns), not from
`db/baseline-schema.sql` — the baseline is 9 tables and 10 columns behind production because
`ensureColumns()` creates objects at runtime.

**Status: LIVE on Oracle.** Schema applied, all data migrated, and the app now reads/writes the ADB
(`TUNEVAULT_DB=oracle`). Cutover completed ~2026-07-24 evening; Neon + Render remain up as the
rollback lever, no longer written. See "Cutover — completed & verified data-clean" below.

## Files — run in this order

| file | contents |
|---|---|
| `schema.sql` | 107 `CREATE TABLE` — columns, defaults, identity, PK, UNIQUE, CHECK |
| `constraints.sql` | 106 foreign keys (separate so table order doesn't matter) |
| `indexes.sql` | 182 indexes incl. 33 function-based (was: partial) + 1 vector index |
| `drop-all.sql` | tears the whole schema down; each drop is independently exception-guarded |
| `migrate-data.js` | copies data Neon → Oracle (`--truncate` to make a re-run replace) |
| `migrate-vectors.js` | copies `oracle_doc_chunks` incl. pgvector → native `VECTOR` (`--resume`) |
| `verify-migration.js` | independently re-counts and re-reads both sides |
| `parity-check.js` | live Neon↔Oracle schema parity — tables/columns, CHECK, NOT NULL, DEFAULT; re-run pre-cutover |
| `gap-recon.js` | read-only pre-cutover delta recon — Neon-only rows in the cutover window, by timestamp count |
| `static-verify.js` | read-only content diff of the no-timestamp tables gap-recon can only total-count |

## Status

- **Schema: APPLIED** to the ADMIN schema of `tunevaultdb` (2026-07-23). 395/395 statements.
- **Data: MIGRATED — all 107 tables, 33,236 rows.** 106 tables / 10,960 rows via `migrate-data.js`,
  plus `oracle_doc_chunks` 22,276 rows via `migrate-vectors.js`.
- **Cutover complete (~2026-07-24 evening):** the app reads/writes Oracle. Neon + Render remain up
  as the rollback lever but are no longer written. Reconciliation confirmed no transactional data was
  lost in the flip — see "Cutover — completed & verified data-clean" below.

## How this was verified

Every statement was executed against the real ADB 26ai instance in a throwaway schema
(`TV_DDL_VALIDATE`), then the schema was dropped. **395/395 statements succeeded**, and a
column-level parity check confirmed **0 missing / 0 extra columns** across all 107 tables. The ADMIN
schema was never written to.

Behaviour was tested too, not just syntax — 19 assertions, all passing:
- the two unique partial indexes reject real violating rows (see item 3);
- the lowercase shim, `text[]`→JSON round-trip + `JSON_TABLE` membership, and the `run_trigger`
  rename incl. its surviving CHECK constraint.

## Type mapping

| PostgreSQL | Oracle 26ai | note |
|---|---|---|
| `integer` / `bigint` / `smallint` | `NUMBER(10)` / `NUMBER(19)` / `NUMBER(5)` | |
| `boolean` | `BOOLEAN` | native in 23ai+; no `NUMBER(1)` workaround needed |
| `jsonb` / `json` | `JSON` | native binary JSON |
| `text[]` | `JSON` | **array semantics change — see below** |
| `text` | `VARCHAR2(4000 CHAR)` or `CLOB` | decided per column, see below |
| `character varying(n)` | `VARCHAR2(n CHAR)` | `CHAR` semantics — the DB is `NLS_LENGTH_SEMANTICS=BYTE` |
| `timestamptz` / `timestamp` | `TIMESTAMP(6) WITH TIME ZONE` / `TIMESTAMP(6)` | |
| `uuid` | `VARCHAR2(36 CHAR)` | |
| `inet` | `VARCHAR2(45 CHAR)` | |
| `vector(1536)` | `VECTOR(1536, FLOAT32)` | native; pgvector not needed |
| `connectivity_mode_enum` | `VARCHAR2(20 CHAR)` + `CHECK` | Oracle has no enum type |
| `nextval(...)` default | `GENERATED BY DEFAULT AS IDENTITY` | |
| `now()` default | `SYSTIMESTAMP` | |
| `now() + interval` | `SYSTIMESTAMP + INTERVAL 'n' UNIT` | 3 columns |
| `gen_random_uuid()` | `SYS_GUID()` reformatted to UUID shape | |

Result: 458 `VARCHAR2`, 371 `NUMBER`, 224 `TIMESTAMP WITH TIME ZONE`, 93 `CLOB`, 77 `JSON`,
59 `BOOLEAN`, 16 `TIMESTAMP`, 3 `DATE`, 1 `VECTOR`.

### text → VARCHAR2 vs CLOB
Decided from **observed data**, not guesswork: `MAX(LENGTH(col))` was measured on the live table.
A column becomes `CLOB` if it is freeform by name (`content`, `stdout`, `traceback`, `sql_text`, …)
or holds > 2,000 characters. **A column referenced by any constraint or index is never CLOB** —
Oracle rejects a LOB as a comparison key (ORA-22848) and cannot index one. That rule is derived
from the real constraint/index definitions, so it holds no matter how a column is named.

163 of 357 text columns had data to measure; the other 194 live in empty tables and defaulted to
`VARCHAR2(4000 CHAR)`. If any of those turns out to hold longer values, widen it before the
data load, not after.

## Decisions (settled 2026-07-23)

**1. Identifiers are unquoted — there are ZERO quoted identifiers in this DDL.**
Oracle folds them to UPPERCASE, so `node-oracledb` returns row keys as `USER_ID`. The shim in
`db/oracle-client.js` (`lowercaseRows`, applied inside `query()`) converts them back, so all ~72
`db/` modules keep reading `row.user_id` unchanged. The key map is built once per result set from
`metaData`, not per row.

Two consequences to remember:
- `getConnection()` hands back a **raw** connection that bypasses `query()`. Its rows come back
  UPPERCASE-keyed — pass them through the exported `lowercaseRows()`.
- Because nothing is quoted, two identifiers had to be renamed (items 5 and 6 below).

**2. `text[]` → `JSON` — confirmed for all 8 columns.** `agent_diagnose_runs.detected_sids`,
`.listener_services`, `agent_tunnels.oracle_sids`, `.pdb_services`, `agents_log_buffer.lines`,
`fndload_history.object_names`, `tns_topology_snapshots.service_names`, `.patch_services`.
App work this creates: write with `JSON.stringify(array)`, and replace Postgres `'x' = ANY(col)`
membership tests with `JSON_TABLE`, e.g.

```sql
SELECT COUNT(*) FROM agent_tunnels t,
       JSON_TABLE(t.oracle_sids, '$[*]' COLUMNS (sid VARCHAR2(64) PATH '$')) j
 WHERE j.sid = 'DEV'
```
Both the round-trip and that query shape were tested against the live DB.

**3. Partial indexes became function-based `CASE` indexes.** Oracle has no `WHERE` clause on an
index; the equivalent is `CASE WHEN <pred> THEN col END`, which stores nothing for non-matching
rows. 33 indexes converted. Two of them are UNIQUE and enforce real business rules, so they were
tested by inserting violating rows rather than assumed:

- `users_google_id_idx` — duplicate `google_id` rejected (ORA-00001); multiple NULLs still allowed.
- `self_hosted_licenses_one_free_per_user` — a second free+active licence for the same user is
  rejected, while free+expired, paid+active, and free+active-for-another-user are all still
  accepted. This is the DB-level guard behind the one-free-trial-per-user rule.

**4. 13 redundant indexes were dropped.** Postgres allows an explicit index that duplicates a
UNIQUE constraint's implicit index; Oracle raises ORA-01408. Oracle's constraint index covers
the same access path, so nothing is lost.

**5. `_migrations` → `migrations` (RENAMED).** Oracle unquoted identifiers cannot start with `_`,
and this port carries no quoted identifiers, so the table and its constraints were renamed
(`_migrations_pkey` → `migrations_pkey`, etc.). Nothing reads it on Oracle — it belongs to
`node-pg-migrate`, which is Postgres-only — so it is a candidate for deletion rather than migration.

**6. `connection_health_runs.trigger` → `run_trigger` (RENAMED).** `TRIGGER` is an Oracle reserved
word; quoting it would put `"TRIGGER"` in every query touching the table forever. Its CHECK
constraint (`sweeper|manual`) carried over intact and was verified to still reject bad values.

**App call sites that must be updated when queries are ported:**
- `db/connection-health.js` — `insertHealthRun({... trigger })` param, the INSERT column list, and
  the `trigger || 'manual'` bind.
- `routes/connections-list.js` — 3 call sites passing `trigger:` (lines ~575, ~611, ~648).

**7. The vector index is a guess that needs tuning.** pgvector `ivfflat (lists=100)` became
`ORGANIZATION NEIGHBOR PARTITIONS DISTANCE COSINE WITH TARGET ACCURACY 95`. Cosine matches the
existing `vector_cosine_ops`, but accuracy/partition counts should be tuned against real RAG query
latency on the 22,276-row `oracle_doc_chunks` table.

**8. Sequences start at 1.** Identity columns need `ALTER TABLE ... MODIFY (id GENERATED BY DEFAULT
AS IDENTITY (START WITH <max+1>))` after the data load, or the first insert collides.

**9. `ON UPDATE` clauses on FKs are dropped** — Oracle has no `ON UPDATE`. `ON DELETE CASCADE` and
`ON DELETE SET NULL` are preserved.

## Data migration — what the first run taught us

**Two Postgres behaviours have no Oracle equivalent, and both only surface with real data.**

**1. Oracle cannot store `''` as a non-NULL value.** It folds the empty string to NULL, so a
Postgres `NOT NULL` text column that legitimately holds `''` is unmigratable — the insert dies with
`ORA-01400` and the row is simply lost. `connection_schedules.alert_email` had one such row.
Every `NOT NULL` text column in Neon was scanned for `''`; that is the only one. Its `NOT NULL` is
relaxed in `schema.sql` (see `NULLABLE_OVERRIDES` in the generator) and the value lands as NULL,
which is behaviour-preserving because `''` and NULL are both falsy wherever `alert_email` is read.
**Re-scan before the final cutover** — a new `''` in any NOT NULL text column will fail the same way.

**2. FK ordering alone is not enough.** `users.team_id → teams` and `teams.owner_id → users` form a
genuine cycle, and `roles.approval_required_from` is self-referencing, so no parent-first ordering
exists. The loader sorts topologically *and* disables FKs for the load, then re-enables them
`ENABLE VALIDATE`, which makes Oracle verify every row already present. Integrity is proven at the
end rather than assumed from the ordering — all 106 came back validated.

**Identity sequences** are reset to `max(id)+1` after the load (`resetIdentitySequences`). Without
that, every table loads fine and then the first application insert collides on the PK.

**Comparing the two databases has two traps**, both of which produced phantom failures first time:
- `LENGTH()` is not comparable across engines. Postgres counts codepoints, Oracle counts UTF-16
  units, so a 🔴 in an AI summary reads as 1 vs 2. Compare content by hash, not length.
- **Neon is still live**, so tables like `agent_command_queue` legitimately gain rows after the copy
  (+27 during the first run). `verify-migration.js` treats extra rows *above* Oracle's high-water
  mark as drift and only fails on rows missing at or below it. **A final delta sync is required at
  DNS cutover** for any table still being written.

## Schema parity audit (pre-cutover, 2026-07-25)

After the query migration, the **live ADB** was re-audited against **live Neon** — a stricter check
than the DDL-time column count, because it diffs the two running databases and covers constraints,
not just column presence. Read-only except the one fix noted below.

**Tables + columns: clean.** All 107 Neon tables and every column exist on the ADB (0 missing, 0
columns missing). The 3 Oracle-only objects are internals, not app schema —
`dbtools$execution_history` and the two `vector$…ivf_flat_centroid*` shadow tables behind the
`oracle_doc_chunks` vector index.

**CHECK constraints: clean.** No value the app writes on Neon is rejected by an Oracle CHECK. Enum
CHECKs were compared as allowed-value *sets* (not clause text, which differs by engine); the
numeric/range CHECKs (only 2 in the whole schema) match. Some enum CHECKs look "Oracle-only" purely
because Postgres enforces the same domain via an ENUM *type*, which is not a CHECK — e.g.
`oracle_connections.connectivity_mode` (`tns|ssh_sqlplus|both`), and the numeric
`email_drip_state.sequence_step ∈ {1,2,3}` (`= ANY(ARRAY[…])` on Neon ≡ `IN (…)` on Oracle).

**NOT NULL: two deliberate relaxations remain, both `''`-driven and behaviour-preserving:**
- `connection_schedules.alert_email` — a migrated `''` row (see the data-migration section, item 1).
- `user_mfa.totp_secret` — `disableMfa()` writes `''` at runtime to clear the secret, which would
  `ORA-01407` against a NOT NULL column; relaxed in `schema.sql` with an inline comment.

**One drift found and fixed — `agent_restart_events.reason_code`.** The live ADB had it NULLABLE
while `schema.sql`, Neon, and the app all treat it as NOT NULL — the writer (`routes/agent.js`
`POST /restart-reason`) returns early on a falsy `reason_code`, so a null can never be inserted, and
the table is empty on both sides, so no `''` data forced it. Accidental drift, **not** an override:
corrected on the ADB with `ALTER TABLE agent_restart_events MODIFY (reason_code NOT NULL)`. It is
deliberately absent from `NULLABLE_OVERRIDES`. After the fix, exactly the two relaxations above
remain.

**DEFAULT presence: clean** — no column defaulted on one engine and not the other.

This is the "re-scan before the final cutover" that the data-migration section calls for, done once.
**Repeat it immediately before the DNS flip:** Neon is still live, so a new `''` in a NOT NULL text
column — or a newly added migration — can reintroduce drift. Re-run with
`node -r dotenv/config db/oracle/parity-check.js` — all three phases (tables/columns, CHECK enum +
NOT NULL + DEFAULT, and numeric CHECKs) in one script; it connects to both DBs, prints the benign
documented differences as INFO, and exits non-zero on any cutover-blocking gap.

## Cutover — completed & verified data-clean (2026-07-25)

The app is **live on Oracle**: `TUNEVAULT_DB=oracle` on the OCI VM, and fresh production writes land
on the ADB (a health check ran ~13 min before this was written; Neon has been read-only since its
last write at **2026-07-24 17:54**). This was confirmed empirically, not from config — by comparing
write-recency of high-churn tables (`health_checks`, `analytics_events`, `agent_channel_state`)
across both DBs; Oracle is current, Neon is frozen.

**The pre-cutover window was reconciled (read-only) and is data-clean.** Bulk copy was 2026-07-23;
Neon kept taking writes until ~07-24 17:54; the planned delta sync was **not** run at the flip, so
every table was checked for Neon rows in `[2026-07-23 → 2026-07-24 17:54]` not present on Oracle.
Identity sequences were reset at migration, so Neon's gap-window ids now **collide** with Oracle's
post-cutover ids — comparison was therefore by **insert-timestamp count** (90 tables) and by
**content / key-set diff** (13 tables with no insert timestamp), *never by id*.

Result — **no transactional or business data lost.** The only Neon-only rows are two ephemeral,
non-transactional tables, and neither warrants a sync:
- `connection_health_runs` — 100 rolling 8-probe diagnostic snapshots from 07-24 afternoon, already
  superseded by 100 fresh post-cutover rows (the table is continuously rewritten; fleet-health dots
  read the latest row per connection, which is current on Oracle).
- `analytics_events` — 7 funnel-tracking events (`page_view`/`cta_click`/…), i.e. marketing telemetry.

Everything transactional (`health_checks`, `payments`, `subscriptions`, `user_credits`,
`tuneops_tickets`, `users`, `oracle_connections`, `agent_command_queue`, …) reconciled exactly.

> **Do NOT run a Neon→Oracle delta sync now — it is backwards.** Oracle is ~a day ahead; a plain sync
> would insert stale/duplicate rows, and `migrate-data.js --truncate` would **destroy** the live
> post-cutover data. The rollback lever is the env flip (`TUNEVAULT_DB=postgres`) or DNS back to
> Render, not a re-sync. If rollback is ever used, Oracle-only writes since cutover would need the
> same additive, id-agnostic reconciliation in reverse.

## The vector table (`oracle_doc_chunks`, the RAG store)

22,276 rows migrated from pgvector to Oracle's native `VECTOR(1536, FLOAT32)` — **bit-exact**, no
NULL embeddings, index rebuilt, approximate nearest-neighbour search returning in ~120 ms.

Four things this table needs that the rest of the schema does not:

- **Paged reads.** pgvector returns each embedding as a ~19 KB text literal, so `SELECT *` over the
  table pulls ~430 MB into one result set. Rows are read by keyset pagination (`id > last`), so
  memory stays flat.
- **Timeouts are load-bearing.** The first run hung indefinitely at 2,500 rows: Neon dropped the
  connection and **node-postgres has no default query timeout**, so the read never returned *and
  never errored*. Oracle's session sat idle on `SQL*Net message from client` for 11 minutes, which
  is how you tell the stall is upstream of Oracle. The pool now sets `query_timeout`,
  `statement_timeout`, `connectionTimeoutMillis` and `keepAlive`, and page reads retry. `--resume`
  continues from Oracle's highest loaded id rather than starting over.
- **The vector index must be built AFTER the load.** It was originally created on an empty table,
  so its IVF centroids were derived from nothing. `migrate-vectors.js` drops it, loads, then
  recreates it (31 s on 22k vectors) so centroids come from real data.
- **Comparing floats needs `Math.fround`.** pgvector's text form parses to a float64 that is *not*
  equal to the float32 it represents, so comparing a raw parse against Oracle's `Float32Array`
  shows a phantom ~1 ULP difference on every element. Put both sides in float32 first. (Confirmed
  independently: `embedding::text` and `array_to_json(embedding::real[])` yield identical float32s,
  so the text form is not lossy.)

Throughput was ~73 rows/s at batch 50 / page 500; the full table takes ~5 minutes.

## Not covered here

- `services/agent-channel.js` uses `LISTEN`/`NOTIFY`, which has no Oracle equivalent — it needs
  redesign onto AQ or polling. `SELECT FOR UPDATE SKIP LOCKED` ports unchanged.
- Query translation across the ~72 `db/` modules.

Regenerate with `scratchpad/gen-ddl.js`; re-validate with `scratchpad/validate-ddl.js`.
