Markdown edition

# Build a private dashboard from real data with Replit Agent

> Define a real event export, build its database, import, API and UI with Replit Agent, then reconcile metrics and check an Invite only release.

By BIGCHANGE.AI Editorial

Published: 2026-10-22T15:31:41.040Z
Updated: 2026-10-22T15:31:41.040Z
Canonical: https://bigchange.ai/blog/replit-agent-private-dashboard-real-data-guide

![Conceptual charcoal illustration of one hand aligning a straightedge with blank rows on an export sheet, with a partial unbranded monitor showing an empty layout behind it.](https://bigchange.ai/api/media/file/replit-private-dashboard-row-check-hero-v2.png)
AI-generated conceptual illustration by BIG CHANGE.

A private dashboard built with Replit Agent needs a definition of the data before it needs a chart. This guide is for an operator permitted to use a real event export, inspect generated SQL and server code, and invite a small internal audience. It walks from the export's row meaning through a managed database, owner-run import, filtered app and checked release. BIG CHANGE has not run this app, imported reader data or tested a Replit account. The code below is an original proposed implementation reference for the reader to integrate and test.

## The big change

- **What changed:** Replit Agent can plan and generate the database, Node/Express API and React view for a dashboard from an operator's approved data contract.
- **Why it matters:** The operator can reconcile the same accepted batch across cards, chart and table, then control when real data enters the app and who may see it.
- **What to watch:** The generated app still needs code review, actual source-row checks and allowed and denied tests at its Invite only URL. Those results are unknown until the operator runs them.

## 1. Write down what the export means

Choose one authorized export with one row per actual event and a stable source record ID. Identify its owner, source system, export time, time zone, permitted viewers, retention rule and acceptable refresh delay. Map five actual columns to `sourceRecordId`, `occurredAt`, `category`, `measureValue` and `unit`. If the source lacks a unit column, have its owner approve a derived export with one explicit unit in every row. Do not infer one from the number.

This version supports one additive event grain and one unit per batch. Define the metric's exact sum rule, what the timestamp marks, and whether negative reversals are legitimate. A snapshot value, several currencies or a nonadditive measure needs a different contract. The count here is accepted event rows; it is not automatically a count of people or orders. The importer rejects duplicate IDs, blanks, invalid dates, mixed units and values with more than four decimal places. Zero is valid. Resolve upstream duplicates before making the export. Each timestamp needs an explicit UTC offset; filters and chart buckets use UTC calendar days.

Remove unrelated personal fields and confirm the right to place this data in the chosen Replit project. Separately select three real IDs and calculate one category/date-filtered count and total from the source rows by hand or by an independently reviewed source query. Keep those values private. They are the acceptance checks, not sample figures to paste into an Agent prompt.

## 2. Check account and audience controls

The [Replit Agent documentation](https://docs.replit.com/features/agent/overview) describes Plan mode and web app generation. Replit's [plan page](https://docs.replit.com/billing/plans/starter-plan) says Plan mode requires Core. Confirm the actual account has Agent, a managed SQL database, production database controls and an effective Invite only audience option before uploading data or paying for a build. Account features and charges may differ.

Decide who can edit project source, hold the production database credential and view the published app. In this design, every admitted viewer sees the same rows. Replit's [access documentation](https://docs.replit.com/features/publishing/private-deployments) says admins retain access to Invite only apps. The Project Editor's Invite control can assign a named person or group a suitable Read-only role, subject to [workspace role limits](https://docs.replit.com/teams/identity-and-access-management/repl-access-management). An invitation to the project may expose project material according to the assigned role. Invite only protects the published host; production credentials and direct database access need their own controls. Stop this route if the actual audience or sharing option cannot meet the export's rights.

## 3. Give Agent a build contract and review its plan

Create a new Replit web project, select Plan mode and provide the approved field mapping, metric rule, single unit, audience and refresh cadence. Attach or paste the public reference sections below. Ask Agent to show its plan before edits. Use this instruction as the core brief:

```prompt
Build a private internal activity dashboard with Replit managed PostgreSQL, one Node/Express server and one React UI. Viewers have read-only pages and APIs. There is no browser import, external connector or AI API call in the running app. Integrate the schema, bounded Node import and API/browser contract below; explain any deviation. Keep database credentials server-side. Generate migrations, the API, all filter/error states, tests and a source diff. Do not import real data, publish, call a paid service or choose Public sharing without the owner's decision. Report actual commands and results separately from untested paths.
```

The reference schema uses immutable batches. An `event_record` belongs to one batch, while `dashboard_state.current_batch_id` is the sole pointer to the accepted batch. The importer locks that state row, inserts the new batch and switches the pointer in one transaction. A rejected or rolled-back import leaves the prior batch current. Retained older batches help diagnose or recover a release and consume storage; an owner must set a later retention policy. Compare Agent's migration to the SQL below before running it on an empty authorized database.

## 4. Validate and import under the owner's control

In the generated Node project, use Node 20 or later, install reviewed versions of `pg` and `csv-parse`, and retain the lockfile. Place the complete importer and helper below at `scripts/import-events.mjs` and `scripts/reference-validate.mjs`; keep the test beside them. The import file's relative helper import must still resolve. Copy the config template into a private, untracked file and replace every placeholder with the source owner's real definitions. Set `additiveGrainApproved` to `true` only after verifying the grain and sum rule. Its unchanged template intentionally fails validation.

Run the pure helper test, then a dry run against an approved local file. Dry run reads no database. Before applying, set `DASHBOARD_DATABASE_URL` privately for the intended environment and compare the connection's host and database name with the reviewed settings. In Replit's Project Editor Shell, the documented default `DATABASE_URL` is for **development**. Production uses the same reviewed Node artifact and dependency versions in an owner-controlled Node environment, with the connection string taken privately from Production Database → Settings. Never put the string in a command argument, Agent chat, source file, log or public receipt. The non-secret command shape is:

```sh
node --test scripts/reference-validate.test.mjs
node scripts/import-events.mjs --mode dry-run --target development --file /private/approved.csv --config /private/metric.json
node scripts/import-events.mjs --mode apply --target development --file /private/approved.csv --config /private/metric.json --expect-host ACTUAL_DEV_DB_HOST --expect-db ACTUAL_DEV_DB_NAME
node scripts/import-events.mjs --mode readback --target development --request-key ACTUAL_64_HEX_KEY --expect-host ACTUAL_DEV_DB_HOST --expect-db ACTUAL_DEV_DB_NAME
```

The dry run prints a file hash, stable request key, row count and metric metadata. Verify those and the target identity before the authorized apply. For production, replace target and expected identity and add `--confirm-production PRODUCTION` to apply. An identical current request is a no-op; an old accepted request or equal/older source-export time is refused. If a response is lost, use `--mode readback` with that exact key on that exact target. Check whether it exists, is current and has `actual_rows = record_count` before deciding what to do. Do not blindly replay an uncertain write. A changed metric or unit needs a separate reviewed transition, while a normal refresh needs a newer approved export.

Before the first production replacement, make and verify a restorable backup with credentials kept out of command history. `DASHBOARD_DATABASE_URL` belongs to the custom Node importer; PostgreSQL client tools do not read it. Configure the PostgreSQL client separately with the approved source host, port, database, user and TLS settings. Before a dump, connect with that client and read `current_database()`, `current_user`, `inet_server_addr()` and `inet_server_port()`; compare the identity and connection settings with Production Database → Settings. A PostgreSQL-compatible route is `pg_dump -Fc --no-owner --no-privileges --file dashboard-before.dump`. Before restoring, independently configure and verify a **different authorized disposable host and database**, then run `pg_restore --no-owner --no-privileges --exit-on-error --dbname TEST_DB_NAME dashboard-before.dump`. Check the archive's exit status and, in the restored database, read the current pointer, batch metadata, record count, actual row count and chosen source IDs. `TEST_DB_NAME` is the verified disposable target, never production; changing only the database name is insufficient to establish a separate target. Even an initially empty production state needs schema and pointer verification. Store the archive under the owner's data policy. The [PostgreSQL backup](https://www.postgresql.org/docs/current/app-pgdump.html) and [restore](https://www.postgresql.org/docs/current/app-pgrestore.html) manuals explain those client tools.

## 5. Inspect the generated API and UI, then reconcile real rows

Agent must implement the complete API/browser contract below in the actual Express and React project. The SQL and importer are reference code; there is no prebuilt server or UI in this article. Each data route must use one checked-out PostgreSQL client and a read-only repeatable-read transaction for its pointer check and queries. PostgreSQL's default Read Committed mode can give two SELECT statements different snapshots; [PostgreSQL isolation rules](https://www.postgresql.org/docs/current/transaction-iso.html) and [node-postgres transaction guidance](https://node-postgres.com/features/transactions) explain why one client and transaction matter here.

The UI should show source name, export and import times, metric rule/unit, filtered count and total, UTC-day chart, category/date controls and a paged record table. The visible batch ID and filter must match every response. On a filter change, late responses from the previous request cannot replace a newer view. The chart needs an accessible value table as well as a visual; test the page with a keyboard and at phone width. “Refresh data” refetches the accepted batch; a new source export still requires the owner-run import.

In development, inspect Database → Development Database → My Data, then compare the current pointer and rows with the permitted source. Use a clearly labelled synthetic fixture to exercise mechanics when sensitive data cannot be placed in development, followed by a separately authorized real-data reconciliation. Check the three selected real IDs, one independently calculated filtered count and fixed-decimal total, the chart's UTC bucket and the table. Test an event exactly at the exclusive `to` boundary. Make an invalid import and verify that the preceding batch remains current. Read the generated files and tests, their actual results, server queries, browser network calls and bundle. No source-only data field, production connection string or write endpoint belongs in the viewer bundle.

## 6. Publish the reviewed revision and check both sides of access

Inspect the [Publishing pane](https://docs.replit.com/features/publishing/overview), its domain, deployment type, security scan, secrets and expected charges. [Replit's database guide](https://docs.replit.com/features/data-and-storage/development-and-production) describes separate development and production stores. On current Helium development infrastructure, publication creates a separate production database **if development contains data**; use only reviewed harmless fixture data for that prerequisite and verify it is absent from production. On legacy Neon infrastructure, the pane offers Create production database and optional development-data copy; create the production database and leave copy off unless the data is approved for production. Inspect the actual account path rather than assuming either branch.

In Tools → Publishing → Sharing choose Invite only. Review the project's named people, groups, Read-only roles and admins. [Replit's Secrets documentation](https://docs.replit.com/core-concepts/project-editor/app-setup/secrets) places production app secrets in Publishing → Adjust settings; keep the database credential in its server environment. Publish the reviewed revision deliberately. At the live URL, test the page and `/api/meta`, `/api/summary`, `/api/timeseries`, `/api/records` as an invited viewer and as an uninvited visitor, including any generated domain alias. The uninvited visitor must get host denial on every path. Check production schema, empty current pointer, connection identity and absence of development fixtures before real import. A “No accepted import” page is legitimate at this point.

After the verified backup and dry run, the authorized operator applies the same importer to production with `--target production`, actual host/database checks and `--confirm-production PRODUCTION`. Read back the request key, current batch, row count and source time. Reload the live dashboard and independently compare the three real IDs, filtered total, chart and table with the export. Record observed allowed and denied responses, deployment identity and logs without recording credentials or private rows. If a sharing change is needed, Replit documents an unpublish/change/republish flow, followed by fresh route checks. The first release has no earlier deployment to roll back to.

If validation rejects a row, repair the source or config under the owner's rules; the prior accepted batch remains current. A wrong number calls for checking grain, unit, offset, duplicate/reversal decision, filter and SQL against actual rows. A blank live view calls for checking `/api/meta`, production database identity, current pointer and logs. An uncertain import calls for exact-key readback before any retry. An exposed credential needs separate rotation; narrowing future sharing cannot undo an earlier disclosure.

Agent use, hosting and production PostgreSQL compute/storage may incur separate account-dependent charges. The running dashboard has no AI API call. This guide has no measured build time, bill, correctness, reliability, security or performance result. If you already have a working dashboard and need a different release route, [our checked Vercel release guide](https://bigchange.ai/blog/ai-built-dashboard-vercel-first-release-guide) starts from that later point.

## Sources & further reading

[Replit Agent](https://docs.replit.com/features/agent/overview) and [plan limits](https://docs.replit.com/billing/plans/starter-plan) document the builder and Plan-mode access. [Replit SQL Database](https://docs.replit.com/features/data-and-storage/sql-database), [development and production databases](https://docs.replit.com/features/data-and-storage/development-and-production), [connection details](https://docs.replit.com/features/data-and-storage/connection-details) and [working with data](https://docs.replit.com/features/data-and-storage/work-with-your-data) establish the environment and inspection path. [Publishing](https://docs.replit.com/features/publishing/overview), [Invite only access](https://docs.replit.com/features/publishing/private-deployments), [role management](https://docs.replit.com/teams/identity-and-access-management/repl-access-management) and [Secrets](https://docs.replit.com/core-concepts/project-editor/app-setup/secrets) describe the access controls. [PostgreSQL isolation](https://www.postgresql.org/docs/current/transaction-iso.html), [node-postgres transactions](https://node-postgres.com/features/transactions) and [CSV Parse sync](https://csv.js.org/parse/api/sync/) ground the proposed implementation. These manuals do not verify the reader's generated app.

## Copyable implementation reference

These are original proposed files for the bounded one-audience design. Review them against the generated project and actual package/database versions. The importer, helper and tests can be placed together under `scripts/`; the schema belongs in a reviewed migration. Agent must still build the complete Express API and React interface to the contract. All code and the contract follow in full so the guide does not depend on private packet files.

### Schema migration (reference-schema.sql)

```sql
-- Original proposed schema for a new, single-audience Replit PostgreSQL app.
-- Review against the generated migration and the actual source contract before use.
-- Apply only to an empty reader-owned database after backup/preview review.
CREATE TABLE import_batch (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  request_key char(64) NOT NULL UNIQUE,
  file_sha256 char(64) NOT NULL,
  source_name text NOT NULL CHECK (length(source_name) BETWEEN 1 AND 200),
  source_exported_at timestamptz NOT NULL,
  imported_at timestamptz NOT NULL DEFAULT now(),
  metric_id text NOT NULL CHECK (length(metric_id) BETWEEN 1 AND 100),
  metric_name text NOT NULL CHECK (length(metric_name) BETWEEN 1 AND 100),
  metric_rule text NOT NULL CHECK (length(metric_rule) BETWEEN 1 AND 500),
  unit text NOT NULL CHECK (length(unit) BETWEEN 1 AND 40),
  record_count bigint NOT NULL CHECK (record_count > 0)
);

CREATE TABLE event_record (
  import_batch_id bigint NOT NULL REFERENCES import_batch(id) ON DELETE RESTRICT,
  source_record_id text NOT NULL CHECK (length(source_record_id) BETWEEN 1 AND 200),
  occurred_at timestamptz NOT NULL,
  category text NOT NULL CHECK (length(category) BETWEEN 1 AND 100),
  measure_value numeric(20,4) NOT NULL,
  PRIMARY KEY (import_batch_id, source_record_id)
);
CREATE INDEX event_record_range_idx
  ON event_record (import_batch_id, occurred_at DESC, source_record_id DESC);
CREATE INDEX event_record_category_range_idx
  ON event_record (import_batch_id, category, occurred_at DESC, source_record_id DESC);

CREATE TABLE dashboard_state (
  singleton boolean PRIMARY KEY DEFAULT true CHECK (singleton),
  current_batch_id bigint REFERENCES import_batch(id) ON DELETE RESTRICT
);
INSERT INTO dashboard_state (singleton, current_batch_id) VALUES (true, NULL);

-- Keep accepted batches immutable. The state row is the only current pointer.
-- Restrict migration and import permissions to authorized server/operator roles.
-- A host Invite-only gate does not constrain direct database clients.

```

### Validation helper (reference-validate.mjs)

```javascript
// Pure, original validation helpers used by the proposed importer.
const fail = message => { throw new Error(message); };

export function utcInstant(value, field) {
  const match = typeof value === 'string' && /^(\d{4})-(\d\d)-(\d\d)T(\d\d):(\d\d):(\d\d)(?:\.(\d{1,3}))?(Z|([+-])(\d\d):(\d\d))$/.exec(value);
  if (!match) fail(`${field}: ISO timestamp with explicit offset required`);
  const date = new Date(value);
  if (!Number.isFinite(date.valueOf())) fail(`${field}: invalid timestamp`);
  const [, y, mo, d, h, mi, sec, ms = '', zone, sign, oh, om] = match;
  const offsetMinutes = zone === 'Z' ? 0 : (sign === '+' ? 1 : -1) * (Number(oh) * 60 + Number(om));
  if (Math.abs(offsetMinutes) > 14 * 60 || (zone !== 'Z' && Number(om) > 59)) fail(`${field}: invalid offset`);
  const local = new Date(date.valueOf() + offsetMinutes * 60000);
  const expected = `${y}-${mo}-${d}T${h}:${mi}:${sec}.${ms.padEnd(3, '0')}Z`;
  if (local.toISOString() !== expected) fail(`${field}: invalid calendar date or time`);
  return date.toISOString();
}

export function nonempty(value, field, max) {
  if (typeof value !== 'string' || !value.trim() || value.length > max) fail(`${field}: missing/too long`);
  return value.trim();
}

export function decimal(value, field, allowNegative) {
  if (typeof value !== 'string' || !/^-?(?:0|[1-9]\d{0,15})(?:\.\d{1,4})?$/.test(value)) fail(`${field}: blank, invalid or over-precision measure`);
  if (value.startsWith('-') && !allowNegative) fail(`${field}: negative/reversal not approved`);
  return value;
}

```

### Pure validation tests (reference-validate.test.mjs)

```javascript
import test from 'node:test';
import assert from 'node:assert/strict';
import { utcInstant, decimal } from './reference-validate.mjs';

test('an offset event enters the correct UTC day', () => {
  assert.equal(utcInstant('2026-10-22T00:30:00+02:00', 'event'), '2026-10-21T22:30:00.000Z');
});
test('calendar rollover is rejected, not normalized into another day', () => {
  assert.throws(() => utcInstant('2026-02-30T12:00:00Z', 'event'));
  assert.throws(() => utcInstant('2026-10-22T12:00:00', 'event'));
});
test('one-unit fixed-scale decimal rejects blank, excessive precision and unapproved reversal', () => {
  assert.equal(decimal('0', 'value', false), '0');
  assert.equal(decimal('12.3400', 'value', false), '12.3400');
  assert.throws(() => decimal('', 'value', true));
  assert.throws(() => decimal('1.23456', 'value', true));
  assert.throws(() => decimal('-1.0000', 'value', false));
  assert.equal(decimal('-1.0000', 'value', true), '-1.0000');
});

```

### Private configuration template (reference-config.template.json)

```json
{
  "sourceName": "REPLACE_WITH_REAL_SOURCE_NAME",
  "sourceExportedAt": "REPLACE_WITH_ACTUAL_ISO_TIMESTAMP_AND_OFFSET",
  "metricId": "REPLACE_WITH_APPROVED_STABLE_METRIC_ID",
  "metricName": "REPLACE_WITH_APPROVED_METRIC_NAME",
  "metricRule": "REPLACE_WITH_APPROVED_ADDITIVE_GRAIN_AND_SUM_RULE",
  "unit": "REPLACE_WITH_EXACT_ONE_UNIT_FROM_EVERY_ROW",
  "additiveGrainApproved": false,
  "allowNegative": false,
  "maxRows": 10000,
  "maxBytes": 5000000,
  "columns": {
    "sourceRecordId": "REPLACE_WITH_ACTUAL_CSV_HEADER",
    "occurredAt": "REPLACE_WITH_ACTUAL_CSV_HEADER",
    "category": "REPLACE_WITH_ACTUAL_CSV_HEADER",
    "measureValue": "REPLACE_WITH_ACTUAL_CSV_HEADER",
    "unit": "REPLACE_WITH_ACTUAL_CSV_HEADER"
  }
}

```

### Owner-run importer (reference-import.mjs)

```javascript
// Original proposed bounded importer for the new schema in reference-schema.sql.
// Requires Node 20+, `npm install pg csv-parse`, and owner review before use.
// No provider/database run has been performed by BIG CHANGE.
import { readFile } from 'node:fs/promises';
import { createHash } from 'node:crypto';
import { Client } from 'pg';
import { parse } from 'csv-parse/sync';
import { utcInstant, nonempty, decimal } from './reference-validate.mjs';

const sha256 = value => createHash('sha256').update(value).digest('hex');
const fail = message => { throw new Error(message); };
function argsOf(argv) {
  const out = {};
  for (let i = 0; i < argv.length; i += 2) {
    const key = argv[i];
    if (!key?.startsWith('--') || !argv[i + 1] || out[key]) fail('Expected unique --key value pairs');
    out[key] = argv[i + 1];
  }
  if (!['dry-run', 'apply', 'readback'].includes(out['--mode'])) fail('Use --mode dry-run|apply|readback');
  if (!['development', 'production'].includes(out['--target'])) fail('Name --target development|production');
  if (out['--mode'] === 'readback' && !/^[a-f0-9]{64}$/.test(out['--request-key'] || '')) fail('Readback needs --request-key');
  if (out['--mode'] !== 'readback' && (!out['--file'] || !out['--config'])) fail('Import needs --file and --config');
  return out;
}
function parseConfig(raw) {
  const c = JSON.parse(raw);
  const columns = c.columns;
  const expectedKeys = ['sourceRecordId', 'occurredAt', 'category', 'measureValue', 'unit'];
  if (!columns || expectedKeys.some(k => !nonempty(columns[k], `columns.${k}`, 100))) fail('Five mapped columns required');
  if (new Set(expectedKeys.map(k => columns[k])).size !== 5) fail('Mapped columns must differ');
  c.sourceName = nonempty(c.sourceName, 'sourceName', 200);
  c.sourceExportedAt = utcInstant(c.sourceExportedAt, 'sourceExportedAt');
  c.metricId = nonempty(c.metricId, 'metricId', 100);
  c.metricName = nonempty(c.metricName, 'metricName', 100);
  c.metricRule = nonempty(c.metricRule, 'metricRule', 500);
  c.unit = nonempty(c.unit, 'unit', 40);
  if (c.additiveGrainApproved !== true) fail('Owner must affirm one additive grain');
  if (typeof c.allowNegative !== 'boolean') fail('Explicit allowNegative required');
  if (!Number.isInteger(c.maxRows) || c.maxRows < 1 || c.maxRows > 10000) fail('maxRows must be 1..10000');
  if (!Number.isInteger(c.maxBytes) || c.maxBytes < 1 || c.maxBytes > 5000000) fail('maxBytes must be 1..5000000');
  return c;
}
function rowsOf(csvBytes, c) {
  if (csvBytes.length > c.maxBytes) fail('CSV exceeds owner-set size limit');
  const csvText = new TextDecoder('utf-8', { fatal: true }).decode(csvBytes);
  const table = parse(csvText, { bom: true, skip_empty_lines: true, relax_column_count: false });
  if (table.length < 2) fail('Nonempty header and at least one real row required');
  const headers = table.shift();
  if (new Set(headers).size !== headers.length) fail('Duplicate CSV header');
  const needed = Object.values(c.columns);
  if (needed.some(h => !headers.includes(h))) fail('Mapped CSV header missing');
  if (table.length > c.maxRows) fail('CSV exceeds owner-set row limit');
  const index = Object.fromEntries(Object.entries(c.columns).map(([k, h]) => [k, headers.indexOf(h)]));
  const ids = new Set();
  return table.map((row, n) => {
    const id = nonempty(row[index.sourceRecordId], `row ${n + 2} id`, 200);
    if (ids.has(id)) fail(`row ${n + 2}: duplicate source ID; resolve upstream before import`);
    ids.add(id);
    const occurredAt = utcInstant(row[index.occurredAt], `row ${n + 2} occurredAt`);
    const category = nonempty(row[index.category], `row ${n + 2} category`, 100);
    const unit = nonempty(row[index.unit], `row ${n + 2} unit`, 40);
    if (unit !== c.unit) fail(`row ${n + 2}: mixed or unexpected unit`);
    const value = decimal(row[index.measureValue], `row ${n + 2}`, c.allowNegative);
    return [id, occurredAt, category, value];
  });
}
async function targetClient(a) {
  const secret = process.env.DASHBOARD_DATABASE_URL;
  if (!secret) fail('Set DASHBOARD_DATABASE_URL privately for the selected environment');
  const u = new URL(secret);
  const host = nonempty(a['--expect-host'], '--expect-host', 255);
  const database = nonempty(a['--expect-db'], '--expect-db', 100);
  if (u.hostname !== host || decodeURIComponent(u.pathname.slice(1)) !== database) fail('Connection target does not match reviewed Settings host/database');
  if (a['--target'] === 'production' && a['--mode'] === 'apply' && a['--confirm-production'] !== 'PRODUCTION') fail('Production apply needs --confirm-production PRODUCTION');
  const client = new Client({ connectionString: secret });
  await client.connect();
  const identity = await client.query('SELECT current_database() AS db, current_user AS db_user');
  if (identity.rows[0].db !== database) { await client.end(); fail('Connected database identity differs'); }
  console.log(JSON.stringify({ target: a['--target'], host, database, dbUser: identity.rows[0].db_user }));
  return client;
}
async function readback(client, key) {
  const result = await client.query(`
    SELECT b.id, b.request_key, b.file_sha256, b.source_exported_at,
           b.record_count, b.metric_id, b.unit, b.imported_at,
           (s.current_batch_id = b.id) AS current,
           (SELECT count(*) FROM event_record e WHERE e.import_batch_id = b.id) AS actual_rows
    FROM import_batch b CROSS JOIN dashboard_state s
    WHERE s.singleton = true AND b.request_key = $1`, [key]);
  console.log(JSON.stringify(result.rows[0] || { requestKey: key, found: false }));
}
async function apply(client, rows, c, fileHash, key) {
  let committed = false;
  try {
    await client.query('BEGIN');
    const state = await client.query('SELECT current_batch_id FROM dashboard_state WHERE singleton = true FOR UPDATE');
    if (state.rowCount !== 1) fail('Missing unique dashboard state row');
    const currentId = state.rows[0].current_batch_id;
    const duplicate = await client.query('SELECT id FROM import_batch WHERE request_key = $1', [key]);
    if (duplicate.rowCount) {
      if (duplicate.rows[0].id !== currentId) fail('Old snapshot replay refused; current snapshot is newer');
      await client.query('COMMIT'); committed = true;
      console.log(JSON.stringify({ requestKey: key, currentBatchId: currentId, result: 'already_current_noop' }));
      return;
    }
    if (currentId !== null) {
      const prior = await client.query('SELECT source_exported_at, source_name, metric_id, metric_name, metric_rule, unit FROM import_batch WHERE id = $1', [currentId]);
      if (prior.rowCount !== 1) fail('Current batch metadata missing');
      if (prior.rows[0].source_name !== c.sourceName || prior.rows[0].metric_id !== c.metricId || prior.rows[0].metric_name !== c.metricName || prior.rows[0].metric_rule !== c.metricRule || prior.rows[0].unit !== c.unit) fail('Source/metric definition/unit changed; requires reviewed schema/data transition');
      if (new Date(c.sourceExportedAt) <= new Date(prior.rows[0].source_exported_at)) fail('Older or conflicting equal-time source snapshot refused');
    }
    const batch = await client.query(`INSERT INTO import_batch
      (request_key, file_sha256, source_name, source_exported_at, metric_id, metric_name, metric_rule, unit, record_count)
      VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9) RETURNING id`,
      [key, fileHash, c.sourceName, c.sourceExportedAt, c.metricId, c.metricName, c.metricRule, c.unit, rows.length]);
    const id = batch.rows[0].id;
    for (const [recordId, occurredAt, category, value] of rows) {
      await client.query(`INSERT INTO event_record
        (import_batch_id, source_record_id, occurred_at, category, measure_value)
        VALUES ($1,$2,$3,$4,$5)`, [id, recordId, occurredAt, category, value]);
    }
    await client.query('UPDATE dashboard_state SET current_batch_id = $1 WHERE singleton = true', [id]);
    await client.query('COMMIT'); committed = true;
    console.log(JSON.stringify({ requestKey: key, currentBatchId: id, recordCount: rows.length, result: 'committed' }));
  } catch (error) {
    if (!committed) { try { await client.query('ROLLBACK'); } catch {} }
    console.error(JSON.stringify({ requestKey: key, result: 'uncertain_or_rejected', errorCode: error.code || 'validation' }));
    console.error('Run --mode readback with this request key against the SAME target before any retry.');
    process.exitCode = 1;
  }
}

const a = argsOf(process.argv.slice(2));
let c, bytes, rows, fileHash, key;
if (a['--mode'] !== 'readback') {
  c = parseConfig(await readFile(a['--config'], 'utf8'));
  bytes = await readFile(a['--file']);
  rows = rowsOf(bytes, c);
  fileHash = sha256(bytes);
  key = sha256(JSON.stringify({ fileHash, sourceName: c.sourceName, sourceExportedAt: c.sourceExportedAt,
    columns: c.columns, metricId: c.metricId, metricName: c.metricName, metricRule: c.metricRule,
    unit: c.unit, allowNegative: c.allowNegative, additiveGrainApproved: c.additiveGrainApproved }));
  console.log(JSON.stringify({ requestKey: key, fileSha256: fileHash, parsedRows: rows.length,
    sourceExportedAt: c.sourceExportedAt, metricId: c.metricId, unit: c.unit, mode: a['--mode'] }));
}
if (a['--mode'] !== 'dry-run') {
  const client = await targetClient(a);
  try {
    if (a['--mode'] === 'readback') await readback(client, a['--request-key']);
    else await apply(client, rows, c, fileHash, key);
  } finally { await client.end(); }
}

```

### API and browser contract (reference-api-contract.md)

#### Original server and browser reference contract

Use with `reference-schema.sql` and `reference-import.mjs`. This is proposed implementation material for the reader's generated Express/React app, not a BIG CHANGE-tested server. Inspect actual dependencies, migrations, route files and generated code before accepting it. All published routes are behind the chosen Replit Invite only host gate; this one-audience version grants each admitted viewer the **same** dataset. Do not use it for per-user private rows.

#### Request and response grammar

`GET /api/meta` returns `{status:"ready",batchId:"<decimal ID>",sourceName,sourceExportedAt,importedAt,metricId,metricName,metricRule,unit,recordCount}` or `{status:"no_import"}`. It never invents a zero-data snapshot. A database error is HTTP 503.

The other three endpoints require `batchId`, `from`, `to` and optional `category`. IDs are decimal strings of 1–18 digits and greater than zero, with no sign/exponent; dates are literal `YYYY-MM-DD`, valid UTC calendar dates, `from < to`, maximum one year unless the owner reviews a larger range. `to` is **exclusive**. Category is either absent for all or a nonempty category present in the current batch (no arbitrary SQL expression). `/api/records` also accepts integer `limit` 1–100 (default 50) and an optional opaque `cursor`. Unknown parameters and invalid values return 400 with a generic error; stale `batchId` returns 409 `{error:"batch_changed"}`; no accepted batch returns 503 `{error:"no_import"}`. Errors use `Cache-Control: private, no-store` and no source rows. Send the same header on successful API and HTML responses; disable any generated static/SSR caching of this data.

Every successful response echoes `{batchId,from,to,category}` so the browser can compare it with the request before rendering. Summary adds `{count,measureTotal,unit}` where `measureTotal` is a fixed-four-decimal string. Time series adds `days:[{day:"YYYY-MM-DD",count,measureTotal}]`; absent days within a ready snapshot are represented as zero only when UI deliberately fills a day grid. Records adds `records:[{sourceRecordId,occurredAt,category,measureValue}]` and `nextCursor` or `null`. Stable order is `(occurred_at DESC, source_record_id DESC)`. No raw database row object or extra export column is serialized.

#### One coherent database snapshot per route

Use `pg.Pool.connect()` to check out **one client** for an entire route. Then `BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ READ ONLY`; perform current pointer, requested batch comparison and all data queries on that client; `COMMIT` or `ROLLBACK` before release. `pool.query()` calls can use different connections and cannot form this transaction. The importer locks the sole `dashboard_state` row, inserts an immutable new batch with its rows, then switches the pointer in one transaction. PostgreSQL repeatable-read reads the same committed snapshot for each SELECT in the API transaction. If an import commits after the route snapshot begins, the route may return a coherent older batch; the browser's next meta refresh sees the newer pointer. No false zero from selecting old metadata and new rows.

The first SELECT is:

```sql
SELECT s.current_batch_id, b.source_name, b.source_exported_at,
       b.imported_at, b.metric_id, b.metric_name, b.metric_rule,
       b.unit, b.record_count
FROM dashboard_state s
LEFT JOIN import_batch b ON b.id = s.current_batch_id
WHERE s.singleton = true;
```

Require exactly one state row. For a data endpoint, compare its validated `batchId` with `current_batch_id` inside this transaction. A mismatch is 409, with no data query. Bind all values as query parameters. `from` and `to` are converted to UTC midnight instants, preserving the exclusive end. `category` is `null` or a validated current-batch category. Use the following kernels with `$1=batchId`, `$2=from UTC instant`, `$3=to UTC instant`, `$4=category`:

```sql
-- /api/summary. COUNT counts source rows at the approved grain.
SELECT count(*)::text AS count,
       coalesce(sum(measure_value), 0)::numeric(38,4)::text AS measure_total
FROM event_record
WHERE import_batch_id = $1
  AND occurred_at >= $2 AND occurred_at < $3
  AND ($4::text IS NULL OR category = $4);

-- /api/timeseries. Day is the UTC calendar day of each included event.
SELECT (occurred_at AT TIME ZONE 'UTC')::date AS day,
       count(*)::text AS count,
       coalesce(sum(measure_value), 0)::numeric(38,4)::text AS measure_total
FROM event_record
WHERE import_batch_id = $1
  AND occurred_at >= $2 AND occurred_at < $3
  AND ($4::text IS NULL OR category = $4)
GROUP BY (occurred_at AT TIME ZONE 'UTC')::date
ORDER BY day;

-- /api/records. $5/$6 are NULL on first page, otherwise a verified cursor tuple;
-- $7 is validated limit+1 to detect a following page.
SELECT source_record_id, occurred_at, category, measure_value::text
FROM event_record
WHERE import_batch_id = $1
  AND occurred_at >= $2 AND occurred_at < $3
  AND ($4::text IS NULL OR category = $4)
  AND ($5::timestamptz IS NULL OR
       (occurred_at, source_record_id) < ($5::timestamptz, $6::text))
ORDER BY occurred_at DESC, source_record_id DESC
LIMIT $7;
```

PostgreSQL may return `count` as a string; convert only after checking it fits the browser's safe integer range, otherwise display a string. Preserve decimal strings as strings through JSON and use a decimal-capable formatter; do not sum money with JavaScript binary floats. Ensure the aggregate fits `numeric(38,4)` or return a server error, never a wrapped/truncated total. The schema is bounded to 10,000 rows per import in this version.

#### Cursor and browser generation rules

The server signs an opaque base64url JSON cursor with HMAC-SHA-256 using a server-only secret. Payload keys are exactly `{batchId,from,to,category,limit,lastOccurredAt,lastSourceRecordId}`. Verify signature with a constant-time comparison, validate every payload field, and require the cursor's batch/filter/limit to match the current request before SQL. Reject tampered/mismatched cursors with 400, changed current batch with 409. Do not put credentials or whole records in the token. Rotate the signing secret deliberately; old cursors may become invalid and the UI starts at page one. Do not accept a browser-supplied raw SQL tuple or offset. Return `limit` records and make `nextCursor` only if the `limit+1` query actually found another row.

For a new filter, date range, batch refresh or page selection, increment a UI request-generation number and abort pending fetches. Fetch meta first, then summary, series and records for its `batchId` and one exact filter tuple. Render **all three together** only when every response is successful, echoes that tuple, and still belongs to the latest generation. Otherwise leave an explicit loading/error/retry state; never leave a new card beside an old chart. On 409, discard all responses, reload meta and restart at page one. Ignore late responses from an older generation even if abort was too late. A next-page fetch also has its own generation and replaces only the table after it confirms the displayed batch/filter tuple is unchanged. The date controls label their values as UTC calendar dates and the UI maps an inclusive selected end date to the next day's exclusive `to`; show the actual UTC range beside chart/cards/table. Viewer locale changes formatting only, never which events enter a bucket.

#### Direct checks the reader records

Use actual owner-approved IDs and definitions, with placeholders rather than invented source facts. In development and again after production import, request `/api/meta`; record actual batch ID and source-as-of time. Request `/api/summary?batchId=<B>&from=<UTC-DATE>&to=<NEXT-UTC-DATE>` and both other routes with exactly the same tuple. Compare `count` and `measureTotal` to independently checked source rows and check three real IDs and chart bucket placement. Test a category filter and a range whose end boundary excludes an event exactly at `to`.

Before acceptance, deliberately test: no import versus valid empty range versus database failure; malformed date, category, batch ID, limit and cursor; stale cursor after a new import; repeated identical import; old-file replay; two import attempts for increasing export times; import commit between the API's pointer SELECT and data SELECT; a filter switch with slow old responses; and a page change during refresh. Expected result is one coherent batch and filter per rendered view, one serialized current pointer, no duplicated rows/count, 409 or old-coherent response at the race boundary, and no old response overwriting new UI state. Record actual observed responses and database rows; the expected cases alone are not a test pass.

## Sources

- [Replit Agent](https://docs.replit.com/features/agent/overview) — Agent and Plan-mode build path; generated behavior untested.
- [Replit development and production databases](https://docs.replit.com/features/data-and-storage/development-and-production) — Separate database environments, Helium/Neon paths and usage billing; primary retained snapshot in private packet.
- [Replit connection details](https://docs.replit.com/features/data-and-storage/connection-details) — Development DATABASE_URL and external production connection boundary.
- [Replit app access](https://docs.replit.com/features/publishing/private-deployments) — Invite only, workspace limits, admin admission and sharing change route.
- [Replit access management](https://docs.replit.com/teams/identity-and-access-management/repl-access-management) — Named/group Invite roles and limitations.
- [PostgreSQL transaction isolation](https://www.postgresql.org/docs/current/transaction-iso.html) — Repeatable-read database snapshot boundary.
- [node-postgres transactions](https://node-postgres.com/features/transactions) — One checked-out client per transaction.
- [CSV Parse sync](https://csv.js.org/parse/api/sync/) — Bounded in-memory parsing used by original proposed importer.