Something went wrong. Try again.
lustre gleam
Something went wrong. Try again.
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374-- extensionscreate extension pg_trgm;
-- types
create type user_role_enum as enum ( 'admin', 'analyst', 'captain', 'developer', 'firefighter', 'sargeant', 'none');
-- tables
create table user_account ( id uuid default uuidv7(), user_role user_role_enum not null, full_name text not null, password_hash text not null, phone text unique not null, email text unique not null check (trim(email) like '%@%'), is_active boolean not null default true, created_at timestamp not null default current_timestamp, updated_at timestamp not null default current_timestamp,
search_email text generated always as (lower(email)) stored,
primary key (id));
create index idx_user_account_search_emailon user_account (search_email);
create index idx_user_account_search_nameon user_account using gin (full_name gin_trgm_ops);
create table crew ( id uuid default uuidv7(), crew_leader uuid not null references user_account (id) on update cascade on delete cascade, crew_name text unique not null, is_active boolean not null default true, created_at timestamp not null default current_timestamp, updated_at timestamp not null default current_timestamp,
primary key (id));
create index idx_crew_leaderon crew (crew_leader);
create table crew_membership ( id uuid default uuidv7(), crew_id uuid not null references crew (id) on update cascade on delete cascade, user_id uuid not null references user_account (id) on update cascade on delete cascade,
primary key (id), unique (crew_id, user_id));
create index idx_crew_membership_crewon crew_membership (crew_id);
create index idx_crew_membership_useron crew_membership (user_id);