Something went wrong. Try again.
lustre gleam
Something went wrong. Try again.
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172-- 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 (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 check (updated_at >= created_at),
primary key (id));
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 check (updated_at >= created_at),
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);