Build log: mapping every record store in Turkey.

We promised in issue 02 a build log from inside Plakla, our directory and reservation platform for Turkey’s independent record stores. Here it is. This one is for the engineers; if you do not care how a store locator works, issue 04 will be kinder to you.

The shape of the product: every record shop in Turkey on one map, with live inventory and a reservation flow, so a collector in Ankara can find a pressing in Istanbul and have it held before making the trip. Next.js 15, React 19, PostgreSQL with PostGIS, Drizzle ORM. Eighty-one cities seeded; three active at launch. One pilot store with about 2,300 products already flowing through it.

Geography, not geometry

Most PostGIS tutorials reach for the geometry type because it is fast and the demos are flat. We use geography(Point, 4326) instead. Geometry measures Euclidean distance on a plane; geography measures great-circle distance in meters on the actual Earth. A 5 km radius in Istanbul is not the same flat circle as one in Ankara — latitude bends it. For a country-wide directory, geography is the semantically honest choice, and the small query cost is the price of not lying.

Drizzle has no native PostGIS support, so the column is a custom type and the spatial index is declared alongside it:

const geography = customType<{ data: string }>({
  dataType: () => 'geography(Point, 4326)',
});

export const stores = pgTable('stores', {
  id: uuid('id').primaryKey().defaultRandom(),
  slug: text('slug').notNull().unique(),
  displayName: text('display_name').notNull(),
  geo: geography('geo'),
  // …address, opening hours, status
}, (t) => ({
  geoGistIdx: index('stores_geo_gist_idx').using('gist', t.geo),
}));

The GIST index is not optional. Without it, every “stores near me” request walks the whole table.

The nearby query

The query behind the map is raw SQL through Drizzle’s sql tag — typed at the edges, honest in the middle. Two PostGIS functions do the work: ST_DWithin for the index-friendly radius filter, ST_Distance for the actual meters.

const point = sql`ST_SetSRID(ST_MakePoint(${lng}, ${lat}), 4326)::geography`;

const rows = await db.execute(sql`
  select
    stores.id,
    stores.slug,
    ST_X(stores.geo::geometry) as lng,
    ST_Y(stores.geo::geometry) as lat,
    ST_Distance(stores.geo, ${point}) as distance_m
  from stores
  where ST_DWithin(stores.geo, ${point}, ${radiusM})
    and stores.status in ('active', 'paused')
  order by distance_m asc, stores.id asc
  limit ${limit}
`);

Pagination is keyset, not offset — the cursor is the tuple (distance_m, store_id), compared lexicographically. “Give me the next fifty stores after 3,200 meters” never re-scans the rows before it, which is the difference between a map that scrolls and a map that times out.

One war story: drizzle-kit occasionally quotes the custom type in generated migrations — "geography(Point, 4326)" — which Postgres rejects. We hand-edit the SQL when it happens. Every abstraction has a seam; this is Drizzle’s, and we would still pick it again.

Tenancy lives in the database

Plakla is multi-tenant: a store is a tenant, users hold memberships with roles (one owner, then managers and staff). The usual approach is to check membership in application code on every query and hope nobody forgets one.

We pushed the boundary into Postgres instead. Row-level security policies decide what each connection can see; the application authenticates the user and sets one session variable.

CREATE POLICY stores_public_read ON stores
  FOR SELECT USING (status IN ('active', 'paused'));

CREATE POLICY stores_member_write ON stores
  FOR ALL
  USING (id IN (SELECT app_user_store_ids()))
  WITH CHECK (id IN (SELECT app_user_store_ids()));

app_user_store_ids() reads app.current_user_id from the session, which the request handler sets through an AsyncLocalStorage bridge. The runtime database role cannot bypass RLS; a separate BYPASSRLS role exists only for the auth adapter and migrations, and the runtime role has its grants on the token tables revoked outright.

The honest trade-off: RLS policies are SQL files in the repo, and debugging a policy that silently filters your rows is character-building. But a forgotten where clause in application code can no longer leak one shop’s inventory to another. We sleep better owning that guarantee at the database layer.

A reservation is a state machine

The reservation flow is six states — pending → confirmed → fulfilled, with rejected, expired, and cancelled as exits. Each transition has its own timestamp column, pending holds expire after 24 hours and confirmed ones after 48, and a cron sweeps expiries every five minutes. When a listing sells elsewhere, every open reservation on it is auto-rejected with a reason the customer actually reads — already sold, not error code 409.

Every transition also writes to an append-only events table with the actor attached. The activity feed, the support view, and the “what happened to my reservation” email are all just reads of that log. We did not invent event sourcing; we borrowed the cheap half of it.

Worth stealing

If you are building any kind of directory on Next.js and Postgres:

• Use geography, accept the cost, and never skip the GIST index.
• Let Drizzle type your CRUD and drop to raw SQL for spatial work without guilt.
• Keyset pagination on (distance, id) — offset pagination on a map is a trap.
• If tenants share a database, consider making Postgres enforce the boundary. RLS is older, duller, and more reliable than your middleware.
• Model state machines as columns-per-transition plus an event log. Your future support inbox will thank you.

Next month

Issue 04: why Flux Money has no server — local-first architecture on Apple platforms, and what “the developer collects no data” costs in engineering terms.

— Eren, Furkan, Berk, Onur