Platform

Identity, tenant catalog, membership, invitations, and API tokens. Lives in the platform schema (no org_id filter applies). Consumed by the user, organization, membership, invitation, and API-token workflows.

Tables owned

Table	Purpose
platform.users	One row per Firebase identity. Profile fields only.
platform.organizations	Tenant catalog. Identity-only (name, slug).
platform.organization_users	Membership rows. Role lives here.
platform.user_invitations	Pending / accepted / declined / revoked invitations.
platform.api_tokens	Personal Access Tokens (PATs). Hash + prefix only; never the plaintext.

Operations

Every op takes a conn as its first parameter and explicit ids — Platform does not use the app.org_id GUC.

Users

• createUser(conn, { firebaseUid, email, displayName? }) → User. Inserts the platform user record after Firebase sign-up.
• updateUser(conn, userId, patch) → User. Edits display name / profile fields. Empty patch returns the row unchanged.
• deleteUser(conn, userId) → void. Hard-deletes the user row; FKs cascade memberships, API tokens, and invitations sent. L3 (W004) purges solely-owned orgs and the Firebase identity around it.
• touchUserLastLogin(conn, userId) → void. Stamps last_login_at = NOW(). Called from W002 (GET /user) on every authenticated hit.
• getUserById(conn, userId) → User | null.
• getUserByFirebaseUid(conn, firebaseUid) → User | null. Looked up on every authenticated request.
• userExistsByEmailInOrg(conn, email, orgId) → boolean. True iff there is an active membership in orgId whose user has this email. Used by W020 to block invites to existing members.

Organizations

• createOrganization(conn, { name, slug }) → Organization. Creates a tenant catalog row.
• updateOrganization(conn, orgId, patch) → Organization. Edits name / slug.
• deleteOrganization(conn, orgId) → void. Hard-deletes the org row (FKs are ON DELETE RESTRICT; the L3 purge clears tenant rows first).
• getOrganizationById(conn, orgId) → Organization | null.
• listOrganizationsForFirebaseUid(conn, firebaseUid) → Array<{ organization: Organization, role: OrgRole }>. One-query join across platform.users + organization_users + platform.organizations. Used by W002 to assemble the user-plus-orgs payload on app load.

Memberships

• addMembership(conn, { userId, orgId, role }) → OrganizationUser. Inserts a (user, org, role) row.
• updateMembershipRole(conn, { userId, orgId, role }) → OrganizationUser. Promotes / demotes a member.
• removeMembership(conn, { userId, orgId }) → void. Drops a membership.
• touchMembershipLastActive(conn, { userId, orgId }) → void. Stamps last_active_at = NOW(). Called from request middleware on every org-scoped request.
• getMembership(conn, { userId, orgId }) → OrganizationUser | null.
• listMembersByOrg(conn, orgId) → OrgMember[]. Memberships joined with the user's identity ({ userId, email, displayName, role, isActive, joinedAt, lastActiveAt }), ordered by join date. Used by W016.
• countOwners(conn, orgId) → number. Count of active owner-role memberships. Used by W018 and W019 to guard the last owner.

Invitations

• createInvitation(conn, { orgId, invitedBy, email, role, expiresAt? }) → UserInvitation. Inserts a pending invitation. token is a DB default (gen_random_uuid()); expiresAt defaults to NOW() + 7 days.
• resendInvitation(conn, invitationId) → UserInvitation | null. Rotates the token and sets expires_at = NOW() + 7 days iff still pending; returns the row or null. Used by W021.
• acceptInvitation(conn, invitationId) → boolean. Sets accepted_at = NOW() iff still pending. Used by W010 inside its locked transaction.
• revokeInvitation(conn, invitationId) → boolean. Sets revoked_at = NOW() iff still pending. Used by W022.
• declineInvitation(conn, invitationId, callerEmail) → boolean. Sets revoked_at = NOW() iff still pending and LOWER(email) = LOWER(callerEmail). Used by W011. Decline and revoke share the revoked_at column — the distinction lives at the call boundary, not in the data.
• getInvitationById(conn, invitationId) → UserInvitation | null.
• getInvitationByToken(conn, token) → UserInvitation | null. For the public lookup (W009); returns null for non-UUID tokens so L4 can 404 cleanly.
• findPendingInvitation(conn, orgId, email) → UserInvitation | null. Pending invitation matching (orgId, email). Used by W020 for resend-on-duplicate.
• listInvitationsByOrg(conn, orgId, statuses?) → UserInvitation[]. Org-side list (W023).
• listInvitationsByEmail(conn, email, statuses?) → UserInvitation[]. The invitee's "my pending invitations" view (W008).

API tokens

• createApiToken(conn, { userId, orgId, name, tokenPrefix, tokenHash, expiresAt }) → ApiToken. Inserts a PAT row (hash + prefix; the plaintext is never persisted). Used by W005.
• getApiTokenByHash(conn, hash) → ApiToken | null. Lookup for the auth middleware. Returns the row regardless of revoked / expired state — the caller decides, so failures surface as a clean 401.
• listApiTokensForUser(conn, userId) → ApiToken[]. Active (non-revoked) tokens, newest first. Used by W006.
• revokeApiToken(conn, tokenId, userId) → boolean. Sets revoked_at = NOW() iff the token belongs to userId and is still active. Used by W007.
• touchApiTokenLastUsed(conn, tokenId) → void. Fire-and-forget; called from the auth middleware on every successful PAT request.

Notes

• This is the only component that operates with no org_id filter — its tables are platform-wide. The app.org_id GUC is irrelevant here.
• L2 only does the DB work. Firebase calls (verify / create / delete identity) and email sending live in L3 workflows, not here.