🗄️ Database Design (MVP)¶

Overview¶

Simple PostgreSQL schema in Supabase with Row Level Security (RLS) for multi-tenancy.

Core Tables¶

1. Organizations¶

organizations
- id (uuid, primary key)
- name (text)
- created_at (timestamp)
- settings (jsonb)

2. Users¶

users
- id (uuid, primary key)
- email (text, unique)
- name (text)
- role (text) -- 'director', 'manager', 'staff'
- created_at (timestamp)

3. Organization Users (Many-to-Many)¶

organization_users
- organization_id (uuid, foreign key)
- user_id (uuid, foreign key)
- role (text) -- role within this org
- joined_at (timestamp)

4. Policies¶

policies
- id (uuid, primary key)
- organization_id (uuid, foreign key)
- title (text)
- content (text)
- version (text)
- status (text) -- 'draft', 'approved', 'archived'
- created_at (timestamp)

5. Essential Eight Assessments¶

e8_assessments
- id (uuid, primary key)
- organization_id (uuid, foreign key)
- assessment_date (date)
- completed_at (timestamp)
- overall_maturity_level (text) -- 'ML0', 'ML1', 'ML2', 'ML3'
- created_by (uuid, foreign key)

5a. E8 Question Answers¶

e8_question_answers
- id (uuid, primary key)
- assessment_id (uuid, foreign key to e8_assessments)
- organization_id (uuid, foreign key) -- for RLS
- question_id (text) -- E8_MFA_001, E8_AP_003, etc.
- selected_option (text)
- answered_at (timestamp)
- answered_by (uuid, foreign key to users)

-- Evidence fields (NEW)
- evidence_files (jsonb) -- Array of {filename, fileUrl, uploadedAt, uploadedBy}
- evidence_notes (text)
- evidence_updated_at (timestamp)
- evidence_updated_by (uuid, foreign key to users)

-- Exception fields (NEW)
- is_exception (boolean, default false)
- exception_justification (text)
- exception_compensating_controls (text)
- exception_risk_acceptance (text)
- exception_review_date (date)
- exception_board_approval_date (date)
- exception_approved_by (uuid, foreign key to users)

5b. E8 Control Reference (Read-Only)¶

e8_control_reference
- id (text, primary key) -- ML2-RA-04
- strategy (text) -- 'admin_privileges', 'mfa', etc.
- maturity_level (text) -- 'ML1', 'ML2', 'ML3'
- description (text) -- Full ACSC requirement
- question_mapping (text) -- E8_AP_003 (which question covers this)
- display_order (int)
- created_at (timestamp)

Note: This table is populated once from ACSC documentation, never modified by users. Used for audit export generation.

6. Tasks¶

tasks
- id (uuid, primary key)
- organization_id (uuid, foreign key)
- title (text)
- description (text)
- assigned_to (uuid, foreign key)
- due_date (date)
- status (text) -- 'pending', 'complete'
- created_at (timestamp)

7. Risks¶

risks
- id (uuid, primary key)
- organization_id (uuid, foreign key)
- title (text)
- likelihood (int) -- 1-5
- impact (int) -- 1-5
- status (text) -- 'open', 'accepted', 'mitigated'
- board_accepted_at (timestamp)

Row Level Security (RLS)¶

Basic Rules¶

-- Users can only see their organization's data
CREATE POLICY "Users can view own org data" ON policies
FOR SELECT USING (
  organization_id IN (
    SELECT organization_id FROM organization_users
    WHERE user_id = auth.uid()
  )
);

-- Directors can approve, others can view
CREATE POLICY "Directors can update policies" ON policies
FOR UPDATE USING (
  organization_id IN (
    SELECT organization_id FROM organization_users
    WHERE user_id = auth.uid()
    AND role = 'director'
  )
);

Indexes¶

Performance Indexes (MVP)¶

-- Lookups by organization
CREATE INDEX idx_policies_org ON policies(organization_id);
CREATE INDEX idx_tasks_org ON tasks(organization_id);
CREATE INDEX idx_e8_assessments_org ON e8_assessments(organization_id);
CREATE INDEX idx_e8_answers_org ON e8_question_answers(organization_id);

-- User lookups
CREATE INDEX idx_org_users ON organization_users(user_id);

-- Date-based queries
CREATE INDEX idx_tasks_due ON tasks(due_date) WHERE status = 'pending';

-- E8 assessment lookups
CREATE INDEX idx_e8_answers_assessment ON e8_question_answers(assessment_id);
CREATE INDEX idx_e8_answers_question ON e8_question_answers(question_id);
CREATE INDEX idx_e8_control_ref_question ON e8_control_reference(question_mapping);

-- Exception tracking
CREATE INDEX idx_e8_exceptions ON e8_question_answers(exception_review_date)
  WHERE is_exception = true;

What We're NOT Building (Yet)¶

Audit log tables (use Supabase built-in)
Complex permission system (keep it simple)
Data warehousing (not needed for MVP)
Caching layers (PostgreSQL is fast enough)
Sharding (single database is fine)

Migration Strategy¶

Start with single Supabase project
Use migrations for schema changes
Test locally with Supabase CLI
Apply to staging, then production

# Create migration
supabase migration new add_policies_table

# Test locally
supabase db reset

# Deploy
supabase db push

Keep the schema simple. Add constraints later. Focus on core features.