# Build an expense approval app with an AI coding agent
> Build a private employee expense approval app with immutable claim history, assigned human review and owner-recorded external reimbursement using a full SQL and API brief.
By BIGCHANGE.AI Editorial
Published: 2026-10-22T21:34:38.340Z
Updated: 2026-10-22T21:34:38.340Z
Canonical: https://bigchange.ai/blog/ai-agent-expense-approval-reimbursement-app-guide
AI-generated conceptual illustration by BIG CHANGE.
An expense app needs more than a form and an Approve button. A business owner must decide which claims qualify, who can review each one, and how an outside reimbursement is recorded without pretending the app moved money. This guide gives you a complete, copyable brief for building that private workflow with an AI coding agent. It is a design to implement and test in your own accounts; BIG CHANGE did not build or run the app.
## The big change
- **What changed:** [Replit Agent's Plan mode](https://docs.replit.com/features/agent/overview) can turn a detailed application specification into a proposed build. The original contract below specifies the expense states, database history, permissions and tests that a short prompt leaves open.
- **Why it matters:** A submitted claim should keep the exact policy and amount that governed it. A separate human approver decides it. The owner records reimbursement only after checking the external transfer.
- **What to watch:** Signing up through Clerk does not grant workspace membership. Test the generated app's current membership checks, private routes, export races and external reimbursement boundary before storing real employee expenses.
This is an employee claim and approval workflow. Our [two-plan subscription manager guide](https://bigchange.ai/blog/ai-coding-agent-two-plan-subscription-manager-guide) covers customer recurring billing, a different task.
## Before you start
The reader is a business owner with authority to set a real USD expense policy, enroll named staff and verify reimbursements in a separate payment system. Decide the permitted categories, per-expense cap, receipt-reference rule, approvers, evidence retention, backups and release conditions. Begin with synthetic people, claims and payment references. The reference handles USD integer cents and full reimbursement only. It has no receipt upload, transfer API, tax calculation, accounting integration or runtime AI model.
You need a Replit account with Agent, its managed Clerk integration, PostgreSQL and an intended host that staff can reach. [Replit documents separate Development and Production databases](https://docs.replit.com/features/data-and-storage/development-and-production) and [publishing access options](https://docs.replit.com/features/publishing/private-deployments). Inspect your account's actual availability, pricing, storage, retention and host behavior. The first owner needs a verified Clerk identity and a seed through authenticated project or database administration in each environment. Do not create a public first-owner route. On 22 October 2026, [Node's release table](https://nodejs.org/en/about/previous-releases) lists 22 and 24 as LTS; use either only if the actual Replit development and published runtimes and locked dependencies support it.
## 1. Give the coding agent the full brief
Open Replit Agent in Plan mode. Paste Appendices A, B and C together. Ask it to show the React screens, Express route map, exact managed Clerk proxy paths, SQL transaction order, permission matrix and test plan before it writes code. Then inspect the generated plan, migrations, source and lockfile. The appendices are the implementation contract, not evidence that an app already exists.
**Check:** The plan includes employee draft/edit/discard/submit, immutable policy and submission cycles, one assigned human approver, return/resubmit/reject/approve, private search/history/export, and owner-recorded external reimbursement and correction. If it reduces the task to an expense form, revise the plan.
## 2. Establish identity and policy
Mount the installed Replit-managed Clerk proxy at its exact generated paths before Express body parsers, as [Replit's integration guide](https://docs.replit.com/features/auth-and-identity/clerk-auth) requires. Leave the document-free landing and exact provider sign-in, sign-up, callback and authentication transport outside application membership and custom staff CSRF checks, while retaining provider protections. All financial HTML, API and single-page-app fallback routes require a current verified session and SQL workspace membership. Clerk's [Express middleware](https://clerk.com/docs/reference/express/clerk-middleware) and [`getAuth()`](https://clerk.com/docs/reference/express/get-auth) supply authentication state; the app must still read current membership.
Apply Appendix B to a new Development database after inspecting it. Seed the workspace and its first owner with the exact verified existing Clerk user ID through authenticated administration. Verify each additional identity with Clerk's [backend `getUser` method](https://clerk.com/docs/reference/backend/user/get-user) and with the intended person before enrollment. Publish a synthetic policy with positive USD cap, permitted categories and a receipt-reference rule. Make a second version later; the earlier submitted claim must still display its original policy version and submitted fields.
**Check:** An unlisted signed-in identity sees an empty workspace list. An absent, foreign, inactive or revoked workspace membership gets the same generic 404 on workspace routes. A current member without owner authority gets 403 on owner actions. No provider signup creates membership.
## 3. Run the claim and human decision cycle
Have an employee create, edit and discard one never-submitted draft; submit another. The server parses USD decimal text to integer cents without floating-point arithmetic and checks the current policy at submission. It copies the fields and policy version into an immutable submission cycle. A row lock gives competing actions an order; an expected revision prevents a stale form from overwriting a newer state. The owner assigns the submitted claim to a different active approver. That approver returns it with a reason; the employee edits and resubmits, producing a second cycle without erasing the first. Test rejection and approval on separate claims.
**Check:** Only the current assignee can decide the current submitted cycle; nobody can approve their own claim. A rejected claim is terminal. After reassignment, the former approver loses detail and export access. One of two competing decisions may commit at a revision; the other gets a conflict. Verify these outcomes in the generated app rather than inferring them from the schema.
## 4. Record a separately verified reimbursement
The owner checks the external payment system and its evidence before attesting in the app. For an approved cycle, record exactly its full approved cents, paid date and unique external payment reference. The app records the owner's assertion; it does not send a transfer or verify bank settlement. A correction appends a reason, preserves the original record and returns the claim to approved so that a newly verified external record can be entered. Correcting the app record never reverses outside funds.
**Check:** An employee cannot record reimbursement. Two competing reimbursement attempts cannot leave two active records, and a retry with the same request key returns the committed result without a second history row. If external transfer status is uncertain, resolve it in the external system before recording anything here.
## 5. Test private history, export and release
Search and detail must scope rows to the owner, the submitter or the currently assigned active approver. Put sensitive filters in JSON request bodies, not URLs. The export in Appendix C uses a shared workspace lock, then a fresh membership and scope read in a new `READ COMMITTED` statement. A revocation or reassignment that commits first must remove access; an export that gets the lock first finishes its authorized bounded snapshot before the writer commits. It caps results at 5,000 rows and 32 MiB, commits before sending, and never streams a partial failure.
Run the full Appendix C acceptance matrix with synthetic identities, including direct guessed IDs, HTML/API/fallback routes, request-key replay, changed member target with the same key, lock ordering, export failure, immutable history and a backup restore in isolation. Inspect the fixed member-change URL and logs: the target Clerk ID belongs in the protected JSON body, never the path, query, redirect or access log. Reload and restart, then read back cycles and policy versions. Use separate Production identities, database, secrets and owner seed; verify the actual host lets intended staff reach the sign-in flow while all expense data remains behind application gates. Record observed results and hold real financial use if any identity, snapshot, approval, reimbursement or export check fails.
The official manuals document building blocks, not this finished app. Replit, Clerk, PostgreSQL and hosting charges depend on the reader's account. Financial history, IDs and request metadata remain linkable to staff; the owner needs a real retention and backup-expiry policy. No tax, legal or accounting certification follows from this guide.
## Appendix A: Complete app brief for the coding agent
### Build brief: an expense tracker with human approval
This is an original, documentation-only specification for a business owner to give an AI coding agent. Build a persistent expense app in which a named employee drafts an expense, submits it under a published owner policy, a different named approver reviews it, and the owner records reimbursement **only after separately verifying an external payment**. The app preserves every submission snapshot, assignment, decision, return/resubmission and reimbursement correction. It provides scoped lists, filters, detail/history and a bounded JSON export. It does not initiate payments, send messages, calculate tax, book accounting entries, certify policy compliance or verify that an external receipt or payment is authentic.
Use Replit Agent Plan mode to request the complete React UI, Express server, Replit-managed Clerk identity and Replit PostgreSQL implementation defined in Appendices B and C. The reader owns the real expense policy, eligible employees/approvers, receipt retention, external payment process, privacy terms and release decision. Begin with synthetic staff, amounts and references. Read generated source, migrations and lockfile before running them. Use Node 22 or 24 only if the actual Replit development/published runtime and locked dependency engines support that active/maintenance LTS release. The provider documents the components; this packet does not assert that any account, app, race, payment, restore or published host was tested.
### Exact scope and money
This reference app is **USD only**, stores integer cents in PostgreSQL `bigint`, accepts decimal display input with exactly zero, one or two fractional digits, converts it on the server without binary floating point and refuses zero, negative, over-cap, fractional-cent, overflow, currency-mismatched or foreign-currency values. Display divides integer cents by 100. There is no foreign exchange, partial reimbursement, tax or mileage calculation. An owner who needs another currency or precision must redesign and re-review the whole money contract before using real expenses. The owner enters a positive per-expense cap, permitted category labels and whether an external receipt reference is required in each immutable policy version. No example limit, category or employee is represented as the business's actual policy. The current policy pointer governs **new** submissions; older submissions retain their exact policy version and submitted fields when that pointer changes.
An expense draft belongs to the submitting member and contains merchant, incurred date, category, purpose, amount and an optional 1–160-character external receipt reference. The reference is a **text identifier for a record held outside this app**, not a URL to fetch, an uploaded file, or proof that the receipt exists. The owner must separately secure and retain external evidence. Employees may edit or discard their own never-submitted drafts; discard hides the draft from normal lists but retains a restricted audit record under the owner's retention policy. After submission, the exact fields are copied to an immutable submission-cycle row. A return reopens the editable draft fields; resubmission creates a new numbered immutable cycle while prior snapshots remain. Rejection ends that expense; a new claim needs a new expense. Approval freezes that cycle. No submitted content can be overwritten to hide an earlier decision.
The owner enrolls exact existing Clerk user IDs as employee or approver, can deactivate memberships, publishes policy versions, assigns or reassigns a submitted claim to one active approver other than its submitter, and sees the whole workspace. Owner member changes use a **fixed route** with the validated target Clerk ID in bounded, CSRF-protected JSON, never in a URL. An approver sees only currently assigned claims and their full history, can approve, reject or return with a recorded reason, and cannot act on their own claim. The owner may also hold the approver role for a claim, but self-approval remains forbidden. An employee sees only their own claims and may edit/resubmit returned ones. Staff sign-up creates an identity only; it never creates SQL membership. Decisions and assignment checks use current membership and locked state, not a role badge from the browser. Reassignment is owner-only while submitted, and the former approver loses detail/export access on the next request. A final decision cannot be reissued through a stale tab.
After an approved claim, an owner who has checked the real external transfer records the exact full approved cents, payment date and unique external reference, with a required explicit verification attestation. The UI calls this **owner-recorded reimbursement**, not a bank confirmation. A mistaken record can receive an append-only owner correction referencing that record and a reason; the app returns the claim to approved for a new verified external record. This correction does not reverse money outside the app. The owner reconciles the external system separately. No employee or approver can mark reimbursement, and the app has no payment credentials or transfer API.
### Identity, data and release
Mount the installed Replit-managed Clerk proxy at its **exact generated paths before body parsers**. Leave only a document-free landing, sign-in/sign-up/callback and required managed authentication bootstrap/static transport outside app membership and custom staff CSRF; retain the provider's own authentication protections. Protect **all** expense/policy/roster/history/export HTML, API and SPA fallback routes with a verified Clerk session and current SQL workspace membership. After authentication, absent, foreign, inactive workspace and revoked membership receive one generic 404 for workspace-scoped routes; an unlisted account's workspace list is empty. Owner-only routes return 403 only within an active authorized workspace. A public Replit host can expose the authentication shell, but expense data stays behind app gates. A private Replit host may be used only if every invited intended employee can actually reach it; verify the host behavior rather than infer it from app code.
Keep financial data, staff IDs, merchant/purpose and receipt references out of URLs, logs, analytics and public bootstrap. Bound JSON and strings, use parameterized SQL, escaped UI text, HTTPS, server-only keys, `Cache-Control: private, no-store`, narrow allowed Origin and session-bound custom CSRF on staff writes. A bounded export first acquires the shared workspace lock in `READ COMMITTED`, then checks **fresh current membership and row scope** in a new statement while every relevant writer's workspace-exclusive lock must wait; finish serialization before releasing the shared lock. PostgreSQL snapshots/events and request-key digests are restricted metadata and may identify staff or reveal financial behavior; do not call them anonymous or PII-free. The owner sets a real retention and backup-expiry policy. There is no automatic legal-erasure claim or hard-delete of submitted audit history.
Release requires observed tests in the reader's Development and separate Production identities/databases: draft→submit→assign→approve/reject/return→resubmit, no self-approval, optimistic conflicts and replay, revocation, scoped search/export, immutable policy/snapshot readback after restart, recorded reimbursement/correction, external reference check, backup restore, signed-out and cross-workspace denial, and exact host routing. A human owner checks generated behavior and policy before real financial records. Any unsupported managed-auth path, incomplete SQL invariant, unverified external evidence process or failed denial test holds the **whole app** rather than silently reducing it to an expense form.
## Appendix B: Full PostgreSQL migration
Inspect a new target database and the generated implementation before applying this reference migration. Copy the SQL in full.
```sql
-- Original reference migration for one NEW empty PostgreSQL database.
-- Inspect generated code and each environment before applying. UUIDs come from
-- trusted Node crypto.randomUUID(), not browser-supplied ownership fields.
BEGIN;
CREATE TABLE ea_workspace (
id uuid PRIMARY KEY,
slug text NOT NULL UNIQUE CHECK (slug ~ '^[a-z0-9][a-z0-9-]{0,79}$'),
name text NOT NULL CHECK (char_length(name) BETWEEN 1 AND 120),
active boolean NOT NULL DEFAULT true,
current_policy_version integer,
created_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE ea_member (
workspace_id uuid NOT NULL REFERENCES ea_workspace(id) ON DELETE RESTRICT,
clerk_user_id text NOT NULL,
role text NOT NULL CHECK (role IN ('owner','employee','approver')),
active boolean NOT NULL DEFAULT true,
joined_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (workspace_id,clerk_user_id)
);
-- USD cents only. Versions and categories are immutable after publication.
CREATE TABLE ea_policy_version (
workspace_id uuid NOT NULL REFERENCES ea_workspace(id) ON DELETE RESTRICT,
version integer NOT NULL CHECK (version > 0),
currency_code char(3) NOT NULL DEFAULT 'USD' CHECK (currency_code = 'USD'),
max_amount_cents bigint NOT NULL CHECK (max_amount_cents BETWEEN 1 AND 9000000000000),
receipt_reference_required boolean NOT NULL,
published_by text NOT NULL,
published_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (workspace_id,version),
FOREIGN KEY (workspace_id,published_by) REFERENCES ea_member(workspace_id,clerk_user_id) ON DELETE RESTRICT
);
CREATE TABLE ea_policy_category (
workspace_id uuid NOT NULL,
policy_version integer NOT NULL,
category text NOT NULL CHECK (char_length(category) BETWEEN 1 AND 60),
PRIMARY KEY (workspace_id,policy_version,category),
FOREIGN KEY (workspace_id,policy_version) REFERENCES ea_policy_version(workspace_id,version) ON DELETE RESTRICT
);
ALTER TABLE ea_workspace ADD CONSTRAINT ea_current_policy_fk
FOREIGN KEY (id,current_policy_version) REFERENCES ea_policy_version(workspace_id,version) ON DELETE RESTRICT;
CREATE TABLE ea_expense (
workspace_id uuid NOT NULL REFERENCES ea_workspace(id) ON DELETE RESTRICT,
id uuid NOT NULL,
submitter_user_id text NOT NULL,
assigned_approver_user_id text,
state text NOT NULL DEFAULT 'draft'
CHECK (state IN ('draft','discarded','submitted','returned','rejected','approved','reimbursed')),
revision bigint NOT NULL DEFAULT 1 CHECK (revision > 0),
current_cycle_no integer NOT NULL DEFAULT 0 CHECK (current_cycle_no >= 0),
merchant text NOT NULL CHECK (char_length(merchant) BETWEEN 1 AND 120),
incurred_on date NOT NULL,
category text NOT NULL CHECK (char_length(category) BETWEEN 1 AND 60),
purpose text NOT NULL CHECK (char_length(purpose) BETWEEN 1 AND 1000),
amount_cents bigint NOT NULL CHECK (amount_cents BETWEEN 1 AND 9000000000000),
receipt_reference text CHECK (receipt_reference IS NULL OR char_length(receipt_reference) BETWEEN 1 AND 160),
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (workspace_id,id),
UNIQUE (workspace_id,id,submitter_user_id),
FOREIGN KEY (workspace_id,submitter_user_id) REFERENCES ea_member(workspace_id,clerk_user_id) ON DELETE RESTRICT,
FOREIGN KEY (workspace_id,assigned_approver_user_id) REFERENCES ea_member(workspace_id,clerk_user_id) ON DELETE RESTRICT,
CHECK (assigned_approver_user_id IS NULL OR assigned_approver_user_id <> submitter_user_id),
CHECK ((state IN ('draft','discarded') AND current_cycle_no=0 AND assigned_approver_user_id IS NULL)
OR (state NOT IN ('draft','discarded') AND current_cycle_no>0))
);
CREATE INDEX ea_expense_submitter_idx ON ea_expense(workspace_id,submitter_user_id,created_at DESC,id DESC);
CREATE INDEX ea_expense_assignee_idx ON ea_expense(workspace_id,assigned_approver_user_id,state,created_at DESC,id DESC);
-- Every submit/resubmit copies exact fields and current policy; old cycles never change.
CREATE TABLE ea_submission (
workspace_id uuid NOT NULL,
expense_id uuid NOT NULL,
cycle_no integer NOT NULL CHECK (cycle_no > 0),
submitter_user_id text NOT NULL,
policy_version integer NOT NULL,
currency_code char(3) NOT NULL DEFAULT 'USD' CHECK (currency_code='USD'),
merchant text NOT NULL CHECK (char_length(merchant) BETWEEN 1 AND 120),
incurred_on date NOT NULL,
category text NOT NULL,
purpose text NOT NULL CHECK (char_length(purpose) BETWEEN 1 AND 1000),
amount_cents bigint NOT NULL CHECK (amount_cents BETWEEN 1 AND 9000000000000),
receipt_reference text CHECK (receipt_reference IS NULL OR char_length(receipt_reference) BETWEEN 1 AND 160),
submitted_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (workspace_id,expense_id,cycle_no),
UNIQUE (workspace_id,expense_id,cycle_no,submitter_user_id),
UNIQUE (workspace_id,expense_id,cycle_no,amount_cents),
FOREIGN KEY (workspace_id,expense_id,submitter_user_id)
REFERENCES ea_expense(workspace_id,id,submitter_user_id) ON DELETE RESTRICT,
FOREIGN KEY (workspace_id,submitter_user_id) REFERENCES ea_member(workspace_id,clerk_user_id) ON DELETE RESTRICT,
FOREIGN KEY (workspace_id,policy_version,category)
REFERENCES ea_policy_category(workspace_id,policy_version,category) ON DELETE RESTRICT
);
CREATE TABLE ea_decision (
workspace_id uuid NOT NULL,
expense_id uuid NOT NULL,
cycle_no integer NOT NULL,
submitter_user_id text NOT NULL,
approver_user_id text NOT NULL,
outcome text NOT NULL CHECK (outcome IN ('approved','rejected','returned')),
reason text NOT NULL CHECK (char_length(reason) BETWEEN 1 AND 1000),
decided_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (workspace_id,expense_id,cycle_no),
UNIQUE (workspace_id,expense_id,cycle_no,outcome),
FOREIGN KEY (workspace_id,expense_id,cycle_no,submitter_user_id)
REFERENCES ea_submission(workspace_id,expense_id,cycle_no,submitter_user_id) ON DELETE RESTRICT,
FOREIGN KEY (workspace_id,approver_user_id) REFERENCES ea_member(workspace_id,clerk_user_id) ON DELETE RESTRICT,
CHECK (approver_user_id <> submitter_user_id)
);
-- A reimbursement is an owner's record of a separately verified external transfer.
CREATE TABLE ea_reimbursement (
workspace_id uuid NOT NULL,
expense_id uuid NOT NULL,
id uuid NOT NULL,
cycle_no integer NOT NULL,
decision_outcome text NOT NULL DEFAULT 'approved' CHECK (decision_outcome='approved'),
amount_cents bigint NOT NULL CHECK (amount_cents BETWEEN 1 AND 9000000000000),
external_reference text NOT NULL CHECK (char_length(external_reference) BETWEEN 1 AND 160),
paid_on date NOT NULL,
recorded_by text NOT NULL,
recorded_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (workspace_id,expense_id,id),
UNIQUE (workspace_id,external_reference),
FOREIGN KEY (workspace_id,expense_id,cycle_no,amount_cents)
REFERENCES ea_submission(workspace_id,expense_id,cycle_no,amount_cents) ON DELETE RESTRICT,
FOREIGN KEY (workspace_id,expense_id,cycle_no,decision_outcome)
REFERENCES ea_decision(workspace_id,expense_id,cycle_no,outcome) ON DELETE RESTRICT,
FOREIGN KEY (workspace_id,recorded_by) REFERENCES ea_member(workspace_id,clerk_user_id) ON DELETE RESTRICT
);
CREATE TABLE ea_reimbursement_correction (
workspace_id uuid NOT NULL,
expense_id uuid NOT NULL,
reimbursement_id uuid NOT NULL,
corrected_by text NOT NULL,
reason text NOT NULL CHECK (char_length(reason) BETWEEN 1 AND 1000),
corrected_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (workspace_id,expense_id,reimbursement_id),
FOREIGN KEY (workspace_id,expense_id,reimbursement_id)
REFERENCES ea_reimbursement(workspace_id,expense_id,id) ON DELETE RESTRICT,
FOREIGN KEY (workspace_id,corrected_by) REFERENCES ea_member(workspace_id,clerk_user_id) ON DELETE RESTRICT
);
-- Restricted, append-only history. IDs/financial metadata are still linkable.
CREATE TABLE ea_event (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
workspace_id uuid NOT NULL REFERENCES ea_workspace(id) ON DELETE RESTRICT,
expense_id uuid NOT NULL,
actor_user_id text NOT NULL,
action text NOT NULL CHECK (action IN ('create','edit','discard','submit','assign','reassign','approve','reject','return','resubmit','reimbursement_record','reimbursement_correct')),
cycle_no integer,
from_state text,
to_state text,
target_user_id text,
occurred_at timestamptz NOT NULL DEFAULT now(),
FOREIGN KEY (workspace_id,expense_id) REFERENCES ea_expense(workspace_id,id) ON DELETE RESTRICT,
FOREIGN KEY (workspace_id,actor_user_id) REFERENCES ea_member(workspace_id,clerk_user_id) ON DELETE RESTRICT,
FOREIGN KEY (workspace_id,target_user_id) REFERENCES ea_member(workspace_id,clerk_user_id) ON DELETE RESTRICT
);
CREATE INDEX ea_event_expense_idx ON ea_event(workspace_id,expense_id,id DESC);
CREATE TABLE ea_admin_event (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
workspace_id uuid NOT NULL REFERENCES ea_workspace(id) ON DELETE RESTRICT,
actor_user_id text NOT NULL,
action text NOT NULL CHECK (action IN ('member_add','member_change','policy_publish','workspace_change')),
target_user_id text,
policy_version integer,
occurred_at timestamptz NOT NULL DEFAULT now(),
FOREIGN KEY (workspace_id,actor_user_id) REFERENCES ea_member(workspace_id,clerk_user_id) ON DELETE RESTRICT,
FOREIGN KEY (workspace_id,target_user_id) REFERENCES ea_member(workspace_id,clerk_user_id) ON DELETE RESTRICT,
FOREIGN KEY (workspace_id,policy_version) REFERENCES ea_policy_version(workspace_id,version) ON DELETE RESTRICT
);
-- One request UUID per logical staff mutation. Store only a canonical-body digest;
-- actor and response bind replays after lock waits without repeating a decision.
CREATE TABLE ea_mutation_receipt (
workspace_id uuid NOT NULL REFERENCES ea_workspace(id) ON DELETE RESTRICT,
actor_user_id text NOT NULL,
request_key uuid NOT NULL,
operation text NOT NULL,
binding_sha256 char(64) NOT NULL CHECK (binding_sha256 ~ '^[0-9a-f]{64}$'),
expense_id uuid,
response_status integer NOT NULL CHECK (response_status BETWEEN 200 AND 299),
result_revision bigint,
committed_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (workspace_id,actor_user_id,request_key),
FOREIGN KEY (workspace_id,actor_user_id) REFERENCES ea_member(workspace_id,clerk_user_id) ON DELETE RESTRICT,
FOREIGN KEY (workspace_id,expense_id) REFERENCES ea_expense(workspace_id,id) ON DELETE RESTRICT
);
CREATE FUNCTION ea_reject_immutable_change() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
RAISE EXCEPTION 'expense policy and history rows are append-only';
END;
$$;
CREATE TRIGGER ea_policy_immutable BEFORE UPDATE OR DELETE ON ea_policy_version FOR EACH ROW EXECUTE FUNCTION ea_reject_immutable_change();
CREATE TRIGGER ea_category_immutable BEFORE UPDATE OR DELETE ON ea_policy_category FOR EACH ROW EXECUTE FUNCTION ea_reject_immutable_change();
CREATE TRIGGER ea_submission_immutable BEFORE UPDATE OR DELETE ON ea_submission FOR EACH ROW EXECUTE FUNCTION ea_reject_immutable_change();
CREATE TRIGGER ea_decision_immutable BEFORE UPDATE OR DELETE ON ea_decision FOR EACH ROW EXECUTE FUNCTION ea_reject_immutable_change();
CREATE TRIGGER ea_reimbursement_immutable BEFORE UPDATE OR DELETE ON ea_reimbursement FOR EACH ROW EXECUTE FUNCTION ea_reject_immutable_change();
CREATE TRIGGER ea_reimbursement_correction_immutable BEFORE UPDATE OR DELETE ON ea_reimbursement_correction FOR EACH ROW EXECUTE FUNCTION ea_reject_immutable_change();
CREATE TRIGGER ea_event_immutable BEFORE UPDATE OR DELETE ON ea_event FOR EACH ROW EXECUTE FUNCTION ea_reject_immutable_change();
CREATE TRIGGER ea_admin_event_immutable BEFORE UPDATE OR DELETE ON ea_admin_event FOR EACH ROW EXECUTE FUNCTION ea_reject_immutable_change();
CREATE TRIGGER ea_mutation_receipt_immutable BEFORE UPDATE OR DELETE ON ea_mutation_receipt FOR EACH ROW EXECUTE FUNCTION ea_reject_immutable_change();
COMMIT;
-- First owner: authenticated project/database administration, exact existing
-- Clerk user ID in this environment. No HTTP bootstrap or browser owner field.
-- INSERT INTO ea_workspace(id,slug,name) VALUES ($1,$2,$3);
-- INSERT INTO ea_member(workspace_id,clerk_user_id,role) VALUES ($1,$4,'owner');
```
## Appendix C: Full server, route, UI and acceptance contract
### Server, route and acceptance contract
This is a documentation-only reference. The reader must inspect the AI-generated implementation and test it in their own account. Use parameterized PostgreSQL on one connection per transaction, `READ COMMITTED` for mutations, no direct browser database access and no assumed payment or file API. Server UUIDs use `crypto.randomUUID()`. Currency is exactly `USD`; persist integer cents as SQL `bigint` and serialize cents as decimal **strings** in JSON. Parse a UI USD amount with regex `^(?:0|[1-9][0-9]{0,10})(?:\.[0-9]{1,2})?$`, split whole and fraction, right-pad fraction to two digits, convert with `BigInt`, and enforce `1 <= cents <= 9000000000000` and the owner policy cap. Do not use `Number`, floating-point multiplication, currency conversion or a browser-supplied cents value for approval/reimbursement. An explicit amount like `12.3` becomes 1,230 cents; `12.345`, `-1`, exponent notation and non-USD currency fail 400. Display `cents / 100` by integer formatting. Dates are strict real `YYYY-MM-DD` calendar dates, stored as SQL `date`, never derived from browser timezone; reject future incurred/paid dates against the server's UTC current date and do not represent this as a tax rule.
### Routing and authorization
At the actual Replit host, mount the **installed managed Clerk proxy on its exact generated paths before any Express body parser**, preserving its callback/bootstrap and provider protections. A document-free `/`, sign-in/sign-up and the exact required managed auth/static transport may load without app SQL membership or custom staff CSRF. Signup does not create a member. Then use bounded parsers, `clerkMiddleware()`/`getAuth(req)`, and a default-deny application router. All `/app`, `/api/workspaces`, expense/policy/member/history/export routes and SPA fallback need a verified session. `GET /api/workspaces` returns only active workspaces where the exact Clerk user ID has an active `ea_member` row; for an authenticated unlisted user it returns `[]`. On `:workspaceId` routes, authenticate first, validate UUID, read current membership and workspace active state; absent, foreign, inactive workspace or revoked membership is one generic 404 **before any expense lookup**. Signed-out app access is 401. Owner-only actions are 403 for an active member without that role. For a particular expense, owner sees all, submitter sees their own, and the currently assigned active approver sees that assigned claim and its history, including its later approved/rejected/reimbursed state; return clears the assignment. All other expense IDs, including previously assigned or foreign IDs, return the same 404. Historical actors do not gain general detail access just because an event names them. Re-check this predicate inside mutation locks and on every search/export page. Never trust path/body submitter, actor, workspace, assignee or role fields as authorization.
Use `Cache-Control: private, no-store` on all app data and HTML. Escape all merchant, purpose, reasons and receipt reference strings; never render visitor/staff HTML or load references as remote URLs. Cap staff serialized UTF-8 JSON at 32 KiB **before parsing**, plus the field bounds below; field character maxima do not guarantee that every max-length JSON combination fits. Reject unknown fields, malformed UUIDs, duplicate JSON keys if parser permits them, invalid Unicode/control characters and extra currency fields with 400; 413 for oversized bytes. Browser writes require exact allowed Origin and a short-lived HMAC custom CSRF token bound to the current Clerk session/user/expiry/nonce; `GET /api/workspaces/:id/csrf` is gated and no-store. Managed auth transport is outside this **app** CSRF gate but keeps provider protections. No CORS credential wildcard. Staff identities and financial text stay out of URLs, logs, analytics and error bodies. Use server-only secrets and HTTPS.
Owner bootstrap is **not HTTP**. Via authenticated project/database administration, create one workspace and member row with an exact existing Clerk user ID separately for Development and Production. Owner enrollment `POST /api/workspaces/:id/owner/members` accepts `{clerkUserId,role,requestKey}` in bounded CSRF-protected JSON, with role `employee` or `approver` (or deliberate owner succession via separately reviewed admin procedure); use the documented Clerk backend `getUser` with server credentials and confirm the intended human before commit. Email similarity never grants membership. Member change `PATCH /api/workspaces/:id/owner/members` uses a fixed path and bounded CSRF-protected JSON `{targetClerkUserId,role,active,expectedMemberState:{role,active},requestKey}`; validate the exact target ID in the body, bind it in the canonical mutation digest, and never put it in a path, query, redirect, analytics event or access log. Under the shared workspace lock, lock affected member rows in Clerk-ID order and compare the target's current role/active values to `expectedMemberState` before updating. A mismatch is 409 with no update. Recheck current owner and target membership/state after waits. A later current-role check denies the revoked user. Prevent deactivating the last active owner and prevent self-demotion in the same operation. Owner-submitted expenses require a separate approver; nobody, including another owner role view, can approve their own claim.
### Routes and lifecycle
All write bodies include `requestKey` as a random UUID per logical action and `expectedRevision` where editing an existing expense. The UI keeps the key after an uncertain response. Owner policy/member writes use an expected workspace/policy pointer where relevant. Validation and responses below are application requirements, not observations.
| Route | Allowed action and committed result |
| --- | --- |
| `GET /api/workspaces/:id/policy`, `GET /api/workspaces/:id/policies/:version` | Active member reads current published policy and immutable version/categories. Never use an edited current pointer to relabel old claims. |
| `POST /api/workspaces/:id/owner/policies` | Owner supplies positive USD cap as decimal amount, nonempty distinct trimmed NFC category names, `receiptReferenceRequired`, expected current-policy version and request key. Under workspace lock insert next version/categories, change pointer, append admin event/receipt in one transaction. A policy edit creates a new version, never updates an old version. Conflict 409 on pointer mismatch. |
| `GET /api/workspaces/:id/owner/members`, `POST /api/workspaces/:id/owner/members`, `PATCH /api/workspaces/:id/owner/members` | Owner roster/read/change. POST body names `clerkUserId`; fixed-path PATCH body names `targetClerkUserId` and expected member state. Backend exact Clerk identity check for enrollment; target membership, role, last-owner and concurrency checks under lock. Bind the validated target in the idempotency digest. No staff ID in a URL, public invitation or auto-enrollment. |
| `POST /api/workspaces/:id/expenses` | Any active member creates their own complete draft only; server chooses `submitter_user_id`, UUID and revision 1, appends create event/receipt. No policy check until submit. 201 on commit. |
| `PATCH /api/workspaces/:id/expenses/:expenseId` | Submitter edits only `draft` or `returned` fields with exact revision and request key; no submitted/approved/rejected/reimbursed edit. 200 plus new revision. |
| `POST /api/workspaces/:id/expenses/:expenseId/discard` | Submitter's never-submitted `draft` only; set `discarded`, append event/receipt. No hard-delete. 200. |
| `POST /api/workspaces/:id/expenses/:expenseId/submit` | Submitter's `draft` or `returned` only. Re-read current policy and exact category under lock, enforce positive amount/cap and required external receipt reference, copy draft fields and policy version to new `ea_submission` cycle, clear assignee, set `submitted`, append submit/resubmit event and receipt atomically. Returned cycle and decision remain immutable. 200. No automatic policy approval. |
| `POST /api/workspaces/:id/owner/expenses/:expenseId/assign` | Owner assigns/reassigns only a `submitted` claim to an exact active member with role `approver` or `owner`, excluding submitter. Check target Clerk membership and workspace after waits; set assignee and event. 200. |
| `POST /api/workspaces/:id/expenses/:expenseId/decision` | Only the **currently assigned** active approver/owner, never submitter, on a `submitted` claim with exact revision. Body `outcome` is `approved`, `rejected` or `returned` and a 1–1,000-character human reason. Insert one immutable `ea_decision` for current cycle, move expense to matching state, clear assignee on return, append event/receipt atomically. Rejected is terminal; returned may be edited and resubmitted under a new cycle. 200. |
| `POST /api/workspaces/:id/owner/expenses/:expenseId/reimbursements` | Owner only; `approved` claim and its matching immutable approved cycle, exact revision, `externalPaymentVerified:true`, full `amountCents` string equal to submitted cents, strict paid date and 1–160-character external payment reference. Human must check the actual external transfer first. Under locks ensure no uncorrected reimbursement, insert immutable record, set `reimbursed`, append event/receipt. 200. No transfer initiated. |
| `POST /api/workspaces/:id/owner/expenses/:expenseId/reimbursements/:recordId/correct` | Owner only, current `reimbursed` record, exact revision, 1–1,000-character reason and explicit acknowledgement that this does **not** reverse external funds. Append immutable correction; set state back to `approved`, event/receipt. Then any new reimbursement needs a newly verified external record and a different reference. 200. |
| `POST /api/workspaces/:id/expenses/search` and `GET /api/workspaces/:id/expenses/:expenseId` | Put sensitive search filters/cursor in JSON body, never URL query text. Scope every row by current role. Owner all, submitter own, currently assigned active approver only that assigned claim; a returned claim clears assignment. `q` is 2–80 literal characters searched against merchant/purpose with escaped SQL `ILIKE` wildcards; category/status/date exact filters. Page max 25, stable `(created_at,id)` keyset; bind cursor to filter and actor scope. Detail includes all immutable cycles, policy versions, assignment/decision/reimbursement/correction and event chronology under the same gate. |
| `POST /api/workspaces/:id/expenses/export` | Put filters in the bounded CSRF-protected JSON body. Download server-generated JSON only after the same current row-level gate; receipt references remain plain escaped strings, never fetched or rendered as links, and JSON avoids CSV formula interpretation. In one **`READ COMMITTED`** transaction make the first database statement `SELECT ... FROM ea_workspace WHERE id=$1 FOR SHARE`; after any wait, use a **new statement** to read current active workspace/member/role and the scoped expense rows. Do not rely on an authorization read or snapshot taken before this lock. Hold the shared workspace lock through bounded row materialization and JSON serialization, then commit before sending the complete buffer. Every membership, assignment, expense and policy writer must take that workspace row `FOR UPDATE` first, so a writer that committed before the export lock is visible to the new statement, and a later writer waits for the authorized export snapshot to finish. Cap at 5,000 rows/32 MiB and return 422 `narrow_export` before any body bytes if exceeded. On lock timeout, database failure or serialization/transaction error, roll back, discard the buffer and make at most one fresh transaction/authorization retry; never stream a partial export or use old membership. Export includes exact cents strings, submission cycles and events. Record owner-approved export handling; no public bucket or arbitrary debug dump. |
[PostgreSQL's transaction-isolation manual](https://www.postgresql.org/docs/current/transaction-iso.html) distinguishes `READ COMMITTED`'s new snapshot per command from `REPEATABLE READ`'s transaction snapshot. The latter can predate a lock wait even when the workspace row itself was not updated, so it cannot be used as the post-wait authorization read described here.
Error contract: 400 invalid shape/money/date; 401 signed out; generic workspace/record 404 as above; 403 active member wrong role; 409 changed request-key binding, revision/policy pointer conflict or stale state; 413 oversized body; 422 policy category/cap/receipt requirement or export bound. Responses never echo merchant/purpose/reason/reference for denial. A lost write response is **unknown**, not permission for a new key.
### Transaction, replay and state guards
For each write, authenticate and validate current workspace membership/role and request shape, then compute SHA-256 over a canonical UTF-8 binding of exact `method`, normalized route IDs, actor Clerk ID, operation, expected revision/pointer and validated body excluding `requestKey`. For a fixed-path member change, this binding includes the normalized `targetClerkUserId`, target role/active fields and expected member state from the validated JSON body; a key reused for another target is 409. In `READ COMMITTED`, read `(workspace_id,actor_user_id,request_key)` **before mutable revision/state/policy checks**. A replay is returned only after confirming that the exact actor remains an **active member of an active workspace**: same key/binding returns the stored generic status, expense ID and result revision; changed binding is 409. This narrow replay of the actor's own committed mutation remains available after assignment, state or policy changes but confers no general expense detail/list/export access. Revoked/inactive members are denied even for replay. For an apparently new key, lock `ea_workspace FOR UPDATE`, re-read the exact key/binding immediately after that wait, **recheck workspace and actor membership under the lock before any replay response**, then lock affected member rows in Clerk-ID order and `ea_expense FOR UPDATE` if one exists, re-reading the key again immediately after any expense wait. Do not check current revision, policy pointer, state, target role, assignee or reimbursement existence until those post-wait replay reads. Recheck active workspace/membership, actor/resource/target bindings, no-self-approval and expected revision after the locks. Inserts and every state change, immutable snapshot/decision/event/reimbursement/correction and `ea_mutation_receipt` commit in one transaction. Increment expense revision exactly once per committed mutation. A unique collision/deadlock rolls back fully before a bounded retry with fresh authorization and replay reads. No separate receipt/quota/decision commit or blind second reimbursement. The shared workspace lock serializes membership, policy, assignments and expense writes; lock waits cannot let a revoked user, changed approver or stale policy submit proceed on an earlier read.
When an owner records reimbursement, compare the approved current cycle and immutable `ea_decision` to the amount and require zero **uncorrected** reimbursement records for this expense. The correction table's one row per reimbursement marks that exact record corrected without editing its original claim. Under the expense lock, a second concurrent record sees either the committed active record or a changed revision and fails; a same-key retry returns the first committed result. `externalPaymentVerified:true` is a human attestation, never provider evidence. An uncertain external transfer is resolved outside the app before recording; do not create a second payment or guess its status here.
### Reader-run acceptance matrix, not claimed results
Use synthetic people, claims and external references in Development. Verify guest/unlisted/revoked/foreign/employee/assigned approver/other approver/owner direct HTML and API responses, plus default-deny fallback. Create, edit and discard a draft; submit under version 1, publish version 2, read old cycle as version 1 and verify new submissions use version 2. Deny zero, 3-decimal, overflow, non-USD, over-cap, unknown category and missing required reference. Return a claim, edit it, resubmit as cycle 2, and read both exact snapshots/reasons. Reject a separate claim and deny resubmit. Assign/reassign and deny the old approver's detail/export access, a guessed ID and self-approval; test owner-submitted claim assigned to a different approver. Race two decisions at one revision: one commit, one 409, one immutable decision. Hold a retry behind the workspace/expense lock while the winner changes revision, policy or assignee; the still-active original actor's same key/binding returns its generic committed result before stale checks, changed binding 409, revoked actor receives denial, and no second history row appears. Verify approver/owner decision reasons and no automated email/payment.
Record one **synthetic** externally verified reimbursement, then correct it and record a new verified reference; check unchanged old record, one correction, current status and all events. Two concurrent reimbursement attempts cannot create two active records. An app record is not proof of a bank transfer; compare the owner's actual external evidence separately. Verify list/search/detail/export visibility and bounds with cross-tenant IDs, after reassignment and after revocation. Reload/restart and inspect policy/snapshot/decision/history persistence, then perform an owner-approved restore drill in an isolated environment. Check actual Production identity, database, allowed host routes, supported Node/lockfile, secrets, charges, retention and staff policy before real financial data. Record each observed pass/failure or HOLD; **none of these tests ran for this packet**.
For export ordering, hold an owner mutation's workspace `FOR UPDATE` lock while it revokes a synthetic member; start that member's export and let it wait. After owner commit, the export's `FOR SHARE` lock and **new `READ COMMITTED` statement** must see revoked membership and return generic 404 with no bytes. Repeat with reassignment of an approver and require no previously assigned financial rows. Reverse the order: let an authorized export acquire `FOR SHARE` first, finish bounded materialization/serialization under that lock, and verify the revocation/reassignment writer waits; only the previously authorized scoped snapshot may be returned. Simulate a lock timeout or transaction error and require rollback, discarded buffer, at most one fresh authorization retry and no partial body. For member privacy, inspect the fixed owner member-change URL and access logs with a synthetic Clerk ID: the ID appears only in the protected bounded body, never path/query/redirect/log; change the target while reusing a request key and require 409 with neither target altered. These are reader-run checks, not performed results.
## Sources and further reading
- [Replit Agent overview](https://docs.replit.com/features/agent/overview) explains Plan mode and the coding-agent workflow. It does not establish that this particular expense app was built.
- [Replit's managed Clerk authentication guide](https://docs.replit.com/features/auth-and-identity/clerk-auth) documents the managed proxy order and distinct identity environments. The generated proxy paths still need inspection.
- [Replit's development and production database guide](https://docs.replit.com/features/data-and-storage/development-and-production) explains the two database environments; it does not verify this app's data or migrations.
- [Replit's publishing access guide](https://docs.replit.com/features/publishing/private-deployments) explains host sharing choices. Host admission and the app's SQL membership are separate checks.
- [Clerk's Express `getAuth()` reference](https://clerk.com/docs/reference/express/get-auth) and [middleware reference](https://clerk.com/docs/reference/express/clerk-middleware) describe server-side authentication state. Neither grants expense roles by itself.
- [Clerk's backend `getUser` reference](https://clerk.com/docs/reference/backend/user/get-user) supports exact ID lookup before the owner enrolls a named person; human identity confirmation remains the owner's responsibility.
- [PostgreSQL's `INSERT` reference](https://www.postgresql.org/docs/current/sql-insert.html), [constraints guide](https://www.postgresql.org/docs/current/ddl-constraints.html) and [explicit locking guide](https://www.postgresql.org/docs/current/explicit-locking.html) support the database primitives in the original contract. They do not validate an unbuilt application's invariants.
- [PostgreSQL's transaction isolation guide](https://www.postgresql.org/docs/current/transaction-iso.html) distinguishes `READ COMMITTED`'s new snapshot per command from a transaction-level repeatable snapshot, which matters after the export lock wait.
- [PostgreSQL's date/time reference](https://www.postgresql.org/docs/current/functions-datetime.html) documents server time functions used by the date rules. The business decides its policy.
- [Node.js release status](https://nodejs.org/en/about/previous-releases) lists release support stages; the actual Replit runtime and locked dependencies still determine compatibility.
## Sources
- [Replit Agent overview](https://docs.replit.com/features/agent/overview) — Plan mode supports project planning; no expense app generation observed.
- [Replit managed Clerk authentication](https://docs.replit.com/features/auth-and-identity/clerk-auth) — Managed proxy order and separate identities; generated paths require inspection.
- [Replit development and production databases](https://docs.replit.com/features/data-and-storage/development-and-production) — Distinct databases; project binding untested.
- [Replit publishing access](https://docs.replit.com/features/publishing/private-deployments) — Host sharing options; intended staff access untested.
- [Clerk Express getAuth](https://clerk.com/docs/reference/express/get-auth) — Server authentication state; SQL membership separate.
- [Clerk Express clerkMiddleware](https://clerk.com/docs/reference/express/clerk-middleware) — Middleware authentication; SQL roles separate.
- [Clerk backend getUser](https://clerk.com/docs/reference/backend/user/get-user) — Exact provider ID lookup before human-confirmed enrollment.
- [PostgreSQL INSERT](https://www.postgresql.org/docs/current/sql-insert.html) — Insert and conflict primitives for proposed transactions.
- [PostgreSQL explicit locking](https://www.postgresql.org/docs/current/explicit-locking.html) — Row lock primitives; no race executed.
- [PostgreSQL constraints](https://www.postgresql.org/docs/current/ddl-constraints.html) — Reference migration constraint primitives; SQL unexecuted.
- [PostgreSQL transaction isolation](https://www.postgresql.org/docs/current/transaction-iso.html) — Fresh READ COMMITTED statement after share-lock wait.
- [PostgreSQL date and time functions](https://www.postgresql.org/docs/current/functions-datetime.html) — Database date/time functions; business rule is proposed.
- [Node.js release status](https://nodejs.org/en/about/previous-releases) — 22/24 LTS status at source check; runtime/dependency compatibility untested.
The BIG CHANGE newsletter
The big picture. At your pace.
Recent stories on AI and robotics, the shifts worth watching and practical ideas to use. Choose a daily briefing, weekly digest or monthly perspective.
Next scheduled send (UTC): . Your first edition arrives at the next scheduled send after you confirm.
Your privacy, your choice.
Necessary storage supports site security and remembers your choices. Optional Google Analytics stays off until you allow it. You can read every story with necessary storage only. Privacy details