Khalil Zero-Cost Stack
Overview
One architecture: React SPA + ONE serverless function + a spreadsheet as the database, all on one origin, on a free host. Proven in production: records, payments, expenses, generated documents, role separation and public one-time client links.
Core principle: the spreadsheet is a real database with real rules. Break the four invariants below and it corrupts quietly.
When to Use
- Internal tool, a handful of named users, a few thousand records/year.
- Owner wants to open the data in a tool they already know.
- $0/month hosting is a requirement.
Do NOT use for: public sign-up, regulated data (health, cards), real concurrency, sub-100ms responses, tens of users. Run the fit test in references/playbook.md §3 before agreeing.
Stack
| Layer |
Choice |
| Frontend |
React 19 + Vite + TS strict, oxlint, plain CSS with design tokens |
| Routing |
None. main.tsx picks a root from window.location.pathname |
| State |
Context for auth + toast; useState/useMemo in App.tsx. No state library |
| Backend |
ONE function, export const config = { path: '/api/*' }, hand-written regex router |
| Data |
Google Sheets REST + hand-rolled RS256 service-account JWT (no SDK) |
| Auth |
bcryptjs hashes in a Users tab, jsonwebtoken bearer tokens, 8h expiry |
| Host |
Netlify — static client/dist + function on one origin, SPA fallback redirect last |
Repository Skeleton
client/ React SPA (own package.json, vite, tsconfig)
netlify/functions/ api.ts — the whole backend, one file
lib/ sheets.ts | stores.ts | auth.ts | errors.ts | types.ts | dates.ts + domain modules
templates/ document templates shipped via netlify.toml included_files
scripts/ migration + backup scripts
docs/ ARCHITECTURE.md (this app) + playbook (the method)
Layer Contracts
| Layer |
Owns |
Never |
netlify/functions/api.ts |
HTTP: path match, method, status, role gate |
Business rules, sheet reads |
lib/stores.ts + domain modules |
Validation, domain shapes, row↔domain mapping |
HTTP objects, raw fetch |
lib/sheets.ts |
Rows, schema bootstrap, tokens |
Domain meaning |
client/src/lib/api.ts |
The ONLY place that calls fetch |
Business rules |
| views/components |
Rendering + local UI state |
Direct fetch |
The Four Invariants
- Columns are read by position — append only. Never insert or reorder a column in
TABS. repairHeaders extends an exact prefix and refuses anything else.
- Every cell is read as text (
cellText), parsed at the domain layer. Write valueInputOption=RAW so ISO dates stay strings.
- Soft delete (
DeletedAtUtc), never deleteRow, for anything another row references. Archive + restore, not delete.
- The server enforces roles. Hiding a tab in the client is never what keeps data private — a role check runs before routing and
requireAdmin runs per route.
Port These Directly
templates/ holds working code to copy almost unchanged:
| File |
What it gives you |
sheets.ts |
Service-account JWT, token cache, ensureSchema single-flight, repairHeaders, readTable, appendRow(s), updateRow, updateCells, deleteRow, nextId — edit only the TABS const |
errors.ts |
HttpError/ValidationError/NotFoundError/ConfigurationError → { status, detail } |
auth.ts |
bcrypt + JWT login, authenticate, requireAdmin, seeded first admin |
dates.ts |
Loose date/money parsing for spreadsheet cells |
netlify.toml, env.example |
Build, function bundling, redirects, env keys |
Build Order
- Fit test (
references/playbook.md §3) → stop here if amber/red.
- Foundation: repo skeleton,
TABS, port sheets.ts + errors.ts, one read route end to end.
- Auth: port
auth.ts, Users tab, seeded admin, login page + AuthContext.
- Core domain: one entity — validate → store → route → view — then repeat.
- Deploy to Netlify with env vars; verify one origin serves app + API.
- Import legacy data (idempotent script).
- Backup to a second Google account before launch — version history dies with the file.
- Everything else.
Details per phase: references/playbook.md §23. File-by-file map of the reference app: references/reference-implementation.md.
Common Mistakes
| Mistake |
Consequence |
Fix |
Insert a column mid-TABS |
Every existing row's data shifts |
Append at the end, always |
Skip ensureSchema single-flight |
Google forks duplicate <tab>_conflict<id> sheets |
Share one bootstrap promise |
updateRow for a partial edit |
Overwrites a concurrent edit's untouched cells |
updateCells for selective writes |
Public link goes through the authed request() |
An expired client token logs the staff user out |
Separate publicRequest(), no 401 handler |
| Role check only in the client nav |
Any token reads everything |
Deny-list path check + requireAdmin in the router |
| Money/dates trusted from JSON |
Silent coercion |
Strings in transit, parse + validate in stores.ts |
| Defer backups |
One deleted file = total loss |
Phase 6, before launch |
Extras Worth Copying
- Public capability links: token hashed at rest, expiry, per-IP rate limit,
no-store headers, routes placed before the bearer check, version nonce on submit. references/playbook.md §15.
- Dev mock API:
import.meta.env.DEV && ?mock=1 wraps the real API with in-memory data — UI work without the function or the sheet.
- One compact breakpoint shared by
styles.css and useIsMobile(); below it: bottom nav, card lists instead of tables, bottom-sheet drawers.
- Per-page nav metadata (
nav.ts: groups, adminOnly, hints, subtitles) so sidebar, bottom bar and page header stay in sync.
- Document generation: docxtemplater + pizzip in the function, template shipped via
included_files. §17.
1---2name: khalil-zero-cost-stack3description: Use when building or extending an internal business/operations app — bookings, clients, stock, jobs, invoices, appointments — that must run on free hosting with no database and no monthly bill, or when porting the React SPA + single serverless function + spreadsheet-as-database architecture to a new project. Also use when adding roles, public one-time client links, document generation, or a spreadsheet schema change to such an app.4---56# Khalil Zero-Cost Stack78## Overview910One architecture: **React SPA + ONE serverless function + a spreadsheet as the database**, all on one origin, on a free host. Proven in production: records, payments, expenses, generated documents, role separation and public one-time client links.1112**Core principle:** the spreadsheet is a real database with real rules. Break the four invariants below and it corrupts quietly.1314## When to Use1516- Internal tool, a handful of named users, a few thousand records/year.17- Owner wants to open the data in a tool they already know.18- $0/month hosting is a requirement.1920**Do NOT use for:** public sign-up, regulated data (health, cards), real concurrency, sub-100ms responses, tens of users. Run the fit test in `references/playbook.md` §3 before agreeing.2122## Stack2324| Layer | Choice |25|---|---|26| Frontend | React 19 + Vite + TS strict, oxlint, plain CSS with design tokens |27| Routing | None. `main.tsx` picks a root from `window.location.pathname` |28| State | Context for auth + toast; `useState`/`useMemo` in `App.tsx`. No state library |29| Backend | ONE function, `export const config = { path: '/api/*' }`, hand-written regex router |30| Data | Google Sheets REST + hand-rolled RS256 service-account JWT (no SDK) |31| Auth | bcryptjs hashes in a `Users` tab, `jsonwebtoken` bearer tokens, 8h expiry |32| Host | Netlify — static `client/dist` + function on one origin, SPA fallback redirect last |3334## Repository Skeleton3536```37client/ React SPA (own package.json, vite, tsconfig)38netlify/functions/ api.ts — the whole backend, one file39lib/ sheets.ts | stores.ts | auth.ts | errors.ts | types.ts | dates.ts + domain modules40templates/ document templates shipped via netlify.toml included_files41scripts/ migration + backup scripts42docs/ ARCHITECTURE.md (this app) + playbook (the method)43```4445## Layer Contracts4647| Layer | Owns | Never |48|---|---|---|49| `netlify/functions/api.ts` | HTTP: path match, method, status, role gate | Business rules, sheet reads |50| `lib/stores.ts` + domain modules | Validation, domain shapes, row↔domain mapping | HTTP objects, raw fetch |51| `lib/sheets.ts` | Rows, schema bootstrap, tokens | Domain meaning |52| `client/src/lib/api.ts` | The ONLY place that calls `fetch` | Business rules |53| views/components | Rendering + local UI state | Direct `fetch` |5455## The Four Invariants56571. **Columns are read by position — append only.** Never insert or reorder a column in `TABS`. `repairHeaders` extends an exact prefix and refuses anything else.582. **Every cell is read as text** (`cellText`), parsed at the domain layer. Write `valueInputOption=RAW` so ISO dates stay strings.593. **Soft delete** (`DeletedAtUtc`), never `deleteRow`, for anything another row references. Archive + restore, not delete.604. **The server enforces roles.** Hiding a tab in the client is never what keeps data private — a role check runs before routing and `requireAdmin` runs per route.6162## Port These Directly6364`templates/` holds working code to copy almost unchanged:6566| File | What it gives you |67|---|---|68| `sheets.ts` | Service-account JWT, token cache, `ensureSchema` single-flight, `repairHeaders`, `readTable`, `appendRow(s)`, `updateRow`, `updateCells`, `deleteRow`, `nextId` — edit only the `TABS` const |69| `errors.ts` | `HttpError`/`ValidationError`/`NotFoundError`/`ConfigurationError` → `{ status, detail }` |70| `auth.ts` | bcrypt + JWT login, `authenticate`, `requireAdmin`, seeded first admin |71| `dates.ts` | Loose date/money parsing for spreadsheet cells |72| `netlify.toml`, `env.example` | Build, function bundling, redirects, env keys |7374## Build Order75761. Fit test (`references/playbook.md` §3) → stop here if amber/red.772. Foundation: repo skeleton, `TABS`, port `sheets.ts` + `errors.ts`, one read route end to end.783. Auth: port `auth.ts`, `Users` tab, seeded admin, login page + `AuthContext`.794. Core domain: one entity — validate → store → route → view — then repeat.805. Deploy to Netlify with env vars; verify one origin serves app + API.816. Import legacy data (idempotent script).827. **Backup to a second Google account before launch** — version history dies with the file.838. Everything else.8485Details per phase: `references/playbook.md` §23. File-by-file map of the reference app: `references/reference-implementation.md`.8687## Common Mistakes8889| Mistake | Consequence | Fix |90|---|---|---|91| Insert a column mid-`TABS` | Every existing row's data shifts | Append at the end, always |92| Skip `ensureSchema` single-flight | Google forks duplicate `<tab>_conflict<id>` sheets | Share one bootstrap promise |93| `updateRow` for a partial edit | Overwrites a concurrent edit's untouched cells | `updateCells` for selective writes |94| Public link goes through the authed `request()` | An expired client token logs the staff user out | Separate `publicRequest()`, no 401 handler |95| Role check only in the client nav | Any token reads everything | Deny-list path check + `requireAdmin` in the router |96| Money/dates trusted from JSON | Silent coercion | Strings in transit, parse + validate in `stores.ts` |97| Defer backups | One deleted file = total loss | Phase 6, before launch |9899## Extras Worth Copying100101- **Public capability links:** token hashed at rest, expiry, per-IP rate limit, `no-store` headers, routes placed *before* the bearer check, version nonce on submit. `references/playbook.md` §15.102- **Dev mock API:** `import.meta.env.DEV && ?mock=1` wraps the real API with in-memory data — UI work without the function or the sheet.103- **One compact breakpoint** shared by `styles.css` and `useIsMobile()`; below it: bottom nav, card lists instead of tables, bottom-sheet drawers.104- **Per-page nav metadata** (`nav.ts`: groups, `adminOnly`, hints, subtitles) so sidebar, bottom bar and page header stay in sync.105- **Document generation:** docxtemplater + pizzip in the function, template shipped via `included_files`. §17.
elias-khalil-eng