apps/backend/src/infrastructure/database/migrations/0002_tenant_identity_foundation.sqlSQL
-- Gastro OS Sprint A — A-011 through A-019
-- Tenant / Identity foundation
-- PostgreSQL 18
create extension if not exists pgcrypto;
create table if not exists organizations (
id uuid primary key default gen_random_uuid(),
name text not null,
slug text not null,
default_locale text not null default 'en-GB',
status text not null default 'active',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint organizations_slug_unique unique (slug),
constraint organizations_status_check check (status in ('active','suspended','archived'))
);
create table if not exists locations (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
name text not null,
code text,
timezone text not null default 'Europe/Berlin',
default_locale text not null default 'de-DE',
business_day_cutoff time not null default '04:00',
status text not null default 'active',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint locations_org_name_unique unique (organization_id, name),
constraint locations_org_code_unique unique (organization_id, code),
constraint locations_status_check check (status in ('active','inactive','archived'))
);
create index if not exists idx_locations_org_status
on locations(organization_id, status);
create table if not exists user_accounts (
id uuid primary key default gen_random_uuid(),
external_auth_subject text,
email_normalized text,
display_name text,
preferred_locale text not null default 'en-GB',
status text not null default 'active',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint user_accounts_external_subject_unique unique (external_auth_subject),
constraint user_accounts_email_unique unique (email_normalized),
constraint user_accounts_status_check check (status in ('active','disabled','locked'))
);
create table if not exists employees (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
user_account_id uuid references user_accounts(id) on delete set null,
employee_number text,
first_name text not null,
last_name text not null,
display_name text,
preferred_locale text,
status text not null default 'active',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint employees_org_employee_number_unique unique (organization_id, employee_number),
constraint employees_status_check check (status in ('active','inactive','on_leave','terminated'))
);
create index if not exists idx_employees_org_status
on employees(organization_id, status);
create table if not exists memberships (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
user_account_id uuid not null references user_accounts(id) on delete restrict,
employee_id uuid references employees(id) on delete set null,
status text not null default 'active',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint memberships_org_user_unique unique (organization_id, user_account_id),
constraint memberships_status_check check (status in ('invited','active','suspended','revoked'))
);
create index if not exists idx_memberships_user_status
on memberships(user_account_id, status);
create table if not exists roles (
id uuid primary key default gen_random_uuid(),
organization_id uuid references organizations(id) on delete cascade,
role_key text not null,
display_name text not null,
description text,
is_system boolean not null default false,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint roles_scope_key_unique unique (organization_id, role_key)
);
create table if not exists permissions (
permission_key text primary key,
description text not null,
created_at timestamptz not null default now()
);
create table if not exists role_permissions (
role_id uuid not null references roles(id) on delete cascade,
permission_key text not null references permissions(permission_key) on delete cascade,
created_at timestamptz not null default now(),
primary key (role_id, permission_key)
);
create table if not exists membership_roles (
membership_id uuid not null references memberships(id) on delete cascade,
role_id uuid not null references roles(id) on delete cascade,
location_id uuid references locations(id) on delete cascade,
created_at timestamptz not null default now(),
primary key (membership_id, role_id, location_id)
);
create index if not exists idx_membership_roles_membership
on membership_roles(membership_id);
create index if not exists idx_membership_roles_location
on membership_roles(location_id);
-- Cross-tenant integrity helpers:
-- a membership may only reference an employee from the same organization.
create or replace function enforce_membership_employee_same_org()
returns trigger
language plpgsql
as $$
declare
employee_org uuid;
begin
if new.employee_id is null then
return new;
end if;
select organization_id into employee_org
from employees
where id = new.employee_id;
if employee_org is null or employee_org <> new.organization_id then
raise exception 'membership employee must belong to same organization'
using errcode = '23514';
end if;
return new;
end;
$$;
drop trigger if exists trg_membership_employee_same_org on memberships;
create trigger trg_membership_employee_same_org
before insert or update of employee_id, organization_id
on memberships
for each row
execute function enforce_membership_employee_same_org();
-- Role assignment scope integrity.
create or replace function enforce_membership_role_scope()
returns trigger
language plpgsql
as $$
declare
membership_org uuid;
role_org uuid;
location_org uuid;
begin
select organization_id into membership_org
from memberships where id = new.membership_id;
select organization_id into role_org
from roles where id = new.role_id;
if membership_org is null then
raise exception 'membership does not exist'
using errcode = '23503';
end if;
if role_org is not null and role_org <> membership_org then
raise exception 'role belongs to another organization'
using errcode = '23514';
end if;
if new.location_id is not null then
select organization_id into location_org
from locations where id = new.location_id;
if location_org is null or location_org <> membership_org then
raise exception 'location belongs to another organization'
using errcode = '23514';
end if;
end if;
return new;
end;
$$;
drop trigger if exists trg_membership_role_scope on membership_roles;
create trigger trg_membership_role_scope
before insert or update
on membership_roles
for each row
execute function enforce_membership_role_scope();