Skip to content
Open the app

The data model

Developers3 min read

You don't query the database directly, but understanding its shape makes every integration — roster sync, SSO, branding — make sense. Here's the model in the few concepts that matter.

Tenant: the institution

An institution is the tenant root. It has a stable slug (the identity the apps resolve by), a name, a timezone, a residency, a brand configuration, and an SSO configuration. Everything tenant-owned carries an institution_id.

Resolution is by slug, not hostname

Tenancy is resolved from the slug, not the domain — which is why a custom subdomain is an optional convenience rather than a prerequisite. See custom subdomains.

The actor model

A signed-in identity maps to exactly one actor — a student or a staff record — per institution:

  • Each actor row has an auth_user_id (who signed in) and an institution_id (which tenant they belong to).
  • SSO links to an existing actor; it never creates one. A first-time sign-in attaches to the student or staff record already present from the roster. The consequence: the roster has to exist before sign-in works.
  • Staff additionally carry a role (institution_admin, content_editor, support_responder) and a scope (which courses, programs, campuses, or cohorts they cover).

The teaching shape: cohorts and weeks

  • A cohort is an intake (a term) belonging to an institution, with a week count and an intake start; it can be archived.
  • A week belongs to a cohort and carries the dates and labels everything anchors to.
  • A student belongs to a cohort; program and campus describe where they sit.
  • Content — events, week blocks, to-dos — hangs off weeks and is targeted by audience. Wellbeing check-ins and help requests belong to students.

The perimeter: row-level security

Isolation is enforced in the database, not in app code. Every tenant-owned row carries its institution_id, and row-level securityRow-level security: Postgres policies that filter which rows a session may read, enforced by the database itself. policies key off it. A handful of server-only paths use a service-role credential that bypasses RLS (roster import is one) and must re-impose tenant scoping by hand — they're treated as the highest-risk code in the system. The institution-facing account is data residency and tenant isolation.

Common questions

Can I get direct database or SQL access?

No. Integration is through the defined endpoints (roster sync, SSO). Direct database access would bypass the tenant perimeter, which is exactly what the model is built to prevent.

What's the difference between auth_user_id and the actor?

auth_user_id is the authenticated identity from sign-in; the actor (student or staff) is the First Six record it links to within an institution. One identity maps to one actor per tenant.

How are two apps consistent if they're separate?

They're separate front ends over the same database — see the two apps. Consistency comes from sharing one source of truth, not from syncing two stores.

The two appsHow First Six is structured — the student app, the staff console, and the shared Supabase backend with row-level security.The OIDC flowHow First Six does single sign-on — the OIDC authorization-code flow, per-tenant config, and how a login links to a student or staff record.Data residency and tenant isolationWhere First Six data is hosted, how it's encrypted, how one institution's data is isolated from another, and the breach-response posture.
Was this helpful?
Need more help?

The fastest answer is usually one question away.

Edit this page on GitHub