Skip to main content
The deep technical decisions behind GreekCore v2 — how tenant isolation is enforced at the database level, how users are provisioned before a tenant context exists, how chat stays in sync, and how the whole local stack boots from one command.

​
1. Multi-tenant RLS & the two-role Postgres split

Tenant isolation is enforced directly inside Postgres, so user chapters cannot leak data to other organizations under any circumstance. The Neon database is split into two connection roles:

Owner role

DATABASE_URL_DIRECT — bypasses RLS. Restricted to schema migrations, dev seeds, and specific auth bootstrap routines.

Runtime role

DATABASE_URL — RLS enabled globally on every tenant-scoped table.
Every query under the runtime role runs inside a transaction opened by a withChapter or withIdentity wrapper. These wrappers set transaction-local GUC (Grand Unified Configuration) variables using set_config(..., true):
  • app.user_id
  • app.chapter_id
  • app.is_staff
  • app.guest_link_id
Because the third argument to set_config is true (the LOCAL scope), the values are discarded the moment the transaction commits or rolls back — preventing session contamination under Neon’s PgBouncer transaction pooling. RLS policies read these settings through stable SQL functions (current_chapter_id(), current_user_id()) to run join-free filters.
Reference: api/packages/db/src/context.ts wraps the GUC transaction scoping; api/packages/db/src/schema/identity.ts defines the tenant-scoped tables referencing chapters.id.

​
2. Owner-bypassing bootstrap path & advisory locking

Some actions must run before a tenant context exists: just-in-time user provisioning from Clerk, active-membership assertion, and invite-code redemption.
1

JIT user provisioning

On a user’s first request, resolveUser provisions or reconciles their local record from Clerk’s profile under the RLS-bypassing owner connection.
2

Serialized invite redemption

Redeeming an invite code must be serialized to avoid double-redemptions and races where multiple users claim to be the chapter “founder” (the first active member, who automatically receives the exec role).
3

Row lock + advisory lock

The routine locks the invite_codes row with FOR UPDATE and takes a transaction-scoped advisory lock: pg_advisory_xact_lock(hashtext(chapter_id), hashtext('chapter_founder')).
4

Single super-admin guarantee

It then performs a member count and inserts the new member, ensuring exactly one super admin is established per chapter.
Reference: redeemInviteCode in api/packages/db/src/identity.ts.

​
3. Stream Chat synchronization & the memory-lock queue

The platform auto-provisions two Stream channels per chapter — chapter-<id> and chapter-<id>-exec — and keeps memberships and officer roles in sync. The ChatSyncService watches membership transitions (joins, role changes, deactivations) and reconciles them against the Stream API. To prevent races — for example, a chat-open action that verifies an exec role racing against an active demotion hook and accidentally re-adding a demoted user to the exec channel — reconciliations are serialized per user/chapter using an in-memory Promise-chain queue (locks.get(key)).
If a Stream connection fails, the service logs a warning but still lets the database transaction commit — a best-effort, decoupled design that never blocks the core write path.
Reference: api/apps/api/src/communications/chat-sync.service.ts.

​
4. Cross-cutting dev tooling & process composition

The workspace root unifies setup and local execution of the independent child repos under one configuration. mise manages pinned SDK runtimes (Node, pnpm, OpenTofu, Docker, Fastify) and orchestrates tasks:
A DAG enforces ordering: the local database container is health-checked, migrations and seeds run, packages (@greekcore/db, @greekcore/ui, @greekcore/auth-web) compile in order, and then the API and both web apps launch simultaneously.
Reference: mise.toml maps the front-door tasks; process-compose.yaml controls service dependencies.

​
The hardest problem

Correct tenant context under PgBouncer transaction pooling. Because physical connections are shared across HTTP requests, session-level variables (SET app.chapter_id = ...) could leak between tenants. The fix has two parts:
  1. LOCAL GUC scopes restrict each variable’s lifespan to its own transaction block.
  2. Disabling prepared statements (prepare: false on the Postgres client) prevents prepared-query variables from bleeding into other pooled sessions.

​
Primary entry points

Last modified on June 22, 2026