A practical guide for backend developers
Build a SaaS backend you can prove is correct.
One real multi-tenant application on PostgreSQL 18 and PostgREST, from an empty schema to a release you can hand over, with every guarantee challenged by a test you can run.
The problem
Your backend works in the demo.
Production asks harder questions.
CRUD tutorials stop exactly where real SaaS systems start to fail. This book is built around the moments that break them.
Who gets it, and how do you prove the other order cannot oversell? Chapter 7 races two real HTTP clients to find out.
Idempotency keys return the original result without a second stock movement or a duplicate order.
A PostgreSQL job queue with leases and fencing tokens lets a replacement finish the work without trusting a stale result.
Row-level security and tenant-aware keys enforce the boundary in the database, even when a query forgets its filter.
What you will build
Ledger & Stock: a multi-tenant sales and inventory SaaS
Two companies share one application and one database. By the last chapter the backend has everything below, and a test for each guarantee.
Tenant isolation
Forced row-level security, membership-based authorization and composite keys that keep every relationship inside its tenant.
A contract-first API
PostgREST over a dedicated api schema, with explicit commands, versioned edits and structured errors.
Correct concurrency
Stock reservations with consistent lock ordering that cannot oversell under competing requests.
Safe retries
Idempotent create, receive, confirm, cancel, ship and reverse commands with replayable results.
Durable jobs & webhooks
A job queue with leases and bounded retries, a transactional outbox and signed webhooks with receiver deduplication.
Search, fields, audit, reports
Full-text and typo-tolerant search, validated JSONB custom fields, an audit trail with a real actor model and operational reports.
Live schema changes
Expand, backfill, validate and enforce while old and new clients keep calling the API.
Tested recovery
A backup restored into a separate cluster that must still enforce permissions, replay saved results and accept new orders.
A release you can explain
Request budgets, a restricted operator snapshot, a verifiable package and an honest list of what production still needs.
Look inside
Designed for reading on screen
Clear chapter structure, highlighted SQL and diagrams, and short sections you can act on.
Download the free sample (PDF)
29 pages: the preface, Chapter 1 and Chapter 7 — the concurrency chapter, complete.
Table of contents
Twenty chapters, four parts
Part I · A Foundation We Can Trust
- 1Two Companies, One ApplicationThe product, its tenants and the guarantees to preserve
- 2The First Executable SlicePostgreSQL, PostgREST and a reproducible first request
- 3Who Owns This Row?Organizations, memberships and tenant-aware relationships
- 4Identity Is Not PermissionVerified JWTs, membership context and row-level security
- 5An API Is a ContractPublic resources, commands, errors and version conflicts
Part II · Business Rules Under Pressure
- 6An Order Is More Than a RowLines, prices, currencies, totals and state
- 7Two Orders, One Item LeftReservations and a real concurrency race
- 8The Customer Clicked TwiceIdempotency keys and safe retries
- 9Cancel, Release, ReverseCancellation, shipment and compensating corrections
Part III · More Than Relational Storage
- 10Work That Survives a RestartA durable job queue with leases
- 11Tell the Outside WorldTransactional outbox and signed webhooks
- 12Find the Product the User MeantFull-text, prefix and typo-tolerant search
- 13Every Customer Wants Another FieldJSONB custom attributes
- 14Who Changed This Order?An audit trail with an explicit actor model
- 15Reports Without GuessworkOperational reports and freshness
Part IV · Operate, Measure, and Evolve
- 16One Tenant Gets BusyRequest budgets and shared-capacity measurements
- 17Change the Schema While the Product LivesExpand, backfill, validate, enforce
- 18Restore Before You Need ToA whole-system backup and restore rehearsal
- 19Ship a System You Can ExplainOperations, diagnostics and a verifiable release
- 20When PostgreSQL Is No Longer EnoughMeasured reasons to add infrastructure
Appendices
- ASQL and PL/pgSQL Conventions
- BDatabase Roles and Privileges
- CAPI Examples and Error Conventions
- DReproducing the Experiments
- EGlossary and Further Reading
Is it for you?
Written for developers who ship backends
A great fit if you…
- build APIs and know tables, joins and basic SQL
- are designing or maintaining a multi-tenant B2B product
- want to understand concurrency, retries and failure, not just avoid them
- prefer running code and tests over diagrams alone
Probably not for you if you…
- are looking for a first introduction to SQL
- need a frontend, payments or hosted-login tutorial
- want a PostgreSQL administration or tuning reference
- only work on Windows without WSL2
Get the book
Everything you need to build it yourself
A PDF designed for screen reading and the complete companion code. Runs on macOS and Linux with PostgreSQL 18, PostgREST 16 and Python 3.10+.
Complete Edition
- The 20-chapter book with 5 appendices (PDF)
- Complete source code: 18 runnable checkpoints
- Final release with 22 migrations, workers and recovery rehearsal
- Recorded verification results and measurements
- Free sample: read the preface and two full chapters first
Team Edition
- Everything in the Complete Edition
- For up to 5 people on the same team
- One purchase, one invoice
FAQ
Questions
Which versions does the book use?
PostgreSQL 18 and PostgREST 16, with Python 3.10 or newer for the worker and test runners. The code was verified with PostgreSQL 18.6 and PostgREST 16.3.
Do I need Docker, a framework or Python packages?
No. The runners and workers use only the Python standard library and the PostgreSQL client tools. Each checkpoint creates its own temporary database on local ports and removes it afterwards.
Does it work on Windows?
The code targets macOS and Linux. On Windows, use WSL2 with a Linux distribution.
Is this a beginner SQL book?
No. You should already know tables, joins and basic SQL and be able to build a simple API. PostgreSQL-specific features are introduced when the application needs them.
Does it include a frontend, payments or hosted login?
No. The book focuses on the backend and data layer. Authentication uses locally signed test tokens so you can exercise the real verification path.
Can I use the code in my own projects?
Yes. All the code from the book is released under the MIT license, so you are free to use, modify and ship it in your own projects, including commercial ones.