Build a Grievance Escalation Console
Build a review-first grievance console with traceable policies and decisions.
Introduction
30 Second Summary
A grievance rarely arrives in a tidy form. Supervisors still need to decide what happens next without losing the original message or the reason behind the decision.
In this project, you will build a grievance operations console that reviews messy CSV exports before committing cases. You will produce reproducible policy recommendations, record separate supervisor decisions, and trace every change through its source and audit history.
What You'll Build
You will import four fictional intake exports, review every uncertain row, and open a dashboard where each recommendation leads back to its policy facts, responsible team, follow-up action, human decision, and original source.
By the end of this project, you'll have:
- A review-first import flow that exposes valid rows, rejections, warnings, duplicate candidates, and uncertain mappings before any case is committed.
- Actionable recommendations that name the next step, reason, department, optional employee, and follow-up deadline while preserving every policy evaluation.
- A traceable supervisor dashboard where five operational metrics open the exact records behind each count and every decision commits with its audit event.
- Secret Mission: Re-import changed source records, reject a recommendation, reassign its owner, request clarification, and reconcile the resulting follow-up and audit chronology.
Are there any prerequisites?
You should already be comfortable with TypeScript, React, Express, SQL, and REST APIs.
You also need Node.js 24.22.0 or higher, Docker Desktop, an editor, and a GitHub account.
Before We Start
Before the hands-on work begins, commit to a review-first workflow that preserves messy source evidence instead of forcing uncertain data into a clean answer. The policy recommends an action, while a supervisor remains responsible for the decision.
Set Up the Full-Stack Workspace
A traceable grievance workflow needs a stable local foundation before any intake data arrives. Keeping one backend application beside one database makes every later transaction easier to inspect.
In this step, you will prepare Node.js, Docker Desktop, and a single repository. You will finish with a healthy PostgreSQL service that later migrations can build on.
In this step, get ready to:
- Verify Node.js and Docker Desktop.
- Create the backend, frontend, migration, test, and dataset folders.
- Start PostgreSQL through Docker Compose.
Check the local tools
The project pins Node.js 24.22.0 or higher. This keeps native TypeScript execution and the workspace dependency versions consistent.
- Check your Node.js version by running this command:
node --version✔️ I see version 24.22.0 or higher
Your Node.js runtime meets the project requirement. Keep this terminal open for the workspace commands.
ⓧ I see an older version
Upgrade Node.js before continuing. Use the official installer so the active terminal can resolve the new runtime.
- Open the official Node.js download page.
- Install Node.js 24.22.0 or a newer release from the 24 LTS line.
- Restart your terminal after the installer finishes.
ⓧ Command not found
Install Node.js before continuing. The installer also provides the npm command used throughout the project.
- Open the official Node.js download page.
- Install Node.js 24.22.0 or a newer release from the 24 LTS line.
- Restart your terminal after the installer finishes.
- Check the Docker client and running engine by running:
docker version
docker compose versionYou should see Docker client and server details followed by a Compose version. The server section proves Docker Desktop is running.
Docker check failing?
Start Docker Desktop if the client cannot reach its server. Install it from the official Docker Desktop guide if the command is missing.
Help me verify the local tools.
Create the local workspace
The repository keeps one Express backend, one React frontend, and PostgreSQL. Migrations, datasets, tests, and documentation stay in the same codebase.
- Move to your Desktop by running this command:
cd ~/Desktop- Create the repository folders by running these commands:
mkdir -p grievance-escalation-console/{server/src,server/migrations,server/tests,web/src,data/exports,docs}
cd grievance-escalation-console- Create the root package manifest by saving this file as package.json inside grievance-escalation-console:
{
"name": "grievance-escalation-console",
"private": true,
"type": "module",
"scripts": {
"dev:server": "node --watch server/src/server.ts",
"test": "node --test server/tests/*.test.ts",
"migrate": "node server/src/migrate.ts",
"generate:data": "node server/src/generate-data.ts",
"ci": "npm test && npm run build"
},
"engines": { "node": ">=24.22.0" },
"dependencies": {
"csv-parse": "7.0.3",
"express": "5.3.0",
"pg": "8.23.1",
"react": "19.3.1",
"react-dom": "19.3.1"
},
"devDependencies": { "typescript": "7.0.2", "vite": "8.3.4" }
}What does this manifest establish?
- scripts keeps setup, migrations, dataset generation, tests, and builds available from one terminal.
- Exact dependency pins make the learning environment reproducible.
- engines rejects an unsupported Node.js runtime before confusing TypeScript errors appear.
- Install the pinned dependencies by running:
npm installThe first install can take a minute while npm downloads the packages. You should see a new node_modules folder and package-lock.json file.
Dependencies not installing?
Confirm the terminal is inside grievance-escalation-console. Check the first npm error for an unsupported Node.js version or a network failure.
Help me diagnose the installation.
Start PostgreSQL
- Create compose.yaml inside grievance-escalation-console with this configuration:
services:
db:
image: postgres:18.6-alpine3.24
environment:
POSTGRES_DB: grievances
POSTGRES_USER: app
POSTGRES_PASSWORD: local-development-only
ports:
- "5432:5432"
volumes:
- pg_data:/var/lib/postgresql
volumes:
pg_data:Why keep one database service?
The Compose service runs one local PostgreSQL database. The named volume preserves data when the container stops.
The password is only for the local learning environment. Production credentials belong in a secret manager.
- Start PostgreSQL in the background by running:
docker compose up -d- Confirm PostgreSQL is accepting connections by running:
docker compose exec db pg_isready -U app -d grievancesYou should see accepting connections. That is the foundation ready: one repository now reaches one local database.
PostgreSQL not ready?
Run the command from the folder containing compose.yaml. If the container exited, inspect its first startup error in Docker Desktop.
Help me debug the database service.
Your local foundation is running. Next, you will fill it with fictional departments, employees, and deliberately messy intake exports.
Import Cases Safely
Generate Messy Intake Exports
Your repository and database are ready. A useful import workflow now needs source material that behaves like real handoffs instead of a polished template.
This step creates a fictional directory and 200 reproducible complaints across WhatsApp, website, Facebook, and manually maintained spreadsheet exports. The data stays synthetic, but its missing values and inconsistent shapes force the console to handle uncertainty honestly.
In this step, get ready to:
- Create the core directory and intake tables through a migration.
- Seed fictional departments and employees.
- Generate four messy CSV exports with deliberate edge cases.
Create the migration foundation
A migration gives every learner the same database shape. The first tables preserve the source handoff before any row becomes a trusted case.
- Create 001_core.sql inside server/migrations.
- Add the department and employee directory with this first section:
-- Store the fictional organizational directory.
CREATE TABLE departments (
id BIGSERIAL PRIMARY KEY,
code TEXT UNIQUE NOT NULL,
name TEXT NOT NULL,
active BOOLEAN NOT NULL DEFAULT TRUE
);
CREATE TABLE employees (
id BIGSERIAL PRIMARY KEY,
employee_code TEXT UNIQUE NOT NULL,
name TEXT NOT NULL,
department_id BIGINT NOT NULL REFERENCES departments(id),
role TEXT NOT NULL,
active BOOLEAN NOT NULL DEFAULT TRUE,
escalation_level INTEGER NOT NULL CHECK (escalation_level BETWEEN 1 AND 5),
official_email TEXT,
official_phone TEXT
);
-- Keep names and contacts fictional in every seed.
INSERT INTO departments (code,name) VALUES
('WATER','Water Services'),('ROADS','Roads and Transport'),
('HEALTH','Public Health'),('ADMIN','Citizen Administration');What does the directory preserve?
- departments identifies the team responsible for an action.
- employees stores fictional staff with stable IDs, roles, active status, escalation levels, and optional official contacts.
- The foreign key prevents an employee from pointing at an unknown department.
- Add the fictional employee seed below the department inserts:
-- Seed fictional staff for deterministic demos.
INSERT INTO employees
(employee_code,name,department_id,role,active,escalation_level,official_email)
SELECT seed.employee_code,seed.name,d.id,seed.role,TRUE,seed.level,seed.email
FROM (VALUES
('EMP-1001','Asha Rai','WATER','Case Officer',1,'asha.rai@example.invalid'),
('EMP-1002','Nabin Shrestha','WATER','Supervisor',2,'nabin.shrestha@example.invalid'),
('EMP-2001','Mina Gurung','ROADS','Case Officer',1,'mina.gurung@example.invalid'),
('EMP-2002','Suman Thapa','ROADS','Supervisor',2,'suman.thapa@example.invalid'),
('EMP-3001','Rojina Karki','HEALTH','Case Officer',1,'rojina.karki@example.invalid'),
('EMP-4001','Bikash Lama','ADMIN','Review Officer',2,'bikash.lama@example.invalid')
) AS seed(employee_code,name,department_code,role,level,email)
JOIN departments d ON d.code=seed.department_code;- Add the import provenance tables below the employee seed:
-- Preserve every import and original source row.
CREATE TABLE import_batches (
id BIGSERIAL PRIMARY KEY,
source_platform TEXT NOT NULL,
source_name TEXT NOT NULL,
source_fingerprint TEXT UNIQUE NOT NULL,
imported_at TIMESTAMPTZ NOT NULL,
imported_by TEXT NOT NULL,
status TEXT NOT NULL CHECK (status IN ('PREVIEW','COMMITTED','CANCELLED'))
);
CREATE TABLE import_rows (
id BIGSERIAL PRIMARY KEY,
batch_id BIGINT NOT NULL REFERENCES import_batches(id),
row_number INTEGER NOT NULL,
source_record_id TEXT,
source_url TEXT,
original_values JSONB NOT NULL,
proposed_values JSONB NOT NULL,
outcome TEXT NOT NULL,
warnings JSONB NOT NULL DEFAULT '[]',
errors JSONB NOT NULL DEFAULT '[]',
duplicate_case_id BIGINT,
UNIQUE(batch_id,row_number)
);Why store every source row?
import_batches records the platform, source file, fingerprint, actor, timestamp, and review state. import_rows keeps the untouched source object beside any proposed normalization.
Rejected and uncertain rows remain visible because they are evidence about the handoff, even when they never become cases.
- Add the case table below the import tables:
-- Create cases only after a human commits reviewed rows.
CREATE TABLE cases (
id BIGSERIAL PRIMARY KEY,
source_platform TEXT NOT NULL,
source_record_id TEXT,
source_url TEXT,
import_batch_id BIGINT NOT NULL REFERENCES import_batches(id),
import_row_id BIGINT UNIQUE NOT NULL REFERENCES import_rows(id),
original_values JSONB NOT NULL,
title TEXT NOT NULL,
description TEXT,
received_date DATE,
normalized_status TEXT NOT NULL,
department_id BIGINT REFERENCES departments(id),
employee_id BIGINT REFERENCES employees(id),
created_at TIMESTAMPTZ NOT NULL,
UNIQUE(source_platform,source_record_id)
);The source identity constraint supports repeat imports without overwriting the case. Null source IDs remain possible because manual spreadsheets do not always provide one.
Run migrations in order
The migration runner applies each new SQL file once. It stores the filename after a successful transaction so a failed migration can be corrected and retried.
- Create migrate.ts inside server/src with this runner:
import { readdir, readFile } from "node:fs/promises";
import { Pool } from "pg";
// Connect to the local Compose database.
const pool = new Pool({
connectionString: "postgres://app:local-development-only@localhost:5432/grievances",
});
await pool.query(`CREATE TABLE IF NOT EXISTS schema_migrations (
filename TEXT PRIMARY KEY,
applied_at TIMESTAMPTZ NOT NULL DEFAULT now()
)`);
const files = (await readdir("server/migrations")).filter((name) => name.endsWith(".sql")).sort();
for (const filename of files) {
const applied = await pool.query("SELECT 1 FROM schema_migrations WHERE filename=$1", [filename]);
if (applied.rowCount) continue;
const client = await pool.connect();
try {
await client.query("BEGIN");
await client.query(await readFile(`server/migrations/${filename}`, "utf8"));
await client.query("INSERT INTO schema_migrations(filename) VALUES($1)", [filename]);
await client.query("COMMIT");
console.log(`applied ${filename}`);
} catch (error) { await client.query("ROLLBACK"); throw error; }
finally { client.release(); }
}
await pool.end();How does the runner stay safe?
- Sorted filenames make migration order deterministic.
- One checked-out PostgreSQL client runs each migration transaction.
- The migration record commits with the schema change, so the two cannot disagree.
- Save 001_core.sql and migrate.ts.
- Apply the migration by running:
npm run migrateYou should see applied 001_core.sql. The database now contains a fictional directory and source-preserving intake tables.
Migration not applying?
Check that PostgreSQL is still running. If the migration failed halfway through, the transaction rolls back, so correct the first SQL error and run the command again.
Help me debug the core migration.
Generate four messy exports
A seeded generator creates the same 200 fictional records on every machine. Each source uses different column names and introduces missing fields, mixed dates, ambiguous statuses, repeated IDs, and similar complaint text.
- Create generate-data.ts inside server/src with this record generator:
import { mkdir, writeFile } from "node:fs/promises";
// Keep the fictional dataset reproducible.
let seed = 20261016;
const random = () => ((seed = Math.imul(seed, 1664525) + 1013904223 >>> 0) / 2 ** 32);
const sources = ["whatsapp", "website", "facebook", "spreadsheet"] as const;
const topics = ["Water supply stopped", "Road damage near school", "Clinic queue delay", "Certificate correction"];
const statuses = ["open", "Resolved", "pending?", "in progress", ""];
function dateValue(index: number): string {
const day = (index % 25) + 1;
if (index % 11 === 0) return "";
if (index % 3 === 0) return `${String(day).padStart(2,"0")}/10/2026`;
if (index % 3 === 1) return `2026-10-${String(day).padStart(2,"0")}`;
return `${day} Oct 2026`;
}
function sourceRecord(index: number, source: typeof sources[number]) {
const repeated = index > 0 && index % 37 === 0 ? index - 1 : index;
const title = index % 29 === 0 ? "Water leaking beside the market" : topics[index % topics.length];
return { id:`${source.slice(0,2).toUpperCase()}-${1000+repeated}`, title, detail:`Fictional report ${index+1}`, date:dateValue(index), status:statuses[Math.floor(random()*statuses.length)], url:source === "website" || source === "facebook" ? `https://example.invalid/${source}/${1000+repeated}` : "" };
}Where does the mess come from?
- seed makes every generated run reproducible.
- dateValue() rotates through ISO, day-first, written, and missing dates.
- sourceRecord() adds repeated source IDs and similar complaint titles for duplicate review.
- Add the source-specific column mapping below sourceRecord():
function exportShape(source: typeof sources[number], record: ReturnType<typeof sourceRecord>) {
// Simulate the columns produced by each intake channel.
if (source === "whatsapp") return { message_id:record.id, message:record.title, notes:record.detail, sent_at:record.date, state:record.status };
if (source === "website") return { submission_id:record.id, subject:record.title, description:record.detail, received_on:record.date, status:record.status, source_url:record.url };
if (source === "facebook") return { post_ref:record.id, post_text:record.title, extra:record.detail, created:record.date, moderation_state:record.status, permalink:record.url };
return { complaint_no:record.id, complaint:record.title, remarks:record.detail, date_received:record.date, case_status:record.status };
}
function csvCell(value: unknown): string {
const text = String(value ?? "");
return `"${text.replaceAll('"','""')}"`;
}
function toCsv(rows: Record<string,unknown>[]): string {
const headers = Object.keys(rows[0]);
return [headers.join(","), ...rows.map((row) => headers.map((key) => csvCell(row[key])).join(","))].join("\n");
}Why use different export shapes?
Each source gets its own realistic header vocabulary. The import review must map those headers instead of assuming one universal template.
csvCell() quotes every value so commas and quotes remain valid CSV content.
- Add the file-writing loop below toCsv():
await mkdir("data/exports", { recursive:true });
for (const source of sources) {
// Write 50 records per source for a 200-case demonstration set.
const rows = Array.from({ length:50 }, (_, index) => exportShape(source, sourceRecord(index, source)));
await writeFile(`data/exports/${source}.csv`, toCsv(rows));
console.log(`${source}: ${rows.length} rows`);
}
console.log("edge cases: mixed dates, missing fields, ambiguous statuses, repeated IDs, similar complaints");- Save generate-data.ts.
- Generate the four source exports by running:
npm run generate:dataYou should see four lines reporting 50 rows followed by the edge-case summary. The data/exports folder now contains 200 reproducible fictional complaints.
Exports not generated?
Confirm all three generator chunks are in one generate-data.ts file. A missing brace near exportShape() can stop the final file-writing loop.
Help me debug the dataset generator.
Your intake data now looks like the handoffs a real operations team must untangle. Next, you will stage every row and make uncertainty visible before a case is committed.
Review and Commit Imports
The four exports now preserve the mess found at each intake source. The next risk is converting that mess into trusted cases before a supervisor has reviewed warnings and duplicate evidence.
This step stages every row, classifies its outcome, and separates preview from commit. Important values are never guessed, and repeat imports cannot silently replace assignments or decisions.
In this step, get ready to:
- Map source-specific columns without hiding uncertainty.
- Preview valid, rejected, warning, duplicate, and changed-source rows.
- Commit only explicitly selected rows with their import audit events.
Add workflow and audit storage
The preview must survive a page refresh or server restart. Storing its row outcomes also proves that rejected data was reviewed instead of silently discarded.
- Create 002_workflow.sql inside server/migrations with this audit table:
-- Store append-only evidence for every business change.
CREATE TABLE audit_events (
id BIGSERIAL PRIMARY KEY,
entity_type TEXT NOT NULL,
entity_id TEXT NOT NULL,
event_type TEXT NOT NULL,
actor_id TEXT NOT NULL,
event_time TIMESTAMPTZ NOT NULL,
before_values JSONB,
after_values JSONB NOT NULL,
metadata JSONB NOT NULL DEFAULT '{}'
);
CREATE INDEX audit_entity_time_idx
ON audit_events(entity_type,entity_id,event_time,id);
-- Track explicit mapping confirmations during review.
ALTER TABLE import_rows
ADD COLUMN mapping_confidence TEXT NOT NULL DEFAULT 'KNOWN',
ADD COLUMN confirmed_by TEXT,
ADD COLUMN confirmed_at TIMESTAMPTZ;What makes the review traceable?
before_values and after_values show what changed. The actor and timestamp show who made the change and when.
Mapping confirmations stay attached to the staged row. The imported case therefore retains evidence of the human review.
- Save 002_workflow.sql.
- Apply the new migration by running:
npm run migrateYou should see applied 002_workflow.sql. Preview outcomes and later business changes now have durable audit storage.
Classify the source rows
Each source uses a known mapping profile, but a known column does not guarantee a trustworthy value. Dates, statuses, and likely duplicates still need row-level review.
- Create import-service.ts inside server/src with these source contracts:
import { createHash } from "node:crypto";
import { parse } from "csv-parse/sync";
import type { Pool, PoolClient } from "pg";
export type Platform = "whatsapp"|"website"|"facebook"|"spreadsheet";
type Mapping = { id:string;title:string;description:string;date:string;status:string;url?:string };
const mappings: Record<Platform,Mapping> = {
whatsapp:{ id:"message_id",title:"message",description:"notes",date:"sent_at",status:"state" },
website:{ id:"submission_id",title:"subject",description:"description",date:"received_on",status:"status",url:"source_url" },
facebook:{ id:"post_ref",title:"post_text",description:"extra",date:"created",status:"moderation_state",url:"permalink" },
spreadsheet:{ id:"complaint_no",title:"complaint",description:"remarks",date:"date_received",status:"case_status" },
};
const knownStatuses: Record<string,string> = { open:"OPEN", resolved:"RESOLVED", "in progress":"IN_PROGRESS" };
const fingerprint = (text:string) => createHash("sha256").update(text).digest("hex");
const normalizedText = (value:unknown) => String(value ?? "").trim();Why avoid a universal mapping?
- mappings documents the expected handoff from each simulated intake platform.
- knownStatuses translates only unambiguous values.
- fingerprint() creates a cryptographic identity for byte-identical repeat files.
- Add the date and row normalization helpers below normalizedText:
function parseDate(value:string): string|null {
// Accept the three demonstrated formats without guessing ambiguous month/day order.
if (!value) return null;
if (/^2026-\d{2}-\d{2}$/.test(value)) return value;
const dayFirst = value.match(/^(\d{2})\/10\/2026$/);
if (dayFirst) return `2026-10-${dayFirst[1]}`;
const written = value.match(/^(\d{1,2}) Oct 2026$/);
return written ? `2026-10-${written[1].padStart(2,"0")}` : null;
}
function normalizeRow(raw:Record<string,unknown>, mapping:Mapping) {
const originalStatus = normalizedText(raw[mapping.status]).toLowerCase();
const receivedDate = parseDate(normalizedText(raw[mapping.date]));
return {
sourceRecordId:normalizedText(raw[mapping.id]) || null,
sourceUrl:mapping.url ? normalizedText(raw[mapping.url]) || null : null,
title:normalizedText(raw[mapping.title]),
description:normalizedText(raw[mapping.description]) || null,
receivedDate,
normalizedStatus:knownStatuses[originalStatus] ?? null,
originalStatus,
};
}How does normalization avoid false certainty?
The date parser accepts only formats present in the demonstration exports. An unsupported or missing date becomes a warning instead of a guessed deadline.
An unknown status remains null. That row receives a clarification outcome before any policy evaluation can run.
- Add the preview function below normalizeRow():
export async function previewImport(pool:Pool, platform:Platform, name:string, csv:string, actorId:string) {
const rawRows = parse(csv,{ columns:true,skip_empty_lines:true }) as Record<string,unknown>[];
const sourceHash = fingerprint(csv);
const prior = await pool.query("SELECT id FROM import_batches WHERE source_fingerprint=$1",[sourceHash]);
if (prior.rows[0]) return { repeatedBatchId:String(prior.rows[0].id), counts:{ alreadyImported:rawRows.length } };
const client = await pool.connect();
try {
await client.query("BEGIN");
const batch = await client.query(`INSERT INTO import_batches(source_platform,source_name,source_fingerprint,imported_at,imported_by,status) VALUES($1,$2,$3,now(),$4,'PREVIEW') RETURNING id`,[platform,name,sourceHash,actorId]);
const batchId = String(batch.rows[0].id);
for (const [index,raw] of rawRows.entries()) await stageRow(client,batchId,index+2,platform,raw);
await client.query("COMMIT");
return await reviewSummary(pool,batchId);
} catch (error) { await client.query("ROLLBACK"); throw error; }
finally { client.release(); }
}How are repeat files handled?
A byte-identical file returns its existing batch before staging another copy. New files use one transaction for the batch and all row outcomes.
The summary is queried from stored rows after commit. The response therefore describes durable review evidence.
- Add the staging classifier below previewImport():
async function stageRow(client:PoolClient,batchId:string,rowNumber:number,platform:Platform,raw:Record<string,unknown>) {
const proposed = normalizeRow(raw,mappings[platform]);
const warnings:string[] = [];
const errors:string[] = [];
if (!proposed.title) errors.push("A complaint title is required");
if (!proposed.receivedDate) warnings.push("Received date needs clarification");
if (!proposed.normalizedStatus) warnings.push("Status mapping needs clarification");
const sameSource = proposed.sourceRecordId ? await client.query("SELECT id,original_values FROM cases WHERE source_platform=$1 AND source_record_id=$2",[platform,proposed.sourceRecordId]) : { rows:[] };
const similar = proposed.title ? await client.query("SELECT id FROM cases WHERE lower(title)=lower($1) LIMIT 1",[proposed.title]) : { rows:[] };
let outcome = errors.length ? "REJECTED" : warnings.length ? "NEEDS_REVIEW" : "VALID";
let duplicateCaseId:string|null = null;
if (sameSource.rows[0]) { duplicateCaseId=String(sameSource.rows[0].id); outcome=JSON.stringify(sameSource.rows[0].original_values)===JSON.stringify(raw)?"ALREADY_IMPORTED":"SOURCE_CHANGE_REVIEW"; }
else if (similar.rows[0]) { duplicateCaseId=String(similar.rows[0].id); outcome="POSSIBLE_DUPLICATE"; }
await client.query(`INSERT INTO import_rows(batch_id,row_number,source_record_id,source_url,original_values,proposed_values,outcome,warnings,errors,duplicate_case_id) VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10)`,[batchId,rowNumber,proposed.sourceRecordId,proposed.sourceUrl,raw,proposed,outcome,JSON.stringify(warnings),JSON.stringify(errors),duplicateCaseId]);
}What outcomes can a row receive?
- REJECTED identifies rows that cannot become cases.
- NEEDS_REVIEW exposes missing dates or ambiguous statuses.
- ALREADY_IMPORTED skips an unchanged source record.
- SOURCE_CHANGE_REVIEW prevents a later export from silently overwriting prior work.
- POSSIBLE_DUPLICATE flags matching complaint text for a supervisor.
- Add the review summary below stageRow():
async function reviewSummary(pool:Pool,batchId:string) {
// Return grouped evidence instead of one opaque success count.
const rows = await pool.query("SELECT id,row_number,source_record_id,original_values,proposed_values,outcome,warnings,errors,duplicate_case_id FROM import_rows WHERE batch_id=$1 ORDER BY row_number",[batchId]);
const groups:Record<string,unknown[]> = { valid:[],rejected:[],warnings:[],possibleDuplicates:[],uncertainMappings:[],alreadyImported:[],changedSourceRecords:[] };
for (const row of rows.rows) {
if (row.outcome === "VALID") groups.valid.push(row);
if (row.outcome === "REJECTED") groups.rejected.push(row);
if (row.warnings.length) groups.warnings.push(row);
if (row.outcome === "POSSIBLE_DUPLICATE") groups.possibleDuplicates.push(row);
if (row.outcome === "NEEDS_REVIEW") groups.uncertainMappings.push(row);
if (row.outcome === "ALREADY_IMPORTED") groups.alreadyImported.push(row);
if (row.outcome === "SOURCE_CHANGE_REVIEW") groups.changedSourceRecords.push(row);
}
return { batchId, counts:Object.fromEntries(Object.entries(groups).map(([key,value]) => [key,value.length])), groups };
}The preview names every category the supervisor must inspect. No row disappears behind a single imported count.
Expose preview and commit routes
The API separates preview from commit. The commit request names the accepted staged rows and any uncertain rows the reviewer explicitly confirmed.
- Create server.ts inside server/src with the preview route:
import express from "express";
import { Pool } from "pg";
import { previewImport, type Platform } from "./import-service.ts";
const app = express();
const pool = new Pool({ connectionString:"postgres://app:local-development-only@localhost:5432/grievances" });
app.post("/api/imports/preview",express.text({ type:"text/csv",limit:"512kb" }),async(req,res) => {
try {
const platform = String(req.headers["x-source-platform"] ?? "") as Platform;
if (!["whatsapp","website","facebook","spreadsheet"].includes(platform)) {
res.status(400).json({ error:{ code:"INVALID_PLATFORM",message:"x-source-platform is required" } });
return;
}
const result = await previewImport(pool,platform,String(req.headers["x-source-name"] ?? "upload.csv"),String(req.body),String(req.headers["x-actor-id"] ?? "import-reviewer"));
res.status(201).json(result);
} catch (error) {
res.status(400).json({ error:{ code:"IMPORT_PREVIEW_FAILED",message:error instanceof Error?error.message:"Preview failed" } });
}
});What does the route validate?
The route accepts raw CSV and requires an explicit source platform. Invalid requests return a stable error code with a useful message.
The 512 KiB body limit bounds memory use before parsing. The generated demonstration files stay comfortably below that limit.
- Add the commit route below the preview route:
app.use(express.json({ limit:"64kb" }));
app.post("/api/imports/:batchId/commit",async(req,res) => {
const actorId = String(req.body.actorId ?? "import-reviewer");
const rowIds = Array.isArray(req.body.rowIds) ? req.body.rowIds.map(String) : [];
const confirmedRowIds = new Set(Array.isArray(req.body.confirmedRowIds) ? req.body.confirmedRowIds.map(String) : []);
const client = await pool.connect();
try {
await client.query("BEGIN");
const rows = await client.query("SELECT * FROM import_rows WHERE batch_id=$1 AND id=ANY($2::bigint[]) FOR UPDATE",[req.params.batchId,rowIds]);
const committed:string[] = [];
for (const row of rows.rows) {
const allowed = row.outcome === "VALID" || confirmedRowIds.has(String(row.id));
if (!allowed || ["REJECTED","ALREADY_IMPORTED","SOURCE_CHANGE_REVIEW"].includes(row.outcome)) continue;
committed.push(await insertCaseAndAudit(client,row,actorId));
}
await client.query("UPDATE import_batches SET status='COMMITTED' WHERE id=$1",[req.params.batchId]);
await client.query("COMMIT");
res.status(201).json({ batchId:req.params.batchId,committedCaseIds:committed,requested:rowIds.length });
} catch (error) { await client.query("ROLLBACK"); res.status(409).json({ error:{ code:"IMPORT_COMMIT_FAILED",message:error instanceof Error?error.message:"Commit failed" } }); }
finally { client.release(); }
});What can the commit accept?
Selected valid rows can commit directly. Warning and possible-duplicate rows require an explicit confirmation ID.
Rejected, unchanged, and changed-source rows cannot use this commit path. Changed source records remain in review so existing assignments and decisions stay untouched.
- Add the transactional case helper and server listener below the commit route:
async function insertCaseAndAudit(client:any,row:any,actorId:string):Promise<string> {
const value = row.proposed_values;
const createdAt = new Date().toISOString();
const inserted = await client.query(`INSERT INTO cases(source_platform,source_record_id,source_url,import_batch_id,import_row_id,original_values,title,description,received_date,normalized_status,created_at) SELECT b.source_platform,$1,$2,b.id,$3,$4,$5,$6,$7,$8,$9 FROM import_batches b WHERE b.id=$10 ON CONFLICT(source_platform,source_record_id) DO NOTHING RETURNING id`,[row.source_record_id,row.source_url,row.id,row.original_values,value.title,value.description,value.receivedDate,value.normalizedStatus??"NEEDS_CLARIFICATION",createdAt,row.batch_id]);
if (!inserted.rows[0]) throw new Error("Source record already exists; review required");
const caseId = String(inserted.rows[0].id);
await client.query(`INSERT INTO audit_events(entity_type,entity_id,event_type,actor_id,event_time,before_values,after_values,metadata) VALUES('case',$1,'CASE_IMPORTED',$2,$3,NULL,$4,$5)`,[caseId,actorId,createdAt,{ caseId,sourceRecordId:row.source_record_id },{ batchId:String(row.batch_id),importRowId:String(row.id) }]);
return caseId;
}
app.get("/api/health",async(_req,res) => res.json({ status:"ok",database:(await pool.query("SELECT 1")).rowCount===1 }));
app.listen(3000,() => console.log("API listening at http://localhost:3000"));Why is the import audit transactional?
The case insert and CASE_IMPORTED audit event share the same checked-out client. A failure rolls back both records.
The unique source identity remains the last safety net against concurrent repeat imports.
- Save import-service.ts and server.ts.
- Start the API by running:
npm run dev:serverYou should see API listening at http://localhost:3000. Keep this terminal running for the preview check.
- Create preview-demo.ts inside server/src with this API example:
import { readFile } from "node:fs/promises";
// Preview one simulated website export without committing cases.
const csv = await readFile("data/exports/website.csv","utf8");
const response = await fetch("http://localhost:3000/api/imports/preview",{
method:"POST",
headers:{
"content-type":"text/csv",
"x-source-platform":"website",
"x-source-name":"website.csv",
"x-actor-id":"demo-reviewer",
},
body:csv,
});
const result = await response.json();
console.log(JSON.stringify(result.counts,null,2));
console.log(`review batch: ${result.batchId ?? result.repeatedBatchId}`);- Open a second terminal in grievance-escalation-console.
- Preview the website export by running:
node server/src/preview-demo.tsYou should see separate counts for valid rows, rejections, warnings, possible duplicates, uncertain mappings, unchanged records, and changed source records. No cases have been committed.
Preview not showing every group?
Confirm the API terminal is still running and the database is available. If the result is a repeated batch, regenerate the database or use another source file for the first preview.
Help me debug the import preview.
Your import boundary now makes uncertainty visible before commit. Next, you will configure Nepal working days so every deadline can be reproduced from stored calendar assumptions.
Detect Deadline Risks
Configure Nepal Working Days
Your import preview now distinguishes trustworthy rows from uncertain handoffs. A received date still has no meaning until the console knows which local dates count toward a Nepal deadline.
This step stores the workweek, timezone, holidays, deadline inclusivity, and missing-date behavior as policy data. The calculator remains a pure function so the same configuration and facts always produce the same trace.
In this step, get ready to:
- Store a versioned Nepal working-day configuration.
- Calculate deadlines with a date-by-date trace.
- Test Saturdays, holidays, inclusivity, and missing dates.
Store the Nepal calendar policy
Nepal time uses Asia/Kathmandu. The demonstration policy treats Sunday through Friday as working days and Saturday as the weekly holiday.
Which assumptions come from Nepal?
Nepal government sources identify Nepal time as GMT+5:45. Government office schedules show Sunday through Friday operations with Saturday as the official weekly holiday.
The complete holiday list remains configuration because applicable national, provincial, and local closures can change. Use the Ministry of Home Affairs 2083 publication as the authoritative source when loading production dates.
- Create 003_policy.sql inside server/migrations with the versioned policy tables:
-- Store complete, versioned calendar assumptions.
CREATE TABLE policy_configs (
id BIGSERIAL PRIMARY KEY,
policy_version TEXT UNIQUE NOT NULL,
timezone TEXT NOT NULL,
working_iso_days INTEGER[] NOT NULL,
allowed_working_days INTEGER NOT NULL CHECK (allowed_working_days > 0),
deadline_inclusivity TEXT NOT NULL CHECK (deadline_inclusivity IN ('DUE_THROUGH_END_OF_DAY','OVERDUE_ON_DUE_DATE')),
missing_date_behavior TEXT NOT NULL CHECK (missing_date_behavior='NEEDS_CLARIFICATION'),
effective_from DATE NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE calendar_holidays (
id BIGSERIAL PRIMARY KEY,
policy_id BIGINT NOT NULL REFERENCES policy_configs(id),
holiday_date DATE NOT NULL,
name TEXT NOT NULL,
source_url TEXT NOT NULL,
UNIQUE(policy_id,holiday_date)
);Why store policy choices as data?
working_iso_days uses ISO weekday numbers, where Monday is 1 and Sunday is 7. Nepal's configured set is {7,1,2,3,4,5}.
deadline_inclusivity makes the due-date boundary explicit. Missing received dates produce clarification instead of a false escalation.
- Add the demonstration policy seed below the table definitions:
-- Seed a Nepal demo policy with selected 2083 holiday dates.
INSERT INTO policy_configs
(policy_version,timezone,working_iso_days,allowed_working_days,deadline_inclusivity,missing_date_behavior,effective_from)
VALUES
('NP-GRIEVANCE-2026-V1','Asia/Kathmandu','{7,1,2,3,4,5}',5,'DUE_THROUGH_END_OF_DAY','NEEDS_CLARIFICATION','2026-04-14');
INSERT INTO calendar_holidays(policy_id,holiday_date,name,source_url)
SELECT p.id,h.day::date,h.name,'https://moha.gov.np/en/page/government-and-public-holidays-in-2083'
FROM policy_configs p
CROSS JOIN (VALUES
('2026-10-20','Dashain holiday'),
('2026-10-21','Dashain holiday'),
('2026-11-11','Tihar holiday')
) AS h(day,name)
WHERE p.policy_version='NP-GRIEVANCE-2026-V1';The selected dates create useful regression boundaries for the demonstration. A production rollout must load the complete applicable holiday calendar and preserve its source.
- Save 003_policy.sql.
- Apply the policy migration by running:
npm run migrateYou should see applied 003_policy.sql. PostgreSQL now holds the exact calendar assumptions that future evaluations will snapshot.
Build the working-day calculator
- Create policy.ts inside server/src with these policy contracts:
export interface PolicySnapshot {
policyVersion:string;
timezone:"Asia/Kathmandu";
workingIsoDays:number[];
holidays:Array<{ date:string;name:string;sourceUrl:string }>;
allowedWorkingDays:number;
deadlineInclusivity:"DUE_THROUGH_END_OF_DAY"|"OVERDUE_ON_DUE_DATE";
missingDateBehavior:"NEEDS_CLARIFICATION";
}
export interface DeadlineTraceEntry {
date:string;
counted:boolean;
reason:"WORKING_DAY"|"WEEKLY_HOLIDAY"|"PUBLIC_HOLIDAY";
runningCount:number;
}
export interface DeadlineResult {
outcome:"CALCULATED"|"NEEDS_CLARIFICATION";
deadline:string|null;
trace:DeadlineTraceEntry[];
}What do the contracts preserve?
- PolicySnapshot contains every assumption required to repeat an evaluation.
- DeadlineTraceEntry explains why each local date counted or did not count.
- DeadlineResult makes clarification a first-class outcome.
- Add the date helpers below DeadlineResult:
function parseDateOnly(value:string):Date|null {
// Parse complete Gregorian dates without local-machine timezone shifts.
if (!/^\d{4}-\d{2}-\d{2}$/.test(value)) return null;
const [year,month,day] = value.split("-").map(Number);
const date = new Date(Date.UTC(year,month-1,day));
return date.getUTCFullYear()===year && date.getUTCMonth()===month-1 && date.getUTCDate()===day ? date : null;
}
const formatDate = (date:Date) => date.toISOString().slice(0,10);
function nextDate(date:Date):Date {
const next = new Date(date);
next.setUTCDate(next.getUTCDate()+1);
return next;
}
function isoWeekday(date:Date):number {
return ((date.getUTCDay()+6)%7)+1;
}
function classification(date:Date,policy:PolicySnapshot) {
const value = formatDate(date);
if (policy.holidays.some((holiday) => holiday.date===value)) return { counted:false,reason:"PUBLIC_HOLIDAY" as const };
if (!policy.workingIsoDays.includes(isoWeekday(date))) return { counted:false,reason:"WEEKLY_HOLIDAY" as const };
return { counted:true,reason:"WORKING_DAY" as const };
}How are dates classified?
UTC methods keep date-only arithmetic stable on any learner machine. The policy timezone still records which civil calendar governs the dates.
The holiday check runs before the weekly check so a named public holiday remains visible in the trace.
- Add the deadline function below classification():
export function calculateDeadline(receivedDate:string|null,policy:PolicySnapshot):DeadlineResult {
// Missing or malformed dates require clarification.
if (!receivedDate) return { outcome:"NEEDS_CLARIFICATION",deadline:null,trace:[] };
const start = parseDateOnly(receivedDate);
if (!start) return { outcome:"NEEDS_CLARIFICATION",deadline:null,trace:[] };
const trace:DeadlineTraceEntry[] = [];
let cursor = start;
let runningCount = 0;
while (runningCount < policy.allowedWorkingDays) {
cursor = nextDate(cursor);
const current = classification(cursor,policy);
if (current.counted) runningCount += 1;
trace.push({ date:formatDate(cursor),counted:current.counted,reason:current.reason,runningCount });
}
return { outcome:"CALCULATED",deadline:formatDate(cursor),trace };
}
export function isOverdue(asOfDate:string,deadline:string,policy:PolicySnapshot):boolean {
return policy.deadlineInclusivity==="DUE_THROUGH_END_OF_DAY" ? asOfDate>deadline : asOfDate>=deadline;
}How is the deadline reproduced?
The count begins after the received date. Every skipped Saturday or holiday still appears in the trace.
isOverdue() applies the stored inclusivity rule instead of burying the boundary inside a comparison.
Add calendar regression tests
- Create policy.test.ts inside server/tests with the shared policy fixture:
import assert from "node:assert/strict";
import test from "node:test";
import { calculateDeadline,isOverdue,type PolicySnapshot } from "../src/policy.ts";
// Mirror the stored Nepal demonstration policy.
const policy:PolicySnapshot = {
policyVersion:"NP-GRIEVANCE-2026-V1",
timezone:"Asia/Kathmandu",
workingIsoDays:[7,1,2,3,4,5],
holidays:[
{ date:"2026-10-20",name:"Dashain holiday",sourceUrl:"https://moha.gov.np/en/page/government-and-public-holidays-in-2083" },
{ date:"2026-10-21",name:"Dashain holiday",sourceUrl:"https://moha.gov.np/en/page/government-and-public-holidays-in-2083" },
],
allowedWorkingDays:1,
deadlineInclusivity:"DUE_THROUGH_END_OF_DAY",
missingDateBehavior:"NEEDS_CLARIFICATION",
};- Add the boundary tests below the policy fixture:
test("Friday plus one working day lands on Sunday",() => {
const result = calculateDeadline("2026-10-16",policy);
assert.equal(result.deadline,"2026-10-18");
assert.deepEqual(result.trace.map((entry) => entry.reason),["WEEKLY_HOLIDAY","WORKING_DAY"]);
});
test("selected Dashain holidays move the deadline",() => {
const result = calculateDeadline("2026-10-19",policy);
assert.equal(result.deadline,"2026-10-22");
assert.deepEqual(result.trace.map((entry) => entry.reason),["PUBLIC_HOLIDAY","PUBLIC_HOLIDAY","WORKING_DAY"]);
});
test("inclusive due date becomes overdue the next day",() => {
assert.equal(isOverdue("2026-10-18","2026-10-18",policy),false);
assert.equal(isOverdue("2026-10-19","2026-10-18",policy),true);
});
test("missing date requests clarification",() => {
assert.equal(calculateDeadline(null,policy).outcome,"NEEDS_CLARIFICATION");
});What do these tests protect?
- The weekend test proves Saturday is excluded while Sunday counts.
- The holiday test proves two configured closures move the deadline.
- The inclusivity test fixes the due-date boundary.
- The missing-date test blocks a false escalation.
- Save policy.ts and policy.test.ts.
Before you run the tests, which boundary do you expect to catch an off-by-one error first?
- Run the calendar regression suite with this command:
npm testYou should see four passing tests. That is the difficult boundary secured: Nepal working days now produce a repeatable deadline and a visible clarification outcome.
Calendar tests not passing?
Check that the test imports ../src/policy.ts and that the selected holiday dates match the fixture. A Friday deadline should skip Saturday and land on Sunday.
Help me debug the calendar tests.
Your calendar can now defend every counted and excluded date. Next, you will turn that trace into an actionable recommendation with a responsible team and follow-up deadline.
Explain Every Flag
Recommend Action and Ownership
Your Nepal calendar now produces a defensible deadline trace. A supervisor still needs more than an overdue label before deciding who should act and what should happen next.
This step appends an actionable recommendation to each case. The result names the next step, reason, responsible department, optional employee suggestion, and follow-up deadline without changing the case owner.
In this step, get ready to:
- Store immutable evaluations and follow-up action records.
- Recommend a next step and responsible team from deterministic rules.
- Append an evaluation without overwriting prior results.
Create immutable evaluation storage
An evaluation is historical evidence. A later policy version or corrected case can create another evaluation, but it must never rewrite the facts used by an earlier result.
- Create 004_evaluations.sql inside server/migrations with the evaluation table:
-- Append policy evaluations without update or delete paths.
CREATE TABLE evaluations (
id BIGSERIAL PRIMARY KEY,
case_id BIGINT NOT NULL REFERENCES cases(id),
policy_id BIGINT NOT NULL REFERENCES policy_configs(id),
policy_version TEXT NOT NULL,
evaluated_at TIMESTAMPTZ NOT NULL,
rule_id TEXT NOT NULL,
input_facts JSONB NOT NULL,
deadline_calculation JSONB NOT NULL,
calculated_deadline DATE,
recommendation_outcome TEXT NOT NULL,
recommended_next_step TEXT NOT NULL,
recommendation_reason TEXT NOT NULL,
recommended_department_id BIGINT REFERENCES departments(id),
suggested_employee_id BIGINT REFERENCES employees(id),
next_follow_up_deadline DATE
);
CREATE INDEX evaluations_case_time_idx
ON evaluations(case_id,evaluated_at DESC,id DESC);What makes an evaluation reproducible?
The row freezes the rule ID, policy version, timestamp, input facts, date trace, recommendation, and ownership suggestion. The case assignment remains separate.
A descending index makes the newest evaluation easy to find while retaining every older evaluation.
- Add the follow-up action table below the evaluation index:
-- Track approved operational work separately from recommendations.
CREATE TABLE follow_up_actions (
id BIGSERIAL PRIMARY KEY,
case_id BIGINT NOT NULL REFERENCES cases(id),
evaluation_id BIGINT NOT NULL REFERENCES evaluations(id),
action_text TEXT NOT NULL,
owner_department_id BIGINT NOT NULL REFERENCES departments(id),
owner_employee_id BIGINT REFERENCES employees(id),
due_date DATE NOT NULL,
status TEXT NOT NULL CHECK (status IN ('OPEN','COMPLETED','CANCELLED')),
created_at TIMESTAMPTZ NOT NULL,
completed_at TIMESTAMPTZ
);
CREATE INDEX follow_up_case_status_idx
ON follow_up_actions(case_id,status,due_date);The table is ready now, but the policy engine does not create an operational action by itself. A supervisor decision in the next step authorizes that record.
- Save 004_evaluations.sql.
- Apply the evaluation migration by running:
npm run migrateYou should see applied 004_evaluations.sql. PostgreSQL can now retain every recommendation and later follow-up action.
Build the recommendation engine
The engine uses explicit keyword rules to recommend a department. Missing dates, ambiguous statuses, and uncertain ownership produce NEEDS_CLARIFICATION rather than a false escalation.
- Create recommendation.ts inside server/src with these contracts:
import { calculateDeadline,isOverdue,type PolicySnapshot } from "./policy.ts";
export interface CaseFacts {
caseId:string;
title:string;
description:string|null;
receivedDate:string|null;
normalizedStatus:string;
asOfDate:string;
}
export interface DirectoryCandidate {
departmentId:string;
departmentCode:string;
employeeId:string|null;
}
export interface Recommendation {
ruleId:string;
outcome:"ESCALATE"|"MONITOR"|"NEEDS_CLARIFICATION";
nextStep:string;
reason:string;
departmentId:string|null;
suggestedEmployeeId:string|null;
calculatedDeadline:string|null;
deadlineCalculation:ReturnType<typeof calculateDeadline>;
nextFollowUpDeadline:string|null;
inputFacts:CaseFacts & { policy:PolicySnapshot };
}What must every recommendation explain?
CaseFacts freezes the case values and the local as-of date. DirectoryCandidate carries a department plus an optional active employee.
Recommendation contains an action and explanation instead of a colour or bare overdue flag.
- Add the deterministic department selector below the contracts:
export function departmentCodeFor(facts:CaseFacts):string|null {
// Recommend a team from explicit, reviewable keyword rules.
const text = `${facts.title} ${facts.description ?? ""}`.toLowerCase();
if (text.includes("water")) return "WATER";
if (text.includes("road") || text.includes("transport")) return "ROADS";
if (text.includes("clinic") || text.includes("health")) return "HEALTH";
if (text.includes("certificate") || text.includes("administration")) return "ADMIN";
return null;
}
function clarification(facts:CaseFacts,policy:PolicySnapshot,reason:string):Recommendation {
return {
ruleId:"RULE-CLARIFICATION-REQUIRED",outcome:"NEEDS_CLARIFICATION",
nextStep:"Confirm the missing or ambiguous case facts before escalation review.",reason,
departmentId:null,suggestedEmployeeId:null,calculatedDeadline:null,
deadlineCalculation:calculateDeadline(facts.receivedDate,policy),nextFollowUpDeadline:null,
inputFacts:{ ...facts,policy },
};
}Why keep ownership conservative?
The keyword rules are visible and testable. They suggest a department without assigning the case or pretending to understand ambiguous text.
Clarification keeps its own next step and reason. It does not create an escalation or follow-up deadline from incomplete evidence.
- Add the recommendation function below clarification():
export function recommend(facts:CaseFacts,policy:PolicySnapshot,candidate:DirectoryCandidate|null):Recommendation {
const deadline = calculateDeadline(facts.receivedDate,policy);
if (deadline.outcome==="NEEDS_CLARIFICATION") return clarification(facts,policy,"The received date is missing or unsupported.");
if (facts.normalizedStatus==="NEEDS_CLARIFICATION") return clarification(facts,policy,"The source status is ambiguous.");
if (!candidate) return clarification(facts,policy,"The responsible department is uncertain.");
const overdue = facts.normalizedStatus!=="RESOLVED" && isOverdue(facts.asOfDate,deadline.deadline!,policy);
return {
ruleId:overdue?"RULE-DEADLINE-OVERDUE":"RULE-DEADLINE-MONITOR",
outcome:overdue?"ESCALATE":"MONITOR",
nextStep:overdue?"Open a supervisor review and contact the responsible department.":"Keep the case open and review it on the follow-up date.",
reason:overdue?`The case remains open after its ${deadline.deadline} deadline.`:`The case remains within its ${deadline.deadline} deadline.`,
departmentId:candidate.departmentId,
suggestedEmployeeId:candidate.employeeId,
calculatedDeadline:deadline.deadline,
deadlineCalculation:deadline,
nextFollowUpDeadline:deadline.deadline,
inputFacts:{ ...facts,policy },
};
}What can the policy recommend?
- ESCALATE recommends supervisor review after a missed deadline.
- MONITOR keeps a case visible before its deadline.
- NEEDS_CLARIFICATION blocks false certainty when required facts or ownership are unresolved.
- The employee remains a suggestion. The function never changes the case owner.
Append and inspect an evaluation
The evaluation route loads the current policy and directory candidate inside one transaction. It appends the evaluation and its audit event with the same timestamp.
- Add these imports to the top of server.ts:
import { departmentCodeFor,recommend } from "./recommendation.ts";
import type { PolicySnapshot } from "./policy.ts";- Add the policy loader below the PostgreSQL pool declaration:
async function loadPolicy(client:any):Promise<PolicySnapshot> {
// Load one complete snapshot for the evaluation transaction.
const result = await client.query(`SELECT p.*,COALESCE(json_agg(json_build_object('date',h.holiday_date::text,'name',h.name,'sourceUrl',h.source_url)) FILTER (WHERE h.id IS NOT NULL),'[]') AS holidays FROM policy_configs p LEFT JOIN calendar_holidays h ON h.policy_id=p.id GROUP BY p.id ORDER BY p.effective_from DESC LIMIT 1`);
const row = result.rows[0];
if (!row) throw new Error("No policy configuration found");
return {
policyVersion:row.policy_version,
timezone:row.timezone,
workingIsoDays:row.working_iso_days,
holidays:row.holidays,
allowedWorkingDays:row.allowed_working_days,
deadlineInclusivity:row.deadline_inclusivity,
missingDateBehavior:row.missing_date_behavior,
};
}- Add the evaluation route above the server listener:
app.post("/api/cases/:id/evaluations",async(req,res) => {
const client = await pool.connect();
try {
await client.query("BEGIN");
const caseResult = await client.query("SELECT id,title,description,received_date::text,normalized_status FROM cases WHERE id=$1 FOR UPDATE",[req.params.id]);
if (!caseResult.rows[0]) { await client.query("ROLLBACK"); res.status(404).json({ error:{ code:"CASE_NOT_FOUND",message:"Case not found" } }); return; }
const policy = await loadPolicy(client);
const row = caseResult.rows[0];
const facts = { caseId:String(row.id),title:row.title,description:row.description,receivedDate:row.received_date,normalizedStatus:row.normalized_status,asOfDate:"2026-10-23" };
const code = departmentCodeFor(facts);
const directory = code ? await client.query(`SELECT d.id AS department_id,e.id AS employee_id FROM departments d LEFT JOIN LATERAL (SELECT id FROM employees WHERE department_id=d.id AND active ORDER BY escalation_level,id LIMIT 1) e ON TRUE WHERE d.code=$1`,[code]) : { rows:[] };
const candidate = directory.rows[0] ? { departmentId:String(directory.rows[0].department_id),departmentCode:code!,employeeId:directory.rows[0].employee_id?String(directory.rows[0].employee_id):null } : null;
const result = recommend(facts,policy,candidate);
const evaluationId = await insertEvaluation(client,result);
await client.query("COMMIT");
res.status(201).json({ evaluationId,...result });
} catch(error) { await client.query("ROLLBACK"); res.status(500).json({ error:{ code:"EVALUATION_FAILED",message:error instanceof Error?error.message:"Evaluation failed" } }); }
finally { client.release(); }
});How does the route avoid automatic assignment?
The local as-of date is explicit in the demonstration facts. A production server derives it in Asia/Kathmandu before opening the transaction.
The active employee query supplies a suggestion only. The route does not update cases.employee_id.
- Add the evaluation insertion helper above the route:
async function insertEvaluation(client:any,result:any):Promise<string> {
const evaluatedAt = new Date().toISOString();
const policyRow = await client.query("SELECT id FROM policy_configs WHERE policy_version=$1",[result.inputFacts.policy.policyVersion]);
const inserted = await client.query(`INSERT INTO evaluations(case_id,policy_id,policy_version,evaluated_at,rule_id,input_facts,deadline_calculation,calculated_deadline,recommendation_outcome,recommended_next_step,recommendation_reason,recommended_department_id,suggested_employee_id,next_follow_up_deadline) VALUES($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14) RETURNING id`,[result.inputFacts.caseId,policyRow.rows[0].id,result.inputFacts.policy.policyVersion,evaluatedAt,result.ruleId,result.inputFacts,result.deadlineCalculation,result.calculatedDeadline,result.outcome,result.nextStep,result.reason,result.departmentId,result.suggestedEmployeeId,result.nextFollowUpDeadline]);
const evaluationId = String(inserted.rows[0].id);
await client.query(`INSERT INTO audit_events(entity_type,entity_id,event_type,actor_id,event_time,before_values,after_values,metadata) VALUES('case',$1,'POLICY_EVALUATED','policy-engine',$2,NULL,$3,$4)`,[result.inputFacts.caseId,evaluatedAt,{ evaluationId,recommendation:result.outcome },{ ruleId:result.ruleId,policyVersion:result.inputFacts.policy.policyVersion }]);
return evaluationId;
}How is evaluation history preserved?
The evaluation and POLICY_EVALUATED event share one transaction and timestamp. Either both commit or neither does.
The helper inserts a new row on every run. No update path can replace an earlier recommendation.
- Save recommendation.ts and server.ts.
- Restart the API by running:
npm run dev:serverYou should see the API listening message without a TypeScript error. The server can now append actionable recommendations.
Recommendation route failing?
Check the first compiler error and confirm every added helper appears outside the route handlers. Verify that recommendation.ts imports ./policy.ts.
Help me debug the recommendation flow.
Your policy now recommends a concrete action without taking authority away from the supervisor. Next, you will approve, reject, reassign, or request clarification while preserving the original evaluation.
Decide Transactionally
Record Supervisor Decisions
Your policy now proposes an action, reason, responsible team, employee suggestion, and follow-up date. That recommendation remains evidence, while a supervisor owns the operational decision.
This step stores approval, rejection, reassignment, or clarification separately from the evaluation. Each successful decision writes its audit event and resulting follow-up change through the same PostgreSQL client and transaction.
In this step, get ready to:
- Store one immutable supervisor decision per evaluation.
- Require reasons for rejection, reassignment, and clarification.
- Commit the decision, audit event, and follow-up change atomically.
Store supervisor decisions
The decision points to the exact evaluation the supervisor reviewed. The original recommendation remains unchanged even when the supervisor rejects it or chooses another owner.
- Create 005_decisions.sql inside server/migrations with the decision table:
-- Keep human decisions separate from policy recommendations.
CREATE TABLE review_decisions (
id BIGSERIAL PRIMARY KEY,
evaluation_id BIGINT UNIQUE NOT NULL REFERENCES evaluations(id),
decision TEXT NOT NULL CHECK (decision IN (
'APPROVED','REJECTED','REASSIGNED','CLARIFICATION_REQUESTED'
)),
reason TEXT,
decided_by TEXT NOT NULL,
decided_at TIMESTAMPTZ NOT NULL,
owner_department_id BIGINT REFERENCES departments(id),
owner_employee_id BIGINT REFERENCES employees(id),
CHECK (
decision='APPROVED'
OR length(trim(COALESCE(reason,''))) > 0
)
);What does the decision preserve?
- The unique evaluation reference allows one final review decision for each policy result.
- REJECTED, REASSIGNED, and CLARIFICATION_REQUESTED require a reason at the database boundary.
- Owner fields record the supervisor's choice without altering the historical recommendation.
- Save 005_decisions.sql.
- Apply the decision migration by running:
npm run migrateYou should see applied 005_decisions.sql. The database now enforces the separation between recommendation and decision.
Decision migration failing?
Check that 004_evaluations.sql applied before this migration. The foreign key cannot reference an evaluation table that does not exist.
Help me debug the decision migration.
Build the transactional decision service
The service validates the requested outcome before opening a transaction. It then locks the evaluation, inserts the human decision, applies the resulting operational change, and appends one audit event.
- Create decision-service.ts inside server/src with these request contracts:
import type { PoolClient } from "pg";
export type DecisionKind =
| "APPROVED"
| "REJECTED"
| "REASSIGNED"
| "CLARIFICATION_REQUESTED";
export interface DecisionInput {
evaluationId:string;
decision:DecisionKind;
reason?:string;
actorId:string;
ownerDepartmentId?:string;
ownerEmployeeId?:string|null;
}
function requireReason(input:DecisionInput):string|null {
const reason = input.reason?.trim() || null;
if (input.decision!=="APPROVED" && !reason) {
throw new Error(`${input.decision} requires a reason`);
}
return reason;
}What is validated before the transaction?
The decision type allows only the four supported supervisor outcomes. A rejection, reassignment, or clarification request fails before any database write when its reason is blank.
The actor ID and selected owner travel with the decision instead of being inferred later.
- Add the operational outcome helper below requireReason():
async function applyOperationalOutcome(client:PoolClient,evaluation:any,input:DecisionInput,decidedAt:string) {
// Rejection closes the review without authorizing a follow-up action.
if (input.decision==="REJECTED") return null;
const departmentId = input.ownerDepartmentId ?? evaluation.recommended_department_id;
if (!departmentId) throw new Error("A responsible department is required");
const employeeId = input.ownerEmployeeId ?? evaluation.suggested_employee_id ?? null;
const actionText = input.decision==="CLARIFICATION_REQUESTED"
? `Clarify case facts: ${input.reason}`
: evaluation.recommended_next_step;
const dueDate = evaluation.next_follow_up_deadline ?? evaluation.calculated_deadline;
if (!dueDate) throw new Error("A follow-up due date is required");
const inserted = await client.query(`INSERT INTO follow_up_actions(case_id,evaluation_id,action_text,owner_department_id,owner_employee_id,due_date,status,created_at) VALUES($1,$2,$3,$4,$5,$6,'OPEN',$7) RETURNING id`,[evaluation.case_id,evaluation.id,actionText,departmentId,employeeId,dueDate,decidedAt]);
await client.query("UPDATE cases SET department_id=$1,employee_id=$2 WHERE id=$3",[departmentId,employeeId,evaluation.case_id]);
return String(inserted.rows[0].id);
}How does each decision affect operations?
- REJECTED preserves the decision but authorizes no action.
- APPROVED uses the recommended owner unless the request supplies an explicit choice.
- REASSIGNED uses the supervisor's selected department and optional employee.
- CLARIFICATION_REQUESTED creates a concrete clarification action with a due date.
- Add the decision transaction below applyOperationalOutcome():
export async function recordDecision(client:PoolClient,input:DecisionInput) {
const reason = requireReason(input);
const decidedAt = new Date().toISOString();
const result = await client.query(`SELECT e.*,pc.id AS policy_id FROM evaluations e JOIN policy_configs pc ON pc.id=e.policy_id WHERE e.id=$1 FOR UPDATE`,[input.evaluationId]);
const evaluation = result.rows[0];
if (!evaluation) throw new Error("Evaluation not found");
const inserted = await client.query(`INSERT INTO review_decisions(evaluation_id,decision,reason,decided_by,decided_at,owner_department_id,owner_employee_id) VALUES($1,$2,$3,$4,$5,$6,$7) RETURNING id`,[input.evaluationId,input.decision,reason,input.actorId,decidedAt,input.ownerDepartmentId??evaluation.recommended_department_id,input.ownerEmployeeId??evaluation.suggested_employee_id]);
const followUpActionId = await applyOperationalOutcome(client,evaluation,input,decidedAt);
await client.query(`INSERT INTO audit_events(entity_type,entity_id,event_type,actor_id,event_time,before_values,after_values,metadata) VALUES('case',$1,'SUPERVISOR_DECISION_RECORDED',$2,$3,$4,$5,$6)`,[String(evaluation.case_id),input.actorId,decidedAt,{ evaluationId:input.evaluationId,recommendation:evaluation.recommendation_outcome },{ decisionId:String(inserted.rows[0].id),decision:input.decision,reason,followUpActionId },{ policyVersion:evaluation.policy_version,ruleId:evaluation.rule_id }]);
return { decisionId:String(inserted.rows[0].id),followUpActionId,decidedAt };
}Why pass one client through every write?
The function receives an already checked-out PostgreSQL client. The route owns BEGIN, COMMIT, and ROLLBACK so every write remains inside one transaction.
The audit event records both the recommendation and the final decision. A reviewer can see disagreement without rewriting history.
- Add this import to the top of server.ts:
import { recordDecision,type DecisionKind } from "./decision-service.ts";- Add the supervisor decision route above the server listener:
app.post("/api/evaluations/:id/decisions",async(req,res) => {
const allowed:DecisionKind[] = ["APPROVED","REJECTED","REASSIGNED","CLARIFICATION_REQUESTED"];
const decision = String(req.body.decision ?? "") as DecisionKind;
if (!allowed.includes(decision)) {
res.status(400).json({ error:{ code:"INVALID_DECISION",message:"Choose a supported supervisor decision" } });
return;
}
const client = await pool.connect();
try {
await client.query("BEGIN");
const result = await recordDecision(client,{
evaluationId:req.params.id,decision,reason:req.body.reason,
actorId:String(req.body.actorId ?? "supervisor-1"),
ownerDepartmentId:req.body.ownerDepartmentId?String(req.body.ownerDepartmentId):undefined,
ownerEmployeeId:req.body.ownerEmployeeId?String(req.body.ownerEmployeeId):null,
});
await client.query("COMMIT");
res.status(201).json(result);
} catch(error) {
await client.query("ROLLBACK");
res.status(409).json({ error:{ code:"DECISION_FAILED",message:error instanceof Error?error.message:"Decision failed" } });
} finally { client.release(); }
});What does the route commit together?
The route validates the decision enum before connecting. The transaction begins before the evaluation lock and ends only after the decision, follow-up change, case assignment, and audit event succeed.
A duplicate decision violates the unique evaluation constraint and rolls back the entire request.
Verify disagreement without overwrite
A designed rejection proves the separation between policy output and human authority. The recommendation must remain in the evaluation while the decision and audit event record the supervisor's disagreement.
- Create decision-demo.ts inside server/src with this database-backed check:
import { Pool } from "pg";
// Reject the newest undecided evaluation without changing its recommendation.
const pool = new Pool({ connectionString:"postgres://app:local-development-only@localhost:5432/grievances" });
const latest = await pool.query(`SELECT e.id,e.recommendation_outcome FROM evaluations e LEFT JOIN review_decisions d ON d.evaluation_id=e.id WHERE d.id IS NULL ORDER BY e.id DESC LIMIT 1`);
if (!latest.rows[0]) throw new Error("Create an evaluation before running this demo");
const response = await fetch(`http://localhost:3000/api/evaluations/${latest.rows[0].id}/decisions`,{
method:"POST",
headers:{ "content-type":"application/json" },
body:JSON.stringify({
decision:"REJECTED",
reason:"The escalation is not proportionate after reviewing the source evidence.",
actorId:"supervisor-demo",
}),
});
console.log("recommendation before review:",latest.rows[0].recommendation_outcome);
console.log("decision response:",await response.json());
await pool.end();What does the rejection demonstrate?
The script reads the policy output before review. It then sends a separate rejection with a required reason.
The response contains a decision ID and no follow-up action ID. Rejection records disagreement without authorizing operational work.
- Save decision-service.ts, decision-demo.ts, and server.ts.
- Restart the API by running:
npm run dev:serverYou should see the API listening message. Keep the process running while you test the rejection in another terminal.
Before you run the demo, do you expect the original recommendation or the new human decision to disappear?
- Run the transactional rejection check from a second terminal:
node server/src/decision-demo.tsYou should see the original recommendation followed by a new decision ID and a null follow-up action ID. The stored evaluation remains intact while the rejection and audit event share one timestamp.
Decision demo failing?
Create at least one evaluation before running the demo. If the request returns a conflict, the selected evaluation already has a decision, so evaluate another case.
Help me debug the decision transaction.
Your console now preserves policy advice and human authority as separate records. Next, you will make that evidence navigable through a dashboard where every metric opens the cases behind it.
Build a Traceable Dashboard
Supervisor decisions now authorize real follow-up work without erasing the policy recommendation. The remaining challenge is turning those records into a screen where every number can be investigated.
This step builds an operational dashboard with five actionable buckets. A supervisor can filter the case table, open a case, and trace the full path from source row to follow-up and audit chronology.
In this step, get ready to:
- Create filterable dashboard and evidence endpoints.
- Build metric cards, a case table, and a traceable evidence drawer.
- Verify that a metric opens the exact records behind its count.
Expose operational dashboard state
A dashboard metric is useful only when it opens a concrete work queue. The backend derives each bucket from cases, the latest evaluation, the final decision, and the current follow-up action.
- Create 006_dashboard.sql inside server/migrations with the operational state view:
-- Present current operational state without deleting history.
CREATE VIEW case_operational_state AS
SELECT
c.id,c.title,c.normalized_status,c.received_date,c.source_platform,
c.department_id,c.employee_id,d.name AS department_name,
e.recommendation_outcome,e.calculated_deadline,
e.recommended_next_step,e.recommendation_reason,
rd.decision,
f.id AS follow_up_id,f.status AS follow_up_status,
f.due_date AS follow_up_due_date
FROM cases c
LEFT JOIN departments d ON d.id=c.department_id
LEFT JOIN LATERAL (
SELECT * FROM evaluations
WHERE case_id=c.id ORDER BY evaluated_at DESC,id DESC LIMIT 1
) e ON TRUE
LEFT JOIN review_decisions rd ON rd.evaluation_id=e.id
LEFT JOIN LATERAL (
SELECT * FROM follow_up_actions
WHERE case_id=c.id ORDER BY created_at DESC,id DESC LIMIT 1
) f ON TRUE;Why derive a current-state view?
The view selects only the newest evaluation and follow-up for current operations. Historical rows remain available through the evidence endpoint.
Department and employee assignments come from the case after a supervisor decision. The recommendation stays available as separate policy evidence.
- Save 006_dashboard.sql.
- Apply the dashboard migration by running:
npm run migrateYou should see applied 006_dashboard.sql. The API can now query current queues without flattening the historical tables.
- Create dashboard-service.ts inside server/src with the metric query:
import type { Pool } from "pg";
export async function dashboardMetrics(pool:Pool) {
// Count operational queues from the same state used by the table.
const result = await pool.query(`SELECT
count(*) FILTER (WHERE normalized_status<>'RESOLVED') AS open,
count(*) FILTER (WHERE recommendation_outcome='ESCALATE') AS overdue,
count(*) FILTER (WHERE department_id IS NULL) AS missing_department,
count(*) FILTER (WHERE department_id IS NOT NULL AND employee_id IS NULL) AS unassigned_employee,
count(*) FILTER (WHERE follow_up_status='OPEN') AS waiting_on_team
FROM case_operational_state`);
const row = result.rows[0];
return {
open:Number(row.open),overdue:Number(row.overdue),
missingDepartment:Number(row.missing_department),
unassignedEmployee:Number(row.unassigned_employee),
waitingOnTeam:Number(row.waiting_on_team),
};
}What work does each metric open?
- Open contains unresolved cases.
- Overdue contains cases whose latest recommendation is escalation.
- Missing Department exposes ownership gaps.
- Unassigned Employee identifies team-owned work without a named person.
- Waiting on Team contains open follow-up actions.
- Add the paginated case query below dashboardMetrics():
export async function dashboardCases(pool:Pool,input:{ bucket?:string;departmentId?:string;page:number;pageSize:number }) {
const conditions:string[] = [];
const values:unknown[] = [];
const add = (sql:string,value?:unknown) => {
conditions.push(sql);
if (value!==undefined) values.push(value);
};
if (input.bucket==="open") add("normalized_status<>'RESOLVED'");
if (input.bucket==="overdue") add("recommendation_outcome='ESCALATE'");
if (input.bucket==="missingDepartment") add("department_id IS NULL");
if (input.bucket==="unassignedEmployee") add("department_id IS NOT NULL AND employee_id IS NULL");
if (input.bucket==="waitingOnTeam") add("follow_up_status='OPEN'");
if (input.departmentId) add(`department_id=$${values.length+1}`,input.departmentId);
const where = conditions.length ? `WHERE ${conditions.join(" AND ")}` : "";
values.push(input.pageSize,(input.page-1)*input.pageSize);
const rows = await pool.query(`SELECT * FROM case_operational_state ${where} ORDER BY id DESC LIMIT $${values.length-1} OFFSET $${values.length}`,values);
const total = await pool.query(`SELECT count(*) FROM case_operational_state ${where}`,values.slice(0,-2));
return { page:input.page,pageSize:input.pageSize,total:Number(total.rows[0].count),items:rows.rows };
}How does filtering stay safe?
Only predefined bucket names can add SQL fragments. Department IDs, limits, and offsets remain parameterized values.
The response includes page metadata so the interface can navigate large case lists without loading every record.
- Add the evidence query below dashboardCases():
export async function caseEvidence(pool:Pool,caseId:string) {
// Return every layer needed to explain one operational record.
const [caseRow,evaluations,decisions,followUps,audit] = await Promise.all([
pool.query(`SELECT c.*,ir.original_values,ir.proposed_values,ir.outcome AS import_outcome,ib.source_name,ib.source_platform,ib.imported_at,ib.imported_by FROM cases c JOIN import_rows ir ON ir.id=c.import_row_id JOIN import_batches ib ON ib.id=c.import_batch_id WHERE c.id=$1`,[caseId]),
pool.query("SELECT * FROM evaluations WHERE case_id=$1 ORDER BY evaluated_at DESC,id DESC",[caseId]),
pool.query(`SELECT d.* FROM review_decisions d JOIN evaluations e ON e.id=d.evaluation_id WHERE e.case_id=$1 ORDER BY d.decided_at DESC,d.id DESC`,[caseId]),
pool.query("SELECT * FROM follow_up_actions WHERE case_id=$1 ORDER BY created_at DESC,id DESC",[caseId]),
pool.query("SELECT * FROM audit_events WHERE entity_type='case' AND entity_id=$1 ORDER BY event_time,id",[caseId]),
]);
if (!caseRow.rows[0]) return null;
return {
case:caseRow.rows[0],evaluations:evaluations.rows,
decisions:decisions.rows,followUps:followUps.rows,audit:audit.rows,
};
}What appears in the evidence drawer?
The evidence response contains the untouched imported object, normalized case, every policy evaluation and date trace, every supervisor decision, follow-up history, source provenance, and chronological audit events.
The endpoint reads history without collapsing it into current state. That is the trail a reviewer needs to defend the case.
- Add these imports to the top of server.ts:
import { caseEvidence,dashboardCases,dashboardMetrics } from "./dashboard-service.ts";- Add the dashboard and evidence routes above the server listener:
app.get("/api/dashboard",async(req,res) => {
const page = Math.max(1,Number(req.query.page) || 1);
const pageSize = Math.min(100,Math.max(1,Number(req.query.pageSize) || 25));
const [metrics,cases] = await Promise.all([
dashboardMetrics(pool),
dashboardCases(pool,{
bucket:req.query.bucket?String(req.query.bucket):undefined,
departmentId:req.query.departmentId?String(req.query.departmentId):undefined,
page,pageSize,
}),
]);
res.json({ metrics,cases });
});
app.get("/api/cases/:id/evidence",async(req,res) => {
const evidence = await caseEvidence(pool,req.params.id);
if (!evidence) {
res.status(404).json({ error:{ code:"CASE_NOT_FOUND",message:"Case not found" } });
return;
}
res.json(evidence);
});The list route supports pagination, metric buckets, and department filtering. The detail route exposes the evidence behind one row.
Build the React console
The interface keeps metrics, the work queue, and case evidence on one screen. Selecting a card changes the table filter, while selecting a row opens its traceable drawer.
- In package.json, find the existing script region:
"dev:server": "node --watch server/src/server.ts",
"test": "node --test server/tests/*.test.ts",
"migrate": "node server/src/migrate.ts",
"generate:data": "node server/src/generate-data.ts",
"ci": "npm test && npm run build"- Replace that region with the frontend and build scripts included:
"dev:server": "node --watch server/src/server.ts",
"dev:web": "vite --config web/vite.config.js",
"test": "node --test server/tests/*.test.ts",
"migrate": "node server/src/migrate.ts",
"generate:data": "node server/src/generate-data.ts",
"build": "vite build --config web/vite.config.js",
"ci": "npm test && npm run build"- Create vite.config.js inside web with this development proxy:
import { defineConfig } from "vite";
import { fileURLToPath } from "node:url";
// Keep frontend requests on /api while Vite proxies to Express.
export default defineConfig({
root:fileURLToPath(new URL(".",import.meta.url)),
server:{ proxy:{ "/api":"http://localhost:3000" } },
build:{ outDir:"dist",emptyOutDir:true },
});- Create index.html inside web with the application root:
<!doctype html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
<title>Grievance Escalation Console</title>
</head>
<body>
<div id="root"></div>
<script type="module" src="/src/main.jsx"></script>
</body>
</html>- Create api.js inside web/src with the response wrapper:
export async function request(path,options) {
// Surface the API's structured error message to the operator.
const response = await fetch(path,options);
const body = await response.json();
if (!response.ok) throw new Error(body.error?.message ?? "Request failed");
return body;
}
export const bucketLabels = {
open:"Open",
overdue:"Overdue",
missingDepartment:"Missing Department",
unassignedEmployee:"Unassigned Employee",
waitingOnTeam:"Waiting on Team",
};What does the client wrapper centralize?
request() converts structured API failures into operator-facing messages. bucketLabels keeps frontend filters aligned with backend bucket keys.
- Create MetricCards.jsx inside web/src with the actionable metrics:
import { bucketLabels } from "./api.js";
export function MetricCards({ metrics,active,onSelect }) {
// Every count is a button that opens its matching work queue.
return <section className="metrics" aria-label="Operational metrics">
{Object.entries(bucketLabels).map(([key,label]) =>
<button
className={active===key?"metric active":"metric"}
key={key}
onClick={() => onSelect(key)}
>
<span>{label}</span>
<strong>{metrics[key] ?? 0}</strong>
</button>
)}
</section>;
}- Create CaseTable.jsx inside web/src with the filtered queue:
export function CaseTable({ result,onOpen }) {
// Keep operational columns visible before the evidence drawer opens.
return <section className="panel">
<table>
<thead><tr><th>Case</th><th>Status</th><th>Department</th><th>Recommendation</th><th>Follow-up</th><th></th></tr></thead>
<tbody>{result.items.map((item) => <tr key={item.id}>
<td>{item.title}</td>
<td>{item.normalized_status}</td>
<td>{item.department_name ?? "Missing"}</td>
<td>{item.recommendation_outcome ?? "Not evaluated"}</td>
<td>{item.follow_up_due_date ?? "None"}</td>
<td><button onClick={() => onOpen(item.id)}>Open evidence</button></td>
</tr>)}</tbody>
</table>
<p>Page {result.page}. Showing {result.items.length} of {result.total} cases.</p>
</section>;
}- Create EvidenceDrawer.jsx inside web/src with the evidence sections:
export function EvidenceDrawer({ evidence,onClose }) {
if (!evidence) return null;
const latest = evidence.evaluations[0];
return <aside className="drawer" aria-label="Case evidence">
<button onClick={onClose}>Close</button>
<h2>{evidence.case.title}</h2>
<h3>Original imported values</h3><pre>{JSON.stringify(evidence.case.original_values,null,2)}</pre>
<h3>Normalized case</h3><pre>{JSON.stringify(evidence.case,null,2)}</pre>
<h3>Policy evaluation and date trace</h3><pre>{JSON.stringify(latest,null,2)}</pre>
<h3>Recommendation</h3><p>{latest?.recommended_next_step}</p><p>{latest?.recommendation_reason}</p>
<h3>Supervisor decisions</h3><pre>{JSON.stringify(evidence.decisions,null,2)}</pre>
<h3>Follow-up actions</h3><pre>{JSON.stringify(evidence.followUps,null,2)}</pre>
<h3>Source provenance</h3><p>{evidence.case.source_platform}: {evidence.case.source_name}</p>
<h3>Audit chronology</h3><ol>{evidence.audit.map((event) => <li key={event.id}>{event.event_time}: {event.event_type} by {event.actor_id}</li>)}</ol>
</aside>;
}How does the drawer preserve the trail?
The drawer exposes every layer requested by an investigator. Raw imports and normalized fields remain side by side, while policy output and supervisor decisions stay visibly separate.
Audit events use chronological order so the operator can follow the case from import through evaluation and decision.
- Create App.jsx inside web/src with the dashboard state:
import { useEffect,useState } from "react";
import { request } from "./api.js";
import { MetricCards } from "./MetricCards.jsx";
import { CaseTable } from "./CaseTable.jsx";
import { EvidenceDrawer } from "./EvidenceDrawer.jsx";
export default function App() {
const [bucket,setBucket] = useState("open");
const [dashboard,setDashboard] = useState({ metrics:{},cases:{ items:[],page:1,total:0 } });
const [evidence,setEvidence] = useState(null);
const [error,setError] = useState("");
useEffect(() => {
request(`/api/dashboard?bucket=${bucket}&page=1&pageSize=25`)
.then(setDashboard).catch((caught) => setError(caught.message));
},[bucket]);
async function openEvidence(caseId) {
try { setEvidence(await request(`/api/cases/${caseId}/evidence`)); }
catch(caught) { setError(caught.message); }
}
return <main><h1>Grievance Escalation Console</h1>
{error && <p role="alert">{error}</p>}
<MetricCards metrics={dashboard.metrics} active={bucket} onSelect={setBucket}/>
<CaseTable result={dashboard.cases} onOpen={openEvidence}/>
<EvidenceDrawer evidence={evidence} onClose={() => setEvidence(null)}/>
</main>;
}- Create main.jsx inside web/src with the React entry point:
import { StrictMode } from "react";
import { createRoot } from "react-dom/client";
import App from "./App.jsx";
import "./styles.css";
// Mount one operational console into the page root.
createRoot(document.getElementById("root")).render(
<StrictMode><App/></StrictMode>
);- Create styles.css inside web/src with the dashboard layout:
:root{font-family:Inter,system-ui,sans-serif;color:#172033;background:#f4f6f9}body{margin:0}button{cursor:pointer}main{padding:24px}.metrics{display:grid;grid-template-columns:repeat(5,minmax(140px,1fr));gap:12px}.metric,.panel,.drawer{background:white;border:1px solid #d9dee8;border-radius:10px;padding:16px}.metric{display:flex;justify-content:space-between;text-align:left}.metric.active{border-color:#3659d9;box-shadow:0 0 0 2px #dbe3ff}.metric strong{font-size:24px}table{width:100%;border-collapse:collapse}.panel{margin-top:16px}th,td{text-align:left;padding:10px;border-bottom:1px solid #e7eaf0}.drawer{position:fixed;inset:24px 24px 24px auto;width:min(640px,85vw);overflow:auto;box-shadow:0 16px 48px #0003}.drawer pre{white-space:pre-wrap;background:#f7f8fb;padding:12px;border-radius:6px}@media(max-width:900px){.metrics{grid-template-columns:1fr 1fr}.panel{overflow:auto}}What does the layout prioritize?
The layout keeps the five queue cards visible above the table. The evidence drawer remains readable on smaller screens and uses preformatted blocks for exact stored values.
Trace a metric to its evidence
- Save the updated package.json and every file inside web.
- Restart the API by running:
npm run dev:server- Start the React development server from a second terminal:
npm run dev:webYou should see a local Vite URL. Open it in your browser to view the console.
Before you select a metric, which case fields do you expect the matching table rows to share?
- Click the Overdue metric card.
- Open one table row with Open evidence.
You should see only cases whose latest recommendation is ESCALATE. The drawer should show the original import, normalized case, deadline trace, recommendation, decision, follow-up, source, and audit chronology.
Dashboard not showing evidence?
Confirm the API is listening on port 3000 and the Vite proxy points to that address. If a metric shows zero, evaluate and commit more generated cases before testing that queue.
Help me debug the dashboard.
Your console now turns every operational count into an explainable work queue. Next, you will document the API and protect its policy, import, transaction, and traceability boundaries with tests and CI.
Document and Test the API
Your dashboard now turns operational metrics into traceable case evidence. The final engineering step makes those contracts explicit and guards them against regressions.
This step documents the REST API, system architecture, dataset provenance, and Nepal policy assumptions. It also adds integration tests and a GitHub Actions gate for every push and pull request.
In this step, get ready to:
- Publish OpenAPI and README documentation.
- Test import safety, transaction rollback, audit traceability, pagination, and filtering.
- Run the same test and build gate locally and in CI.
Document the API and architecture
The API documentation should explain how callers preview imports, commit reviewed rows, append evaluations, record supervisor decisions, and trace dashboard records. Stable error codes help clients respond without parsing prose.
- Create openapi.yaml inside docs with the core API paths:
openapi: 3.1.1
info:
title: Grievance Escalation Console API
version: 1.0.0
servers:
- url: http://localhost:3000
paths:
/api/imports/preview:
post:
summary: Stage a CSV and return grouped review outcomes.
responses:
"201": { description: Review batch created. }
"400": { description: Invalid source or CSV. }
/api/imports/{batchId}/commit:
post:
summary: Commit explicitly selected staged rows.
responses:
"201": { description: Selected cases committed. }
"409": { description: Commit conflict or changed source. }
/api/cases/{id}/evaluations:
post:
summary: Append a policy evaluation.
responses:
"201": { description: Evaluation appended. }
/api/evaluations/{id}/decisions:
post:
summary: Record a supervisor decision transactionally.
responses:
"201": { description: Decision and operational outcome committed. }What does this contract clarify?
OpenAPI 3.1.1 describes the main write boundaries and their expected status codes. The summaries preserve the crucial distinction between preview, commit, evaluation, and decision.
- Add the read paths below the decision path:
/api/dashboard:
get:
summary: List actionable metrics and paginated cases.
parameters:
- { in: query, name: bucket, schema: { type: string } }
- { in: query, name: departmentId, schema: { type: string } }
- { in: query, name: page, schema: { type: integer, minimum: 1 } }
- { in: query, name: pageSize, schema: { type: integer, minimum: 1, maximum: 100 } }
responses:
"200": { description: Metrics and filtered cases. }
/api/cases/{id}/evidence:
get:
summary: Return source, policy, decision, follow-up, and audit evidence.
responses:
"200": { description: Complete traceable case evidence. }
"404": { description: Case not found. }
/api/health:
get:
summary: Check API and database availability.
responses:
"200": { description: Service is healthy. }
components:
schemas:
ApiError:
type: object
required: [error]
properties:
error: { type: object, required: [code, message] }- Add this documentation route above the server listener in server.ts:
app.get("/openapi.yaml",(_req,res) => {
// Serve the versioned API contract from the repository.
res.sendFile("docs/openapi.yaml",{ root:process.cwd() });
});- Create README.md inside grievance-escalation-console with this architecture overview:
# Grievance Escalation Console
A review-first local console for traceable grievance imports, policy recommendations, supervisor decisions, and follow-up work.
## Architecture
```mermaid
flowchart LR
CSV[Four fictional CSV exports] --> Preview[Import preview and review]
Preview --> Commit[Transactional case commit]
Commit --> DB[(PostgreSQL)]
DB --> Policy[Versioned Nepal policy engine]
Policy --> Decision[Supervisor decision]
Decision --> FollowUp[Follow-up action]
DB --> API[Express and TypeScript API]
API --> UI[React operations dashboard]
```
The API and audit writes use one PostgreSQL database. React reads paginated queues and complete evidence through REST endpoints.What does the architecture emphasize?
The diagram shows the control boundary before case creation and the separate boundary before operational action. Every path converges on PostgreSQL so transactions can protect related writes.
- Add the data and policy notes below the architecture section:
## Dataset provenance
- All people, departments, contacts, source URLs, and grievance records are fictional.
- `server/src/generate-data.ts` creates 200 reproducible records across WhatsApp, website, Facebook, and spreadsheet exports.
- Deliberate edge cases include missing fields, mixed date formats, ambiguous statuses, repeated source IDs, and similar complaints.
- Original values remain attached to import rows and committed cases.
## Policy assumptions
- Timezone: `Asia/Kathmandu`.
- Workweek: Sunday through Friday. Saturday is the weekly holiday.
- Demonstration deadline: five working days after receipt.
- Deadline inclusivity: due through the end of the calculated local date.
- Missing or unsupported received dates produce `NEEDS_CLARIFICATION`.
- Selected 2083 holidays are demonstration seeds. Production deployments must load the complete applicable Ministry of Home Affairs calendar.
## Source
- Nepal Ministry of Home Affairs: https://moha.gov.np/en/page/government-and-public-holidays-in-2083The README labels synthetic data and incomplete holiday seeds explicitly. That prevents a demonstration dataset or partial calendar from being mistaken for official production data.
Add database integration tests
The integration suite targets boundaries that unit tests cannot prove alone. It checks stored import identity, changed-source review, rollback behavior, audit linkage, and filtered pagination.
- Create imports.test.ts inside server/tests with the source-change fixture:
import assert from "node:assert/strict";
import test from "node:test";
import { Pool } from "pg";
import { previewImport } from "../src/import-service.ts";
const pool = new Pool({ connectionString:"postgres://app:local-development-only@localhost:5432/grievances" });
const sourceId = `TEST-${Date.now()}`;
const header = "submission_id,subject,description,received_on,status,source_url";
const original = `${header}\n${sourceId},Water supply stopped,Fictional test,2026-10-01,open,https://example.invalid/test`;
const changed = `${header}\n${sourceId},Water supply stopped,Changed fictional text,2026-10-01,open,https://example.invalid/test`;
test("changed source records require review",async() => {
const first:any = await previewImport(pool,"website","first.csv",original,"test-runner");
const row = first.groups.valid[0];
const inserted = await pool.query(`INSERT INTO cases(source_platform,source_record_id,source_url,import_batch_id,import_row_id,original_values,title,description,received_date,normalized_status,created_at) VALUES('website',$1,$2,$3,$4,$5,$6,$7,$8,'OPEN',now()) RETURNING id`,[sourceId,row.source_url,first.batchId,row.id,row.original_values,row.proposed_values.title,row.proposed_values.description,row.proposed_values.receivedDate]);
assert.ok(inserted.rows[0].id);
const review:any = await previewImport(pool,"website","changed.csv",changed,"test-runner");
assert.equal(review.counts.changedSourceRecords,1);
});- Add the repeat-import test below the changed-source test:
test("byte-identical repeat files return the existing batch",async() => {
// The changed file was staged by the previous test.
const repeated:any = await previewImport(pool,"website","changed-again.csv",changed,"test-runner");
assert.equal(repeated.counts.alreadyImported,1);
assert.ok(repeated.repeatedBatchId);
});
test.after(async() => {
await pool.end();
});What do the import tests protect?
The first test proves a known source ID with changed raw content stays in review. The second proves an identical file reuses its stored fingerprint instead of creating another batch.
- Create transactions.test.ts inside server/tests with this rollback test:
import assert from "node:assert/strict";
import test from "node:test";
import { Pool } from "pg";
import { recordDecision } from "../src/decision-service.ts";
const pool = new Pool({ connectionString:"postgres://app:local-development-only@localhost:5432/grievances" });
test("a follow-up failure rolls back the decision",async() => {
const evaluation = await pool.query("SELECT id FROM evaluations WHERE recommended_department_id IS NULL ORDER BY id DESC LIMIT 1");
if (!evaluation.rows[0]) return;
const evaluationId = String(evaluation.rows[0].id);
const client = await pool.connect();
try {
await client.query("BEGIN");
await assert.rejects(() => recordDecision(client,{ evaluationId,decision:"APPROVED",actorId:"test-supervisor" }));
await client.query("ROLLBACK");
} finally { client.release(); }
const decisions = await pool.query("SELECT id FROM review_decisions WHERE evaluation_id=$1",[evaluationId]);
assert.equal(decisions.rowCount,0);
});
test.after(async() => pool.end());What does the rollback test prove?
The test deliberately approves an evaluation without a responsible department. The follow-up helper fails after the transaction begins, then the test confirms no decision survived the rollback.
- Create dashboard.test.ts inside server/tests with the pagination and filter checks:
import assert from "node:assert/strict";
import test from "node:test";
import { Pool } from "pg";
import { dashboardCases } from "../src/dashboard-service.ts";
const pool = new Pool({ connectionString:"postgres://app:local-development-only@localhost:5432/grievances" });
test("dashboard pagination respects page size",async() => {
const result = await dashboardCases(pool,{ page:1,pageSize:1 });
assert.ok(result.items.length <= 1);
assert.equal(result.pageSize,1);
});
test("open bucket excludes resolved cases",async() => {
const result = await dashboardCases(pool,{ bucket:"open",page:1,pageSize:100 });
assert.ok(result.items.every((item:any) => item.normalized_status!=="RESOLVED"));
});
test("audit events retain chronological order",async() => {
const caseResult = await pool.query("SELECT id FROM cases ORDER BY id LIMIT 1");
if (!caseResult.rows[0]) return;
const events = await pool.query("SELECT event_time FROM audit_events WHERE entity_id=$1 ORDER BY event_time,id",[String(caseResult.rows[0].id)]);
const times = events.rows.map((row) => new Date(row.event_time).getTime());
assert.deepEqual(times,[...times].sort((a,b) => a-b));
});
test.after(async() => pool.end());What do the dashboard tests protect?
The pagination test protects response size. The bucket test protects operational meaning, while the audit test confirms the chronology returned to the evidence drawer.
Run the CI gate
The local gate catches failures before a push. GitHub Actions runs the same migration, test, and build sequence in a clean environment with pinned action majors.
- Create ci.yml inside .github/workflows with this workflow:
name: ci
on:
push:
pull_request:
permissions:
contents: read
jobs:
test-and-build:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v7
- uses: actions/setup-node@v7
with:
node-version: 24
package-manager-cache: false
- run: npm ci
- run: docker compose up -d
- run: npm run migrate
- run: npm run generate:data
- run: npm test
- run: npm run build
- if: always()
run: docker compose down -vWhat does the workflow reproduce?
- GitHub Actions checks out the repository and configures Node.js 24.
- npm ci installs the exact lockfile dependency graph.
- Docker Compose starts the same PostgreSQL service used locally.
- Migrations, generated data, tests, and the frontend build run in order.
- The cleanup step removes the CI database volume even after a failure.
- Save README.md, docs/openapi.yaml, the test files, and .github/workflows/ci.yml.
Before you run the gate, which boundary do you expect to fail first if a future change weakens import review or deadline logic?
- Run the complete local test and build gate with this command:
npm run ciYou should see the policy, import, transaction, dashboard, and audit tests pass before the Vite production build completes. The command stops at the first regression.
Local CI gate failing?
Use the first failing test file to identify the boundary. Confirm PostgreSQL is running and all six migrations have applied before diagnosing an integration assertion.
Help me diagnose the CI gate.
- Commit the verified repository changes in your Git client.
- Push the commit to your GitHub repository.
- Open the repository's Actions tab.
- Open the newest ci workflow run.
You should see the test-and-build job complete successfully. The clean runner has reproduced your migrations, regression tests, and production build.
Remote workflow failing?
Open the first failed workflow step and compare it with the same local command. A missing lockfile breaks npm ci, while a database startup problem appears before migrations.
Help me debug the remote workflow.
That is the production boundary secured. Your console now documents its assumptions, preserves its evidence, tests its failure modes, and repeats the same quality gate in a clean CI environment.
Secret mission
Reconcile a Changed-Source Audit Drill
Re-import unchanged and edited source records, then reject, reassign, and request clarification on fresh evaluations. Reconcile the resulting follow-up actions and audit chronology without rewriting the policy evidence.
Clean Up Your Resources
Clean Up Your Resources
Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs locally, so there are no ongoing cloud charges.
Resources you used:
- The db Docker Compose service and its pg_data volume.
- The local Node.js API and Vite development processes.
- The grievance-escalation-console folder on your Desktop, including generated CSV files and database migrations.
Keep everything running
No action is needed. Choose this if you are still reviewing imports, recommendations, decisions, or audit evidence.
- Leave the API and Vite development terminals running.
- Leave the db service running so the console can read its PostgreSQL records.
- Keep the grievance-escalation-console folder on your Desktop.
Pause - I'll come back to this later
Shut down running processes to free memory and ports while preserving the project files and pg_data volume.
- Press Ctrl+C in the terminal running the Express API.
- Press Ctrl+C in the terminal running Vite.
- Move to the grievance-escalation-console folder in your terminal.
- Stop PostgreSQL while preserving its named volume by running:
docker compose stopYou should see the db service stop. Run docker compose up -d when you return.
Database service still running?
Confirm the terminal is in the folder containing compose.yaml and Docker Desktop is running.
Help me pause the local stack.
Delete - I don't want to use this again
This permanently removes the local database evidence and project folder. Export anything you need before continuing because these commands cannot restore the deleted records.
- Press Ctrl+C in the API terminal.
- Press Ctrl+C in the Vite terminal.
- Move to the grievance-escalation-console folder in your terminal.
- Remove the database container and pg_data volume by running:
docker compose down -vYou should see the database container and named volume removed.
Delete the local repository folder for your operating system:
macOS
- Move to your Desktop and remove the project folder by running:
cd ~/Desktop
rm -rf grievance-escalation-consoleOpen Finder on your Desktop. You should no longer see the grievance-escalation-console folder.
Windows
- Move to your Desktop and remove the project folder by running:
Set-Location "$HOME\Desktop"
Remove-Item -Recurse -Force grievance-escalation-consoleOpen File Explorer on your Desktop. You should no longer see the grievance-escalation-console folder.
Cleanup did not finish?
Confirm Docker Desktop is running before removing the volume. Check that no terminal is still using the project folder before deleting it.
Help me verify the local cleanup.
Nice Work!
Nice Work!
You did it! You built a review-first grievance operations console that preserves source evidence, produces reproducible recommendations, and keeps human decisions traceable.
You've learned how to:
- Build review-first CSV imports that expose rejections, warnings, duplicates, uncertain mappings, unchanged files, and changed source records before commit.
- Create reproducible Nepal policy evaluations with versioned assumptions, working-day traces, actionable ownership recommendations, and clarification outcomes.
- Record transactional supervisor decisions with follow-up actions, append-only audit evidence, traceable dashboard queues, OpenAPI documentation, regression tests, and CI.
- Secret Mission: Re-import unchanged and edited source records, then reject, reassign, and request clarification while reconciling follow-up actions with the ordered audit chronology.
Ready to quiz yourself?