Database Design

Global Watch uses Supabase (PostgreSQL) as its database layer, with a focus on security, multi-tenancy, and maintainability. This document covers the schema organization, Row Level Security (RLS) policies, and best practices.

Architecture Overview

The database architecture is built around these core principles:

Supabase PostgreSQL - Managed PostgreSQL with built-in auth
Row Level Security (RLS) - Authorization at the database level
Account-based multi-tenancy - Data isolation through account_id
Schema files - Numbered SQL files for reproducible schemas
Migrations - Timestamped migration files for changes

Schema Organization

Database schemas are organized in numbered files:

apps/web/supabase/
├── schemas/              # Schema definition files
│   ├── 01-enums.sql
│   ├── 02-config.sql
│   ├── 03-accounts.sql
│   ├── 04-roles.sql
│   ├── 05-memberships.sql
│   ├── 06-roles-permissions.sql
│   ├── 07-invitations.sql
│   ├── 08-billing-customers.sql
│   ├── 09-subscriptions.sql
│   ├── 10-orders.sql
│   ├── 11-notifications.sql
│   ├── 12-one-time-tokens.sql
│   ├── 13-mfa.sql
│   ├── 14-super-admin.sql
│   ├── 15-account-views.sql
│   └── 16-storage.sql
├── migrations/           # Timestamped migrations
├── seed.sql              # Development seed data
└── tests/                # Database tests

Core Tables

Schema File	Tables	Purpose
`03-accounts.sql`	`accounts`	Personal and team accounts
`04-roles.sql`	`roles`	Role definitions
`05-memberships.sql`	`memberships`	User-account relationships
`06-roles-permissions.sql`	`role_permissions`	Permission assignments
`07-invitations.sql`	`invitations`	Team invitations
`09-subscriptions.sql`	`subscriptions`	Billing subscriptions

Account Model

Accounts are the foundation of multi-tenancy:

CREATE TABLE public.accounts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name VARCHAR(255),
  slug VARCHAR(255) UNIQUE,
  is_personal_account BOOLEAN NOT NULL DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now()
);

Account Types

Type	`is_personal_account`	Description
Personal	`true`	Individual user account (ID matches `auth.uid()`)
Team	`false`	Shared workspace with members

Row Level Security (RLS)

RLS is mandatory for all tables containing user data.

Enabling RLS

-- Always enable RLS on new tables
ALTER TABLE public.my_table ENABLE ROW LEVEL SECURITY;

Policy Patterns

Personal Account Data

-- Only the account owner can access
CREATE POLICY "personal_data_access" ON public.personal_settings
  FOR ALL USING (
    account_id = (SELECT auth.uid())
  );

Team Account Data (Any Member)

-- Any team member can read
CREATE POLICY "team_data_read" ON public.team_projects
  FOR SELECT
  TO authenticated
  USING (
    public.has_role_on_account(account_id)
  );

Team Account Data (Specific Role)

-- Only owners can modify settings
CREATE POLICY "team_settings_write" ON public.team_settings
  FOR UPDATE
  TO authenticated
  USING (
    public.has_role_on_account(account_id, 'owner')
  );

Combined Personal + Team

-- Works for both personal and team accounts
CREATE POLICY "data_access" ON public.projects
  FOR SELECT
  TO authenticated
  USING (
    account_id = (SELECT auth.uid()) OR
    public.has_role_on_account(account_id)
  );

Helper Functions

Global Watch provides helper functions for access control:

Account Access

-- Check if user has any role on account
public.has_role_on_account(account_id UUID)
-- Returns: BOOLEAN

-- Check if user has specific role on account
public.has_role_on_account(account_id UUID, role_name TEXT)
-- Returns: BOOLEAN

-- Check if user is the account owner
public.is_account_owner(account_id UUID)
-- Returns: BOOLEAN

-- Check if user is a team member
public.is_team_member(account_id UUID, user_id UUID)
-- Returns: BOOLEAN

Permissions

-- Check if user has specific permission
public.has_permission(user_id UUID, account_id UUID, permission_name TEXT)
-- Returns: BOOLEAN

-- Check if user has more elevated role than target
public.has_more_elevated_role(target_user_id UUID, target_account_id UUID, role_name TEXT)
-- Returns: BOOLEAN

Billing & Subscriptions

-- Check if account has active subscription
public.has_active_subscription(account_id UUID)
-- Returns: BOOLEAN

Super Admin

-- Check if current user is super admin
public.is_super_admin()
-- Returns: BOOLEAN

Configuration

-- Check if feature is enabled
public.is_set(field_name TEXT)
-- Returns: BOOLEAN

Creating Tables

Standard Table Template

CREATE TABLE IF NOT EXISTS public.my_data (
  -- Primary key
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  
  -- Account association (required for multi-tenancy)
  account_id UUID REFERENCES public.accounts(id) ON DELETE CASCADE NOT NULL,
  
  -- Your fields
  name VARCHAR(255) NOT NULL,
  description TEXT,
  status public.my_status NOT NULL DEFAULT 'active',
  
  -- Timestamps
  created_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  updated_at TIMESTAMPTZ DEFAULT now() NOT NULL,
  
  -- User tracking (optional)
  created_by UUID REFERENCES auth.users(id),
  updated_by UUID REFERENCES auth.users(id)
);

-- Enable RLS
ALTER TABLE public.my_data ENABLE ROW LEVEL SECURITY;

-- Create policies
CREATE POLICY "my_data_read" ON public.my_data
  FOR SELECT TO authenticated
  USING (
    account_id = (SELECT auth.uid()) OR
    public.has_role_on_account(account_id)
  );

CREATE POLICY "my_data_write" ON public.my_data
  FOR INSERT TO authenticated
  WITH CHECK (
    account_id = (SELECT auth.uid()) OR
    public.has_role_on_account(account_id)
  );

-- Add timestamp trigger
CREATE TRIGGER set_timestamps
  BEFORE UPDATE ON public.my_data
  FOR EACH ROW
  EXECUTE FUNCTION public.trigger_set_timestamps();

Using Enums

-- Define enum type
CREATE TYPE public.my_status AS ENUM ('active', 'inactive', 'pending');

-- Use in table
CREATE TABLE public.my_table (
  status public.my_status NOT NULL DEFAULT 'pending'
);

Adding Constraints

CREATE TABLE public.my_table (
  -- Email validation
  email VARCHAR(255) NOT NULL CHECK (email ~* '^.+@.+\..+$'),
  
  -- Positive numbers
  count INTEGER NOT NULL CHECK (count >= 0),
  
  -- Non-empty strings
  name VARCHAR(255) NOT NULL CHECK (length(trim(name)) > 0)
);

Migrations

Creating Migrations

# Generate migration from schema changes
pnpm --filter web supabase:db:diff my_migration_name

# Apply migrations (reset database)
pnpm supabase:web:reset

Migration Best Practices

One change per migration - Keep migrations focused
Descriptive names - Use clear, descriptive migration names
Test locally first - Always test on local Supabase
Update schema files - Keep schema files in sync with migrations

TypeScript Types

Auto-Generated Types

import { Tables } from '@kit/supabase/database';

// Table row types
type Account = Tables<'accounts'>;
type Subscription = Tables<'subscriptions'>;
type Notification = Tables<'notifications'>;

Insert/Update Types

import { TablesInsert, TablesUpdate } from '@kit/supabase/database';

type AccountInsert = TablesInsert<'accounts'>;
type AccountUpdate = TablesUpdate<'accounts'>;

Regenerating Types

# Generate TypeScript types from database
pnpm supabase:web:typegen

Function Security

Creating Secure Functions

CREATE OR REPLACE FUNCTION public.my_function(param1 TEXT)
RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = ''
AS $$
BEGIN
  -- Function body
END;
$$;

-- Grant permissions
GRANT EXECUTE ON FUNCTION public.my_function(TEXT) TO authenticated, service_role;

Security Guidelines

Setting	Purpose
`SECURITY DEFINER`	Run with function owner's privileges
`SET search_path = ''`	Prevent search path injection
`GRANT EXECUTE`	Control who can call the function

Use SECURITY DEFINER sparingly. Only when the function needs elevated privileges that the caller shouldn't have directly.

Views

Creating Secure Views

CREATE OR REPLACE VIEW public.my_view
WITH (security_invoker = true)
AS
SELECT 
  a.id,
  a.name,
  m.role
FROM public.accounts a
JOIN public.memberships m ON m.account_id = a.id
WHERE m.user_id = auth.uid();

Always use security_invoker = true for views. This ensures RLS policies are applied based on the querying user, not the view owner.

Triggers

Timestamp Automation

-- Use the built-in timestamp trigger
CREATE TRIGGER set_timestamps
  BEFORE UPDATE ON public.my_table
  FOR EACH ROW
  EXECUTE FUNCTION public.trigger_set_timestamps();

User Tracking

-- Track who created/updated records
CREATE TRIGGER set_user_tracking
  BEFORE INSERT OR UPDATE ON public.my_table
  FOR EACH ROW
  EXECUTE FUNCTION public.trigger_set_user_tracking();

Best Practices Summary

Security

✅ Always enable RLS on tables with user data
✅ Use helper functions for access control
✅ Associate data with accounts via account_id
✅ Use explicit schema references (public.table_name)
✅ Set search_path = '' in functions
❌ Never use SECURITY DEFINER without good reason

Naming Conventions

Type	Convention	Example
Tables	snake_case, plural	`accounts`, `subscriptions`
Functions	snake_case, verb phrases	`create_team_account`, `verify_nonce`
Triggers	descriptive action	`set_slug_from_account_name`
Enums	snake_case	`subscription_status`

Data Access

Use has_role_on_account() for team membership checks
Use has_permission() for specific permission checks
Use is_account_owner() for ownership verification
Use auth.uid() for current user ID

Common Commands

# Start local Supabase
pnpm supabase:web:start

# Stop Supabase
pnpm supabase:web:stop

# Reset database (apply all migrations)
pnpm supabase:web:reset

# Generate TypeScript types
pnpm supabase:web:typegen

# Create migration from schema changes
pnpm --filter web supabase:db:diff migration_name

Next Steps

Hexagonal Architecture - Repository pattern implementation
Multi-Tenancy - Subdomain routing
Development Setup - Local environment configuration

Database Design

On this page