apps/backend/src/infrastructure/database/migrations/0060_m1_organization_graph.sqlSQL
-- M1 Organization Graph. Hierarchy is separate from the tenant security boundary.
create table if not exists organization_units (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
parent_unit_id uuid references organization_units(id) on delete restrict,
unit_type text not null default 'custom',
name text not null,
code text not 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 organization_units_org_code_unique unique (organization_id, code),
constraint organization_units_type_check check (unit_type in ('group','brand','region','property','cluster','custom')),
constraint organization_units_status_check check (status in ('active','inactive','archived')),
constraint organization_units_not_self_parent check (parent_unit_id is null or parent_unit_id<>id)
);
create index if not exists idx_organization_units_org_parent on organization_units(organization_id,parent_unit_id,status);
alter table locations add column if not exists organization_unit_id uuid references organization_units(id) on delete restrict;
create index if not exists idx_locations_organization_unit on locations(organization_unit_id);