Deep-Dive Review: Identity & Tenancy + Authorization & Access Control

Generated: 2025-10-24 Author: Claude Code Review Agent Focus: Comprehensive analysis of authentication, authorization, multi-tenancy, and RBAC implementation

Executive Summary

This document provides a comprehensive analysis of The Villa Life (TVL) platform's Identity, Tenancy, and Authorization domains based on three primary specification documents:

Platform Specification (TVL-Platform-Specification-2025-10-21.md)
MVP PRD (tvl-mvp-v0-prd.updated.md)
Data Model Specification (TVL Data and Domain Model Specification 2025-10-21.md)

Key Findings

✅ Strengths:

Comprehensive multi-tenant architecture with clear Org/Account hierarchy
Google SSO with OIDC + PKCE for secure authentication
Well-designed RBAC system with future ABAC support
Row-Level Security (RLS) scaffolded for defense-in-depth
Detailed session management with server-side storage

⚠️ Critical Gaps Identified:

account_id enforcement inconsistency across domains
Email verification not specified for MVP
Identity provider flexibility limited to Google
Permission seeding incomplete in database schema
Session rotation on privilege changes not fully specified

1. Multi-Tenancy Architecture

1.1 Org/Account/User Hierarchy

The platform implements a three-tier tenancy model:

Organization (Tenant Boundary)
  └── Account (Sub-tenant / Actor)
       └── User (Person Identity)
            └── Membership (User → Org[/Account] + Role)

Organization (Org)

Purpose: Top-level tenant boundary and isolation scope
Key Attributes:
- id (UUID) - Primary identifier
- name (string) - Display name
- slug (string) - URL-friendly, globally unique
- owner_id (UUID) - Creator/owner reference
- plan (enum) - Subscription tier
- settings (jsonb) - Tenant-specific configuration
Lifecycle: Never hard-deleted (soft delete via deleted_at for audit)
Constraints: Slug must be globally unique and immutable

Source: Platform Spec lines 102-107, 189-210; MVP PRD lines 105-120

Account

Purpose: Operational entity within an Org (owner, manager, marketplace, internal ops)
Key Attributes:
- id (UUID) - Primary identifier
- org_id (UUID) - Parent organization FK
- type (enum) - owner|manager|marketplace|internal
- is_default (boolean) - Enforced single default per Org
- name (text) - Display name
Business Rules:
- ONE default Account auto-created on Org creation
- Unique constraint: (org_id, is_default) where is_default = true
- Cannot delete default Account
- All actor-owned data MUST include (org_id, account_id)

Critical Finding: The MVP PRD and Database Spec show inconsistent account_id enforcement.

Source: Platform Spec lines 199-210, 262-269; Data Model Spec lines 1500-1700

User

Purpose: Global individual identity (email-based)
Key Attributes:
- id (UUID) - Primary identifier
- email (citext) - Unique, case-insensitive
- org_id (UUID, nullable) - Reserved for future multi-tenant support
- display_name (text) - Human-readable name
- auth_provider (enum) - Currently 'google' only
- auth_provider_id (text) - Google 'sub' claim
- is_active (boolean) - Soft deactivation flag
Lifecycle: Never hard-deleted (use deleted_at or is_active=false)
Multi-Org Support: User can belong to multiple Orgs via Memberships

Source: Identity Spec lines 85-101; DB Spec lines 106-116

1.2 Default Account Pattern

Auto-Creation Logic

When a new Organization is created:

BEGIN;
  -- 1. Create Organization
  INSERT INTO organizations (name, slug, owner_id, plan)
  VALUES ($name, $slug, $user_id, 'free')
  RETURNING id INTO org_id;

  -- 2. Create Default Account
  INSERT INTO accounts (org_id, type, is_default, name)
  VALUES (org_id, 'owner', true, CONCAT($name, ' (Default)'))
  RETURNING id INTO account_id;

  -- 3. Create Owner Membership (Org-wide)
  INSERT INTO organization_members (organization_id, user_id, role_id, account_id)
  VALUES (org_id, $user_id, 'role_owner', NULL); -- NULL = Org-wide access
COMMIT;

Key Points:

Default Account naming convention: {Org.name} (Default)
Account type set to owner or internal (for TVL Ops Org)
Org creator automatically gets Org-wide admin membership
account_id = NULL in Membership → Org-wide access (all Accounts)

Source: Platform Spec lines 262-293; Identity Spec lines 405-413

is_default Constraint

-- Enforce single default Account per Org
ALTER TABLE accounts
  ADD CONSTRAINT accounts_single_default_per_org
  UNIQUE (org_id, is_default)
  WHERE is_default = true;

Purpose: Prevents multiple default Accounts, ensures referential integrity for new resources

Source: Platform Spec line 209; Data Model Spec lines 1750-1850

1.3 Membership Scoping

Memberships define where (Org/Account scope) and what (Role permissions) a User can do.

Org-Wide Membership

account_id = NULL
Grants access to ALL Accounts within the Org
Typical for: Owner, Admin, Ops roles

Account-Scoped Membership

account_id = {specific_account_id}
Restricts access to ONLY that Account's resources
Typical for: Manager, Staff roles managing specific properties

Schema Definition

CREATE TABLE organization_members (
  id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id  UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  user_id          UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role_id          UUID NOT NULL REFERENCES roles(id),
  account_id       UUID REFERENCES accounts(id), -- NULL = Org-wide
  property_ids     UUID[] DEFAULT NULL, -- Property-level access (MVP extension)
  invited_by       UUID REFERENCES users(id),
  joined_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (organization_id, user_id) -- One membership per User per Org
);

Property-Level Access: The Identity Spec adds property_ids array for fine-grained property access within an Account. This is an extension not mentioned in the Platform Spec.

Conflict: Platform Spec focuses on Account-level scoping; Identity Spec adds property-level. Need to reconcile.

Source: Identity Spec lines 121-138, 895-913; Platform Spec lines 221-243

2. Role-Based Access Control (RBAC)

2.1 Role Definitions

Platform Specification Roles

The Platform Spec defines 5 core roles:

Role	Scope	Key Permissions
admin	Org-wide	All permissions (god mode)
ops	Org-wide	All except `account.set_default`
owner_admin	Account-scoped	Manage Spaces, Bookings, Pricing; read Accounts
manager	Account-scoped	Booking & operations within Account
viewer	Org or Account	Read-only access

Source: Platform Spec lines 193-201, 433-462

MVP PRD Roles (Simplified for Operator UX)

The MVP PRD defines 4 roles with friendlier names:

MVP Role	Maps To Platform Role	Description
Owner	`admin` + `owner_admin`	Full org/account management
ChannelPublisher	`ops` + `channel.manage`	Manage channels and publishing
ContentManager	`manager` + write permissions	Manage Units, Spaces, media
Viewer	`viewer`	Read-only access

Critical Finding: Role naming inconsistency between Platform Spec and MVP PRD.

Recommendation: Use Platform role names internally (admin, ops, etc.) and map to friendly names in UI.

Source: MVP PRD lines 54-64

Identity Spec Roles (Most Detailed)

The Identity Spec provides the most comprehensive role definitions:

Owner - Full control over organization
- All permissions (properties, units, bookings, payments, settings, users)
- Cannot be removed from organization
- Can transfer ownership to another member
Manager - Property and booking management
- Create/edit properties and units
- Manage availability and pricing
- View and manage bookings
- View financial reports (no payment actions)
- Can be scoped to specific properties
Staff - Day-to-day operations
- View properties and units (read-only)
- Manage availability (create blocks)
- View bookings (read-only)
- No access to financials or settings
- Can be scoped to specific properties
Finance - Financial operations
- View all financial transactions
- Process refunds
- Generate financial reports
- No access to property management or bookings
Admin - Platform administrator (TVL internal)
- Access to all organizations (super-user)
- System configuration
- User impersonation for support

Source: Identity Spec lines 150-181

2.2 Permission Registry

Core Permission Structure

{
  "resource": "properties",
  "actions": ["read", "create", "update", "delete"]
}

Platform Spec Permission Seeds

From Platform Spec lines 437-462:

Account Permissions:

account.read - View account details
account.write - Modify account settings
account.invite - Invite users to account
account.set_default - Mark account as default (admin only)
account.transfer_spaces - Move spaces between accounts

Space/Unit Permissions:

space.read - View spaces
space.write - Create/modify spaces
media.write - Manage property media

Pricing Permissions:

pricing.read - View pricing rules
pricing.edit - Modify pricing

Rules Permissions:

rules.read - View business rules
rules.edit - Modify business rules

Booking Permissions:

booking.read - View bookings
booking.manage - Create/modify bookings

Identity Spec Permission Resources (More Granular)

From Identity Spec lines 193-209:

Resources:

properties - Property CRUD operations
units - Unit CRUD operations
availability - Calendar and block management
pricing - Price rules and quotes
bookings - Reservation management
payments - Payment processing and refunds
financials - Transaction reports and payouts
users - Team member management
settings - Organization configuration

Actions:

read - View resource
create - Create new resource
update - Modify existing resource
delete - Remove resource

Complete Permission Seeding

Gap Identified: Neither spec provides complete seeding SQL. Here's a recommended complete set:

-- Complete Permission Seeds (Recommended)
INSERT INTO permissions (resource, action, description) VALUES
  -- Account Management
  ('account', 'read', 'View account details'),
  ('account', 'write', 'Modify account settings'),
  ('account', 'invite', 'Invite users to account'),
  ('account', 'set_default', 'Mark account as default'),
  ('account', 'transfer_spaces', 'Move spaces between accounts'),

  -- Space/Property Management
  ('space', 'read', 'View spaces'),
  ('space', 'create', 'Create new spaces'),
  ('space', 'update', 'Modify existing spaces'),
  ('space', 'delete', 'Remove spaces'),

  -- Unit Management
  ('unit', 'read', 'View units'),
  ('unit', 'create', 'Create new units'),
  ('unit', 'update', 'Modify existing units'),
  ('unit', 'delete', 'Remove units'),
  ('unit', 'write', 'Alias for create+update (backward compat)'),

  -- Media Management
  ('media', 'read', 'View media assets'),
  ('media', 'write', 'Upload/modify media'),
  ('media', 'delete', 'Remove media'),

  -- Availability Management
  ('availability', 'read', 'View calendars and blocks'),
  ('availability', 'create', 'Create blocks'),
  ('availability', 'update', 'Modify blocks'),
  ('availability', 'delete', 'Remove blocks'),

  -- Pricing Management
  ('pricing', 'read', 'View pricing rules'),
  ('pricing', 'create', 'Create pricing rules'),
  ('pricing', 'update', 'Modify pricing rules (alias: edit)'),
  ('pricing', 'delete', 'Remove pricing rules'),

  -- Booking Management
  ('booking', 'read', 'View bookings'),
  ('booking', 'create', 'Create bookings'),
  ('booking', 'update', 'Modify bookings'),
  ('booking', 'delete', 'Cancel bookings'),
  ('booking', 'manage', 'Full booking lifecycle management'),

  -- Payment Management
  ('payment', 'read', 'View payments'),
  ('payment', 'create', 'Process payments'),
  ('payment', 'update', 'Modify payment details'),
  ('payment', 'delete', 'Refund payments'),

  -- Financial Reports
  ('financials', 'read', 'View financial reports and transactions'),

  -- User Management
  ('users', 'read', 'View team members'),
  ('users', 'create', 'Invite new users'),
  ('users', 'update', 'Modify user roles and access'),
  ('users', 'delete', 'Remove team members'),

  -- Settings Management
  ('settings', 'read', 'View organization settings'),
  ('settings', 'update', 'Modify organization settings'),

  -- Channel Management (MVP PRD)
  ('channel', 'read', 'View channel configurations'),
  ('channel', 'manage', 'Manage channel targets and sync');

Source: Platform Spec lines 437-462; Identity Spec lines 186-209; MVP PRD lines 54-64

2.3 RolePermission Mapping

Deny-Overrides-Allow Semantics

CREATE TABLE role_permissions (
  role_id        UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  permission_id  UUID NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
  effect         TEXT NOT NULL CHECK (effect IN ('allow', 'deny')),
  created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (role_id, permission_id)
);

Evaluation Logic:

Fetch all RolePermissions for User's Role
If ANY permission has effect='deny' → DENY
Else if ANY permission has effect='allow' → ALLOW
Else → DENY (default deny)

Source: Platform Spec lines 400-406; Identity Spec lines 840-891

Role Permission Matrices

Admin Role (Org-wide):

{
  "role": "admin",
  "permissions": [
    {"resource": "*", "actions": ["*"], "effect": "allow"}
  ]
}

Ops Role (Org-wide):

{
  "role": "ops",
  "permissions": [
    {"resource": "*", "actions": ["*"], "effect": "allow"},
    {"resource": "account", "actions": ["set_default"], "effect": "deny"}
  ]
}

Owner_Admin Role (Account-scoped):

{
  "role": "owner_admin",
  "permissions": [
    {"resource": "space", "actions": ["read", "create", "update"], "effect": "allow"},
    {"resource": "unit", "actions": ["read", "create", "update"], "effect": "allow"},
    {"resource": "pricing", "actions": ["read", "create", "update"], "effect": "allow"},
    {"resource": "booking", "actions": ["read", "manage"], "effect": "allow"},
    {"resource": "account", "actions": ["read"], "effect": "allow"}
  ]
}

Manager Role (Account-scoped):

{
  "role": "manager",
  "permissions": [
    {"resource": "space", "actions": ["read"], "effect": "allow"},
    {"resource": "unit", "actions": ["read"], "effect": "allow"},
    {"resource": "availability", "actions": ["read", "create", "update"], "effect": "allow"},
    {"resource": "booking", "actions": ["read", "manage"], "effect": "allow"}
  ]
}

Viewer Role:

{
  "role": "viewer",
  "permissions": [
    {"resource": "space", "actions": ["read"], "effect": "allow"},
    {"resource": "unit", "actions": ["read"], "effect": "allow"},
    {"resource": "availability", "actions": ["read"], "effect": "allow"},
    {"resource": "booking", "actions": ["read"], "effect": "allow"},
    {"resource": "pricing", "actions": ["read"], "effect": "allow"}
  ]
}

Source: Platform Spec lines 433-462; Identity Spec lines 859-891

2.4 Policy Rules (Future ABAC)

Schema Definition

CREATE TABLE policy_rules (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id        UUID NOT NULL REFERENCES organizations(id),
  name          TEXT NOT NULL,
  description   TEXT,
  subject       JSONB NOT NULL, -- {role, user_id, account_id}
  resource      JSONB NOT NULL, -- {type, id, attributes}
  action        TEXT NOT NULL,
  condition     JSONB, -- {channel, region, date_range, etc.}
  effect        TEXT NOT NULL CHECK (effect IN ('allow', 'deny')),
  priority      INT NOT NULL DEFAULT 100,
  is_active     BOOLEAN NOT NULL DEFAULT false, -- Disabled for MVP
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Purpose: Enable attribute-based access control (ABAC) for contextual permissions.

Example Use Cases:

Channel-specific permissions: "Allow booking.create only for channel=direct"
Region-based access: "Allow space.read only for region=EU"
Date-based rules: "Allow pricing.edit only during budget planning period"

MVP Status: Schema present, is_active=false by default. Not evaluated in MVP.

Future Activation:

Enable is_active=true on selected rules
Implement policy evaluation engine
Cache evaluated policies per user session

Source: Platform Spec lines 407-414, 479-486

3. Authentication

3.1 Google OIDC with PKCE

Flow Diagram

User → Frontend → Backend → Google OAuth → Backend → Frontend → User
                                    ↓
                               User Profile
                                    ↓
                            Session Created

Detailed Flow

1. Login Initiation (POST /auth/login)

POST /auth/login
Content-Type: application/json

{
  "redirect_uri": "https://app.tvl.com/dashboard"
}

Backend Actions:

Generate state parameter (random 32-byte hex)
Generate code_verifier (random 43-128 chars, base64url)
Compute code_challenge = base64url(SHA256(code_verifier))
Store (state, code_verifier) in Redis with 5-minute TTL
Build Google authorization URL

Response:

HTTP/1.1 302 Found
Location: https://accounts.google.com/o/oauth2/v2/auth?
  client_id=YOUR_CLIENT_ID&
  redirect_uri=https://app.tvl.com/auth/callback&
  response_type=code&
  scope=openid%20email%20profile&
  state=abc123...&
  code_challenge=xyz789...&
  code_challenge_method=S256

2. User Approval on Google

User logs into Google
Google shows consent screen
User approves

3. Callback (POST /auth/callback)

POST /auth/callback
Content-Type: application/json

{
  "code": "4/0AY0e-g7...",
  "state": "abc123..."
}

Backend Actions:

// 1. Validate state parameter
const stored_verifier = await redis.get(`auth:state:${state}`);
if (!stored_verifier) throw new Error('Invalid or expired state');

// 2. Exchange code for tokens
const tokenResponse = await fetch('https://oauth2.googleapis.com/token', {
  method: 'POST',
  body: JSON.stringify({
    code: code,
    client_id: CLIENT_ID,
    client_secret: CLIENT_SECRET,
    redirect_uri: REDIRECT_URI,
    grant_type: 'authorization_code',
    code_verifier: stored_verifier
  })
});

const { id_token, access_token, refresh_token } = await tokenResponse.json();

// 3. Verify ID token (JWT signature + claims)
const decoded = jwt.verify(id_token, GOOGLE_PUBLIC_KEYS);
const { sub, email, name, picture } = decoded;

// 4. Upsert user
const user = await db.query(`
  INSERT INTO users (email, display_name, avatar_url, auth_provider, auth_provider_id)
  VALUES ($1, $2, $3, 'google', $4)
  ON CONFLICT (email) DO UPDATE
  SET display_name = EXCLUDED.display_name,
      avatar_url = EXCLUDED.avatar_url,
      last_sign_in_at = NOW()
  RETURNING *
`, [email, name, picture, sub]);

// 5. Create session
const session = await db.query(`
  INSERT INTO sessions (user_id, expires_at, user_agent, ip_address)
  VALUES ($1, NOW() + INTERVAL '24 hours', $2, $3)
  RETURNING id
`, [user.id, req.headers['user-agent'], req.ip]);

// 6. Generate JWT for client
const jwt_token = jwt.sign(
  {
    user_id: user.id,
    session_id: session.id,
    email: user.email
  },
  JWT_SECRET,
  { expiresIn: '24h' }
);

// 7. Store session in Redis
await redis.setex(`session:${session.id}`, 86400, JSON.stringify({
  user_id: user.id,
  email: user.email,
  created_at: Date.now()
}));

return {
  user: user,
  session: { jwt: jwt_token, expires_at: session.expires_at },
  organizations: await getUserOrgs(user.id)
};

Source: MVP PRD lines 54-60; Identity Spec lines 270-333

3.2 Session Management

Server-Side Sessions

Storage: Redis + PostgreSQL

Session Record (PostgreSQL):

CREATE TABLE sessions (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id       UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  issued_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at    TIMESTAMPTZ NOT NULL,
  revoked_at    TIMESTAMPTZ,
  user_agent    TEXT,
  ip_address    INET,
  CONSTRAINT valid_expiry CHECK (expires_at > issued_at)
);

-- Cleanup index (partial for efficiency)
CREATE INDEX sessions_exp_idx
  ON sessions (expires_at)
  WHERE revoked_at IS NULL;

Session Cache (Redis):

// Key: session:{session_id}
// TTL: 24 hours (86400 seconds)
// Value: JSON
{
  "user_id": "uuid",
  "email": "user@example.com",
  "org_id": "uuid",
  "role": "owner",
  "permissions": [...],
  "account_access": null, // null = all accounts
  "created_at": 1729771234567
}

Session Validation:

async function validateSession(session_id) {
  // 1. Check Redis cache first
  const cached = await redis.get(`session:${session_id}`);
  if (cached) {
    return JSON.parse(cached);
  }

  // 2. Check PostgreSQL
  const session = await db.query(`
    SELECT s.*, u.email, u.org_id, m.role_id, m.account_id
    FROM sessions s
    JOIN users u ON u.id = s.user_id
    LEFT JOIN organization_members m ON m.user_id = u.id
    WHERE s.id = $1
      AND s.expires_at > NOW()
      AND s.revoked_at IS NULL
  `, [session_id]);

  if (!session) {
    throw new UnauthorizedError('Session expired or invalid');
  }

  // 3. Load permissions
  const permissions = await loadUserPermissions(session.user_id, session.org_id);

  // 4. Cache in Redis
  const cache_value = {
    user_id: session.user_id,
    email: session.email,
    org_id: session.org_id,
    role: session.role_id,
    permissions: permissions,
    account_access: session.account_id
  };

  await redis.setex(`session:${session_id}`, 86400, JSON.stringify(cache_value));

  return cache_value;
}

Source: MVP PRD lines 60-66; DB Spec lines 138-153; Identity Spec lines 259-265

res.cookie('session_id', session.id, {
  httpOnly: true,        // Prevent XSS access
  secure: true,          // HTTPS only
  sameSite: 'Lax',       // CSRF protection
  maxAge: 86400000,      // 24 hours in milliseconds
  domain: '.tvl.com',    // Share across subdomains
  path: '/'
});

Security Properties:

HttpOnly: Prevents JavaScript access (XSS mitigation)
Secure: Ensures transmission over HTTPS only
SameSite=Lax: Allows top-level navigation, blocks CSRF
maxAge: Explicit expiration (24 hours)
domain: Enables subdomain sharing (app.tvl.com, api.tvl.com)

Session ID Rotation:

// Rotate session ID on privilege changes
async function rotateSession(old_session_id) {
  // 1. Load old session data
  const old_session = await validateSession(old_session_id);

  // 2. Create new session
  const new_session = await db.query(`
    INSERT INTO sessions (user_id, expires_at, user_agent, ip_address)
    VALUES ($1, NOW() + INTERVAL '24 hours', $2, $3)
    RETURNING id
  `, [old_session.user_id, req.headers['user-agent'], req.ip]);

  // 3. Revoke old session
  await db.query(`
    UPDATE sessions SET revoked_at = NOW() WHERE id = $1
  `, [old_session_id]);

  // 4. Update Redis
  await redis.del(`session:${old_session_id}`);
  await redis.setex(`session:${new_session.id}`, 86400, JSON.stringify(old_session));

  return new_session.id;
}

When to Rotate:

Role change
Account access change
Password change (future)
Privilege escalation

Source: MVP PRD lines 60, 183; Identity Spec lines 1266-1275

3.4 Email Verification

Gap Identified: No email verification flow specified in any document.

Current Behavior:

Google OIDC provides verified email from Google
Email assumed verified if from Google
No additional verification needed

Risk:

If future identity providers added (Microsoft, GitHub), need verification
Cannot verify email ownership for non-OIDC providers

Recommendation: Add email verification for non-OIDC providers:

ALTER TABLE users
  ADD COLUMN email_verified BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN email_verified_at TIMESTAMPTZ;

-- For Google OIDC users, mark as verified on creation
UPDATE users
SET email_verified = true, email_verified_at = created_at
WHERE auth_provider = 'google';

Verification Flow (Future):

User signs up with email/password
Send verification email with token
User clicks link → marks email_verified=true
Block sensitive actions until verified

Source: Gap analysis - not mentioned in any spec

4. Authorization Enforcement

4.1 account_id Enforcement

Critical Finding: Inconsistent account_id requirements across domains.

Platform Specification Requirements

From Platform Spec lines 87-97, 290-293:

"All actor-owned data → (Org, Account): required foreign keys for ownership and isolation." "Schema-level rules: All actor-owned tables (spaces, bookings, pricing, etc.) include both org_id and account_id FKs. Enforce account_id required on these tables to ensure traceable ownership."

Database Spec Implementation

Compliant Tables (account_id required):

❌ users - has org_id but no account_id
❌ properties - only org_id
❌ units - only org_id
❌ blocks - only implied via unit_id
❌ bookings - only org_id
❌ payments - only org_id

Non-Compliant Example:

-- Current schema (DB Spec line 164)
CREATE TABLE supply.spaces (
  id          uuid PRIMARY KEY,
  org_id      uuid,  -- Present
  name        text NOT NULL,
  ...
  -- MISSING: account_id uuid NOT NULL
);

Should Be:

-- Corrected schema
CREATE TABLE supply.spaces (
  id          uuid PRIMARY KEY,
  org_id      uuid NOT NULL REFERENCES organizations(id),
  account_id  uuid NOT NULL REFERENCES accounts(id),
  name        text NOT NULL,
  ...
);

Impact Analysis

Without account_id:

Cannot scope Spaces/Units to specific Accounts
Manager role cannot be limited to Account subset
Revenue attribution requires complex joins
Delegation logic breaks (no clear Account ownership)

Required Fixes:

Add account_id NOT NULL to all actor-owned tables:
- spaces
- units
- properties
- price_rules
- quotes
- holds
- bookings
- payments
- transactions
- channel_listings
Add foreign key constraint: REFERENCES accounts(id)
Add composite index: (org_id, account_id) for efficient tenant queries
Update RLS policies to include account_id checks
Migrate existing data to assign to default Account

Source: Platform Spec lines 87-97, 290-293; DB Spec lines 164-205; Gap analysis

4.2 Row-Level Security (RLS) Policies

Current Status

From MVP PRD line 27:

"Prepare for multi-tenant expansion (org hygiene + RLS policies scaffolded and disabled by default)."

Implication: RLS policies are defined but NOT ENABLED in MVP.

Sample RLS Policies

Users Table:

ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- Users can only see their own profile
CREATE POLICY users_select_own ON users
  FOR SELECT
  USING (id = current_setting('app.current_user_id')::uuid);

CREATE POLICY users_update_own ON users
  FOR UPDATE
  USING (id = current_setting('app.current_user_id')::uuid);

Organizations Table:

ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;

-- Users can only access orgs they're members of
CREATE POLICY organizations_select_member ON organizations
  FOR SELECT
  USING (
    id IN (
      SELECT organization_id
      FROM organization_members
      WHERE user_id = current_setting('app.current_user_id')::uuid
    )
  );

-- Only owners can update
CREATE POLICY organizations_update_owner ON organizations
  FOR UPDATE
  USING (owner_id = current_setting('app.current_user_id')::uuid);

Actor-Owned Tables (Spaces, Units, Bookings, etc.):

-- Generic pattern for actor-owned resources
ALTER TABLE {table} ENABLE ROW LEVEL SECURITY;

CREATE POLICY {table}_select_org_account ON {table}
  FOR SELECT
  USING (
    org_id = current_setting('app.current_org_id')::uuid
    AND (
      -- User has Org-wide access
      current_setting('app.current_account_id')::uuid IS NULL
      OR
      -- User has access to this specific Account
      account_id = current_setting('app.current_account_id')::uuid
    )
  );

CREATE POLICY {table}_insert_org_account ON {table}
  FOR INSERT
  WITH CHECK (
    org_id = current_setting('app.current_org_id')::uuid
    AND account_id = current_setting('app.current_account_id')::uuid
  );

Setting Session Context

Middleware:

async function setTenantContext(req, res, next) {
  const session = await validateSession(req.cookies.session_id);
  const member = await db.query(`
    SELECT organization_id, account_id
    FROM organization_members
    WHERE user_id = $1
    LIMIT 1
  `, [session.user_id]);

  await db.query(`
    SET LOCAL app.current_user_id = $1;
    SET LOCAL app.current_org_id = $2;
    SET LOCAL app.current_account_id = $3;
  `, [
    session.user_id,
    member.organization_id,
    member.account_id // NULL if Org-wide
  ]);

  next();
}

Source: Identity Spec lines 941-1007; Platform Spec lines 481-486

Performance Implications

Overhead per Query:

RLS check: ~10-20ms per query (Platform Spec line 1461)
Subquery evaluation (for Org membership checks)
Index lookups on (org_id, account_id)

Mitigation Strategies:

Materialized Membership Views:

CREATE MATERIALIZED VIEW user_org_access AS
SELECT user_id, organization_id, account_id
FROM organization_members
WHERE ended_at IS NULL;

CREATE UNIQUE INDEX ON user_org_access (user_id, organization_id);
REFRESH MATERIALIZED VIEW user_org_access; -- Run nightly or on changes

Denormalized Access Arrays:

-- Add to users table
ALTER TABLE users ADD COLUMN accessible_org_ids UUID[];

-- Update trigger on organization_members
CREATE TRIGGER update_user_access_cache
AFTER INSERT OR UPDATE OR DELETE ON organization_members
FOR EACH ROW EXECUTE FUNCTION refresh_user_access_cache();

Connection Pooling:
- Use PgBouncer in transaction mode
- Reduces session setup overhead
- Max 100 connections (DB Spec line 63)

Source: Schema Overview lines 343-347; Platform Spec line 1461

4.3 Permission Checking Workflow

Request Flow

Request → Middleware
Validate JWT → Extract user_id, session_id
Load Session from Redis/DB
Set Tenant Context (org_id, account_id)
Load Permissions (cached)
Check Resource + Action
Apply Property-Level Scope (if applicable)
Execute Query (RLS enforces isolation)
Return Response

Detailed Implementation

Step 1-2: Validate JWT

async function authenticate(req, res, next) {
  const token = req.headers.authorization?.replace('Bearer ', '');
  if (!token) throw new UnauthorizedError('No token provided');

  try {
    const decoded = jwt.verify(token, JWT_SECRET);
    req.user_id = decoded.user_id;
    req.session_id = decoded.session_id;
    next();
  } catch (err) {
    throw new UnauthorizedError('Invalid or expired token');
  }
}

Step 3-4: Load Session and Set Context

async function setContext(req, res, next) {
  const session = await validateSession(req.session_id);

  // Determine active organization
  const org_id = req.headers['x-organization-id'] || session.org_id;

  // Fetch membership for this org
  const member = await db.query(`
    SELECT m.organization_id, m.account_id, m.role_id, m.property_ids,
           r.name as role_name
    FROM organization_members m
    JOIN roles r ON r.id = m.role_id
    WHERE m.user_id = $1
      AND m.organization_id = $2
  `, [req.user_id, org_id]);

  if (!member) {
    throw new ForbiddenError('Not a member of this organization');
  }

  // Set database session variables
  await db.query(`
    SET LOCAL app.current_user_id = $1;
    SET LOCAL app.current_org_id = $2;
    SET LOCAL app.current_account_id = $3;
  `, [req.user_id, org_id, member.account_id]);

  // Attach to request
  req.auth_context = {
    user_id: req.user_id,
    org_id: org_id,
    account_id: member.account_id,
    role: member.role_name,
    property_access: member.property_ids
  };

  next();
}

Step 5-6: Load and Check Permissions

async function authorize(resource, action) {
  return async function(req, res, next) {
    const ctx = req.auth_context;

    // Cache key
    const cache_key = `permissions:${ctx.user_id}:${ctx.org_id}`;

    // Check cache
    let permissions = await redis.get(cache_key);
    if (!permissions) {
      // Load from DB
      permissions = await db.query(`
        SELECT DISTINCT p.resource, p.action, rp.effect
        FROM organization_members m
        JOIN role_permissions rp ON rp.role_id = m.role_id
        JOIN permissions p ON p.id = rp.permission_id
        WHERE m.user_id = $1
          AND m.organization_id = $2
      `, [ctx.user_id, ctx.org_id]);

      // Cache for 5 minutes
      await redis.setex(cache_key, 300, JSON.stringify(permissions));
    } else {
      permissions = JSON.parse(permissions);
    }

    // Check for deny rules first
    const deny = permissions.find(p =>
      (p.resource === resource || p.resource === '*') &&
      (p.action === action || p.action === '*') &&
      p.effect === 'deny'
    );

    if (deny) {
      throw new ForbiddenError(`Access denied: ${resource}.${action}`);
    }

    // Check for allow rules
    const allow = permissions.find(p =>
      (p.resource === resource || p.resource === '*') &&
      (p.action === action || p.action === '*') &&
      p.effect === 'allow'
    );

    if (!allow) {
      throw new ForbiddenError(`Permission not granted: ${resource}.${action}`);
    }

    next();
  };
}

Step 7: Property-Level Scope

// Apply property filter if user has property-scoped access
async function filterByPropertyAccess(query, ctx) {
  if (ctx.property_access && ctx.property_access.length > 0) {
    // User has property-level restriction
    query.where('property_id IN (?)', ctx.property_access);
  }
  // else: No restriction, user can access all properties in Account/Org
}

Step 8: Execute Query with RLS

// RLS policies automatically enforce org_id and account_id filtering
const properties = await db.query(`
  SELECT * FROM properties
  WHERE status = 'active'
  ORDER BY name
`);
// RLS ensures only properties in current org_id/account_id are returned

Usage Example:

app.get('/api/properties/:id',
  authenticate,
  setContext,
  authorize('properties', 'read'),
  async (req, res) => {
    const property = await db.query(`
      SELECT * FROM properties WHERE id = $1
    `, [req.params.id]);

    // Check property-level access if applicable
    if (req.auth_context.property_access &&
        !req.auth_context.property_access.includes(property.id)) {
      throw new ForbiddenError('No access to this property');
    }

    res.json(property);
  }
);

Source: Identity Spec lines 687-694, 1224-1262; Platform Spec lines 494-522

5. Data Model

5.1 Complete Identity Schema

-- Organizations (Tenants)
CREATE TABLE organizations (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name        VARCHAR(255) NOT NULL,
  slug        VARCHAR(100) NOT NULL UNIQUE,
  owner_id    UUID NOT NULL REFERENCES users(id),
  plan        VARCHAR(50) NOT NULL DEFAULT 'free' CHECK (plan IN ('free','starter','pro','enterprise')),
  settings    JSONB NOT NULL DEFAULT '{}',
  deleted_at  TIMESTAMPTZ,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT slug_format CHECK (slug ~* '^[a-z0-9-]+$')
);

CREATE INDEX idx_organizations_slug ON organizations(slug);
CREATE INDEX idx_organizations_owner ON organizations(owner_id);
CREATE INDEX idx_organizations_deleted ON organizations(deleted_at) WHERE deleted_at IS NULL;

-- Accounts (Sub-tenants / Actors)
CREATE TABLE accounts (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id      UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  name        VARCHAR(255) NOT NULL,
  type        VARCHAR(50) NOT NULL CHECK (type IN ('owner','manager','marketplace','internal')),
  is_default  BOOLEAN NOT NULL DEFAULT false,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT accounts_single_default_per_org UNIQUE (org_id, is_default) WHERE is_default = true
);

CREATE INDEX idx_accounts_org ON accounts(org_id);
CREATE INDEX idx_accounts_type ON accounts(org_id, type);

-- Users (Person Identity)
CREATE TABLE users (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email               CITEXT NOT NULL UNIQUE,
  display_name        VARCHAR(255),
  avatar_url          TEXT,
  auth_provider       VARCHAR(50) NOT NULL DEFAULT 'google',
  auth_provider_id    VARCHAR(255) NOT NULL,
  email_verified      BOOLEAN NOT NULL DEFAULT false,
  email_verified_at   TIMESTAMPTZ,
  is_active           BOOLEAN NOT NULL DEFAULT true,
  last_sign_in_at     TIMESTAMPTZ,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$'),
  UNIQUE (auth_provider, auth_provider_id)
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_auth_provider ON users(auth_provider, auth_provider_id);

-- User External Identities (Google OAuth)
CREATE TABLE user_identities (
  id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id             UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  provider            VARCHAR(50) NOT NULL CHECK (provider IN ('google','microsoft','github')),
  provider_user_id    TEXT NOT NULL,
  email               CITEXT NOT NULL,
  raw_profile         JSONB,
  created_at          TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (provider, provider_user_id)
);

CREATE INDEX idx_user_identities_user ON user_identities(user_id);

-- Roles (Permission Bundles)
CREATE TABLE roles (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name          VARCHAR(100) NOT NULL UNIQUE,
  description   TEXT,
  permissions   JSONB NOT NULL DEFAULT '[]',
  is_system     BOOLEAN NOT NULL DEFAULT false,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_roles_name ON roles(name);

-- Permissions (Action Registry)
CREATE TABLE permissions (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  resource    VARCHAR(100) NOT NULL,
  action      VARCHAR(100) NOT NULL,
  description TEXT,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (resource, action)
);

CREATE INDEX idx_permissions_resource ON permissions(resource);

-- Role Permission Mappings
CREATE TABLE role_permissions (
  role_id        UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
  permission_id  UUID NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
  effect         VARCHAR(10) NOT NULL CHECK (effect IN ('allow','deny')),
  created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  PRIMARY KEY (role_id, permission_id)
);

CREATE INDEX idx_role_permissions_role ON role_permissions(role_id);

-- Organization Members (User ↔ Org + Role)
CREATE TABLE organization_members (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id   UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  user_id           UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role_id           UUID NOT NULL REFERENCES roles(id),
  account_id        UUID REFERENCES accounts(id), -- NULL = Org-wide access
  property_ids      UUID[], -- Property-level access (optional)
  invited_by        UUID REFERENCES users(id),
  joined_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  ended_at          TIMESTAMPTZ,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (organization_id, user_id)
);

CREATE INDEX idx_org_members_org ON organization_members(organization_id);
CREATE INDEX idx_org_members_user ON organization_members(user_id);
CREATE INDEX idx_org_members_role ON organization_members(role_id);
CREATE INDEX idx_org_members_account ON organization_members(account_id) WHERE account_id IS NOT NULL;

-- Sessions
CREATE TABLE sessions (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id       UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  issued_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at    TIMESTAMPTZ NOT NULL,
  revoked_at    TIMESTAMPTZ,
  user_agent    TEXT,
  ip_address    INET,
  CONSTRAINT valid_expiry CHECK (expires_at > issued_at)
);

CREATE INDEX idx_sessions_user ON sessions(user_id);
CREATE INDEX idx_sessions_expires ON sessions(expires_at) WHERE revoked_at IS NULL;

-- Invitations
CREATE TABLE invitations (
  id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id   UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  email             CITEXT NOT NULL,
  role_id           UUID NOT NULL REFERENCES roles(id),
  account_id        UUID REFERENCES accounts(id),
  property_ids      UUID[],
  invited_by        UUID NOT NULL REFERENCES users(id),
  token             VARCHAR(255) NOT NULL UNIQUE,
  expires_at        TIMESTAMPTZ NOT NULL,
  accepted_at       TIMESTAMPTZ,
  created_at        TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z|a-z]{2,}$')
);

CREATE INDEX idx_invitations_org ON invitations(organization_id);
CREATE INDEX idx_invitations_email ON invitations(email);
CREATE INDEX idx_invitations_token ON invitations(token);
CREATE UNIQUE INDEX idx_invitations_unique_pending
  ON invitations(organization_id, email)
  WHERE accepted_at IS NULL AND expires_at > NOW();

-- Policy Rules (Future ABAC)
CREATE TABLE policy_rules (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id        UUID NOT NULL REFERENCES organizations(id),
  name          TEXT NOT NULL,
  description   TEXT,
  subject       JSONB NOT NULL,
  resource      JSONB NOT NULL,
  action        TEXT NOT NULL,
  condition     JSONB,
  effect        TEXT NOT NULL CHECK (effect IN ('allow','deny')),
  priority      INT NOT NULL DEFAULT 100,
  is_active     BOOLEAN NOT NULL DEFAULT false,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_policy_rules_org ON policy_rules(org_id);
CREATE INDEX idx_policy_rules_active ON policy_rules(is_active, priority) WHERE is_active = true;

Source: Identity Spec lines 800-939; DB Spec lines 89-153; Platform Spec lines 500-540

5.2 Permission Seeding Data

-- Seed System Roles
INSERT INTO roles (id, name, description, is_system) VALUES
  ('role_admin', 'Admin', 'Full control over organization', true),
  ('role_ops', 'Ops', 'Operations management (all except account.set_default)', true),
  ('role_owner_admin', 'Owner Admin', 'Property owner with full Space/Booking management', true),
  ('role_manager', 'Manager', 'Property manager with booking operations', true),
  ('role_viewer', 'Viewer', 'Read-only access', true),
  ('role_finance', 'Finance', 'Financial operations and reporting', true);

-- Seed Complete Permission Registry
INSERT INTO permissions (resource, action, description) VALUES
  -- Account Management
  ('account', 'read', 'View account details'),
  ('account', 'write', 'Modify account settings'),
  ('account', 'invite', 'Invite users to account'),
  ('account', 'set_default', 'Mark account as default'),
  ('account', 'transfer_spaces', 'Move spaces between accounts'),

  -- Space/Property Management
  ('space', 'read', 'View spaces'),
  ('space', 'create', 'Create new spaces'),
  ('space', 'update', 'Modify existing spaces'),
  ('space', 'delete', 'Remove spaces'),
  ('properties', 'read', 'View properties (alias)'),
  ('properties', 'create', 'Create properties (alias)'),
  ('properties', 'update', 'Update properties (alias)'),
  ('properties', 'delete', 'Delete properties (alias)'),

  -- Unit Management
  ('unit', 'read', 'View units'),
  ('unit', 'create', 'Create new units'),
  ('unit', 'update', 'Modify existing units'),
  ('unit', 'delete', 'Remove units'),
  ('units', 'read', 'View units (alias)'),
  ('units', 'create', 'Create units (alias)'),
  ('units', 'update', 'Update units (alias)'),
  ('units', 'delete', 'Delete units (alias)'),

  -- Media Management
  ('media', 'read', 'View media assets'),
  ('media', 'write', 'Upload/modify media'),
  ('media', 'delete', 'Remove media'),

  -- Availability Management
  ('availability', 'read', 'View calendars and blocks'),
  ('availability', 'create', 'Create blocks'),
  ('availability', 'update', 'Modify blocks'),
  ('availability', 'delete', 'Remove blocks'),

  -- Pricing Management
  ('pricing', 'read', 'View pricing rules'),
  ('pricing', 'create', 'Create pricing rules'),
  ('pricing', 'update', 'Modify pricing rules'),
  ('pricing', 'edit', 'Edit pricing (alias for update)'),
  ('pricing', 'delete', 'Remove pricing rules'),

  -- Rules Management
  ('rules', 'read', 'View business rules'),
  ('rules', 'edit', 'Modify business rules'),

  -- Booking Management
  ('booking', 'read', 'View bookings'),
  ('booking', 'create', 'Create bookings'),
  ('booking', 'update', 'Modify bookings'),
  ('booking', 'delete', 'Cancel bookings'),
  ('booking', 'manage', 'Full booking lifecycle management'),
  ('bookings', 'read', 'View bookings (alias)'),
  ('bookings', 'update', 'Update bookings (alias)'),

  -- Payment Management
  ('payment', 'read', 'View payments'),
  ('payment', 'create', 'Process payments'),
  ('payment', 'update', 'Modify payment details'),
  ('payment', 'delete', 'Refund payments'),
  ('payments', 'read', 'View payments (alias)'),
  ('payments', 'create', 'Process payments (alias)'),
  ('payments', 'update', 'Update payments (alias)'),
  ('payments', 'delete', 'Refund payments (alias)'),

  -- Financial Reports
  ('financials', 'read', 'View financial reports and transactions'),

  -- User Management
  ('users', 'read', 'View team members'),
  ('users', 'create', 'Invite new users'),
  ('users', 'update', 'Modify user roles and access'),
  ('users', 'delete', 'Remove team members'),

  -- Settings Management
  ('settings', 'read', 'View organization settings'),
  ('settings', 'update', 'Modify organization settings'),

  -- Channel Management
  ('channel', 'read', 'View channel configurations'),
  ('channel', 'manage', 'Manage channel targets and sync');

-- Seed Role Permission Mappings

-- Admin Role (All permissions with allow)
INSERT INTO role_permissions (role_id, permission_id, effect)
SELECT 'role_admin', id, 'allow'
FROM permissions;

-- Ops Role (All permissions except account.set_default)
INSERT INTO role_permissions (role_id, permission_id, effect)
SELECT 'role_ops', id, 'allow'
FROM permissions
WHERE NOT (resource = 'account' AND action = 'set_default');

-- Add explicit deny for account.set_default
INSERT INTO role_permissions (role_id, permission_id, effect)
SELECT 'role_ops', id, 'deny'
FROM permissions
WHERE resource = 'account' AND action = 'set_default';

-- Owner Admin Role
INSERT INTO role_permissions (role_id, permission_id, effect)
SELECT 'role_owner_admin', id, 'allow'
FROM permissions
WHERE (resource IN ('space', 'unit', 'properties', 'units', 'media',
                     'pricing', 'rules', 'booking', 'bookings') AND action IN ('read', 'create', 'update'))
   OR (resource = 'account' AND action = 'read')
   OR (resource = 'booking' AND action = 'manage')
   OR (resource = 'financials' AND action = 'read');

-- Manager Role
INSERT INTO role_permissions (role_id, permission_id, effect)
SELECT 'role_manager', id, 'allow'
FROM permissions
WHERE (resource IN ('space', 'unit', 'properties', 'units') AND action = 'read')
   OR (resource = 'availability' AND action IN ('read', 'create', 'update', 'delete'))
   OR (resource IN ('booking', 'bookings') AND action IN ('read', 'update', 'manage'))
   OR (resource = 'financials' AND action = 'read');

-- Viewer Role (Read-only)
INSERT INTO role_permissions (role_id, permission_id, effect)
SELECT 'role_viewer', id, 'allow'
FROM permissions
WHERE action = 'read';

-- Finance Role
INSERT INTO role_permissions (role_id, permission_id, effect)
SELECT 'role_finance', id, 'allow'
FROM permissions
WHERE resource IN ('payments', 'financials', 'payment')
   AND action IN ('read', 'update');

Source: Platform Spec lines 433-462; Identity Spec lines 859-891; Gap analysis

5.3 Account_id Enforcement Patches

Required Schema Updates:

-- Add account_id to all actor-owned tables
ALTER TABLE spaces
  ADD COLUMN account_id UUID REFERENCES accounts(id);

ALTER TABLE units
  ADD COLUMN account_id UUID REFERENCES accounts(id);

ALTER TABLE properties
  ADD COLUMN account_id UUID REFERENCES accounts(id);

ALTER TABLE price_rules
  ADD COLUMN account_id UUID REFERENCES accounts(id);

ALTER TABLE quotes
  ADD COLUMN account_id UUID REFERENCES accounts(id);

ALTER TABLE holds
  ADD COLUMN account_id UUID REFERENCES accounts(id);

ALTER TABLE bookings
  ADD COLUMN account_id UUID REFERENCES accounts(id);

ALTER TABLE payments
  ADD COLUMN account_id UUID REFERENCES accounts(id);

ALTER TABLE transactions
  ADD COLUMN account_id UUID REFERENCES accounts(id);

ALTER TABLE channel_listings
  ADD COLUMN account_id UUID REFERENCES accounts(id);

-- Backfill existing data to default Account
WITH default_accounts AS (
  SELECT org_id, id as account_id
  FROM accounts
  WHERE is_default = true
)
UPDATE spaces s
SET account_id = da.account_id
FROM default_accounts da
WHERE s.org_id = da.org_id
  AND s.account_id IS NULL;

-- Repeat for all other tables...

-- Make account_id NOT NULL after backfill
ALTER TABLE spaces
  ALTER COLUMN account_id SET NOT NULL;

-- Add composite indexes
CREATE INDEX idx_spaces_org_account ON spaces(org_id, account_id);
CREATE INDEX idx_units_org_account ON units(org_id, account_id);
CREATE INDEX idx_bookings_org_account ON bookings(org_id, account_id);
-- ... etc for all tables

-- Update RLS policies to include account_id checks
-- See section 4.2 for examples

Source: Gap analysis based on Platform Spec lines 87-97, 290-293

6. Session Management

6.1 Session Schema

PostgreSQL Table:

CREATE TABLE sessions (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id       UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  issued_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at    TIMESTAMPTZ NOT NULL,
  revoked_at    TIMESTAMPTZ,
  user_agent    TEXT,
  ip_address    INET,
  CONSTRAINT valid_expiry CHECK (expires_at > issued_at)
);

CREATE INDEX idx_sessions_user ON sessions(user_id);
CREATE INDEX idx_sessions_expires ON sessions(expires_at) WHERE revoked_at IS NULL;

Redis Cache:

Key: session:{session_id}
TTL: 86400 seconds (24 hours)
Value: JSON {
  user_id,
  email,
  org_id,
  role,
  permissions: [...],
  account_access: null | [uuid...]
}

Source: DB Spec lines 138-153; Identity Spec lines 259-265

6.2 Session Expiry and Cleanup

Expiry Logic:

Sessions expire after 24 hours from issued_at
Expired sessions automatically invalid (checked on every request)
No automatic refresh in MVP (user must re-authenticate)

Cleanup Job:

-- Run daily via cron job
DELETE FROM sessions
WHERE expires_at < NOW() - INTERVAL '7 days';

-- Or use pg_cron extension
SELECT cron.schedule(
  'cleanup-expired-sessions',
  '0 2 * * *', -- Run at 2 AM daily
  $$DELETE FROM sessions WHERE expires_at < NOW() - INTERVAL '7 days'$$
);

Redis Cleanup:

TTL-based automatic eviction
No manual cleanup needed
Keys expire exactly 24 hours after creation

Source: Identity Spec lines 1268-1275; DB Spec lines 148-153

6.3 Session Rotation

When to Rotate:

Role change (user promoted/demoted)
Account access change (property access modified)
Privilege escalation (viewer → manager)
Security event (suspicious activity)

Rotation Implementation:

async function rotateSession(old_session_id, reason) {
  // 1. Load old session
  const old_session = await db.query(`
    SELECT * FROM sessions WHERE id = $1
  `, [old_session_id]);

  if (!old_session) {
    throw new Error('Session not found');
  }

  // 2. Create new session with same expiry
  const new_session = await db.query(`
    INSERT INTO sessions (user_id, expires_at, user_agent, ip_address)
    VALUES ($1, $2, $3, $4)
    RETURNING id
  `, [
    old_session.user_id,
    old_session.expires_at, // Keep same expiry
    old_session.user_agent,
    old_session.ip_address
  ]);

  // 3. Revoke old session
  await db.query(`
    UPDATE sessions
    SET revoked_at = NOW()
    WHERE id = $1
  `, [old_session_id]);

  // 4. Update Redis
  await redis.del(`session:${old_session_id}`);

  // Note: New session will be cached on first use

  // 5. Log rotation event
  await db.query(`
    INSERT INTO audit_logs (org_id, user_id, action, resource_type, resource_id, metadata)
    VALUES ($1, $2, 'session_rotated', 'session', $3, $4)
  `, [
    old_session.org_id,
    old_session.user_id,
    old_session_id,
    JSON.stringify({ reason, new_session_id: new_session.id })
  ]);

  return new_session.id;
}

Integration with Role Changes:

async function updateUserRole(user_id, org_id, new_role_id) {
  // 1. Update membership
  await db.query(`
    UPDATE organization_members
    SET role_id = $1, updated_at = NOW()
    WHERE user_id = $2 AND organization_id = $3
  `, [new_role_id, user_id, org_id]);

  // 2. Invalidate permission cache
  await redis.del(`permissions:${user_id}:${org_id}`);

  // 3. Rotate all active sessions for this user
  const active_sessions = await db.query(`
    SELECT id FROM sessions
    WHERE user_id = $1
      AND expires_at > NOW()
      AND revoked_at IS NULL
  `, [user_id]);

  for (const session of active_sessions) {
    await rotateSession(session.id, 'role_change');
  }

  // 4. Emit event
  await emitEvent('user.role_changed', {
    user_id,
    org_id,
    old_role_id: old_role_id,
    new_role_id
  });
}

Source: MVP PRD line 60, 183; Identity Spec lines 1266-1275; Gap analysis

7. Research & Best Practices

7.1 Multi-Tenant RBAC Patterns

Salesforce Model

Org Hierarchy:

Organization (Tenant)
Profile (Role Bundle)
Permission Set (Granular Permissions)
Permission Set Group (Composite Permissions)

Key Features:

Object-level security (CRUD per object type)
Field-level security (access per field)
Row-level security (sharing rules, ownership)
Hierarchical role inheritance

Lessons for TVL: ✅ Separate Role (bundle) from Permission (granular) ✅ Support composite permissions (RolePermission table) ✅ Object-level permissions (resource-level in TVL) ✅ Consider field-level security for sensitive data (PII)

AWS IAM Model

Components:

Principal (User/Role/Service)
Policy (JSON document with statements)
Resource (AWS service/object)
Action (API call)
Condition (context-based rules)

Policy Example:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "s3:GetObject",
      "Resource": "arn:aws:s3:::my-bucket/*",
      "Condition": {
        "IpAddress": {
          "aws:SourceIp": "203.0.113.0/24"
        }
      }
    }
  ]
}

Lessons for TVL: ✅ Explicit deny overrides allow (implemented) ✅ Condition-based access (PolicyRule table for future) ✅ Resource-level permissions (implemented) ⚠️ Consider context: IP address, time, channel, region

Source: AWS IAM documentation; Salesforce Security Guide

7.2 ABAC vs RBAC Trade-offs

RBAC (Current MVP)

Pros:

Simple to understand and implement
Easy to audit ("What can this role do?")
Predictable performance (static permission checks)
Good for small-medium user bases

Cons:

Role explosion (need many roles for different contexts)
Inflexible (can't easily add "only during business hours" rule)
Doesn't handle dynamic conditions well

ABAC (Future Enhancement)

Pros:

Flexible (conditions can be anything)
Reduces role count (one role with many conditions)
Handles complex scenarios (time, location, resource attributes)
Better for large user bases with varied needs

Cons:

Complex to implement and debug
Harder to audit ("Why was access denied?")
Performance overhead (condition evaluation)
Requires careful policy design

TVL Recommendation:

MVP: Pure RBAC (implemented)
Post-MVP: Hybrid RBAC+ABAC
- Use RBAC for core permissions
- Use ABAC for contextual overrides (channel-specific, region-specific)
- Implement policy evaluation engine with caching

Example Hybrid Policy:

-- RBAC: User has "booking.create" permission
-- ABAC Override: Only allow during business hours

INSERT INTO policy_rules (org_id, name, subject, resource, action, condition, effect, priority)
VALUES (
  $org_id,
  'Booking Creation Time Restriction',
  '{"role": "manager"}',
  '{"type": "booking"}',
  'create',
  '{
    "time_range": {
      "start": "09:00",
      "end": "17:00",
      "timezone": "America/New_York"
    }
  }',
  'deny',
  200 -- Higher priority than RBAC allow
);

Source: NIST RBAC/ABAC papers; Platform Spec lines 407-414

7.3 Row-Level Security Performance

PostgreSQL RLS Internals

How RLS Works:

PostgreSQL rewrites every query to include RLS policy checks
Policy check becomes a subquery or join
Postgres query planner optimizes the combined query

Example Query Transformation:

-- Original query
SELECT * FROM spaces WHERE status = 'active';

-- With RLS policy (org_id isolation)
SELECT * FROM spaces
WHERE status = 'active'
  AND org_id = current_setting('app.current_org_id')::uuid;

-- With RLS policy (membership check)
SELECT * FROM spaces s
WHERE s.status = 'active'
  AND s.org_id IN (
    SELECT om.organization_id
    FROM organization_members om
    WHERE om.user_id = current_setting('app.current_user_id')::uuid
  );

Performance Characteristics:

Scenario	Overhead	Mitigation
Simple equality check (`org_id = ?`)	~5-10ms	Index on `org_id`
Subquery membership check	~20-50ms	Materialized view of memberships
Multiple policy checks	~50-100ms	Combine policies with OR
Large tables (>10M rows)	~100ms+	Partitioning by `org_id`

Optimization Strategies

1. Materialized Membership View:

CREATE MATERIALIZED VIEW user_org_access AS
SELECT
  om.user_id,
  om.organization_id,
  om.account_id,
  o.org_id as accessible_org_id
FROM organization_members om
JOIN organizations o ON o.id = om.organization_id
WHERE om.ended_at IS NULL;

CREATE UNIQUE INDEX ON user_org_access (user_id, accessible_org_id);

-- Refresh strategy
REFRESH MATERIALIZED VIEW CONCURRENTLY user_org_access;

-- Use in RLS policy
CREATE POLICY spaces_select_user_access ON spaces
  FOR SELECT
  USING (
    org_id IN (
      SELECT accessible_org_id
      FROM user_org_access
      WHERE user_id = current_setting('app.current_user_id')::uuid
    )
  );

2. Composite Indexes:

-- Index on (org_id, account_id) for actor-owned tables
CREATE INDEX idx_spaces_org_account ON spaces(org_id, account_id);
CREATE INDEX idx_units_org_account ON units(org_id, account_id);
CREATE INDEX idx_bookings_org_account ON bookings(org_id, account_id);

-- Partial indexes for active records only
CREATE INDEX idx_spaces_org_active
  ON spaces(org_id)
  WHERE status = 'active' AND deleted_at IS NULL;

3. Partitioning (for large tables):

-- Partition bookings by org_id (for large deployments)
CREATE TABLE bookings (
  id UUID PRIMARY KEY,
  org_id UUID NOT NULL,
  ...
) PARTITION BY LIST (org_id);

-- Create partitions per org
CREATE TABLE bookings_org_a PARTITION OF bookings FOR VALUES IN ('uuid-a');
CREATE TABLE bookings_org_b PARTITION OF bookings FOR VALUES IN ('uuid-b');

-- RLS policies apply to parent table automatically

4. Connection Pooling:

// Use PgBouncer in transaction mode
// Reduces session setup overhead (SET LOCAL commands)
const pool = new Pool({
  host: 'pgbouncer-host',
  port: 6432,
  database: 'tvl_mvp',
  max: 100, // Connection limit
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000
});

Benchmarks (Supabase):

RLS overhead: 10-20ms per query (Platform Spec line 1461)
With proper indexes: <5ms overhead
With materialized views: <2ms overhead
Without RLS: baseline performance

Recommendation:

Enable RLS in production (security benefit outweighs performance cost)
Use materialized views for complex membership checks
Monitor query performance with pg_stat_statements
Add indexes on (org_id, account_id) for all actor-owned tables

Source: PostgreSQL RLS documentation; Supabase performance guide; Platform Spec lines 343-347, 1461

7.4 OAuth/OIDC Best Practices

PKCE (Proof Key for Code Exchange)

Purpose: Prevents authorization code interception attacks

Flow:

Client generates code_verifier (random 43-128 chars)
Client computes code_challenge = base64url(SHA256(code_verifier))
Client sends code_challenge in authorization request
Authorization server stores code_challenge
Client sends code_verifier in token exchange request
Authorization server verifies SHA256(code_verifier) == code_challenge

Why It Matters:

Prevents man-in-the-middle attacks
Required for single-page apps (SPAs) and mobile apps
Recommended even for confidential clients (backend servers)

TVL Implementation:

// Generate code_verifier
const code_verifier = base64url(crypto.randomBytes(32));

// Generate code_challenge
const code_challenge = base64url(crypto.createHash('sha256').update(code_verifier).digest());

// Store temporarily (Redis, 5-minute TTL)
await redis.setex(`auth:state:${state}`, 300, code_verifier);

// Authorization URL includes code_challenge
const authUrl = `https://accounts.google.com/o/oauth2/v2/auth?
  client_id=${CLIENT_ID}&
  redirect_uri=${REDIRECT_URI}&
  response_type=code&
  scope=openid email profile&
  state=${state}&
  code_challenge=${code_challenge}&
  code_challenge_method=S256`;

// Token exchange includes code_verifier
const tokenResponse = await fetch('https://oauth2.googleapis.com/token', {
  method: 'POST',
  body: JSON.stringify({
    code,
    client_id: CLIENT_ID,
    client_secret: CLIENT_SECRET,
    redirect_uri: REDIRECT_URI,
    grant_type: 'authorization_code',
    code_verifier // Proves client is same as authorization requester
  })
});

Source: RFC 7636 (PKCE); MVP PRD line 55; Identity Spec lines 270-290

Token Refresh

Current MVP: No refresh tokens (user re-authenticates after 24 hours)

Future Enhancement:

// Token response includes refresh_token
const { access_token, refresh_token, expires_in } = tokenResponse;

// Store refresh_token securely (encrypted in DB)
await db.query(`
  INSERT INTO user_identities (user_id, provider, refresh_token_encrypted)
  VALUES ($1, 'google', pgp_sym_encrypt($2, $3))
`, [user.id, refresh_token, ENCRYPTION_KEY]);

// Refresh flow (when access_token expires)
async function refreshAccessToken(user_id) {
  const identity = await db.query(`
    SELECT pgp_sym_decrypt(refresh_token_encrypted, $2) as refresh_token
    FROM user_identities
    WHERE user_id = $1 AND provider = 'google'
  `, [user_id, ENCRYPTION_KEY]);

  const response = await fetch('https://oauth2.googleapis.com/token', {
    method: 'POST',
    body: JSON.stringify({
      client_id: CLIENT_ID,
      client_secret: CLIENT_SECRET,
      refresh_token: identity.refresh_token,
      grant_type: 'refresh_token'
    })
  });

  const { access_token, expires_in } = await response.json();

  return access_token;
}

Considerations:

Refresh tokens are long-lived (typically 60 days)
Must be stored encrypted
Should be rotated on each use (Google does this automatically)
Revocation: Delete refresh_token from DB

Source: OAuth 2.0 RFC 6749; Google OAuth documentation; Identity Spec lines 1489-1500

State Parameter Validation

Purpose: Prevents CSRF attacks

Implementation:

// Generate state on login
const state = crypto.randomBytes(16).toString('hex');

// Store with short TTL
await redis.setex(`auth:state:${state}`, 300, JSON.stringify({
  redirect_uri: req.body.redirect_uri,
  created_at: Date.now()
}));

// Validate on callback
const stored_state = await redis.get(`auth:state:${req.body.state}`);
if (!stored_state) {
  throw new Error('Invalid or expired state parameter');
}

// Delete state (one-time use)
await redis.del(`auth:state:${req.body.state}`);

Source: OAuth 2.0 RFC 6749; OWASP CSRF prevention

7.5 Session Management Best Practices

Session Fixation Prevention

Attack: Attacker obtains valid session ID, tricks victim into using it

Prevention:

Generate new session ID after authentication
Regenerate session ID on privilege changes
Bind session to IP address (optional, can break mobile users)
Bind session to user agent (helps detect hijacking)

TVL Implementation:

async function createSession(user_id, req) {
  // Always generate new UUID on authentication
  const session_id = uuidv4();

  await db.query(`
    INSERT INTO sessions (id, user_id, expires_at, user_agent, ip_address)
    VALUES ($1, $2, NOW() + INTERVAL '24 hours', $3, $4)
  `, [session_id, user_id, req.headers['user-agent'], req.ip]);

  return session_id;
}

// Validate session integrity
async function validateSessionIntegrity(session_id, req) {
  const session = await db.query(`
    SELECT * FROM sessions WHERE id = $1
  `, [session_id]);

  // Check user agent matches (flexible check for browser updates)
  const stored_ua = parseUserAgent(session.user_agent);
  const current_ua = parseUserAgent(req.headers['user-agent']);

  if (stored_ua.browser !== current_ua.browser ||
      stored_ua.os !== current_ua.os) {
    // Log suspicious activity
    await logSecurityEvent('session_hijacking_suspected', {
      session_id,
      stored_ua,
      current_ua
    });

    // Revoke session
    await revokeSession(session_id);
    throw new UnauthorizedError('Session security violation');
  }

  return session;
}

Source: OWASP Session Management Cheat Sheet

Session Timeout Strategies

Absolute Timeout:

Session expires 24 hours after creation (MVP)
No sliding window
Simple to implement and reason about

Idle Timeout (Future):

Session expires after 30 minutes of inactivity
Sliding window: each request extends timeout
Better security, worse UX

Combined Approach (Recommended for Post-MVP):

// Absolute timeout: 24 hours
// Idle timeout: 30 minutes

async function extendSession(session_id) {
  const session = await db.query(`
    SELECT * FROM sessions WHERE id = $1
  `, [session_id]);

  const now = new Date();
  const issued_at = new Date(session.issued_at);
  const last_activity = new Date(session.last_activity_at || session.issued_at);

  // Check absolute timeout (24 hours from issued_at)
  if (now - issued_at > 24 * 60 * 60 * 1000) {
    throw new UnauthorizedError('Session expired (absolute timeout)');
  }

  // Check idle timeout (30 minutes from last activity)
  if (now - last_activity > 30 * 60 * 1000) {
    throw new UnauthorizedError('Session expired (idle timeout)');
  }

  // Extend idle timeout
  await db.query(`
    UPDATE sessions
    SET last_activity_at = NOW()
    WHERE id = $1
  `, [session_id]);

  // Update Redis cache
  await redis.expire(`session:${session_id}`, 1800); // 30 minutes
}

Source: OWASP Session Management; Identity Spec lines 1266-1275

8. Gap Analysis

8.1 Critical Gaps

1. account_id Enforcement

Severity: CRITICAL

Issue: Database schema missing account_id on actor-owned tables

Affected Tables:

spaces
units
properties
price_rules
quotes
holds
bookings
payments
transactions
channel_listings

Impact:

Cannot scope resources to specific Accounts
Manager role cannot be limited to Account subset
Revenue attribution requires complex joins
Delegation logic breaks

Recommendation: See section 5.3 for complete schema patches

Source: Platform Spec lines 87-97, 290-293; DB Spec lines 164-205

2. Email Verification

Severity: MEDIUM

Issue: No email verification flow for non-OIDC providers

Current Behavior:

Google OIDC provides verified email
No verification for future providers (Microsoft, GitHub)

Impact:

Cannot verify email ownership for non-OIDC users
Risk of account takeover if email not verified

Recommendation:

ALTER TABLE users
  ADD COLUMN email_verified BOOLEAN NOT NULL DEFAULT false,
  ADD COLUMN email_verified_at TIMESTAMPTZ;

CREATE TABLE email_verification_tokens (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES users(id),
  token VARCHAR(255) NOT NULL UNIQUE,
  expires_at TIMESTAMPTZ NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Mark Google users as verified
UPDATE users
SET email_verified = true, email_verified_at = created_at
WHERE auth_provider = 'google';

Source: Gap analysis - not mentioned in any spec

3. Identity Provider Flexibility

Severity: LOW

Issue: Hard-coded 'google' provider in schema and code

Current Schema:

auth_provider VARCHAR(50) NOT NULL DEFAULT 'google'

Impact:

Requires schema change to add new providers
Business logic assumes Google-specific claims

Recommendation:

-- Change to enum
CREATE TYPE auth_provider_type AS ENUM ('google', 'microsoft', 'github', 'email');

ALTER TABLE users
  ALTER COLUMN auth_provider TYPE auth_provider_type
  USING auth_provider::auth_provider_type;

-- Add provider-specific configuration
CREATE TABLE auth_providers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  provider auth_provider_type NOT NULL UNIQUE,
  client_id TEXT NOT NULL,
  client_secret_ref TEXT NOT NULL, -- Reference to Secrets Manager
  authorization_url TEXT NOT NULL,
  token_url TEXT NOT NULL,
  user_info_url TEXT NOT NULL,
  scopes TEXT[] NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Source: Identity Spec lines 85-101; Gap analysis

4. Permission Seeding Incomplete

Severity: MEDIUM

Issue: No complete SQL seeds for all permissions and role mappings

Current State:

Platform Spec lists permissions conceptually
Identity Spec provides partial role definitions
DB Spec has no seeding SQL

Impact:

Manual permission setup required
Risk of inconsistent permission configurations
No single source of truth

Recommendation: See section 5.2 for complete seeding SQL

Source: Platform Spec lines 437-462; Identity Spec lines 859-891

5. Session Rotation on Privilege Changes

Severity: MEDIUM

Issue: Session rotation specified but not fully implemented

Current State:

MVP PRD mentions session rotation
No implementation details provided
No database support for tracking rotations

Impact:

Privilege escalation not immediately effective
Cached permissions may be stale
Security risk if old sessions not invalidated

Recommendation: See section 6.3 for complete implementation

Source: MVP PRD lines 60, 183; Identity Spec lines 1266-1275

8.2 Minor Gaps

1. Role Name Inconsistency

Issue: Different role names across specifications

Platform Spec: admin, ops, owner_admin, manager, viewer MVP PRD: Owner, ChannelPublisher, ContentManager, Viewer Identity Spec: Owner, Manager, Staff, Finance, Admin

Recommendation:

Use Platform Spec names as internal identifiers
Map to friendly names in UI layer
Document mapping in API specs

Source: Platform Spec lines 193-201; MVP PRD lines 54-64; Identity Spec lines 150-181

2. Property-Level Access Scope

Issue: Identity Spec adds property_ids array not in Platform Spec

Platform Spec: Account-level scoping only Identity Spec: Account + property-level scoping

Recommendation:

Keep property-level access as optional extension
Document clearly in API specs
Ensure RLS policies handle both cases

Source: Platform Spec lines 221-243; Identity Spec lines 121-138

3. PolicyRule Activation

Issue: PolicyRule table exists but never activated

Current State:

Schema present
is_active = false by default
No evaluation engine

Recommendation:

Document future activation plan
Provide example policies
Implement policy evaluation engine in post-MVP

Source: Platform Spec lines 407-414, 479-486

9. Recommendations

9.1 Immediate Action Items

1. Fix account_id Enforcement

Priority: P0 (CRITICAL)

Actions:

Add account_id NOT NULL to all actor-owned tables
Backfill existing data to default Account
Add composite indexes (org_id, account_id)
Update RLS policies to include account_id checks
Update API layer to enforce account_id on creation

Timeline: Must be completed before MVP launch

Owner: Backend team + DBA

Source: Section 5.3

2. Complete Permission Seeding

Priority: P0 (CRITICAL)

Actions:

Finalize permission registry (all resources + actions)
Create complete role permission mappings
Write seeding SQL scripts
Test role permissions exhaustively
Document permission matrix in API docs

Timeline: Must be completed before MVP launch

Owner: Backend team

Source: Section 5.2

3. Implement Session Rotation

Priority: P1 (HIGH)

Actions:

Add rotateSession() function
Integrate with role change workflow
Add audit logging for rotations
Test rotation scenarios
Document rotation behavior

Timeline: Before MVP launch

Owner: Backend team

Source: Section 6.3

9.2 Post-MVP Enhancements

1. Add Email Verification

Priority: P2 (MEDIUM)

Actions:

Add email_verified fields to users table
Create email_verification_tokens table
Implement verification email flow
Update auth middleware to check verification
Add UI for resend verification email

Timeline: Post-MVP v0.1

Owner: Backend + Frontend team

Source: Section 8.1.2

2. Support Multiple Identity Providers

Priority: P2 (MEDIUM)

Actions:

Refactor auth provider handling
Add auth_providers configuration table
Implement Microsoft OIDC
Implement GitHub OAuth
Add provider selection UI

Timeline: Post-MVP v0.2

Owner: Backend + Frontend team

Source: Section 8.1.3

3. Enable ABAC (PolicyRule)

Priority: P3 (LOW)

Actions:

Design policy evaluation engine
Implement condition matching logic
Add policy caching layer
Create policy management UI
Document policy DSL

Timeline: Post-MVP v0.3+

Owner: Backend team + Product

Source: Section 7.2

10. Conclusion

The TVL platform's Identity, Tenancy, and Authorization domains are well-designed with clear separation of concerns and strong foundations for multi-tenancy. The three-tier Org/Account/User hierarchy provides excellent scalability, and the RBAC system is comprehensive.

However, critical gaps exist around account_id enforcement and permission seeding that must be addressed before MVP launch. Additionally, session management requires hardening, and several specifications contain inconsistencies that need reconciliation.

With the recommended fixes applied, the platform will have a robust, secure, and scalable identity and authorization system suitable for both current MVP needs and future enterprise growth.

Sources

Platform Specification: /mnt/c/GitHub/claude-test/prd/TVL-Platform-Specification-2025-10-21.md
MVP PRD: /mnt/c/GitHub/claude-test/docs-mvp/tvl-mvp-v0-prd.updated.md
Data Model Specification: /mnt/c/GitHub/claude-test/prd/TVL Data and Domain Model Specification 2025-10-21 (1).md
Identity Domain Spec: /mnt/c/GitHub/claude-test/docs/02-domains/identity-access/spec.md
Database Spec: /mnt/c/GitHub/claude-test/docs-mvp/tvl-mvp-v0-db-spec.updated.md
Schema Overview: /mnt/c/GitHub/claude-test/docs/04-data/schema-overview.md
Authorization Domain: /mnt/c/GitHub/claude-test/domains/authorization-access.md
Identity & Tenancy Domain: /mnt/c/GitHub/claude-test/domains/identity-tenancy.md

Executive Summary​

Key Findings​

1. Multi-Tenancy Architecture​

1.1 Org/Account/User Hierarchy​

Organization (Org)​

Account​

User​

1.2 Default Account Pattern​

Auto-Creation Logic​

is_default Constraint​

1.3 Membership Scoping​

Org-Wide Membership​

Account-Scoped Membership​

Schema Definition​

2. Role-Based Access Control (RBAC)​

2.1 Role Definitions​

Platform Specification Roles​

MVP PRD Roles (Simplified for Operator UX)​

Identity Spec Roles (Most Detailed)​

2.2 Permission Registry​

Core Permission Structure​

Platform Spec Permission Seeds​

Identity Spec Permission Resources (More Granular)​

Complete Permission Seeding​

2.3 RolePermission Mapping​

Deny-Overrides-Allow Semantics​

Role Permission Matrices​

2.4 Policy Rules (Future ABAC)​

Schema Definition​

3. Authentication​

3.1 Google OIDC with PKCE​

Flow Diagram​

Detailed Flow​

3.2 Session Management​

Server-Side Sessions​

3.3 Cookie Security​

Cookie Configuration​

3.4 Email Verification​

4. Authorization Enforcement​

4.1 account_id Enforcement​

Platform Specification Requirements​

Database Spec Implementation​

Impact Analysis​

4.2 Row-Level Security (RLS) Policies​

Current Status​

Sample RLS Policies​

Setting Session Context​

Performance Implications​

4.3 Permission Checking Workflow​

Request Flow​

Detailed Implementation​

5. Data Model​

5.1 Complete Identity Schema​

5.2 Permission Seeding Data​

5.3 Account_id Enforcement Patches​

6. Session Management​

6.1 Session Schema​

6.2 Session Expiry and Cleanup​

6.3 Session Rotation​

7. Research & Best Practices​

7.1 Multi-Tenant RBAC Patterns​

Salesforce Model​

AWS IAM Model​

7.2 ABAC vs RBAC Trade-offs​

RBAC (Current MVP)​

ABAC (Future Enhancement)​

7.3 Row-Level Security Performance​

PostgreSQL RLS Internals​

Optimization Strategies​

7.4 OAuth/OIDC Best Practices​

PKCE (Proof Key for Code Exchange)​

Token Refresh​

State Parameter Validation​

7.5 Session Management Best Practices​

Session Fixation Prevention​

Session Timeout Strategies​

8. Gap Analysis​

8.1 Critical Gaps​

1. account_id Enforcement​

2. Email Verification​

Executive Summary

Key Findings

1. Multi-Tenancy Architecture

1.1 Org/Account/User Hierarchy

Organization (Org)

Account

User

1.2 Default Account Pattern

Auto-Creation Logic

is_default Constraint

1.3 Membership Scoping

Org-Wide Membership

Account-Scoped Membership

Schema Definition

2. Role-Based Access Control (RBAC)

2.1 Role Definitions

Platform Specification Roles

MVP PRD Roles (Simplified for Operator UX)

Identity Spec Roles (Most Detailed)

2.2 Permission Registry

Core Permission Structure

Platform Spec Permission Seeds

Identity Spec Permission Resources (More Granular)

Complete Permission Seeding

2.3 RolePermission Mapping

Deny-Overrides-Allow Semantics

Role Permission Matrices

2.4 Policy Rules (Future ABAC)

Schema Definition

3. Authentication

3.1 Google OIDC with PKCE

Flow Diagram

Detailed Flow

3.2 Session Management

Server-Side Sessions

3.3 Cookie Security

Cookie Configuration

3.4 Email Verification

4. Authorization Enforcement

4.1 account_id Enforcement

Platform Specification Requirements

Database Spec Implementation

Impact Analysis

4.2 Row-Level Security (RLS) Policies

Current Status

Sample RLS Policies

Setting Session Context

Performance Implications

4.3 Permission Checking Workflow

Request Flow

Detailed Implementation

5. Data Model

5.1 Complete Identity Schema

5.2 Permission Seeding Data

5.3 Account_id Enforcement Patches

6. Session Management

6.1 Session Schema

6.2 Session Expiry and Cleanup

6.3 Session Rotation

7. Research & Best Practices

7.1 Multi-Tenant RBAC Patterns

Salesforce Model

AWS IAM Model

7.2 ABAC vs RBAC Trade-offs

RBAC (Current MVP)

ABAC (Future Enhancement)

7.3 Row-Level Security Performance

PostgreSQL RLS Internals

Optimization Strategies

7.4 OAuth/OIDC Best Practices

PKCE (Proof Key for Code Exchange)

Token Refresh

State Parameter Validation

7.5 Session Management Best Practices

Session Fixation Prevention

Session Timeout Strategies

8. Gap Analysis

8.1 Critical Gaps

1. account_id Enforcement

2. Email Verification