# Data Model This document describes planned entities and relationships for Cloutal Workspace. It does not define final SQL, migrations, column lists, indexes, or database constraints. ## Status labels - **Approved foundation** — already established by the repository documentation. - **Recommended direction** — the preferred approach, subject to product-owner approval. - **Deferred decision** — intentionally decided later. - **Open question** — requires product-owner input. ## Data classification - **Approved foundation** — Organization-scoped records must include an organization boundary and must not be readable, writable, listable, searchable, exportable, downloadable, or inferable across organization boundaries. - **Recommended direction** — Global records identify people or platform concepts that are not owned by one organization. - **Recommended direction** — Organization-scoped records belong to exactly one organization unless explicitly designed otherwise. - **Recommended direction** — Platform-administration records support reviewed platform operations and must remain separate from organization-administrator privileges. - **Recommended direction** — Sensitive records require hashing, encryption at rest, environment-managed secrets, log redaction, or exclusion from Cloutal Workspace servers depending on the data type. - **Recommended direction** — Retention/deletion records control lifecycle state without preserving unnecessary customer content after deletion. ## Planned major entities ### Users - **Approved foundation** — Users are global accounts that may belong to one or more organizations through membership records. - **Approved foundation** — A user can have separate roles in separate organizations. - **Sensitive data** — Email addresses, password hashes, reset state, verification state, session-revocation markers, and security metadata require careful handling. - **Retention/deletion** — User records may remain when the person belongs to another active organization; organization-specific content must follow the organization's lifecycle. ### User identities - **Recommended direction** — User identities connect a global user to authentication methods such as local password credentials and Google OAuth/OpenID Connect identities. - **Approved foundation** — A verified email claim from an appropriately validated OAuth/OpenID Connect provider may satisfy email verification. - **Approved foundation** — External identity linking must verify ownership, prevent account-linking conflicts, and must not automatically merge accounts based only on an untrusted or unverified email value. - **Sensitive data** — OAuth access and refresh tokens, if stored, must be encrypted at rest and redacted from logs. ### Pending registrations and email verification - **Recommended direction** — Password signup creates either an unverified global user account or a secure pending-registration record before email verification. - **Recommended direction** — The record should preserve the initial organization information and enough state to complete the same signup intent idempotently after verification. - **Sensitive data** — Verification tokens must be time-limited, single-use, stored only as hashes, and redacted from logs. - **Retention/deletion** — Abandoned or expired unverified signup records should be cleaned up by maintenance jobs without creating an organization or trial. ### Organizations - **Recommended direction** — Organizations are the tenant boundary and own workspace data, billing state, trial state, retention state, branding, custom domains, files, exports, and audit events. - **Recommended direction** — Organization records should track lifecycle states such as trialing, active, grace-period, suspended, read-only, retention, deletion-pending, and deleted. - **Approved foundation** — Organization creation and trial activation after signup verification must be idempotent so retries do not create duplicate organizations or trial periods. - **Retention/deletion** — Organization-owned customer content is subject to trial, retention, hold, and deletion rules. ### Memberships - **Approved foundation** — Memberships link global users to organizations and hold organization-specific roles. - **Approved foundation** — Authorization must verify both user permission and organization membership using trusted server-side active-organization context. - **Approved foundation** — Users must not gain access by changing organization identifiers in URLs, forms, JSON, headers, cookies, or API requests. - **Approved foundation** — Initial owner-membership creation after signup verification must be idempotent so retries do not create duplicate memberships. ### Invitations - **Recommended direction** — Invitations are organization-scoped and invite a person to join an organization with a specific role. - **Sensitive data** — Invitation tokens should be hashed when stored and redacted from logs. - **Retention/deletion** — Expired invitations should be cleaned up by maintenance jobs. ### Roles and permissions - **Recommended direction** — Initial role concepts include organization owners, editors, regular users, and separate platform administrators. - **Recommended direction** — Platform administrators are not organization administrators and should require explicit support-access controls. - **Deferred decision** — Final permission names and role matrices should be approved before implementation. ### Subscriptions - **Recommended direction** — Subscription records are organization-scoped and map an organization to Stripe customer identifiers, subscription identifiers, price identifiers, status, grace-period state, cancellation state, and reactivation state. - **Sensitive data** — Stripe identifiers may be stored in the database, but Stripe secrets belong in environment configuration. Full card numbers and CVV data must not be stored. ### Billing-event records - **Recommended direction** — Billing-event records store webhook receipt metadata, deduplication keys, processing status, and relevant non-secret Stripe event references. - **Recommended direction** — Signed webhook verification and idempotent processing must prevent duplicate state transitions. - **Sensitive data** — Stripe webhook secrets are excluded from the database and application logs. ### Custom domains - **Recommended direction** — Custom-domain records are organization-scoped and track hostnames, verification status, CNAME onboarding state, certificate status, canonical URL behavior, and DNS health. - **Approved foundation** — Domain ownership verification must prevent one customer from claiming another customer's hostname, and a hostname must be verified and mapped to the correct organization before tenant-scoped requests or authentication handoffs are accepted. - **Recommended direction** — Centralized OAuth callback handling should avoid per-customer OAuth callback sprawl. ### Customer-domain authentication state - **Deferred decision** — Browser session cookies cannot be shared across unrelated customer-owned domains and the central Cloutal login domain. A future reviewed decision must determine whether the model needs dedicated one-time handoff-token records, authentication-event records only, independently established domain sessions, or another approach. - **Approved foundation** — Reusable session credentials must never be placed in query strings. - **Approved foundation** — Any handoff token must be short-lived, single-use, audience-bound to the intended verified hostname, and stored or validated in a replay-resistant way. - **Retention/deletion** — Handoff-token or authentication-event records should retain only the minimum metadata required for replay prevention, troubleshooting, and security auditing. ### Branding settings - **Recommended direction** — Branding settings are organization-scoped and include organization display name, logo reference, theme colors, PWA name, and PWA icon reference. - **Sensitive data** — Branding files must be tenant-aware customer files and should be stored in shared or object storage when the system scales. ### Audit events - **Recommended direction** — Audit events record signup, login success/failure, logout, password reset, Google linking, organization creation, invitations, invitation acceptance, role changes, user removal, branding changes, custom-domain changes, subscription changes, exports, suspension, reactivation, deletion, platform-administrator support access, and security-sensitive configuration changes. - **Recommended direction** — Audit logs must not contain passwords, password hashes, reset tokens, OAuth tokens, Stripe secrets, payment-card information, or full sensitive request bodies. - **Retention/deletion** — Minimal deletion audit receipts may be retained after customer-content deletion when needed for accountability. ### Files - **Recommended direction** — File records are organization-scoped and point to tenant-aware customer uploads, generated exports, logos, icons, or attachments. - **Recommended direction** — File download authorization must verify membership, permission, organization context, file ownership, lifecycle state, and safe path/object references. - **Retention/deletion** — Files are subject to organization retention and deletion jobs. ### Notifications - **Recommended direction** — Notification records may support email verification, password resets, invitations, trial reminders, failed-payment notices, deletion warnings, and operational notices. - **Sensitive data** — Tokens should be hashed; message bodies should avoid unnecessary secrets and sensitive request data. ### Retention holds - **Recommended direction** — Retention holds are platform-administration records linked to organizations and may pause deletion for support, legal, billing, or operational reasons. - **Recommended direction** — Holds must be auditable, time-bounded when possible, and visible to authorized platform administrators. ### Deletion jobs - **Recommended direction** — Deletion-job records track idempotent lifecycle work, retries, final warnings, active-data deletion, and minimal deletion receipts. - **Recommended direction** — Active data deletion is separate from encrypted backup expiration under the backup-retention policy. ## Signup data lifecycle 1. **Approved foundation** — Signup is submitted with email, password, and initial organization information. 2. **Approved foundation** — An unverified global user account or secure pending-registration record is created. 3. **Approved foundation** — A time-limited, single-use email-verification token is generated and stored only as a hash. 4. **Approved foundation** — The verification email is sent. 5. **Approved foundation** — The user verifies control of the email address, or an appropriately validated external provider supplies a verified email claim. 6. **Approved foundation** — The organization is created once. 7. **Approved foundation** — The initiating user receives the organization-owner membership once. 8. **Approved foundation** — The seven-day trial begins once. ## Open questions - **Open question** — Which organization roles and permissions are included at launch? - **Open question** — What audit-log retention and deletion-receipt retention periods are required?