Building MapChat · Part 2 of 4 · Architecture case study
How we built MapChat's backend: one Postgres for an app and a venue CRM
- The problem. The first version ran on a no-code platform whose backend couldn't carry the product, and no licensed venue or event feed existed to fill the app.
- My role. I designed the replacement, from the schema and access rules to the data flows and the contract with the venue CRM team, and built it with AI coding agents under the process in Part 3.
- What happened. The backend was replaced in two days while the old web app stayed live. One Postgres now serves the app and the CRM, and the catalog grew from 1,925 to 17,000+ venues with map and feed loading in under a second.
Reversing my own call
The native rebuild started on February 23, 2026, and the plan I made the next day was to keep Bubble.io, the no-code platform behind the first version, as the backend. Five weeks of driving its API from the new app showed where it broke. Every call was capped at 50 records. Day queries were rounded in UTC, so the app once showed 16 events where Bubble held 156. List fields silently dropped empty values. Distance math was wrong at high latitudes and couldn't use an index. And every endpoint change had to be written up as click-by-click instructions for a visual editor.
On April 1 I reversed the decision. The new backend, on Supabase and PostgreSQL, landed in two days. It was rewritten service by service (auth, profiles, venues and events, check-ins, waves, then chat) while the Bubble web app stayed live. Each limit became a design rule.
- What broke on BubbleThe rule that replaced it
- List fields dropped empty values→Junction tables, never arrays
- Distance math wrong and unindexed→PostGIS geography with a spatial index
- A new venue attribute meant a new column→Tags stored as rows, so adding one is an insert
- Day queries rounded in UTC→An IANA time zone on every venue, event and special
- Endpoints edited by clicking→Versioned SQL migrations and typed functions in git
The architecture
Two products share one database. The app is React Native on Expo, one codebase for iOS and Android. The venue CRM is a separate product that our founder's team builds and runs. Supabase provides Postgres with PostGIS, sign-in, realtime updates, storage, scheduled jobs and Edge Functions.
Two rules decide where logic lives. Reads and multi-step writes are Postgres functions by default, so they run inside the same access rules as everything else. Edge Functions are the exception, kept for work that has to reach the outside world, such as email, SMS, push, image processing, account deletion, the recurrence engine, the AI bio writer and the link to the games server. They sit outside the database's safety net and start slower, so each one has to earn its place.
Rules that live in the schema
I wanted the database itself to enforce the product's rules, so that no client, agent or future feature could forget them.
- Access is structural. Row-level security is the primary access layer, on every table. In our docs the rule is that “if data should not be accessible, the policy must prevent it structurally, not the client code.” Sensitive writes go only through server-side functions.
- Configuration is data. Venue attributes are tag rows, not 200+ boolean columns. Conversations live in one table with a kind, so a new type of conversation is an enum value, not a new subsystem.
- History can't be skipped. The audit log is written by database triggers, because logging in application code can be bypassed.
- Migrations are append-only. A committed migration is never edited, and any fix is a new one. Each starts from a failing integration test, is checked by a probe that runs as the real database role, gets an independent review, and is applied by a single applier, never by the agent that wrote it.
The schema has grown to 160 tables, with row-level security on every one of them.
One database, two products
The CRM team owns the catalog of venues, events and specials, and we own the social product. On April 28 we locked a single-database design. The CRM writes the catalog, the app reads it, and social data belongs to the app, with the split enforced by row-level security. CRM-only tables sit in their own schema that the app never sees. A catalog row reaches phones only when a database trigger marks it visible, and that flag cascades from a venue to its events and specials.
The two teams' coding agents worked from a written contract. Every request was a handoff packet with its decisions confirmed (“do not re-litigate”), a verification gate, and a clear boundary, since our side never pushed code to theirs. I defined the venue data dictionary the catalogers work against. Before approving the CRM's cutover to the shared database, we re-ran its 12 acceptance checks independently, and all 12 passed.
The existing catalog moved in through an all-SQL migration in nine phases, ordered by foreign keys, safe to re-run and gated on a 50-venue dry run. It loaded 1,925 venues and 40,260 related rows in about 11 minutes, with a 100% row match. Production moved to the new schema on May 4 with 166K rows loaded and every user account preserved.
Supply with no licensed feed
A venue app with no venues is dead on arrival, and there was no licensed venue or event feed to buy. The CRM team built the catalog instead. Venues are seeded from Google Places and state liquor-license lists, then enriched by agents that browse venue websites, Instagram and Yelp the way a person would. I designed the scraping and enrichment harnesses those agents run, through written handoffs that the CRM's coding agent carried out. An LLM classifies each find with a plain test (“Is there something happening beyond the food/drink deal itself? If not, it's NOT an event”), and a recurring event is admitted only when its schedule is about 80% confirmed.
Two pieces on our side made that supply usable. The recurrence engine stores repeating events and specials as standard calendar rules (RFC 5545) and turns them into dated rows every day, so reading a night's events is one indexed range scan and each night can carry its own attendees and changes. Before we switched to that engine in May, an audit of all 13,477 production rules found none malformed and recovered 178 series that had never produced a date. The old path stayed disabled as a rollback for nine days.
The second piece opens cities on its own. You count as in-market when at least 3 published venues sit within 35 miles, checked by a query that takes about 12 ms instead of a count that took over a second, and the check fails open. New metros go live with no app release.
The catalog now covers markets from Los Angeles and Washington, DC, to New York, Boston and Dallas. The growth is the CRM team's work, on a platform we built.
The one AI feature, built to a cost and safety bar
The first version already had an AI bio writer, built on the no-code platform. We kept the feature and rebuilt it as an Edge Function calling GPT-4o. A model call costs real money, so the chain fails closed at the cheapest possible point. A kill switch in the app's config comes first, then a signed-in check at the edge, a moderation check on every field the user wrote, and a rate limit checked before any token is spent. Generate mode returns three candidates with suggested tweaks, refine mode returns one, and bios are capped at 250 characters.
The launch flag stayed off until a 9-case deterministic test and a 10-case human-graded eval passed against the deployed function. The human review changed the prompt the same day, adding a ban on exclamation marks, a guard against generic openers, a rule against inventing details in suggested tweaks, and an instruction to keep the user's own voice. In production it has handled 59 of 59 calls successfully, at about $0.003 a call and a 4-second median.
Fast on a small database
The database runs on a small instance with a 256 MB cache, and the CRM's scrapers rewrite event rows all day. Guessing at performance fixes wasn't safe, so I made a performance lab the standing gate. It's a local Docker replica of production, seeded at production scale and queried as the app's real role rather than an admin, and the lab's query plan has to match production's before a change ships. Since June 19, every database performance change has gone through it.
When venue-feed loads started failing now and then, production statistics traced the cause to cache residency, a working set of roughly 442 MB against that 256 MB cache, rather than to query cost. The fix cut the data each query touches, so the feed's first page now does about 63 times less work. It was measured in the lab first, production matched the lab's plan within 1.5%, and feed errors went from 63 in two weeks to none in the next 42 calls. A timeout increase applied along the way was labeled, in its own migration, as a mitigation and not a fix. Map and feed now load in under a second on a cold launch and in about 200 ms warm, while the catalog has grown ninefold.