apps/backend/src/infrastructure/database/migrations/0057_m1_places_scope.sqlSQL
-- M1 Place scope foundation. Places extend durable Locations without replacing them.
create table if not exists places (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
location_id uuid not null references locations(id) on delete restrict,
name text not null,
code text,
place_type text not null default 'permanent',
status text not null default 'planned',
starts_at timestamptz,
ends_at timestamptz,
created_by_user_id uuid references user_accounts(id) on delete set null,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
archived_at timestamptz,
version bigint not null default 1,
constraint places_org_location_code_unique unique (organization_id, location_id, code),
constraint places_type_check check (place_type in ('permanent','temporary_fixed','seasonal','recurring','mobile','external')),
constraint places_status_check check (status in ('planned','active','closing','closed','archived')),
constraint places_period_check check (ends_at is null or starts_at is null or ends_at > starts_at)
);
create index if not exists idx_places_org_location_status on places(organization_id, location_id, status);
create index if not exists idx_places_active_period on places(organization_id, starts_at, ends_at);