Fidello: a multi-tenant loyalty platform where the database is the backend
Problem
Small coffee shops run loyalty on paper cards: they get lost, they get forged, and the owner learns nothing from them. Existing digital alternatives ask the customer to install an app, which kills adoption at the counter, or lock the business into a single-brand wallet. I wanted a platform where:
- a customer collects a stamp by scanning a QR at the till — nothing to install, no account required to start;
- several businesses coexist on the same platform with strict data isolation (a manager from shop A can never see shop B);
- a business can onboard itself, run tiers and redemptions and read its own KPIs;
- fraud vectors specific to stamps (replayed QR codes, cashier abuse) are closed by design, not by policy.
Approach
Two deliberate bets. First, Spec-Driven Development end to end: every one of the 97 OpenSpec changes started as a proposal, a set of verifiable scenarios and a design before any code — with five specialised AI agents (backend, frontend, security-reviewer, solution-architect, product-strategy) working against those artifacts. Second, no application server: business rules live in PostgreSQL (RPC in PL/pgSQL) behind Supabase, and the React PWA is a thin client. That removed a whole tier to secure, deploy and test, and made the security model something a reviewer can read in SQL.
The core journeys were validated by product during an early pilot, and each of the 28 user journeys has a twin runbook that describes how to operate and troubleshoot it.
Architecture
Figure 1 — Fidello runtime and delivery pipeline. Everything below the HTTPS line is Supabase; there is no custom server to run.
Reading the diagram top-down: five actor roles share one PWA (route-level code-splitting loads only the actor's screens). Every request hits Supabase's row-level security before any row is visible; anything that mutates state — registering a visit, adding stamps, redeeming a reward, onboarding a business — goes through a PL/pgSQL RPC that validates the caller's role and tenant, then writes to the tables. Edge Functions exist only for things Postgres cannot do (create auth users for onboarding, send the platform report). pg_cron schedules the monthly platform report and housekeeping jobs.
Decisions
D1 · Business rules in PL/pgSQL SECURITY DEFINER, not in an API tier
Why: one place to enforce tenant and role checks, testable against a real Postgres, and the client can never bypass them because it only holds the anonymous key. Trade-off: PL/pgSQL is less ergonomic than TypeScript; mitigated with 337 DB tests that run the RPCs against a real database on every CI run.
D2 · Multi-tenancy with Row-Level Security
Why: isolation is enforced by the database engine for every query path (REST, RPC, Realtime), not by remembering a WHERE business_id = …. Alternative rejected: a schema or database per tenant — operationally heavier for a young product and it does not simplify the cross-tenant views the platform admin needs.
D3 · Guest-first identity with QR tokens that expire
Why: adoption at the counter. A guest gets stamps with a device identity and can later link that progress to a registered account. The customer's QR is a short-lived token with a TTL and a SHA-256 fingerprint, so a screenshot cannot be replayed at another till; anonymous RPCs are rate-limited.
D4 · Feature flags (global + per-business override) instead of branches
Why: the platform needs to switch capabilities per business without a deploy. It is also how the "built but off" integrations (Wallet, OAuth, e-mail, WhatsApp) stay in the codebase without being sold as active: the flag is off and no credentials are configured.
D5 · Immutable migrations + real-database tests as the contract
Why: with the database as the backend, the schema is the API. 91 sequential migrations are the only source of the schema; CI has two blocking jobs — UI tests (Vitest + Testing Library + msw) and DB tests against a real Postgres via a pg harness — and a deploy to QAS only happens when both are green.
D6 · 100% SDD with an architecture review package
Why: 97 OpenSpec changes gave every feature a proposal, scenarios and a design before code; 28 journeys have a twin runbook. The repository also ships an architecture review package (pieces, where each rule lives, deployment, scale limits, recommendations) so an external reviewer can evaluate the system without reading the code.
Result
- Deployed to QAS/PROD (Vercel + Supabase Cloud), with the core journeys validated by product during an early pilot.
- 793 tests, 100% green — 337 database tests against real Postgres and 456 UI tests — on every push, as blocking CI jobs.
- Security that a reviewer can read: RLS policies,
SECURITY DEFINERRPCs, expiring QR tokens, rate limiting and a PII-free audit log, all in SQL. - Operable: 28 journeys with runbooks, 5 scheduled jobs, feature flags per business, and a review package for external audits.
The repository is private. I am happy to walk through the code, the specs and the review package on a call.