Rules-driven invoicing platform with operator and client portals

Auto-Invoice

A full-stack TypeScript platform that ingests usage spreadsheets from Google Drive, runs them through a declarative pricing-rules engine, and turns the result into invoices, ledger entries and versioned PDFs, with separate operator and client portals behind role-based access.

Case study by Hiyan Kubagawa · updated November 2026

Context: Professional · National Sales Partners
Role: Primary developer
Period: 2025 — 2026
Stack: TypeScript, Next.js 16, React 19, Supabase (Postgres · Auth · Storage), Google OAuth

At a glance

01 · Problem
Turning daily usage spreadsheets into invoices under different rules for every client is repetitive, and an invoice that is wrong, duplicated or missing a day is expensive to unwind.
02 · Solution
A Next.js and Supabase platform that ingests spreadsheets from Google Drive, prices them with declarative per-client rules, and produces invoices, ledger entries and versioned PDFs.
03 · Impact
One repeatable flow from raw spreadsheet to finished invoice. Imports and invoice creation are safe to re-run, rule changes are template edits rather than code changes, and clients only ever see their own documents.

Framework: Next.js 16 App Router · React 19
Language: TypeScript end to end
Data: Supabase Postgres · Storage buckets
Auth: Supabase Auth · SSR cookie sessions
Ingest: Google OAuth 2.0 · Drive API · XLSX
Engine: Declarative JSON pricing rules
Documents: pdf-lib rendering · versioned storage
Access: Operator / client roles · route + API guards

The problem

Invoicing from usage data starts with a folder of daily spreadsheets and ends with a document a client will check line by line. Every client is billed under different rules, spreadsheets arrive late or get re-exported, and an invoice that is wrong, duplicated or missing a day is expensive to unwind. The job was to make the whole path from raw file to finished invoice repeatable, auditable and safe to re-run.

Architecture

Architecture, simplified · provider shown genericallyTypeScript · Next.js 16 · React 19 · scroll →

Engineering patterns

P1 · Ingest

Spreadsheets in, clean rows out, safe to repeat

Daily usage workbooks come from a connected Google Drive folder. Every stage is idempotent, and the cursor only moves when a run succeeds with data.

01ListDrive folderfiles where modifiedTime > cursor
02Fetchstream → Bufferany response shape normalized
03ParseSheetJSsheet_to_json · date from filename
04Normalizetyped rowsnumbers · dates · source_file
05Upsertchunks of 1,000onConflict (user, date, campaign, file)
06Commitadvance cursoronly if files > 0

mode=incremental

Reads only new or re-exported files since the last successful import.

mode=full

Ignores the cursor and re-reads everything. Upserts make it safe.

empty run

Cursor is left untouched, so a run that saw nothing can never skip a file later.

P2 · Rules engine

Pricing as data: try it

Each client's billing is a typed JSON template. Change the rules and watch the engine re-price eight synthetic rows live.

filters · minutes ≥ 10

template.rules.jsonlive

{
  "filters": [
    {
      "field": "minutes",
      "op": ">=",
      "value": 10
    }
  ],
  "group_by": [
    "campaign"
  ],
  "metrics": [
    {
      "name": "billable_events",
      "op": "SUM_COLUMN",
      "column": "billable"
    }
  ],
  "pricing": [
    {
      "description": "Billable events · {{campaign}}",
      "quantity": "{{billable_events}}",
      "unit_price": 0.5,
      "min_total": 40
    }
  ],
  "adjustments": [
    {
      "type": "round",
      "to": 2
    }
  ]
}

8 rows in5 after filters2 groups

Line	Qty	Rate	Amount
Billable events · ALPHA	278	0.50	139.00
Billable events · BRAVO	111	0.50	55.50
Total			194.50

Synthetic rows, invented campaigns and a made-up rate. Same function powers preview and the final invoice.

P3 · Mapping

Which client does this usage belong to?

Campaign codes resolve through a precedence cascade. Type a code or pick one.

campaign code

exactALPHA-MAINClient Ano
prefixBRV-Client BMATCH
regex^ZX\d{3}$Client Cskipped
fallback*Unmapped bucket—

Most specific rule wins. Anything that matches nothing is never billed silently: it lands in an Unmapped bucket for a person to review.

P4 · Create flow

An invoice is all or nothing

Without one wrapping transaction, the create route runs as a saga: every write has a compensating delete. Break it on purpose.

01Previewdry-run the rules
02Guardno invoice for client + period
03Insert invoiceheader row
04Insert linesone per priced line
05Ledger entryeffective date
06Render PDFpdf-lib
07Store versionstorage + document row

Pick a scenario to run the create flow.

P5 · Access

Defense in depth for two audiences

EdgeNext.js proxyRefreshes the Supabase session cookies on every page request, so server and browser agree on who is signed in.
PagesRoleRouteGuardOperator and client areas render only for their role. Signed-out visitors go to sign-in with a return path; inactive profiles are turned away.
APIHandler checksRoute handlers re-check the caller's session or bearer token, and their role where it matters, returning 401 or 403 before touching data.
SecretsServer-only keysThe service-role key exists only inside route handlers. Browser bundles carry the public anon key only.

Approach

Usage data comes straight from Google Drive. Each user connects Drive with OAuth; an ingest route lists the folder, parses each workbook with SheetJS, normalizes rows and upserts them in chunks keyed on (user, date, campaign, source file), so importing the same file twice changes nothing.

Imports are incremental: a per-user cursor on file modification time means only new or re-exported files are read. The cursor only advances after a run that actually imported something, so an empty or failed run can never skip data (a bug I found and fixed in production: cursor poisoning).

Billing rules are data, not code. Each client has a JSON template the engine evaluates in five stages: header aliases, filters, grouping, metrics (COUNT, COUNT_WHERE, SUM_COLUMN) and pricing blocks whose quantities are tokens such as {{billable_events}}, with minimum floors, rounding and tax.

Creating an invoice previews first with the same engine, refuses a second invoice for the same client and period (HTTP 409), then writes the invoice, its lines and a ledger entry, compensating (deleting) earlier writes if a later one fails. The PDF is rendered with pdf-lib and stored as a new document version.

What was built

01Google Drive connection with OAuth and folder selection
02Incremental and full-resync spreadsheet ingest
03Rules-driven pricing templates per client
04Campaign-to-client mapping: exact, prefix and regex rules
05Live invoice preview before anything is written
06Duplicate-period guard and compensating rollback
07Ledger entries and recorded payments per invoice
08Versioned PDF documents in object storage
09Operator portal: clients, team, templates, generation
10Client portal: their invoices and documents only

auto-invoice / operator / generaterecreated · synthetic data

GenerateNew invoice

Preview · not saved

ClientClient A ▾

PeriodMay 01 – May 31

TemplateStandard · v3

DueNet 15

248 rows→196 after filters→2 groups→2 lines

Line	Qty	Rate	Amount
Billable events · ALPHA	1,412	0.50	706.00
Billable events · BRAVO	64	0.50	40.00 floor
Total			746.00

Re-run previewCreate & publish

Operator view: pick client, period and template, and see the engine's preview before anything is written.

auto-invoice / clientrecreated · synthetic data

Client portalClient A · Invoices

Signed in · client role

May 2026Due 15 days after issue
Published746.00PDF v1
Apr 2026Due 15 days after issue
Payment recorded702.50PDF v2
Mar 2026Due 15 days after issue
Payment recorded688.00PDF v1

Client portal: their invoices, status and every PDF version.

INVOICEPeriod 05/01 – 05/31

Bill toClient A

Issued06/01

Due06/16

Description	Qty	Rate	Amount
Billable events · ALPHA	1,412	0.50	706.00
Billable events · BRAVO	64	0.50	40.00

Total746.00

generated · pdf-lib · document v1

The generated document, rendered server-side with pdf-lib.

Recreated interfaces with invented names, periods and amounts. Not screenshots of the production system.

TypeScriptNext.js 16React 19Supabase (Postgres · Auth · Storage)Google OAuthDrive APISheetJSpdf-libTailwind CSS

The hard parts

Idempotent by construction

Re-running an import or retrying a request must never double-count. Composite conflict keys on upsert and the duplicate-period guard make every write safe to repeat.

No partial invoices

Postgres writes go through a REST client without one wrapping transaction, so the create flow is a small saga: each step has a compensating delete that runs if a later step fails.

Rules people can change

Pricing differs per client and changes over time. Putting it in a typed JSON DSL with a pure evaluator means new rules are a template edit, and the same function powers preview and final invoice.

Two audiences, one app

Operators and clients share one codebase but get different areas: sessions refresh in a Next.js proxy, pages are guarded by role, and API handlers re-check the caller before touching data.

Outcome

From raw spreadsheet to finished invoice PDF in one flow
Imports and invoice creation safe to re-run
Separate operator and client portals on one codebase

NextCommunication Systems Modernization →Contact-center platform migration, integrations and reporting