Car Dealership Inventory System

Overview

A coding kata turned into a complete, deployed application. Administrators add, edit, restock, and soft-delete vehicle listings; signed-in users search the catalogue and buy vehicles one unit at a time, then see their purchase history. The backend is FastAPI with PostgreSQL and Alembic migrations, the frontend is a React single-page app, and every change reaches main through a pull request that must pass lint, backend, frontend, and end-to-end checks. I built it with AI coding agents under a strict red-green-refactor workflow, so the commit history shows each failing test before the code that made it pass.

Problem

Inventory systems look like simple CRUD until two people try to buy the last car at the same moment. The kata asked for listings, search, purchase, and restock, but the interesting work was in the rules around them: stock must never go negative under concurrent requests, every stock change should be explainable later, deleting a listing must not break the history of what was sold, and the database schema has to change safely while the app is live. I also wanted to find out whether AI agents could speed up the work without weakening test-driven development.

Constraints

• Solo project built over three days, from 2026-10-24 to 2026-10-26
• Kata scope — listings model stocked offerings, not VIN-tracked cars, and prices are single-currency INR
• Tests had to come first for every behavior, with red and green commits kept visible in the history
• Free-tier hosting: a Vercel Python function and Neon serverless Postgres reached through a transaction pooler
• The demo administrator login is public, so anyone can edit the catalogue and it is reset periodically
• Deliberately deferred: refresh tokens, rate limiting, email verification, and password reset

Approach

I started with an architecture review and wrote down each choice as a short ADR — thirteen in total, covering the stack, authentication, purchases, soft delete, auditing, currency, error format, and identifiers. Each feature then began with an interview that settled its open questions (access rules, response codes, schema, concurrency) before any code. Implementation followed one red-green cycle per behavior: a failing test committed first, then the smallest change that passed, then a refactor only when it helped. Backend tests run against real PostgreSQL in Testcontainers; frontend tests use Vitest, Testing Library, and MSW; one Playwright journey exercises the whole stack. Once the core worked, I added CI, branch protection, and a deploy pipeline that applies migrations to Neon before Vercel serves the new code.

Key Decisions

1. Decision 01

   Atomic conditional UPDATE for purchases

   Reasoning

   A purchase decrements stock with a single UPDATE ... WHERE quantity > 0 AND deleted_at IS NULL, in the same transaction as the insert of a Purchase record with price, make, model, and category snapshots. I first built the naive read-then-write version and wrote a concurrency test that fires 20 buyers at 5 units — all 20 succeeded, and the test failed. The atomic version makes exactly 5 succeed and 15 receive 409, with the history showing both commits.

   Alternatives considered

   • Read the quantity, check it, then write (races under concurrent buyers)
   • SELECT ... FOR UPDATE row locking (works, but more moving parts)
   • Optimistic locking with a version column and retries

2. Decision 02

   Soft delete with audit columns and Stock Adjustment records

   Reasoning

   Deleting a listing sets deleted_at and deleted_by instead of removing the row, guarded by a check that both are set together. Past purchases still point at a real listing, and the deletion itself is attributable. Every restock also writes a Stock Adjustment row, so each supported stock change is backed by either a Purchase or an adjustment record.

   Alternatives considered

   • Hard delete (breaks the link from historical purchases)
   • A generic audit-log table (more flexible, but harder to query and test)

3. Decision 03

   Test against real PostgreSQL with Testcontainers

   Reasoning

   The rules that matter here — conditional updates, check constraints, foreign keys with ON DELETE RESTRICT, and pg_trgm similarity — only behave correctly in Postgres. Running the 160 backend tests against a disposable Postgres container meant the concurrency test was exercising the real engine rather than a stand-in.

   Alternatives considered

   • SQLite in memory (fast, but different locking and no pg_trgm)
   • Mocking the database layer (would not catch the purchase race)

4. Decision 04

   pg_trgm word similarity for as-you-type search

   Reasoning

   Separate make and model boxes meant typing "Hilux" into Make found nothing. A single search box now matches each word against make or model in any order and tolerates small typos ("toyta", "nexn"), using pg_trgm word similarity with a 0.5 cut-off. No trigram index is added yet; at catalogue sizes a sequential scan is fast, and a GIN index is the noted scaling follow-up.

   Alternatives considered

   • Plain ILIKE substring matching (no typo tolerance)
   • An external search service (too much infrastructure for the scope)

5. Decision 05

   Migrate Neon first, then deploy to Vercel, from CI only

   Reasoning

   On a push to main, CI runs all four checks, then applies Alembic migrations over Neon's direct (non-pooled) endpoint, and only then runs the production deploy. New code never meets an old schema, and a failed check or migration stops the release. The trade-off is that migrations must stay backward compatible with the code still serving during them.

   Alternatives considered

   • Vercel Git integration deploys in parallel with the migration (races)
   • Running migrations during the Vercel build (wrong endpoint, no gate)

6. Decision 06

   Rebase-only merges behind a no-bypass ruleset

   Reasoning

   Main requires a pull request and four passing checks, with an empty bypass list so the rules apply to me too. Merges are rebase-only, because a squash merge would fold each failing-test commit and its implementation into one and hide the red-green-refactor history.

   Alternatives considered

   • Squash merges (cleaner log, but loses the TDD evidence)
   • Merge commits (keeps commits, but no linear history)

Tech Stack

• Python
• FastAPI
• PostgreSQL
• SQLAlchemy
• Alembic
• pg_trgm
• React
• TypeScript
• TanStack Query
• Vitest
• MSW
• Playwright
• Testcontainers
• GitHub Actions
• Vercel
• Neon

Result & Impact

• Automated Tests 160 backend (pytest) and 61 frontend (Vitest), plus one Playwright journey
• Backend Coverage 98%
• Concurrency Check 20 buyers vs 5 units — exactly 5 succeed
• TDD History About 320 commits, 127 of them failing-test (red) commits
• Design Records 13 ADRs

This is the most complete project I have shipped end to end: a public demo, a documented architecture, and a pipeline where nothing reaches production without passing tests and a successful migration. It is still a kata — the demo catalogue is sample data and nobody buys real cars through it — but it let me practise the parts of backend work that CRUD tutorials skip: concurrency, auditability, safe schema changes, and release gates. It was also my first project built mostly through AI agents, and the strict TDD workflow is what made that trustworthy: every generated change had to turn a specific failing test green, and an independent review agent caught a real security flaw in code the implementing agent had just written.

Learnings

1. A concurrency bug is easiest to believe when a test proves it first — the failing 20-vs-5 test made the case for the atomic UPDATE better than any explanation
2. Testing against the real database engine catches behavior that mocks and SQLite would hide
3. Deploy order is a design decision: migrating before deploying forces backward-compatible migrations, which is a good constraint to learn early
4. Writing decisions down as short ADRs made later features faster, because each interview started from what was already settled
5. AI agents are fast at writing code but need a tight loop — one failing test, the smallest fix, my review — to stay honest
6. A second agent reviewing the code without seeing the original reasoning found problems the first pass had argued itself into