# Bibleit domain model PostgreSQL is Bibleit's mandatory source of truth. The schema is public by design; deployment credentials and user data are not. ## Identity and resource ownership - `users` represents a human; `principal_key` identifies the protocol principal. - `auth_identities` maps Google, GitHub, or email identities to a user. Matching email addresses never automatically join identities. Email identities use `password_credentials` with Argon2 hashes. - `accounts` is the resource ownership boundary (`personal` or `organization`) and owns the unique public handle and workspace name. `account_memberships` associates a user with their personal account. - `organizations` is the explicit organization model: its `account_id` is both its internal identifier and the foreign key to its resource account. It owns the logo, website, status, seat limit, and timestamps. Public routes use the account handle. - `organization_memberships` associates users with organizations and scoped owner/admin/operator/viewer roles. Invitations reserve seats while pending; `organization_member_limits` assigns individual quotas within approved ceilings. - `permissions` is the common resource/action vocabulary, including `organization`. - `roles`, `role_permissions`, `membership_roles`, and `user_permissions` serve personal and server authority. `organization_role_permissions` serves workspace authority; personal grants never override organization membership. - `contributor_recognitions` contains one lifecycle per personal account: Pending → Approved, Pending → Needs changes, Needs changes → Pending/Approved. Direct maintainer grants may start Approved. Database triggers reject invalid transitions and retain previous evidence/reviews in the row's JSON history. - `plan_access` is a read-only derived view for personal accounts only: approved recognition means Contributor; otherwise Starter. Organizations derive access from scoped memberships and workspace capacity. There is no independent mutable plan flag, award table, subscription, or billing state. - `plans`, `plan_limits`, and `account_limit_overrides` define quota vocabulary and approved overrides. Starter has 3 translations, 1 Live, 1 token, 1 SSH key, 1 collaborator per Live, and 50 viewer connections. Contributor has 5 translations, 5 Lives, 3 tokens, 3 SSH keys, 3 collaborators, and 250 viewer connections. - `access_tokens` identifies a user acting in an account; only the secret's SHA-256 hash is stored. Token scopes narrow personal authority. Organization authority must also be resolved against the selected workspace's active membership. - `ssh_keys` belongs to a user and stores only public material. ## Content and transient state - `account_translations` is the account's enabled Bible translation library. - `lives` belongs to the resource account. `owner_user_id` records its creator; organization Lives survive creator removal. `live_translation_selections` stores each Live's selected Bible version slugs and display order, not Bible content or interface localization. The current rendered payload remains JSON. - `remembered_logins` stores hashes of 90-day browser shortcut tokens linked to identities. A shortcut cannot authenticate a user; sessions remain separate. - `browser_sessions`, `oauth_states`, `auth_action_tokens`, and `cli_authorizations` persist authentication state. Abuse counters use bounded ETS tables with hashed identifiers; they reset on restart and are local to each Erlang node. - WebSocket connections, subscribers, monitors, and OTP PIDs stay in memory. - Live management uses a separate authenticated WebSocket watcher, excluded from audience counts and viewer quotas. Joins, departures, and Live changes trigger updates coalesced over 250 ms. Audience changes send only statistics; studio changes and reconnections send a full snapshot. Each send checks the browser session and current management access; a 15-second heartbeat also closes revoked idle connections. Browsers reconnect with backoff and receive a fresh snapshot, disconnect while hidden, and advance elapsed time locally instead of polling the server. Translation bodies/indexes and SSH host private keys stay outside PostgreSQL. ## Lifecycle rules A verified user receives a personal account and Starter access without a plan row. Contributor approval derives permanent access. Organization approval creates a separate workspace whose founder is owner. Owners must transfer ownership before account deletion if other members remain; deleting the last member removes the organization and its resources. These checks share transactional organization locks. Deleting a user cascades personal credentials and sessions; deleting an account cascades its resources. Authentication codes are short-lived and single-use. ## Indexing policy PostgreSQL automatically creates indexes for primary keys and unique constraints, but not for the referencing side of foreign keys. The initial schema therefore adds explicit indexes for: - user-to-account and provider-identity resolution; - reverse RBAC relationships used by joins and cascades; - token, SSH-key, browser-session, and authorization ownership; - partial scans of active access tokens and SSH keys; - expiry cleanup for browser sessions, email actions, OAuth state, and CLI authorization flows; - Live ownership and deterministic `created_at, id` restore ordering. Composite indexes follow the leftmost-prefix rule: existing composite primary keys serve queries beginning with their account, user, role, token, or Live identifier. An index is not added merely because a column exists; new indexes should correspond to an observed query/filter/order pattern and be checked with `EXPLAIN (ANALYZE, BUFFERS)` against production-shaped data. This avoids unnecessary write amplification while preserving the hot authentication and ownership paths. The development schema is consolidated in `priv/sql/001_initial.sql`. Existing pre-consolidation databases require an explicit rebuild; future changes can add ordered revisions after this baseline. There is no DETS fallback, bootstrap account, or automatic DETS import. Personal usernames and organization handles share a lowercase `accounts.handle` with one database unique constraint, including for direct SQL writes. Personal accounts may have no handle until onboarding; organization accounts require one. Display names are not unique. Handles and organization names have one source of truth in `accounts`; there is no separate handle registry or synchronization trigger. Organization creation derives the handle from the entered name: lowercase, transliterated Latin characters, and single hyphen separators. The display name remains readable profile text. Private contact email is separate from identity; a changed address activates only after verification. Pending verification hashes expire after 24 hours and are single-use. No personal/business ownership category or legal-entity metadata is stored. Profile and membership administration live at `/organizations/:handle/settings/profile`, with separate `members`, `roles`, `translations`, `plan`, and `account` sections. `/organizations/:handle` is the workspace overview, with statistics and the regular Live rows. PostgreSQL stores organization profile pictures behind `/avatars/:key`. Uploads accept PNG, JPEG, WebP, or GIF up to 2 MB and require organization update permission. Replacing or removing a picture retires the old key. Live settings provide an Organization Live toggle and workspace picker. The creator can move a personal Live into an organization they may create Lives in; moving it out also requires administration permission in its current organization. Moves retain the Live URL, enforce destination quotas, replace translation selections with the destination library, and revoke prior personal collaborators, pending invitations, and active viewer connections. Moves and Live creation are serialized by the Live registry. Browser viewers receive a workspace-change event and reload to refresh branding and reauthorize against the destination; secret revocation remains a terminal access-revoked event. Organization-owned Lives use the organization picture in their public viewer. Assigned member limits and workspace capacity requests are presented in the organization Plan & usage section. Membership is invitation-only. Owners and admins invite members by username or email; the recipient must accept the invitation. There is no organization directory, join request table, or join-request setting. Self-service organizations have a `created_by_user_id` distinct from current ownership. Verified users can create one workspace under the `organization.create` quota, with three reserved/active seats including the owner. Free workspaces persist Starter capacity as overrides; existing custom workspaces retain their limits. `organization_quota_requests` stores higher-capacity review lifecycles independently from personal plans. An organization account lock serializes capacity requests, reviews, cancellation, and membership mutations. Pending requests have a partial unique index per org. Workspace limits use explicit overrides; `plan_access` contains personal accounts only. Only Starter and Contributor are personal plans. Organization membership grants no automatic Contributor recognition or guaranteed personal GitHub issue review. Workspace creation uses `organization.create`; only capacity increases require a request lifecycle. Organization routes resolve the unique handle to the internal account UUID before authorization. Signed-in members visiting old UUID URLs are redirected to the canonical handle path, preserving the settings section and query string. Changing a display name does not change the handle; changing the username redirects to the new path. The organization username `new` is reserved. Account settings display the email supplied by the sign-in provider as a disabled field for every provider. Language and theme preferences live in Profile. Notification preferences are per-category channel maps with independent `inbox` and `email` flags. “Invitations and verification links” (`link_verification`) controls Live and organization invitations, email and organization contact verification, and password-reset links for existing recipients. Verification and recovery links can also appear in the recipient's inbox. New recipients without an account use the default email delivery. Legacy invitation preferences are merged per channel: an opt-out in either category keeps that channel off until the user saves an explicit shared choice. Delivery failure leaves the invitation valid and presents a manual link to the inviter. Organization settings explain each section’s purpose. Overview summarizes Lives, active members, and pending invitations. The role guide reads the enforced `organization_role_permissions` mapping and shows separate Owner, Admin, Operator, and Viewer access with membership counts and links to role assignment and usage. The shared translation library seeds new organization Lives. Owners and admins can add translations from the catalog or remove entries within organization capacity. Removing a library entry preserves existing Live translation selections. Operators and viewers cannot change the shared library. Organization invitation links render a public preview of the workspace and role. Opening a link does not accept it. Guests can accept or reject directly from the preview. Acceptance opens signup with the invited email prefilled; alternative providers and sign-in are available under “Sign in with another profile”. A CSRF-protected rejection revokes the invitation without requiring an account. The accepted invitation intent is stored in an HttpOnly cookie and survives authentication and username setup. Email verification tokens retain that intent, including when verification opens in another browser. After setup, membership is created only for the invited user or matching verified email, while the invitation is still pending and unexpired. Invalid, revoked, expired, and consumed links cannot create membership. Signed-in users can view and act on only their own invitation; other accounts receive a 404 without workspace or recipient details. Logout and signing in with a different recipient clear the browser's pending invitation intent; the unrelated account continues to its dashboard rather than the invite. Successful organization invitation submissions redirect with HTTP 303 to the Members page. A short-lived, HttpOnly notice cookie scoped to that page preserves the delivery confirmation or manual fallback link for one GET. Refreshing that page does not repeat invitation creation or notification delivery. ### Organization contact verification New organizations created with a contact address different from the creator’s verified email remain limited to profile edits, verification, and deletion until the contact address is verified. Invitations, organization Lives, member limits, and translation management require verification. Creation records the pending contact inside the organization transaction, before the workspace becomes visible. Changing an already verified contact preserves the current address and workspace access until the replacement is verified. Existing internal organizations without a contact address retain their previous access behavior. Inbox notifications store structured system context alongside fallback text. Titles and descriptions follow the current interface language; sender names, handles, and organization names retain their original text. Organization invitations expose Accept and Reject while available. Verification and other links expose a context action that marks the item as read before opening the existing flow. Each notification can be deleted without cancelling its link or revoking membership. All notification mutations require the browser CSRF token and are scoped to the current recipient.