Zoth's Postgres-only decision and the Neo4j removal

This is the story to tell when an interviewer asks "tell me about a technical decision you regret / reversed." It has everything: an initial reasonable choice, a measured realization, a clean migration, and a principle extracted.

The original design

Zoth's domain looks graph-shaped: users belong to groups, users report to managers (a tree), users hold access to resources, policies match on groups and resources. Phase 1 (visible in stale docs/CURRENT_STATE.md and docs/GUIDE.md) used Neo4j alongside Postgres, with GORM for relational data. The org hierarchy lived in graph edges.

Why it felt right: approval routing walks the management chain ("approve by the requester's manager, else their manager's manager…"). That sounds like a graph traversal.

Why it was wrong

1. Dual-write problem. Identity data arrives from WorkOS sync (users, groups, memberships). With Neo4j, every sync writes to Postgres and the graph — and when one write fails you get silent divergence. Your INDEX.md now states the principle explicitly: "No graph database, no dual-write."
2. The traversals are trivial. Zoth needs exactly two "graph" operations: manager chain (a path up a tree) and group membership (a join table). Both are textbook SQL:
   • users.manager_id self-referential FK (ON DELETE SET NULL)
   • user_groups(user_id, group_id) join table with composite PK
   • "active access" is just access_requests WHERE status = 'ACTIVE'
3. Ops cost. Neo4j is another stateful service — for a $30/month cluster running on one node, a second database is pure overhead.

The replacement, in code

Migration 002_drop_neo4j.sql (in migrations/postgres/) adds the relational structures with COMMENTs documenting the replacement. The manager chain became a recursive CTE in internal/db/sqlc/queries.sql:

WITH RECURSIVE manager_chain AS (
  SELECT u.*, 1 AS level FROM users u WHERE u.id = $1
  UNION ALL
  SELECT u.*, mc.level + 1 FROM users u
  INNER JOIN manager_chain mc ON u.id = mc.manager_id
  WHERE mc.manager_id IS NOT NULL AND mc.level < 25
)
SELECT * FROM manager_chain WHERE level > 1 ORDER BY level ASC;

Details worth knowing cold:

• level < 25 is a cycle guard. IdP manager data can contain cycles (A→B→A). Without a cap, a recursive CTE loops forever. The cap bounds worst case at 25 rows scanned.
• approval.ResolveApprovers returns this chain; RouteApproval assigns the first (closest) manager as the pending approver.
• If the chain is empty (no manager on file), the request is UNROUTED — anyone in the tenant may decide, and the decision is audited with authority: "UNROUTED". You should have an opinion about this: it's a fail-open choice for availability of the workflow; a stricter design would fail-closed (park the request until an admin routes it). Interviewers may poke exactly there.

The general principle

Model your queries, not your entities. "It's a graph" is a statement about the shape of data; the engineering question is the shape of access paths. Neo4j earns its keep when traversal is multi-hop, variable-depth, and ad-hoc (friend-of-friend recommendations, fraud rings). Zoth's access path is one fixed 1–25-hop walk up a tree, executed rarely, on a small table — a B-tree index on manager_id (idx_users_manager_id) serves it in microseconds.

Same logic, other domains, for practice: Discord switched trillions of messages to Cassandra then back to ScyllaDB for ops reasons; Instagram's first architecture ran everything on Postgres until scale forced changes. The lesson is identical: right-size the database to the query, and prefer one source of truth to two.

Also note what zoth did not remove: pgvector. policy_documents.embedding vector(768) is a specialized index inside the same Postgres — the RAG layer got a "polyglot" capability without a second database. That's the correct way to add a second data model: as an extension of the system of record, not as a peer system.