Incomex AI Portal
KB-735A rev 3

Codex Reports Live Refresh Factory — Production Survey and Local Fix

14 min read Revision 3
codexreportslive-refreshfactorypgdirectusnuxtnginxproduction-evidence2026-07-10

title: "Codex — Reports Live Refresh Factory — Production Survey and Local Fix" tags:

  • codex
  • reports
  • live-refresh
  • factory
  • pg
  • directus
  • nuxt
  • nginx
  • production-evidence
  • 2026-07-10 date: 2026-07-10 status: local-fixed-awaiting-owner-gate

Codex — Reports Live Refresh Factory — Production Survey and Local Fix

0. Executive verdict

Local factory fix: READY_FOR_OWNER_GATE. Production deployment: NOT RUN.

The root problem is in the shared Reports mold and deployment artifact, not in individual report pages:

  • Production's existing “Làm mới” button did not issue a new source request.
  • The legacy snapshot v_report_pg was stale while the live PG census had changed.
  • The repository nginx/config artifact did not carry the complete live Reports/MCP route set.
  • The computed-report route did not send an explicit no-store response header.

The local repair makes source selection metadata-driven and makes one refresh re-query the selected source. It also adds a paired verifier and CI/deploy guards. No production database write, push, CI run, or deploy was performed.

1. Mission declarations

Vĩnh viễn

table_registry.view_sql is the source selector for computed reports; base-table reports remain Directus-backed. The shared mold is the only UI path, so a new report declaration inherits live refresh behavior.

Nhầm được không

The live route accepts only table_id; it resolves SQL server-side, validates SELECT/WITH, executes through the read-only PG gateway, and returns no-store. The publisher and verifier reject unsafe metadata or mismatched API/PG counts. CI/deploy guard checks the canonical nginx route set.

100% tự động

A base-table spec goes through dot-list-publish (read-only Directus tracking, PK detection, registry metadata, paired verification). A computed report spec goes through dot-report-publish (read-only SQL probe, metadata upsert, paired verification). No per-report page or hand-written count formula is required.

2. Production survey (read-only)

2.1 Registry inventory

Production table_registry has 28 non-archived rows. Seven active rows are under /reports:

table_id source live API/PG count
tbl_balo_function Directus base table balo_thuc_the, PK stt 654 = 654
tbl_balo_table Directus base table balo_thuc_the, PK stt 383 = 383
tbl_balo_trigger Directus base table balo_thuc_the, PK stt 410 = 410
tbl_balo_view Directus base table balo_thuc_the, PK stt 699 = 699
tbl_loai Directus base table entity_species, PK id 42 = 42
tbl_report_home live view_sql 6 = 6
tbl_report_pg live view_sql 12 = 12

Exact paired verifier output:

PASS source=directus_base_table table_id=tbl_balo_function directus_count=654 pg_count=654
PASS source=directus_base_table table_id=tbl_balo_table directus_count=383 pg_count=383
PASS source=directus_base_table table_id=tbl_balo_trigger directus_count=410 pg_count=410
PASS source=directus_base_table table_id=tbl_balo_view directus_count=699 pg_count=699
PASS source=directus_base_table table_id=tbl_loai directus_count=42 pg_count=42
PASS source=live_view_sql table_id=tbl_report_home api_count=6 pg_count=6
PASS source=live_view_sql table_id=tbl_report_pg api_count=12 pg_count=12

2.2 Stale snapshot evidence

The legacy snapshot table still contains:

table|383|2026-06-27 06:27:39.296757+00
sequence|149|2026-06-27 06:27:39.296757+00

The live route currently returns:

HTTP/1.1 200 OK
source=pg:table_registry.view_sql:tbl_report_pg
count=12
table=385
sequence=151
generated_at=2026-07-10T08:07:32.136Z

This proves the old snapshot was not a current source of truth.

2.3 Production UI failure before the fix

Playwright against https://vps.incomexsaigoncorp.vn was 0/3:

  • Home refresh: no new /api/reports/live?table_id=tbl_report_home response.
  • PG refresh: no new /api/reports/live?table_id=tbl_report_pg response.
  • Balo refresh: no new Directus data request.

A direct browser trace after clicking “Làm mới” showed only the initial registry/auth requests; no source re-query was emitted.

The production live endpoint response also lacked an explicit Cache-Control: no-store header.

2.4 Non-Reports caveat

Two non-Reports registry pages (tbl_registry_changelog, tbl_registry_collections) returned HTTP 403 through the public UI proxy. They are under /knowledge, not the seven Reports routes, and no production permission was changed.

3. Local repair

Shared mold and source selection

  • web/components/shared/DirectusTable.vue
    • Detects view_sql metadata and routes computed reports to the live gateway.
    • Refresh awaits the common useAsyncData refresh and emits refreshed.
    • Existing base-table callers retain the Directus path.
  • web/composables/useDirectusTable.ts
    • Live requests send only table_id.
    • Live responses use cache: 'no-store'.
    • Directus count/list paths remain shared and filter-consistent.

Live gateway

  • web/server/api/reports/live.get.ts
    • Looks up view_sql by parameterized table_id.
    • Rejects unsafe/non-SELECT SQL.
    • Executes via rpQuery with read-only transaction settings.
    • Returns source, generated_at, count, rows and no-store.

Reports Home totals

  • web/pages/reports/index.vue
    • Re-reads each target's registry metadata.
    • Uses the live gateway for computed targets and Directus aggregate for base tables.
    • Refreshes totals on the shared mold's refreshed event.

Factory and verifier

  • dot/bin/dot-list-publish
    • Ensures read-only Directus tracking.
    • Auto-detects a single-column PK when omitted.
    • Upserts only metadata and verifies API/PG equality.
  • dot/bin/dot-report-publish
    • Accepts live SELECT/WITH metadata.
    • Runs a read-only probe.
    • Never creates, truncates, or populates snapshot tables.
  • dot/bin/dot-table-ui-verify
    • Paired read-only verifier for both source variants.
  • scripts/integrity/check-reports-factory.sh
    • Guards source files, route order, no-store, read-only gateway, runtime PG dependency, and publisher/verifier pairing.

Deployment source of truth

  • .gitignore no longer blocks the Reports route source.
  • infra/nginx/conf.d/default.conf contains the canonical Reports, registries-pivot, MCP and UI-preview blocks.
  • CI and both deploy paths run the factory guard, nginx -t, and a live PG Reports health check.
  • web/package.json declares runtime pg so the SSR artifact contains the database driver.

4. Local verification evidence

pnpm run typecheck
exit_code=0

pnpm run build
[nitro] ✔ You can preview this build using node .output/server/index.mjs
✨ Build complete!

node ../scripts/validate-contracts.js
Điều 31 Contract Validation: 6 PASS, 0 FAIL, 1 SKIP

bash scripts/integrity/check-reports-factory.sh
PASS reports-factory: all static invariants satisfied

Local Playwright against the built SSR artifact:

Running 3 tests using 3 workers
✓ Home refresh re-reads its live rows and all displayed totals
✓ computed PG report refresh gets a newer generated dataset
✓ base-table report refresh stays on the Directus live-table path
3 passed

5. Owner gate / next step

This report is local-fixed and evidence-backed, but production is intentionally unchanged.

Required next governed sequence:

  1. Owner approves the local diff.
  2. Commit only the scoped factory/report/nginx/CI files.
  3. Push one PR and wait for CI.
  4. Run the standard deploy path.
  5. Verify /reports, all seven Reports routes, live generated_at, and API/PG equality again.

Do not treat the current production URL as repaired until step 5 is green.

6. Files and evidence

  • Factory guard: scripts/integrity/check-reports-factory.sh
  • Progress/evidence note: knowledge/current-state/reports/reports-live-refresh-factory-progress-2026-07-10.md
  • Reports contract: web/tests/contracts/reports.json
  • E2E proof: web/tests/e2e/reports-live.spec.ts
  • Production live route: https://vps.incomexsaigoncorp.vn/api/reports/live?table_id=tbl_report_pg

7. Evidence update — B/C/D safety gate (2026-07-10 16:xx ICT)

Status: NOT DEPLOY-READY; Owner gate remains closed. No production database write, source push, merge, CI run, nginx change, or deploy was performed.

B. Nginx and repository mine removal

Read-only live check:

```text REQUIRED_ANCHORS_MISSING target=repo count=0 REQUIRED_ANCHORS_MISSING target=live count=0 nginx: the configuration file /etc/nginx/nginx.conf syntax is ok nginx: configuration file /etc/nginx/nginx.conf test is successful ```

Both repo and live have /api/reports/, /api/registries-pivot/, and all four MCP route includes. The required Reports route occurs before the generic /api/ catch-all.

A real pre-deploy mine was found: canonical default.conf includes well-known-whitelist.conf, while that companion file is absent on the live host and the deploy workflow previously copied only default.conf. Replacing nginx config would therefore fail nginx -t. The existing deploy workflow was repaired locally in both direct/artifact paths to copy the existing companion before nginx -t; the static guard verifies this transfer.

The guard now also fails when a required Reports source is ignored, untracked, modified-but-uncommitted, missing a protected nginx block, lacks route no-store, lacks the shared refresh event, or loses either publisher/verifier pairing. The runtime deploy health check now requires Cache-Control: no-store and two distinct generated_at values, not merely count=12.

C. Verifier now catches the actual frozen-report failure

The paired live verifier was tightened without adding a new mechanism:

  1. requires Cache-Control: no-store;
  2. calls the live route twice and requires distinct generated_at;
  3. compares all normalized API rows with the rows from the stored view_sql in a BEGIN READ ONLY transaction; volatile timestamps and bigint JSON transport differences are normalized;
  4. fetches SSR and requires a rendered report timestamp no more than 120 seconds old.

Historical stale report-pg values versus PG-live normalize to different rows:

```text OLD: table=383, sequence=149 LIVE: table=385, sequence=151 EXPECTED_FAIL live rows differ: API display is not PG-live ```

This closes the prior false-pass: both sources had 12 rows, so a count-only verifier could pass even when the displayed numbers were stale.

Current production deliberately fails the stricter verifier before deployment:

```text FAIL live response is cacheable (Cache-Control no-store required) VERIFIER_EXIT=5 ```

Browser proof of the legacy deployed UI remains failing: after clicking Home refresh, Playwright timed out waiting 10 seconds for /api/reports/live?table_id=tbl_report_home. Thus production is not claimed repaired.

Local built SSR proof (read-only production data tunnel) passes:

```text Cache-Control: no-store, max-age=0 generated_at #1 = 2026-07-10T09:22:13.154Z generated_at #2 = 2026-07-10T09:22:15.015Z SSR refreshed_at = 2026-07-10T09:22:18.778Z 3 Playwright tests passed: Home live refresh, computed PG click/network/DOM refresh, base-table Directus refresh ```

The source selection remains shared: view_sql reports use the PG gateway; base-table reports retain Directus. No Balo count/snapshot behavior was changed.

D. Deployment source truth

This local working tree is not yet a deployable repository source:

  • Production deploy builds GitHub main via Actions checkout and rsyncs web/.output; it does not build from the VPS host repo.
  • The VPS hand-deployed line is vps/main (contains older route/pages/gateway), while local has newer uncommitted/untracked Reports sources.
  • .gitignore no longer blocks the Reports paths, but the route, pages, gateway, verifier, publishers, and guard are not all tracked/committed. The enhanced guard intentionally fails locally with: DirectusTable.vue has uncommitted changes: a CI checkout would deploy older code.

Do not merge or deploy automatically. Owner approval is required to reconcile a clean branch from actual GitHub main with the hand-deployed vps/main delta, scope the Reports-only files, commit, run CI, and then open the deploy gate. Only after that can the production A-round evidence be collected.

Round 2 read-only no-regression baseline

Before any deploy, live nginx still served the unrelated guarded surfaces through its own local front door:

200 /
200 /reports
200 /reports/report-balo-table
200 /reports/report-loai
200 /knowledge/registries
200 /knowledge/workflows
200 /api/registries-pivot/summary

The pivot endpoint returned source=pg:v_count_integrity/v_count_drift/v_registries_pivot_tree and a current generated_at=2026-07-10T09:37:34.365Z. No Balo/Loai, Registries, Workflows, Home, registries-pivot, or MCP configuration was modified on production.