PRE-EXISTING BUGS FOUND DURING THE ORACLE QUERY MIGRATION
=========================================================

Bugs that exist in the CURRENT PostgreSQL/Neon code, found while translating db/
modules for Oracle. They are NOT caused by the migration and are NOT fixed in
that pass — they need their own session, with their own testing and route-layer
changes.

Format: module / function — symptom — evidence — what a fix would involve.

-----------------------------------------------------------------------------
RESOLUTION LOG (2026-07-24) — all 10 fixed on branch oci-query-migration, each
tested against the live ADB. Two UNDOCUMENTED bugs on the same report paths were
found while exercising #4-addendum/#5 end to end and fixed alongside them:
  #11 db/compliance-reports.js getSoxData — ssh_audit has no created_at; the
      timestamp column is `ts` (3 refs). ORA-00904 "SA"."CREATED_AT". Fixed.
  #12 db/compliance-reports.js getAccessData — team_invites has no accepted_at
      column at all. ORA-00904 "TI"."ACCEPTED_AT". Fixed → NULL AS accepted_at.
Bug #2 is an Oracle-internal ORA-00600 (managed-service defect), not TuneVault
code — nothing to fix here; left as documented.
Per-bug STATUS lines below updated to FIXED as each landed.
-----------------------------------------------------------------------------


1. db/admin-agents.js — upsertLogTail() and getLogTail()
   STATUS: already fixed in commit cb19904 before this policy was set. Left fixed
           rather than reverted, because reverting would deliberately restore a
           statement that cannot execute on either engine. Flagged here so the
           route-layer half is not forgotten.

   SYMPTOM: both functions throw on every call, in production, today.

   EVIDENCE: they reference columns log_lines, line_count and flushed_at. The
   real table — in Oracle AND in Neon — is:
       agents_log_buffer (id, connection_id, lines, updated_at)
   where `lines` was text[] in Postgres and is JSON in Oracle. Verified directly
   against Neon:
       SELECT log_lines, line_count, flushed_at FROM agents_log_buffer LIMIT 1
       -> ERROR: column "log_lines" does not exist
   So the agent log-tail drill-in on /admin/agents cannot ever have worked since
   whenever the table shape changed.

   REMAINING WORK: DONE (2026-07-24). On inspection the /log-tail route already
   runs its own SELECT lines, updated_at and returns { lines, updatedAt }, and
   public/admin/agents.html already reads data.lines / data.updatedAt / data.note
   — so the route+frontend half was already correct. getLogTail() is exported but
   unused (the route doesn't call it); its stale docstring (claimed log_lines/
   line_count/flushed_at) was corrected to the real { lines, updated_at } shape.
   Tested: upsertLogTail -> getLogTail round-trips 3 lines (blank filtered) with
   updated_at; the route's query+shaping builds { lines, updatedAt }.


(Add further entries below as they are found.)


2. ORACLE-SIDE DEFECT (not TuneVault code) — ORA-00600 on column DDL against
   ORACLE_CONNECTIONS in the ADB instance.

   SYMPTOM: ALTER TABLE oracle_connections DROP COLUMN <c>  and
            ALTER TABLE oracle_connections RENAME COLUMN <a> TO <b>
   both kill the server process with:
       ORA-00600: internal error code, arguments: [kkbrcupfind2], ...
   surfacing to the client as NJS-500 (connection closed or broken).
   Reproduced 3 times, on different columns, in separate sessions.

   NOT AFFECTED — verified working on the same table:
     ALTER TABLE ... ADD (<c> ...)      <- this is what ensureColumns() uses at
                                           boot, so startup is NOT at risk
     ALTER TABLE ... SET UNUSED COLUMN  <- used to retire the helper columns
     SELECT / INSERT / UPDATE / DELETE
   After each ORA-00600 the table was re-checked: 3 rows intact, status VALID,
   no UNUSED-column corruption, all indexes VALID, readable and writable.

   CONSEQUENCE: two columns on oracle_connections could not be converted from
   CLOB to VARCHAR2 and remain CLOB — last_test_message and
   agent_restart_loop_reason. Both are only ever ASSIGNED in SQL (SET) and never
   used as a comparison key, so ORA-22848 cannot be triggered by them; leaving
   them as CLOB is functionally harmless. 60 of 62 columns were converted.

   FOLLOW-UP OPTIONS, none urgent: (a) leave as-is; (b) rebuild the table via
   CREATE TABLE AS SELECT + rename, which avoids DROP/RENAME COLUMN entirely;
   (c) raise an SR with Oracle Support for the ORA-00600 — ADB is a managed
   service and this is their internal error, worth reporting regardless.


3. db/alert-policies.js — getCheckScore()

   STATUS: FIXED (2026-07-24). SELECT score -> SELECT overall_score in
           getCheckScore; read updated to rows[0].overall_score. Tested: returns
           53 for run 1, null for a missing id; health_score policy path evaluates.

   SYMPTOM: throws on every call, on both engines.

   EVIDENCE: it runs `SELECT score FROM health_checks WHERE id = $1`, but the
   column is `overall_score` — verified in Oracle (user_tab_columns) AND in Neon
   (information_schema). There is no `score` column on that table anywhere.

   Postgres fails loudly with 'column "score" does not exist'. Oracle fails more
   confusingly with ORA-00938 "not enough arguments for function", because SCORE
   is an Oracle built-in (Oracle Text) — so the parser reads `score` as a
   function call rather than an unknown identifier. Worth knowing generally: a
   typo'd column whose name collides with an Oracle builtin does not produce the
   ORA-00904 you would expect.

   IMPACT: getCheckScore feeds the 'health_score' alert policy type — the
   Low Health Score (<50) default policy seeded for every user. That policy
   cannot currently evaluate.

   FIX WHEN DONE: rename to overall_score in the query. One-word change, but it
   needs a test that the health_score policy path actually evaluates, which is
   why it is not being done blind here.


4. db/clone-recipes.js — six functions reference columns that do not exist

   STATUS: FIXED (2026-07-24). s/t.display_name -> s/t.name (4 join sites);
           listConnectionsForUser -> name AS display_name + is_ebs AS ebs_detected
           (no is_demo column exists — key dropped); appendStepResult drops the
           non-existent clone_history.updated_at. The duplicate bad query in
           routes/ebs-clone.js was corrected too (kept in Postgres dialect — the
           route layer isn't translated yet). Tested: startRun→appendStepResult→
           getRunById round-trips, 1 step persisted, current_step advanced.

   SYMPTOM: every read that joins oracle_connections throws on both engines.

   EVIDENCE: the module selects s.display_name / t.display_name, and
   listConnectionsForUser selects display_name, ebs_detected, is_demo. None of
   those columns exist on oracle_connections — verified in Oracle
   (user_tab_columns) AND in Neon (information_schema). The real columns are
   name, client_name, ebs_instance_name, service_name, username.

   AFFECTED (throw today):
     listRecipes, getRecipeById, findRecipeByPair(*), listHistory, getRunById,
     listConnectionsForUser
   (*) findRecipeByPair selects * from clone_recipes only and is FINE; listed
       here only because its callers usually pair it with getRecipeById.

   NOT AFFECTED (verified working on Oracle): createRecipe/updateRecipe's INSERT
   and UPDATE halves, startRun, appendStepResult, finishRun, touchRecipeRun,
   deleteRecipe — everything that touches clone_history or writes clone_recipes
   without the join.

   KNOCK-ON: createRecipe and updateRecipe END by calling getRecipeById to return
   the row, so they throw too even though their writes succeed. The write IS
   committed before the throw — a caller that catches the error will find the
   recipe present, which is a confusing state to debug.

   FIX WHEN DONE: s.display_name -> s.name (and t.name), and
   listConnectionsForUser -> name plus whatever replaced ebs_detected/is_demo.
   Needs a look at what the clone wizard UI actually renders, which is why it is
   not being guessed at here.

   ADDENDUM — appendStepResult is broken for a DIFFERENT missing column:
   it sets `updated_at` on clone_history, and that table has no updated_at
   (columns: id, recipe_id, company_id, source/target_connection_id, started_by,
   started_at, completed_at, status, current_step, total_steps, step_results,
   duration_ms, error_message). Same in Neon. So clone progress reporting has
   never worked either — a clone run records its start and its finish, but no
   per-step results. Translated faithfully (still sets updated_at, still throws)
   rather than silently repaired; the FOR UPDATE read-modify-write around it is
   therefore written but unexercised until this is fixed.

   ADDENDUM to bug 3 — the same wrong column name appears in a second module:
   db/compliance-reports.js getSoxData() selects `hc.score` from health_checks
   (line ~112). The column is overall_score. So the SOX Change Report has never
   been generatable either — it throws before assembling any data.

   Note the different error text for the SAME mistake: unqualified `score`
   (alert-policies) gives ORA-00938 because SCORE parses as an Oracle Text
   builtin, while qualified `hc.score` (here) gives the ORA-00904 you would
   expect. Worth knowing when grepping for this class of bug — the symptom is not
   consistent.

   Both are one-word fixes (score -> overall_score) but are left alone under the
   don't-fix-in-this-pass policy; each needs its report path exercised end to end.


5. db/compliance-reports.js — getAccessData() selects tm.created_at

   STATUS: FIXED (2026-07-24). tm.created_at -> tm.joined_at (SELECT + ORDER BY);
           the teamMembers query's stray array bind was made a named object too.
           Also fixed #12 (team_invites.accepted_at -> NULL AS accepted_at) on the
           same path. Tested: getAccessData runs clean, teamMembers[0].joined_at
           present.

   SYMPTOM: throws on both engines; the Access Audit report cannot be generated.

   EVIDENCE: team_members has no created_at. Its columns are id, team_id,
   user_id, role, joined_at, role_id — same in Neon. The intended column is
   almost certainly joined_at.

   Taken with the getSoxData hc.score defect above, TWO of the three compliance
   report types (SOX Change, Access Audit) have never been generatable. Only
   Activity Summary works. That is worth knowing before the compliance feature is
   demoed or sold against — the UI offers all three.

   FIX WHEN DONE: tm.created_at -> tm.joined_at, and verify what the report
   renders that column as.


6. db/first-run.js — getTopFindings / resolveCheckResult / snoozeCheckResult
   filter check_results by run_id against a health_checks.id

   STATUS: FIXED (2026-07-24). db/first-run.js getTopFindings now takes a
           CONNECTION id and derives the latest run's UUID run_id from
           check_results itself (fleet.js pattern); routes/first-run.js passes
           connId. resolveCheckResult/snoozeCheckResult scope by
           check_results.connection_id directly (the row carries it; no
           health_checks join, so no UUID-vs-NUMBER compare). db/manager.js
           getFleetSummary re-derives the real UUID run_id per connection before
           counting severities (was counting WHERE run_id IN (<health_checks.id>)).
           Tested against the live ADB: getTopFindings(169)=5 findings;
           resolve/snooze mutate the right row and no-op on the wrong connection;
           getFleetSummary(user 1) returns conn 169 with crit=7/high=11 instead of
           throwing ORA-01722; getWeeklyStatusData runs.

   SYMPTOM: the entire first-run "wow moment" findings path throws on BOTH
   engines. getTopFindings is called with a health-check id (an integer);
   resolve/snooze guard ownership with `run_id IN (SELECT id FROM health_checks
   …)`. But check_results.run_id is a UUID string, not a health_checks.id.

   EVIDENCE: verified directly against both live databases.
     Neon:   check_results.run_id = uuid,  health_checks.id = integer.
             getTopFindings(int)  -> "invalid input syntax for type uuid: 123"
             resolve/snooze guard -> "operator does not exist: uuid = integer"
             sample run_id: 54241721-3f2f-4779-9542-55371bdf35d2
     Oracle: check_results.run_id = VARCHAR2, health_checks.id = NUMBER.
             All three -> ORA-01722, because Oracle implicitly TO_NUMBER()s the
             VARCHAR2 run_id column to match the NUMBER side, and a UUID has a
             '-' in it. (Worth knowing: a text/number column mismatch surfaces
             at RUNTIME as ORA-01722 across the whole scan, not as a parse-time
             type error.)

   So routes/first-run.js's GET findings endpoint (getTopFindings on
   latestRun.id), plus the Resolve and Snooze finding actions, cannot have worked
   against real data on either engine. The lookup key is simply wrong — run_id is
   the per-run UUID that check_results rows are actually stamped with; the code
   passes the numeric health_checks.id instead.

   NOT a migration artifact: the translation preserves the throw exactly. Proven
   in test-first-run.js — getTopFindings/resolve/snooze are asserted to STILL
   throw ORA-01722, while the same ranking works when called with the real UUID
   run_id, so the SQL is otherwise correct.

   TWO Oracle-DIALECT fixes were made in the same functions (these are mine to
   make, not pre-existing bugs — Postgres's TEXT/JSONB types did not need them):
     - resolveCheckResult: COALESCE(ai_summary, 'literal') is ORA-00932 because
       ai_summary is a CLOB and the literal is CHAR -> COALESCE(ai_summary,
       TO_CLOB('…')).
     - snoozeCheckResult: raw_payload || jsonb_build_object(...) has no Oracle
       equivalent (JSON_TRANSFORM banned) -> read-modify-write under SELECT …
       FOR UPDATE. Both are exercised in isolation from the broken guard and pass.

   FIX WHEN DONE: pass the run's UUID (check_results.run_id) rather than
   health_checks.id. Likely means threading the real run_id through
   getLatestHealthRun/routes, or joining check_results -> health_checks on
   connection_id + run identity. Needs the first-run route exercised end to end,
   which is why it is not guessed at here.

   ALSO AFFECTS db/manager.js getFleetSummary (found while translating it): its
   severity-count query does `check_results WHERE run_id = ANY($1)` with $1 =
   latestRuns' health_checks.id (NUMBER) — same mismatch, same ORA-01722. Because
   it has no try/catch, getFleetSummary throws for ANY visible connection that has
   a completed run, and getWeeklyStatusData throws too (it awaits getFleetSummary
   in a Promise.all). Notably db/fleet.js does NOT have this bug: it re-derives
   the real UUID run_id from check_results (its Step 3a) before counting.
   manager.js never does that. Same one-line class of fix — join/derive the UUID
   run_id — but a separate call site, so listed here rather than folded in.


7. db/outreach.js — authorizeRecipient() writes a non-existent column

   STATUS: FIXED (2026-07-24). Dropped `, updated_at = NOW()` from the UPDATE
           (outreach_recipients has no such column). Tested: insert temp recipient
           -> authorizeRecipient flips send_authorized TRUE -> cleanup; missing id
           returns null.

   SYMPTOM: throws on both engines every time an operator authorizes a recipient
   for a send.

   EVIDENCE: authorizeRecipient runs
       UPDATE outreach_recipients SET send_authorized = true, updated_at = NOW()
   but outreach_recipients has NO updated_at column. Verified directly:
     Neon:   information_schema columns = id, batch_id, email, name, company,
             send_authorized, sent_at, status, created_at (no updated_at) ->
             "column \"updated_at\" of relation \"outreach_recipients\" does not exist"
     Oracle: same column set -> ORA-00904: "UPDATED_AT": invalid identifier
   (The sibling writes markRecipientSent/markRecipientBlocked do NOT touch
   updated_at and work fine; only authorizeRecipient references it.)

   IMPACT: the send-authorization step of the outreach flow cannot complete — a
   recipient can never be flipped to send_authorized = true through this function.
   Combined with the hard OUTREACH_UNLOCK_TOKEN lock, outreach is doubly gated,
   but this is still a latent crash on the one path meant to arm a send.

   FIX WHEN DONE: drop `, updated_at = NOW()` from the UPDATE (the table has no
   such column), or add the column if an audit timestamp is wanted. One-line
   change, left alone under the don't-fix-in-this-pass policy.


8. db/roles.js — two functions reference columns that do not exist

   STATUS: FIXED (2026-07-24). 8a: dropped `, updated_at = NOW()` from the
           team_members UPDATE (no such column). 8b: rewritten against the real
           check_results shape — status AS result/severity, check_category AS
           category, recommendation AS details, executed_at AS run_at; latest run
           derived from check_results' UUID run_id (fleet.js pattern), no
           health_checks join. routes/roles.js status mapping updated for the real
           green/amber/red verdicts. Tested: assignRoleToMember sets role_id;
           getPatchStatusForConnection(141) returns the EBS_ADOP_SESSIONS row with
           all expected keys; empty for a connection with no patch checks.

   8a. assignRoleToMember() sets a non-existent team_members.updated_at
       SYMPTOM: throws on both engines whenever a member's role is (re)assigned.
       EVIDENCE: it runs
           UPDATE team_members SET role_id = $1, updated_at = NOW() WHERE ...
       but team_members has NO updated_at column. Verified in Oracle AND (via the
       DDL generated from live Neon) Postgres — columns are id, team_id, user_id,
       role, joined_at, role_id.
         Oracle:   ORA-00904: "UPDATED_AT": invalid identifier
         Postgres: column "updated_at" of relation "team_members" does not exist
       (Same class as bug #7's outreach_recipients.updated_at.)
       FIX WHEN DONE: drop `, updated_at = NOW()` (team_members has no such
       column), or add the column if an audit timestamp is wanted.

   8b. getPatchStatusForConnection() selects a fistful of non-existent columns
       SYMPTOM: throws on both engines; the role-filtered patch-status widget can
       never render.
       EVIDENCE: the query selects cr.result, cr.severity, cr.category, cr.details
       and joins on cr.health_check_id, plus hc.run_at — NONE of which exist.
       Verified directly against both live databases. The real check_results
       columns (both engines) are: id, connection_id, run_id, check_id,
       check_category, status, metric_name, metric_value, metric_unit,
       raw_payload, ai_summary, recommendation, executed_at, created_at — so
       result->status, category->check_category, and there is no severity /
       details / health_check_id at all. health_checks has no run_at either
       (created_at / completed_at instead), and check_results links to a run by
       the UUID run_id, not a health_checks.id FK.
         Oracle:   ORA-00904: "CR"."HEALTH_CHECK_ID": invalid identifier
         Postgres: column cr.result does not exist
       So this query was written against a check_results shape that never shipped.
       FIX WHEN DONE: rewrite against the real columns — status (not result/
       severity), check_category (not category), drop details, join on run_id/
       connection_id (not health_check_id), and use completed_at (not run_at).
       A non-trivial rewrite that needs the patch-status widget's real contract,
       which is why it is not guessed at here. The ILIKE -> LOWER(...) LIKE and
       $1 -> :p1 dialect fixes are already in place, so once the columns are
       corrected the query is otherwise Oracle-valid.


9. db/ssh-targets.js — getConnectionsForUser selects a non-existent
   oracle_connections.connection_name

   STATUS: FIXED (2026-07-24). connection_name -> name (SELECT + ORDER BY),
           aliased AS connection_name so the dropdown UI keeps its key. Tested:
           getConnectionsForUser(92) returns 3 rows, each with connection_name.

   SYMPTOM: throws on both engines whenever the SSH-target UI populates its
   connection dropdown for a user.

   EVIDENCE: the query is
       SELECT id, connection_name, connection_type FROM oracle_connections
       WHERE user_id = $1 ORDER BY connection_name
   but oracle_connections has no connection_name column — the display name column
   is `name`. Verified directly:
     Neon:   "column \"connection_name\" does not exist"
     Oracle: ORA-00904: "CONNECTION_NAME": invalid identifier
   (health_checks has connection_name; oracle_connections does not — an easy
   confusion. getConnectionProxyById on the same table is fine — it uses real
   columns.)

   FIX WHEN DONE: connection_name -> name (twice: SELECT list and ORDER BY),
   and alias it AS connection_name if the dropdown UI expects that key. One
   rename, left alone under the don't-fix-in-this-pass policy.


10. db/upgrade-verifications.js — the post-upgrade / degraded-status flow is
    broken three ways, all on BOTH engines

    STATUS: FIXED (2026-07-24) — plus 3 MASKED CHECK-constraint bugs that the
            probe_8 parse error had been hiding. insertPostUpgradeValidationRun
            inserts os='post-upgrade', topology='post-upgrade-v3-to-v6',
            trigger_source='post-upgrade-agent' — all three violate CHECK
            constraints on installer_validation_runs (OS/TOPOLOGY/TRIGGER_SOURCE),
            so even after adding probe_8 the INSERT still failed. Because ORA-00904
            (missing probe_8 column) is a PARSE error, the statement never reached
            constraint evaluation, so only the first error was originally visible.
            FIXES APPLIED to the live ADB + db/oracle/schema.sql:
              10a: ADD probe_8_status/ms/error columns + probe_8 CHECK
                   (IN pass/fail/skip); widened the OS/TOPOLOGY/TRIGGER_SOURCE
                   CHECKs to allow the post-upgrade sentinel values.
              10b: added 'upgrade-degraded' to the oracle_connections STATUS CHECK
                   (drop+re-add the constraint — NOT column DDL, so bug #2's
                   ORA-00600 does not apply; verified clean).
              10c: db/upgrade-verifications.js clearUpgradeDegradedStatus now sets
                   status='active' (the default), not NULL (a NOT NULL column).
            Tested end to end against the live ADB: a post-upgrade run inserts with
            probe_8_status + all three sentinel labels; mark→'upgrade-degraded',
            clear→'active'. Schema changes applied to the live ADB; Neon parity is
            carried by node-pg-migrate migration
            migrations/1783700000000_upgrade_verification_schema_fixes.js (same
            columns + widened CHECKs, idempotent), which runs on the next deploy —
            validated against live Neon in a rolled-back txn (11/11, incl. a
            sentinel INSERT), no production change yet.

    10a. insertPostUpgradeValidationRun() writes a non-existent probe_8_status
         SYMPTOM: throws on every call; a post-upgrade validation run can never be
         recorded.
         EVIDENCE: the INSERT lists probe_7_status AND probe_8_status, but
         installer_validation_runs only has probe_1_status .. probe_7_status
         (verified in Oracle AND Neon).
           Oracle:   ORA-00904: "PROBE_8_STATUS": invalid identifier
           Postgres: column "probe_8_status" does not exist
         FIX WHEN DONE: drop probe_8_status (or add probe_8_* columns if an 8th
         probe is intended — the agent sends probe_7 + probe_8).

    10b. markConnectionUpgradeDegraded() sets a status the CHECK forbids
         SYMPTOM: throws whenever it matches a real connection.
         EVIDENCE: it runs UPDATE oracle_connections SET status='upgrade-degraded',
         but the status CHECK constraint allows only ('pending_registration',
         'active', 'stale') — same constraint on both engines. Verified against a
         real row:
           Oracle:   ORA-02290: check constraint (…STATUS_CHECK) violated
           Postgres: new row … violates check constraint
                     "oracle_connections_status_check"
         So a connection can NEVER be flagged upgrade-degraded.
         FIX WHEN DONE: add 'upgrade-degraded' to the status CHECK (a schema
         change on both engines), or use an existing allowed value / a separate
         column to represent degradation.

    10c. clearUpgradeDegradedStatus() sets status = NULL (a NOT NULL column)
         Latent, because of 10b: since a row can never reach 'upgrade-degraded',
         clear's WHERE (… AND status='upgrade-degraded') never matches, so its
         SET status = NULL never executes — a safe no-op in practice. BUT if 10b
         were fixed, this would immediately start throwing: status is NOT NULL on
         both engines (ORA-01407 / not-null violation). It should reset to 'active'
         (the default), not NULL.
         FIX WHEN DONE (alongside 10b): SET status = 'active', not NULL.
