Skip to content

1.1. Role‐Based Access Control (RBAC) Architecture

Jump to bottom
Benthara edited this page Oct 10, 2025 · 2 revisions

Role-Based Access Control (RBAC) Architecture

1. Core Architectural Principles

Principle Implementation Reflection
Privacy by Design Platform-level roles (Admin, Engineer) cannot see private tenant/user content or identities unless explicit consent, legal audit, or system diagnostics require it.
Data Sovereignty Each workspace/tenant owns its data — even the platform operator is only a processor, not a controller.
Unified Identity, Multi-Workspace Membership Users have one master account, but can join multiple tenants with different roles (similar to how Slack, Notion, or GitHub orgs work).
Minimal Visibility Model Platform Admins/Agents can see metadata (tenant name, health, billing status) but not content/data within the tenant.
Extensible Project Model Projects can be of multiple types (GitCMS, PaperTrail, Docs, Sheets, Forms, Kanban, etc.), and workspaces (can be assign as tenants) act as containers (like Google Workspace → Drive folders).

2. Role Definitions

Level Role Scope Notes
000 Platform Admin Global Can manage system policies, plans, and compliance checks. Cannot see tenant content or user PII without legal override or consent.
100 Platform Engineer Global Handles system health, backups, performance. No visibility into tenant content or real user identities.
200 Tenant/Workspace Admin Tenant/Workspace Full control over that workspace — can manage members, billing, and content.
300 Admin App/Workspace Manages content and permissions within a workspace or project folder.
400 Editor App Create, edit, and publish content. No access to tenant settings.
500 Moderator App Manage contributions, handle reports/comments.
600 Contributor App Create drafts or personal content.
700 Viewer App Authenticated, read-only member of workspace or shared link.
800 Guest App Temporary or external viewer; may have read/comment privileges only when explicitly shared.
950 Automation/Bot Service Scoped per-token automation (backup, sync, CI/CD) with no human-equivalent privileges.

3. Capability Matrix

(aligned with Privacy-by-Design and Workspace Federation)

Legend: ✅ full 🔸 limited (consent / scoped) ❌ none

Capability Platform Admin Platform Engineer Tenant Admin Admin Editor Moderator Contributor Viewer Guest Bot
Platform Settings & Policies ✅ 🔸 (ops configs) ❌ ❌ ❌ ❌ ❌ ❌ ❌ ❌
Tenant Lifecycle (create/suspend/delete) ✅ 🔸 (technical actions only) ❌ ❌ ❌ ❌ ❌ ❌ ❌ ❌
System Health & Monitoring (non-PII) ✅ ✅ ❌ ❌ ❌ ❌ ❌ ❌ ❌ ✅
View Tenant Metadata (name, plan, region) ✅ ✅ ❌ ❌ ❌ ❌ ❌ ❌ ❌ ❌
View Tenant Member Identities (PII) 🔸 (only anonymized or compliance-approved) ❌ ✅ 🔸 (within scope) ❌ ❌ ❌ ❌ ❌ ❌
View Tenant Content 🔸 (aggregated, anonymized analytics only) ❌ ✅ ✅ ✅ 🔸 (flagged/reports) 🔸 (own) ❌ ❌ 🔸 (per token)
Modify Tenant Content ❌ ❌ ✅ ✅ ✅ 🔸 (moderation) 🔸 (own only) ❌ ❌ 🔸
Access Private Workspaces 🔸 (via explicit legal/compliance override) ❌ ✅ ✅ ✅ ✅ 🔸 (own only) ❌ ❌ 🔸
Cross-Tenant Access ❌ ❌ ❌ ✅ (if invited to multiple workspaces) ✅ ✅ ✅ 🔸 (shared links) 🔸 🔸
User & Role Management (within workspace) 🔸 (consent-based audit only) ❌ ✅ ✅ ❌ ❌ ❌ ❌ ❌ ❌
Project Management (create / delete projects) ❌ ❌ ✅ ✅ 🔸 ❌ ❌ ❌ ❌ 🔸
Project Types (GitCMS, Docs, Sheets, etc.) ❌ ❌ ✅ ✅ ✅ 🔸 🔸 🔸 ❌ ✅ (per type)
Comment / Collaboration Tools ❌ ❌ ✅ ✅ ✅ ✅ ✅ ✅ 🔸 ❌
Moderation / Reporting ❌ ❌ ✅ ✅ 🔸 ✅ ❌ ❌ ❌ ❌
Audit Logs (tenant) 🔸 (aggregated) 🔸 ✅ ✅ ❌ ❌ ❌ ❌ ❌ 🔸
Audit Logs (platform) ✅ ✅ ❌ ❌ ❌ ❌ ❌ ❌ ❌ ❌
Integrations & API Tokens (workspace scope) 🔸 (manage scopes, not data) ✅ (infra only) ✅ ✅ 🔸 ❌ ❌ ❌ ❌ ✅
Billing / Subscription ✅ 🔸 (system health only) ✅ 🔸 (view only) ❌ ❌ ❌ ❌ ❌ ❌
System Maintenance / Deployments ✅ ✅ ❌ ❌ ❌ ❌ ❌ ❌ ❌ ✅
Data Export / Portability 🔸 (compliance process) ❌ ✅ ✅ ✅ ❌ ❌ ❌ ❌ ✅ (backups only)
Data Deletion (tenant scope) 🔸 (compliance verified) ❌ ✅ ✅ ❌ ❌ ❌ ❌ ❌ ❌
Read Public Content ✅ ✅ ✅ ✅ ✅ ✅ ✅ ✅ ✅ ✅

3.1. Key Details (Capability Matrix)

Platform Roles (000–100)

  • Cannot view or alter tenant data or real user identities

    only system metadata, resource utilization, and anonymized aggregates.

  • PII access requires explicit “Compliance Mode” flag (logged + auditable).
  • Engineering visibility limited to telemetry and encrypted payload sizes — not decrypted data.

Tenant & Workspace Roles (200–800)

  • Full sovereignty: tenant owns its workspace and can self-manage users.
  • Admins can invite cross-org users via identity links (not new accounts).
  • User identities are unified globally (one profile → many workspace memberships).

Automation/Bot (950)

  • Bots must use scoped tokens (capabilities embedded in token claims). Example scopes: content:read, backup:run, sync:drive, ci:deploy.

3.2. Future-Ready Additions (for later)

  • Compliance Auditor Role (optional) – read-only audit access to logs + encrypted data footprints.
  • Workspace Federation Layer – allow peer-to-peer workspace links (shared projects between orgs).
  • Consent Ledger – every access granted to platform staff logged with justification & expiry.

3.3 Capability Matrix (JSON)

{
  "meta": {
    "version": "2.0",
    "capability_value_semantics": ["allow", "deny", "consent", "compliance", "scoped", "anonymized"],
    "notes": "Privacy-by-design: platform roles have no tenant content/PII access by default; PI/PII access requires consent or compliance override and is fully audited."
  },
  "capabilities_catalog": [
    { "key": "platform_settings", "description": "Manage platform-wide settings, policies, and feature flags." },
    { "key": "tenant_lifecycle", "description": "Create/suspend/delete tenants or workspaces." },
    { "key": "system_health_monitoring", "description": "View platform health/telemetry without tenant data visibility." },
    { "key": "view_tenant_metadata", "description": "View tenant/workspace metadata (name, plan, region)." },
    { "key": "view_member_identities", "description": "View real user identities and emails within a tenant/workspace." },
    { "key": "view_content_private", "description": "View private tenant content (drafts, non-public data)." },
    { "key": "modify_content", "description": "Create/edit/publish/delete tenant content." },
    { "key": "access_private_workspaces", "description": "Enter private workspaces not explicitly shared." },
    { "key": "cross_tenant_access", "description": "Participate across multiple tenants via memberships." },
    { "key": "manage_workspace_users_roles", "description": "Invite members and assign roles within a tenant/workspace." },
    { "key": "project_manage", "description": "Create/archive/delete projects within a workspace." },
    { "key": "project_types_manage", "description": "Enable/manage project types (GitCMS, Docs, Sheets, Forms, Kanban, PaperTrail, etc.)." },
    { "key": "comment_collaborate", "description": "Comment, review, react, collaborate in-app." },
    { "key": "moderate_review", "description": "Moderate submissions, handle reports, approve/reject." },
    { "key": "audit_logs_tenant", "description": "View tenant-scoped audit logs." },
    { "key": "audit_logs_platform", "description": "View platform-scoped audit logs." },
    { "key": "integrations_api_tokens", "description": "Create/manage integrations and API tokens (workspace scope)." },
    { "key": "billing_subscription", "description": "Manage billing, subscriptions, and invoices." },
    { "key": "system_maintenance", "description": "Perform deployments, backups, restores, migrations." },
    { "key": "data_export_portability", "description": "Export workspace data (portability)." },
    { "key": "data_deletion_tenant", "description": "Delete tenant/workspace data." },
    { "key": "read_public_content", "description": "Read publicly shared content." },
    { "key": "aggregated_analytics", "description": "Access anonymized/aggregate analytics only (no PII)." },
    { "key": "compliance_override_access", "description": "Activate compliance/legal override for time-boxed, audited access." },
    { "key": "token_scopes_manage", "description": "Define and assign fine-grained token scopes (least privilege)." }
  ],
  "roles": [
    {
      "id": 0,
      "key": "platform_admin",
      "label": "Platform Admin",
      "level": 0,
      "scope": "global",
      "description": "Full platform governance without default access to tenant content or identities.",
      "capabilities": {
        "platform_settings": "allow",
        "tenant_lifecycle": "allow",
        "system_health_monitoring": "allow",
        "view_tenant_metadata": "allow",
        "aggregated_analytics": "anonymized",
        "view_member_identities": "compliance",
        "view_content_private": "compliance",
        "modify_content": "deny",
        "access_private_workspaces": "compliance",
        "cross_tenant_access": "deny",
        "manage_workspace_users_roles": "consent",
        "project_manage": "deny",
        "project_types_manage": "deny",
        "comment_collaborate": "deny",
        "moderate_review": "deny",
        "audit_logs_tenant": "anonymized",
        "audit_logs_platform": "allow",
        "integrations_api_tokens": "allow",
        "billing_subscription": "allow",
        "system_maintenance": "allow",
        "data_export_portability": "compliance",
        "data_deletion_tenant": "compliance",
        "read_public_content": "allow",
        "compliance_override_access": "allow",
        "token_scopes_manage": "allow"
      }
    },
    {
      "id": 1,
      "key": "platform_engineer",
      "label": "Platform Engineer",
      "level": 100,
      "scope": "global",
      "description": "Operations & reliability; no tenant data/PII by default.",
      "capabilities": {
        "platform_settings": "deny",
        "tenant_lifecycle": "consent",
        "system_health_monitoring": "allow",
        "view_tenant_metadata": "allow",
        "aggregated_analytics": "anonymized",
        "view_member_identities": "deny",
        "view_content_private": "deny",
        "modify_content": "deny",
        "access_private_workspaces": "deny",
        "cross_tenant_access": "deny",
        "manage_workspace_users_roles": "deny",
        "project_manage": "deny",
        "project_types_manage": "deny",
        "comment_collaborate": "deny",
        "moderate_review": "deny",
        "audit_logs_tenant": "anonymized",
        "audit_logs_platform": "allow",
        "integrations_api_tokens": "allow",
        "billing_subscription": "deny",
        "system_maintenance": "allow",
        "data_export_portability": "deny",
        "data_deletion_tenant": "deny",
        "read_public_content": "allow",
        "compliance_override_access": "consent",
        "token_scopes_manage": "allow"
      }
    },
    {
      "id": 2,
      "key": "tenant_admin",
      "label": "Tenant Admin",
      "level": 200,
      "scope": "tenant",
      "description": "Owns a workspace/tenant; full control within that boundary.",
      "capabilities": {
        "platform_settings": "deny",
        "tenant_lifecycle": "deny",
        "system_health_monitoring": "deny",
        "view_tenant_metadata": "allow",
        "aggregated_analytics": "allow",
        "view_member_identities": "allow",
        "view_content_private": "allow",
        "modify_content": "allow",
        "access_private_workspaces": "allow",
        "cross_tenant_access": "allow",
        "manage_workspace_users_roles": "allow",
        "project_manage": "allow",
        "project_types_manage": "allow",
        "comment_collaborate": "allow",
        "moderate_review": "allow",
        "audit_logs_tenant": "allow",
        "audit_logs_platform": "deny",
        "integrations_api_tokens": "allow",
        "billing_subscription": "allow",
        "system_maintenance": "deny",
        "data_export_portability": "allow",
        "data_deletion_tenant": "allow",
        "read_public_content": "allow",
        "compliance_override_access": "deny",
        "token_scopes_manage": "allow"
      }
    },
    {
      "id": 3,
      "key": "admin",
      "label": "Admin",
      "level": 300,
      "scope": "tenant",
      "description": "Top admin within a workspace’s apps and projects.",
      "capabilities": {
        "platform_settings": "deny",
        "tenant_lifecycle": "deny",
        "system_health_monitoring": "deny",
        "view_tenant_metadata": "allow",
        "aggregated_analytics": "allow",
        "view_member_identities": "consent",
        "view_content_private": "allow",
        "modify_content": "allow",
        "access_private_workspaces": "allow",
        "cross_tenant_access": "allow",
        "manage_workspace_users_roles": "allow",
        "project_manage": "allow",
        "project_types_manage": "allow",
        "comment_collaborate": "allow",
        "moderate_review": "allow",
        "audit_logs_tenant": "allow",
        "audit_logs_platform": "deny",
        "integrations_api_tokens": "allow",
        "billing_subscription": "consent",
        "system_maintenance": "deny",
        "data_export_portability": "allow",
        "data_deletion_tenant": "consent",
        "read_public_content": "allow",
        "compliance_override_access": "deny",
        "token_scopes_manage": "allow"
      }
    },
    {
      "id": 4,
      "key": "editor",
      "label": "Editor",
      "level": 400,
      "scope": "tenant",
      "description": "Create, edit, and publish within assigned projects.",
      "capabilities": {
        "platform_settings": "deny",
        "tenant_lifecycle": "deny",
        "system_health_monitoring": "deny",
        "view_tenant_metadata": "allow",
        "aggregated_analytics": "allow",
        "view_member_identities": "deny",
        "view_content_private": "allow",
        "modify_content": "allow",
        "access_private_workspaces": "allow",
        "cross_tenant_access": "allow",
        "manage_workspace_users_roles": "deny",
        "project_manage": "consent",
        "project_types_manage": "deny",
        "comment_collaborate": "allow",
        "moderate_review": "consent",
        "audit_logs_tenant": "deny",
        "audit_logs_platform": "deny",
        "integrations_api_tokens": "consent",
        "billing_subscription": "deny",
        "system_maintenance": "deny",
        "data_export_portability": "consent",
        "data_deletion_tenant": "deny",
        "read_public_content": "allow",
        "compliance_override_access": "deny",
        "token_scopes_manage": "deny"
      }
    },
    {
      "id": 5,
      "key": "moderator",
      "label": "Moderator",
      "level": 500,
      "scope": "tenant",
      "description": "Moderation and abuse handling within scope.",
      "capabilities": {
        "platform_settings": "deny",
        "tenant_lifecycle": "deny",
        "system_health_monitoring": "deny",
        "view_tenant_metadata": "allow",
        "aggregated_analytics": "allow",
        "view_member_identities": "deny",
        "view_content_private": "consent",
        "modify_content": "consent",
        "access_private_workspaces": "allow",
        "cross_tenant_access": "allow",
        "manage_workspace_users_roles": "deny",
        "project_manage": "deny",
        "project_types_manage": "deny",
        "comment_collaborate": "allow",
        "moderate_review": "allow",
        "audit_logs_tenant": "consent",
        "audit_logs_platform": "deny",
        "integrations_api_tokens": "deny",
        "billing_subscription": "deny",
        "system_maintenance": "deny",
        "data_export_portability": "deny",
        "data_deletion_tenant": "deny",
        "read_public_content": "allow",
        "compliance_override_access": "deny",
        "token_scopes_manage": "deny"
      }
    },
    {
      "id": 6,
      "key": "contributor",
      "label": "Contributor",
      "level": 600,
      "scope": "tenant",
      "description": "Create/edit own drafts; submit for review.",
      "capabilities": {
        "platform_settings": "deny",
        "tenant_lifecycle": "deny",
        "system_health_monitoring": "deny",
        "view_tenant_metadata": "allow",
        "aggregated_analytics": "deny",
        "view_member_identities": "deny",
        "view_content_private": "consent",
        "modify_content": "consent",
        "access_private_workspaces": "allow",
        "cross_tenant_access": "allow",
        "manage_workspace_users_roles": "deny",
        "project_manage": "deny",
        "project_types_manage": "deny",
        "comment_collaborate": "allow",
        "moderate_review": "deny",
        "audit_logs_tenant": "deny",
        "audit_logs_platform": "deny",
        "integrations_api_tokens": "deny",
        "billing_subscription": "deny",
        "system_maintenance": "deny",
        "data_export_portability": "deny",
        "data_deletion_tenant": "deny",
        "read_public_content": "allow",
        "compliance_override_access": "deny",
        "token_scopes_manage": "deny"
      }
    },
    {
      "id": 7,
      "key": "viewer",
      "label": "Viewer",
      "level": 700,
      "scope": "tenant",
      "description": "Authenticated read-only member.",
      "capabilities": {
        "platform_settings": "deny",
        "tenant_lifecycle": "deny",
        "system_health_monitoring": "deny",
        "view_tenant_metadata": "allow",
        "aggregated_analytics": "deny",
        "view_member_identities": "deny",
        "view_content_private": "deny",
        "modify_content": "deny",
        "access_private_workspaces": "allow",
        "cross_tenant_access": "allow",
        "manage_workspace_users_roles": "deny",
        "project_manage": "deny",
        "project_types_manage": "deny",
        "comment_collaborate": "allow",
        "moderate_review": "deny",
        "audit_logs_tenant": "deny",
        "audit_logs_platform": "deny",
        "integrations_api_tokens": "deny",
        "billing_subscription": "deny",
        "system_maintenance": "deny",
        "data_export_portability": "deny",
        "data_deletion_tenant": "deny",
        "read_public_content": "allow",
        "compliance_override_access": "deny",
        "token_scopes_manage": "deny"
      }
    },
    {
      "id": 8,
      "key": "guest",
      "label": "Guest",
      "level": 800,
      "scope": "tenant",
      "description": "Temporary/external viewer; minimal access.",
      "capabilities": {
        "platform_settings": "deny",
        "tenant_lifecycle": "deny",
        "system_health_monitoring": "deny",
        "view_tenant_metadata": "consent",
        "aggregated_analytics": "deny",
        "view_member_identities": "deny",
        "view_content_private": "deny",
        "modify_content": "deny",
        "access_private_workspaces": "deny",
        "cross_tenant_access": "deny",
        "manage_workspace_users_roles": "deny",
        "project_manage": "deny",
        "project_types_manage": "deny",
        "comment_collaborate": "consent",
        "moderate_review": "deny",
        "audit_logs_tenant": "deny",
        "audit_logs_platform": "deny",
        "integrations_api_tokens": "deny",
        "billing_subscription": "deny",
        "system_maintenance": "deny",
        "data_export_portability": "deny",
        "data_deletion_tenant": "deny",
        "read_public_content": "allow",
        "compliance_override_access": "deny",
        "token_scopes_manage": "deny"
      }
    },
    {
      "id": 9,
      "key": "automation_bot",
      "label": "Automation / Bot",
      "level": 950,
      "scope": "service",
      "description": "Non-human principal; all access must be token-scoped and least-privilege.",
      "capabilities": {
        "platform_settings": "deny",
        "tenant_lifecycle": "deny",
        "system_health_monitoring": "scoped",
        "view_tenant_metadata": "scoped",
        "aggregated_analytics": "scoped",
        "view_member_identities": "deny",
        "view_content_private": "scoped",
        "modify_content": "scoped",
        "access_private_workspaces": "scoped",
        "cross_tenant_access": "deny",
        "manage_workspace_users_roles": "deny",
        "project_manage": "scoped",
        "project_types_manage": "deny",
        "comment_collaborate": "deny",
        "moderate_review": "deny",
        "audit_logs_tenant": "scoped",
        "audit_logs_platform": "deny",
        "integrations_api_tokens": "scoped",
        "billing_subscription": "deny",
        "system_maintenance": "scoped",
        "data_export_portability": "scoped",
        "data_deletion_tenant": "deny",
        "read_public_content": "allow",
        "compliance_override_access": "deny",
        "token_scopes_manage": "scoped"
      }
    }
  ]
}

4. PostgreSQL DDL

-- =========================================================
-- EXTENSIONS
-- =========================================================
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "citext";

-- =========================================================
-- ENUMS
-- =========================================================
CREATE TYPE role_scope                AS ENUM ('global', 'tenant', 'service');
CREATE TYPE user_type                 AS ENUM ('human', 'bot');
CREATE TYPE workspace_type            AS ENUM ('organization', 'personal');
CREATE TYPE membership_status         AS ENUM ('active', 'invited', 'suspended');
CREATE TYPE capability_value          AS ENUM ('allow', 'deny', 'consent', 'compliance', 'scoped', 'anonymized');
CREATE TYPE principal_type            AS ENUM ('user', 'membership'); -- for tokens
CREATE TYPE project_type              AS ENUM ('workspace', 'gitcms', 'papertrail', 'docs', 'sheets', 'forms', 'kanban', 'other');
CREATE TYPE share_permission          AS ENUM ('view', 'comment', 'edit', 'publish', 'admin'); -- for links
CREATE TYPE audit_channel             AS ENUM ('platform', 'tenant', 'project');
CREATE TYPE consent_subject_type      AS ENUM ('user', 'membership', 'project', 'tenant');
CREATE TYPE compliance_reason         AS ENUM ('law_enforcement', 'legal_hold', 'data_export', 'incident_response', 'other');

-- =========================================================
-- USERS & IDENTITY
-- =========================================================
CREATE TABLE users (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_type         user_type NOT NULL DEFAULT 'human',
  username          CITEXT UNIQUE,
  display_name      TEXT,
  picture_url       TEXT,
  is_active         BOOLEAN NOT NULL DEFAULT TRUE,
  primary_email_id  UUID,                         -- set after first email verified
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  CONSTRAINT users_primary_email_fk
    FOREIGN KEY (primary_email_id) REFERENCES user_emails(id) DEFERRABLE INITIALLY DEFERRED
);

CREATE TABLE user_emails (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id           UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  email             CITEXT NOT NULL,
  is_verified       BOOLEAN NOT NULL DEFAULT FALSE,
  is_primary        BOOLEAN NOT NULL DEFAULT FALSE,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  verified_at       TIMESTAMPTZ,
  UNIQUE (email),
  UNIQUE (user_id, email)
);

-- Optional: per-org aliases (if an org assigns an alias email to the same user)
CREATE TABLE user_email_aliases (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id           UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  tenant_id         UUID NOT NULL,
  alias             CITEXT NOT NULL,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, alias)
);

CREATE TABLE user_profiles (
  user_id           UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  locale            TEXT,
  timezone          TEXT,
  meta              JSONB NOT NULL DEFAULT '{}'
);

-- =========================================================
-- WORKSPACES / TENANTS
-- =========================================================
CREATE TABLE tenants (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug              CITEXT UNIQUE NOT NULL,
  name              TEXT NOT NULL,
  workspace_type    workspace_type NOT NULL DEFAULT 'organization',
  plan              TEXT DEFAULT 'free',
  region            TEXT,                          -- e.g., 'eu-central-1'
  is_active         BOOLEAN NOT NULL DEFAULT TRUE,
  created_by        UUID REFERENCES users(id),
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX tenants_active_idx ON tenants(is_active);

-- =========================================================
-- ROLES & CAPABILITIES (privacy semantics)
-- =========================================================
CREATE TABLE roles (
  id                SMALLINT PRIMARY KEY,          -- 0..999
  key               TEXT UNIQUE NOT NULL,          -- 'platform_admin', ...
  label             TEXT NOT NULL,
  level             INTEGER NOT NULL CHECK (level BETWEEN 0 AND 999),
  scope             role_scope NOT NULL,           -- global | tenant | service
  description       TEXT NOT NULL,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE capabilities (
  key               TEXT PRIMARY KEY,
  description       TEXT NOT NULL
);

-- capability_value follows your v2 semantics
CREATE TABLE role_capabilities (
  role_id           SMALLINT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  capability_key    TEXT NOT NULL REFERENCES capabilities(key) ON DELETE CASCADE,
  value             capability_value NOT NULL DEFAULT 'deny',
  PRIMARY KEY (role_id, capability_key)
);

-- =========================================================
-- MEMBERSHIPS (user in tenant) + ROLE ASSIGNMENTS
-- =========================================================
CREATE TABLE memberships (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id           UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  tenant_id         UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  status            membership_status NOT NULL DEFAULT 'active',
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (user_id, tenant_id)
);
CREATE INDEX memberships_user_idx   ON memberships(user_id);
CREATE INDEX memberships_tenant_idx ON memberships(tenant_id);

CREATE TABLE membership_roles (
  membership_id     UUID NOT NULL REFERENCES memberships(id) ON DELETE CASCADE,
  role_id           SMALLINT NOT NULL REFERENCES roles(id) ON DELETE RESTRICT,
  PRIMARY KEY (membership_id, role_id)
);

-- Global roles (platform scope)
CREATE TABLE user_global_roles (
  user_id           UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role_id           SMALLINT NOT NULL REFERENCES roles(id) ON DELETE RESTRICT,
  PRIMARY KEY (user_id, role_id),
  CONSTRAINT ugr_role_scope_global CHECK (
    (SELECT scope FROM roles WHERE roles.id = role_id) = 'global'
  )
);

-- =========================================================
-- CONSENT LEDGER & COMPLIANCE OVERRIDES
-- =========================================================
-- Records explicit grant of consent by a controller (tenant) or subject (user).
CREATE TABLE consent_ledger (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id         UUID REFERENCES tenants(id) ON DELETE CASCADE,
  subject_type      consent_subject_type NOT NULL,   -- user | membership | project | tenant
  subject_id        UUID NOT NULL,                   -- FK semantic (not enforced across multiple tables)
  capability_key    TEXT NOT NULL REFERENCES capabilities(key) ON DELETE RESTRICT,
  granted_by_user   UUID REFERENCES users(id) ON DELETE SET NULL,
  reason            TEXT,
  starts_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at        TIMESTAMPTZ,                     -- must be time-boxed ideally
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX consent_ledger_tenant_idx   ON consent_ledger(tenant_id, capability_key);
CREATE INDEX consent_ledger_subject_idx  ON consent_ledger(subject_id, subject_type);
CREATE INDEX consent_ledger_expiry_idx   ON consent_ledger(expires_at);

-- Compliance override requires heavy auditing and justification
CREATE TABLE compliance_overrides (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id         UUID REFERENCES tenants(id) ON DELETE CASCADE,
  actor_user_id     UUID REFERENCES users(id) ON DELETE SET NULL,   -- typically Platform Admin
  reason_code       compliance_reason NOT NULL,
  reason_detail     TEXT,
  capability_key    TEXT NOT NULL REFERENCES capabilities(key) ON DELETE RESTRICT,
  scope_filter      JSONB NOT NULL DEFAULT '{}'::jsonb,             -- e.g., {"project_id": "..."}
  starts_at         TIMESTAMPTZ NOT NULL DEFAULT now(),
  expires_at        TIMESTAMPTZ NOT NULL,                           -- must be time-boxed
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX compliance_overrides_tenant_idx ON compliance_overrides(tenant_id, capability_key, expires_at);

-- =========================================================
-- API TOKENS (scoped; user or membership principals)
-- =========================================================
CREATE TABLE api_tokens (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  principal_type    principal_type NOT NULL,         -- user or membership
  principal_id      UUID NOT NULL,                   -- users.id or memberships.id
  tenant_id         UUID,                            -- populated if principal=membership (redundant but helpful)
  name              TEXT NOT NULL,
  hash              TEXT NOT NULL,                   -- hashed token
  scopes            JSONB NOT NULL DEFAULT '[]',     -- e.g., ["content:read","backup:run"]
  claims            JSONB NOT NULL DEFAULT '{}',     -- additional claims (capability gates)
  expires_at        TIMESTAMPTZ,
  last_used_at      TIMESTAMPTZ,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  CHECK ((tenant_id IS NULL) OR (principal_type = 'membership'))
);
CREATE INDEX api_tokens_principal_idx ON api_tokens(principal_type, principal_id);
CREATE INDEX api_tokens_tenant_idx    ON api_tokens(tenant_id);
CREATE INDEX api_tokens_last_used_idx ON api_tokens(last_used_at);

-- =========================================================
-- AUDIT LOGS (PII-safe; event metadata only)
-- =========================================================
CREATE TABLE audit_logs (
  id                BIGSERIAL PRIMARY KEY,
  channel           audit_channel NOT NULL,           -- platform | tenant | project
  tenant_id         UUID,
  project_id        UUID,
  actor_user_id     UUID REFERENCES users(id) ON DELETE SET NULL,
  actor_token_id    UUID REFERENCES api_tokens(id) ON DELETE SET NULL,
  action            TEXT NOT NULL,                    -- e.g., 'content.publish'
  target_table      TEXT,
  target_id         TEXT,
  ip                INET,
  user_agent        TEXT,
  metadata          JSONB NOT NULL DEFAULT '{}',
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX audit_logs_tenant_time_idx  ON audit_logs(tenant_id, created_at DESC);
CREATE INDEX audit_logs_actor_time_idx   ON audit_logs(actor_user_id, created_at DESC);
CREATE INDEX audit_logs_channel_time_idx ON audit_logs(channel, created_at DESC);

-- =========================================================
-- PROJECTS & PERMISSIONS (workspace folders + apps)
-- =========================================================
CREATE TABLE projects (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id         UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  parent_id         UUID REFERENCES projects(id) ON DELETE CASCADE, -- folders/hierarchy
  type              project_type NOT NULL,
  key               CITEXT,                      -- optional short code (e.g., "OPS", "SITE")
  name              TEXT NOT NULL,
  description       TEXT,
  is_archived       BOOLEAN NOT NULL DEFAULT FALSE,
  created_by        UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, key)
);
CREATE INDEX projects_tenant_idx ON projects(tenant_id);

-- Optional: per-project role overrides (default is tenant membership roles)
CREATE TABLE project_memberships (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  project_id        UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  membership_id     UUID NOT NULL REFERENCES memberships(id) ON DELETE CASCADE,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (project_id, membership_id)
);

CREATE TABLE project_membership_roles (
  project_membership_id UUID NOT NULL REFERENCES project_memberships(id) ON DELETE CASCADE,
  role_id           SMALLINT NOT NULL REFERENCES roles(id) ON DELETE RESTRICT,
  PRIMARY KEY (project_membership_id, role_id)
);

-- =========================================================
-- SHARING (PUBLIC/EXTERNAL LINKS)
-- =========================================================
CREATE TABLE share_links (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id         UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  project_id        UUID REFERENCES projects(id) ON DELETE CASCADE,
  target_table      TEXT,                            -- e.g., 'documents'
  target_id         UUID,                            -- referenced entity id (nullable if project-level)
  token_hash        TEXT NOT NULL,                   -- hashed secret
  permission        share_permission NOT NULL DEFAULT 'view',
  expires_at        TIMESTAMPTZ,
  created_by        UUID REFERENCES users(id) ON DELETE SET NULL,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX share_links_project_idx ON share_links(project_id);
CREATE INDEX share_links_target_idx  ON share_links(target_table, target_id);

-- =========================================================
-- VIEWS (effective roles & privacy-aware projections)
-- =========================================================
-- Effective tenant roles for a user (via membership)
CREATE VIEW v_effective_membership_roles AS
SELECT
  m.user_id,
  m.tenant_id,
  r.id     AS role_id,
  r.key    AS role_key,
  r.level  AS role_level,
  r.scope  AS role_scope
FROM memberships m
JOIN membership_roles mr ON mr.membership_id = m.id
JOIN roles r             ON r.id = mr.role_id
WHERE m.status = 'active';

-- Effective project roles (if project override exists; else fall back to tenant roles at query time)
CREATE VIEW v_effective_project_roles AS
SELECT
  pm.membership_id,
  pm.project_id,
  r.id     AS role_id,
  r.key    AS role_key,
  r.level  AS role_level
FROM project_memberships pm
JOIN project_membership_roles pmr ON pmr.project_membership_id = pm.id
JOIN roles r ON r.id = pmr.role_id;

-- Platform/global roles for a user
CREATE VIEW v_effective_global_roles AS
SELECT
  ugr.user_id,
  r.id     AS role_id,
  r.key    AS role_key,
  r.level  AS role_level
FROM user_global_roles ugr
JOIN roles r ON r.id = ugr.role_id
WHERE r.scope = 'global';

-- Consent currently in force (not expired)
CREATE VIEW v_active_consents AS
SELECT *
FROM consent_ledger
WHERE expires_at IS NULL OR expires_at > now();

-- Compliance overrides currently in force
CREATE VIEW v_active_compliance_overrides AS
SELECT *
FROM compliance_overrides
WHERE expires_at > now();

-- =========================================================
-- INDEXING ESSENTIALS (already sprinkled above)
-- =========================================================

4.1 Notes & Rationale

  • Unified Identity, Multi-Email: users ←→ user_emails (verified; primary). You can map org aliases via user_email_aliases without duplicating users.
  • Workspaces / Tenants: tenants.workspace_type supports organization and personal workspaces (single user, shareable later).
  • RBAC with Privacy Semantics: roles + capabilities + role_capabilities.value (enum) encode allow/deny/consent/compliance/scoped/anonymized.
  • Consent & Compliance:
    • consent_ledger records explicit, time-boxed consent (by tenant admin or subject).
    • compliance_overrides is heavy-duty: reason code, JSON scope filters, hard expiry—must be audited.
  • Tokens (Bots & Integrations): api_tokens with principal (user or membership) + scopes/claims for least-privilege automation. Tie a bot to a tenant by creating a bot users row, then a memberships row, and mint a token with tenant scope.
  • Projects & Project Types: projects supports foldering (parent_id), types (GitCMS, PaperTrail, Docs, Sheets, Forms, Kanban, Workspace). Optional per-project overrides via project_memberships + project_membership_roles.
  • Share Links: share_links give granular view/comment/edit/publish/admin access with tokenized, time-boxed links (no account needed).
  • Auditing: audit_logs channelized (platform/tenant/project). Avoid storing PII in logs; keep IDs + metadata.

4.2 RLS (Row Level Security) — high-level plan (add later)

Enable RLS on tenant-scoped tables (memberships, projects, content tables, etc.). Use JWT claims or SET LOCAL app.current_principal to evaluate:

  • tenant access via membership
  • project overrides via v_effective_project_roles
  • capability resolution that respects consent_ledger and compliance_overrides
  • platform roles can never bypass RLS except when a compliance override record exists (checked by a policy function).
Clone this wiki locally