The Geofence Ledger

Actual Refind is a location-locked, AI-verified lost-and-found network. A public listing that describes an item accurately is a gift to a scammer — anyone reading “black Herschel backpack, red zipper pull” can now claim it. So Refind inverts the listing: the item’s details are the secret, extracted by GPT-4o Vision and never shown, and a seeker proves ownership by describing them while physically standing within 150 m of where the item was found. A passing score proposes a match; the finder confirms it; contact details are revealed. Both trust mechanisms — the geofence and the blind claim — live in the database. The question this study asks is whether the schema can keep the promise the product makes.

What makes the data hard to model is not volume — six tables with clean foreign keys is a simple schema. It is that the central object is not a record but a process: an adversarial, multi-step verification funnel where each step is a trust decision that can be wrong, retried, or attacked.

• The data is evidence, not state. “This seeker scored 0.91” is not a current-value fact like an email address. Its value is entirely in when and how it was produced.
• The adversary is a user in good standing — valid JWT, standing in the right place. Every defence has to live in the data model, because authentication has already been passed.
• One item, many competing claimants. A scarce resource contested by mutually-exclusive parties: a concurrency problem wearing a product costume.
• Secrecy is a per-column property. One items row holds both the public face and the answer key the public face exists to protect.

The fixed constraints, from the code: PostgreSQL + PostGIS is settled (and correct); identity is the Supabase JWT sub, used verbatim as users.id; the 150 m radius is server-authoritative; reads are polled (8 s map, 4 s chat), so load scales with session duration, not actions; and every write costs an AI inference — seconds long, metered in dollars. One constraint is a gift: the radius means the hottest query can never return a large result set, no matter how big the product grows. And one is absolute: a wrong connection is unrecoverable in the physical world, so the match decision needs strict consistency.

The repository ships its own design document arguing why the data is relational and why PostGIS is the right geospatial engine. I agree with all of it, and this study does not relitigate it. But that document argues the choice of engine; the problem I found is in the choice of representation, one level below. Reading the models and migrations first suggested a well-considered schema. Reading the services — where the queries live — told a different story.

The schema as it exists today — every table, column, and index — is documented in the DB design view. Its conceptual model is worth restating, because one absence drives everything below: a Person creates an Item holding a Secret behind a Disguise; another Person standing at the right Place attempts a Proof against the Secret. And Proof — the entity the whole product turns on — has no table. It exists only as the current contents of a mutable Claim row.

I traced all sixteen operations through the routers into the services. Six of them carry the story; the rest are routine reads (three of which are N+1 loops, all fixable by joins).

The core problem

Actual In claim_service.submit_claim, a resubmission does not fail and does not create a second row. It reuses the existing one:

claim = (await session.execute(
    select(Claim).where(Claim.item_id == item.id,
                        Claim.seeker_id == seeker_id)
)).scalar_one_or_none()

result = await scoring_service.score_answers(item.private_details, answers)
...
claim.answers  = answers          # ← previous attempt destroyed
claim.ai_score = score            # ← previous score destroyed
claim.status = CLAIM_AI_PASSED if passed else CLAIM_AI_FAILED

Actual A failed claim leaves the item active, and nothing else gates the endpoint. MAX_CLAIM_ATTEMPTS_PER_DAY = 5 is declared in config.py and — I grepped the entire backend and test suite — referenced nowhere else. It is a dead setting.

Inferred The consequences compound. A seeker can submit, read their score, adjust, and resubmit without limit — each response returns ai_score, a numeric gradient pointing at the secret. Guessing colours is a twelve-try exhaustive search with a scoreboard. And after a successful attempt, the row shows one clean passing set of answers: the nine wrong guesses that preceded it left no trace, so a finder reviewing a 0.91 cannot distinguish a genuine owner from someone who ground it out — and neither can a fraud investigation, later, with full database access. The unique constraint guarantees one row where it was intended to guarantee one attempt. The claims row conflates two entities — “this seeker’s relationship to this item” and “this specific attempt at proving ownership” — and lets the second overwrite itself inside the first. That is a data-modelling defect, not a missing feature: a rate limiter would slow the attack but cannot restore evidence that was never written down.

Two more load-bearing problems

Additional findings

Two independent axes: how to represent the verification funnel (where the core problem lives), and how to index position (where the read volume lives). They compose freely.

A · Mutable status columns — the current design

One row per (item, seeker); attempt data in mutable columns. Simple, fast, cheap, and structurally incapable of answering “how many times did this person try, and with what?” — the question a fraud review, a rate limiter, and a dispute all need. History costs nothing because history is discarded.

B · Append-only attempt log beside a mutable summary

Split claims in two: claim_attempts is immutable — one row per submission with answers, score, model version, GPS, timestamp — while claims becomes a thin per-(item, seeker) summary pointing at the winning attempt. Current state stays a single-row read; rate limiting becomes a real query instead of an impossible one; the fast path stays fast. Cost: two places to write, kept honest in one transaction.

C · Full event sourcing with derived projections

One events table as the only authority; everything else a projection. Total auditability — and the wrong shape here. Projections are eventually consistent, and Refind has a read that cannot be: the confirm path. It also turns the PostGIS index, the most valuable structure in the system, into a derived artifact to rebuild rather than a primary one to query, and imposes projection maintenance on a small team. The cost is paid on every read; the benefit accrues on rare audits.

D · Document-oriented aggregate

One document per item, embedding questions, claims, attempts, match. The finder’s review becomes one read — a read that happens roughly once per recovery — while the two reads Refind performs most, “items near me” and “my claims,” become scatter-gathers. It concentrates writes on popular items (precisely the contended ones) and forfeits the geography type. Optimizing the rarest read at the expense of the most frequent one.

E · Hybrid — B, finished

The attempt ledger, plus: enums and CHECK constraints on every status; partial unique indexes that make the illegal states unrepresentable; explicit row locking on the two contended transitions; and the secret split into its own table so the privacy boundary is a join you must write deliberately — later, a GRANT — rather than a code convention. Every piece is independently shippable behind its own migration.

The geospatial axis

The current index is a plain GIST over geography — correct at every latitude, but spanning all items ever reported while the query only wants active ones. A partial GIST (WHERE status = 'active') is a one-line migration: identical correctness, index proportional to what is on the map, entries removed by resolution itself. The rejected options all trade exactness on a boundary that is a security control: geohash/H3 cells have edge artifacts; projected SRIDs distort somewhere (and most of that win is free by passing use_spheroid := false — sub-metre error on a radius the UI rounds to 0.1 m); a cache tier adds staleness and infrastructure, worth holding in reserve.

Proposed E plus the partial spatial index: keep the relational PostGIS core exactly where it is, add an append-only attempt ledger, move the illegal states from “the code tries not to” into “the database will not,” and make the GIST index partial on status = 'active'. Explicitly not recommended: changing engine, event sourcing, or documents.

1. The core problem is about what is stored, not where. The funnel loses its evidence because attempts overwrite each other. That is fixed by adding an immutable row per attempt — an entity the model is missing. No engine change addresses it, and none is needed to.
2. The one decision that must never be wrong needs a real lock, not a better paradigm. Confirming a match is irreversible: a stranger is told where to meet someone and given their email. SELECT … FOR UPDATE on the item row, plus a partial unique index permitting at most one live match per item, makes the double-confirm structurally impossible. Event sourcing’s eventual consistency is the wrong shape for exactly this.
3. The geofence is the app’s best asset and PostGIS is what makes it exact. A 150 m security boundary must not have false positives at a cell edge or projection error at high latitude. The partial index makes the hot query strictly faster at every scale, and keeps paying, because the ratio of resolved to active items only grows.
4. Constraints outlive the code that respects them. Today’s privacy guarantee is “no response model happens to include private_details” — a convention, one hurried pull request from failing. A separate item_secrets table turns it into structure.
5. Every step is independently shippable. The partial index is one migration with no code change; the ledger is one migration and one rewritten service; the constraints are a third. A small team lands these one at a time. C and D require rewriting the application before any value arrives.

Why not the others, in one breath. Not A: the current design is not merely unoptimized — it destroys information at write time, and every day of operation destroys more. Not B alone: it fixes the evidence and leaves the race, the orphans, and the convention-only privacy boundary in place; B is the right core, E is B finished. Not C: it trades the strong guarantee the product needs for total auditability it can get from one append-only table. Not D: it optimizes a once-per-recovery read at the cost of the 7.5-per-minute one.

Everything here is Proposed. Eight tables — two new (item_secrets, claim_attempts), none removed. Enums replace bare status strings, every FK declares deletion behaviour, composite indexes match the real query predicates, and keys move to gen_random_uuid().

Three constraints do the heavy lifting:

CREATE UNIQUE INDEX uq_one_live_match_per_item ON matches (item_id)
  WHERE status IN ('pending_finder','confirmed');

ALTER TABLE matches ADD CONSTRAINT uq_match_claim UNIQUE (claim_id);

CREATE UNIQUE INDEX uq_attempt_no ON claim_attempts (claim_id, attempt_no);

uq_one_live_match_per_item is the keystone. Three lines, and the entire class of concurrency bugs — competing passing claims, orphaned siblings, double confirmation — becomes impossible to represent. The second concurrent match is not created and cleaned up; it is refused at write time by the database.

The corrected write path

# submit_claim, proposed. AI call happens BEFORE the transaction opens.
score = await score_answers(...)          # network, seconds, no DB held

async with session.begin():
    item = await session.execute(
        select(Item).where(Item.id == item_id)
        .with_for_update()                # serialize contenders on this item
    )
    if item.status != 'active':
        raise Conflict("This item is no longer available.")

    claim = upsert_claim(item_id, seeker_id)
    if claim.attempt_count >= MAX_CLAIM_ATTEMPTS_PER_DAY:
        raise TooManyRequests(...)        # the dead setting, finally enforced

    attempt = ClaimAttempt(               # append — never an update
        claim_id=claim.id,
        attempt_no=claim.attempt_count + 1,
        answers=answers, ai_score=score,
        threshold_at_time=settings.claim_score_threshold,
        claimed_lat=lat, claimed_lng=lng,
    )
    claim.attempt_count += 1

    if passed:
        match = Match(item_id=item.id, claim_id=claim.id,
                      winning_attempt_id=attempt.id, ...)
        item.status = 'claim_pending'     # uq_one_live_match_per_item
                                          # refuses any second live match

Three structural changes in that block: the AI call is outside the transaction, the item row is locked so contenders serialize, and the attempt is appended rather than overwritten. The lock and the partial index are belt and braces — the lock makes the common case orderly; the index makes the bad state impossible even if a future code path forgets the lock.

The brute-force attack becomes visible, then preventable: every guess is a row, the count is on the summary, and the dead limit is finally enforceable. A finder sees “passed on attempt 12 after 11 failures in 4 minutes” — the signature that is currently invisible. Double-confirmation becomes unrepresentable rather than cleaned up. The N+1 loops collapse into joins that are flat in a user’s history. The spatial index tracks the live map instead of all history, so it gets smaller as items resolve. And the privacy boundary changes its failure mode: leaking the answer key stops being “forgot to exclude a field” — silent, invisible in review — and becomes “wrote a join with no reason to exist,” which is neither.

Growth

Inferred Refind’s load shape is unusual and favourable. Reads dominate and are structurally bounded — the geofence caps the hottest query’s working set to one 150 m circle with LIMIT 100, permanently, no matter how large the product grows. Writes are rare but individually enormous: each spans an AI inference, so the right unit is connection-seconds, not rows. The real risk is geographic, not global — a busy transit station concentrates items, claimants, and lock contention into one circle.

Three bottlenecks arrive in order, and none requires revisiting the data model: the N+1-over-unindexed-scan pattern (fixed outright by the new indexes and joins), connection-pool exhaustion on report bursts (fixed by moving inference off the transaction, then off the request path entirely), and spatial index growth (fixed by the partial index; beyond that, read replicas suit a poll that is already 8 seconds stale by design). If sharding is ever needed, the data partitions perfectly by region — no query ever spans more than 150 m.

What I would measure before believing any of it — this study ran nothing, so the projections above are reasoning, not numbers: p95 latency of the nearby query against active-item count; connection hold-time distribution on the report path; the attempts-per-claim distribution (the abuse signal itself); partial-index size against the active set over time; and pool saturation under a simulated report burst.

What I would change later, on a clear trigger

• Partition claim_attempts by month once the ledger reaches tens of millions of rows — append-only and time-ordered, the ideal candidate.
• Replace polling with push when read volume becomes the constraint: today’s load scales with session duration; push scales with things actually happening. Most polls return nothing.
• Asynchronous report processing (status='processing', pin appears when extraction completes) — which also decouples an OpenAI outage from the ability to report a find.
• UUIDv7 keys if insert throughput ever matters: time-ordered keys pack B-trees, and keep the unguessability that matters here.

Engineering lessons

1. A constraint guarantees what it says, not what you meant. UNIQUE(item_id, seeker_id) was documented as “one claim per seeker per item” and understood as “one attempt.” It delivered the first faithfully while the code quietly used it to overwrite the second. When a constraint is load-bearing, verify what the surrounding code does when it would fire.
2. Store observations, derive conclusions. Almost every problem here traces to storing a verdict where an event belonged. Verdicts can be recomputed from events; events cannot be recovered from verdicts.
3. Constraints outlive the code that respects them. “No response model includes private_details” is true today and one hurried pull request from being false. A table boundary or a unique index states the invariant somewhere that does not depend on the next engineer remembering it.
4. Read the queries, not just the schema. The models and migrations look sound in isolation; every significant problem in this study was found in the service layer. A schema is a hypothesis about access patterns — only the queries reveal whether it was right.
5. A product constraint can be an engineering asset. The 150 m geofence was chosen to stop scammers browsing from their couch. It also permanently bounds the hottest query’s working set and makes the data shard cleanly by geography.
6. Measure writes in the right unit. Refind’s writes are trivial by row count and severe by connection-seconds, because each spans an AI inference. Rows per second is the wrong metric for any system whose transactions contain a network call.