V1__create_schema.sql
4,558 bytes
| 1 | create table app_user ( |
|---|---|
| 2 | id uuid primary key, |
| 3 | display_name varchar(80) not null, |
| 4 | email varchar(254) not null unique, |
| 5 | password_hash varchar(100) not null, |
| 6 | share_slug varchar(100) not null unique, |
| 7 | home_city varchar(100), |
| 8 | home_country_code varchar(2), |
| 9 | bio varchar(280), |
| 10 | avatar_url varchar(500), |
| 11 | profile_public boolean not null default false, |
| 12 | created_at timestamp with time zone not null, |
| 13 | updated_at timestamp with time zone not null |
| 14 | ); |
| 15 | |
| 16 | create table session_token ( |
| 17 | id uuid primary key, |
| 18 | user_id uuid not null references app_user(id) on delete cascade, |
| 19 | token_hash varchar(64) not null unique, |
| 20 | expires_at timestamp with time zone not null, |
| 21 | created_at timestamp with time zone not null |
| 22 | ); |
| 23 | |
| 24 | create index idx_session_token_user on session_token(user_id); |
| 25 | create index idx_session_token_expiry on session_token(expires_at); |
| 26 | |
| 27 | create table destination ( |
| 28 | code varchar(2) primary key, |
| 29 | name varchar(100) not null, |
| 30 | local_name varchar(100) not null, |
| 31 | region_name varchar(100) not null, |
| 32 | summary varchar(500) not null, |
| 33 | center_lat double precision not null, |
| 34 | center_lng double precision not null, |
| 35 | accent_color varchar(7) not null, |
| 36 | display_order integer not null |
| 37 | ); |
| 38 | |
| 39 | create table dish ( |
| 40 | slug varchar(120) primary key, |
| 41 | destination_code varchar(2) not null references destination(code), |
| 42 | name varchar(140) not null, |
| 43 | local_name varchar(140), |
| 44 | category varchar(30) not null, |
| 45 | description varchar(500) not null, |
| 46 | why_it_matters varchar(500) not null, |
| 47 | importance smallint not null, |
| 48 | image_url varchar(500), |
| 49 | vegetarian boolean not null, |
| 50 | spicy_level smallint not null, |
| 51 | display_order integer not null, |
| 52 | constraint chk_dish_importance check (importance between 1 and 5), |
| 53 | constraint chk_dish_spicy_level check (spicy_level between 0 and 3) |
| 54 | ); |
| 55 | |
| 56 | create index idx_dish_destination on dish(destination_code, display_order); |
| 57 | |
| 58 | create table tasting ( |
| 59 | id uuid primary key, |
| 60 | user_id uuid not null references app_user(id) on delete cascade, |
| 61 | dish_slug varchar(120) not null references dish(slug), |
| 62 | restaurant_name varchar(160), |
| 63 | city varchar(100) not null, |
| 64 | country_code varchar(2) not null, |
| 65 | tasted_on date not null, |
| 66 | rating smallint not null, |
| 67 | note varchar(500), |
| 68 | photo_url varchar(500), |
| 69 | latitude double precision, |
| 70 | longitude double precision, |
| 71 | created_at timestamp with time zone not null, |
| 72 | updated_at timestamp with time zone not null, |
| 73 | constraint chk_tasting_rating check (rating between 1 and 5), |
| 74 | constraint chk_tasting_coordinates check ( |
| 75 | (latitude is null and longitude is null) |
| 76 | or (latitude between -90 and 90 and longitude between -180 and 180) |
| 77 | ) |
| 78 | ); |
| 79 | |
| 80 | create index idx_tasting_user_date on tasting(user_id, tasted_on desc); |
| 81 | create index idx_tasting_user_dish on tasting(user_id, dish_slug); |
| 82 | create index idx_tasting_trip_match on tasting(user_id, country_code, tasted_on); |
| 83 | |
| 84 | create table trip ( |
| 85 | id uuid primary key, |
| 86 | user_id uuid not null references app_user(id) on delete cascade, |
| 87 | destination_code varchar(2) not null references destination(code), |
| 88 | city varchar(100) not null, |
| 89 | starts_on date not null, |
| 90 | ends_on date not null, |
| 91 | created_at timestamp with time zone not null, |
| 92 | updated_at timestamp with time zone not null, |
| 93 | constraint chk_trip_dates check (ends_on >= starts_on) |
| 94 | ); |
| 95 | |
| 96 | create index idx_trip_user_dates on trip(user_id, starts_on, ends_on); |
| 97 | |
| 98 | create table trip_mission_item ( |
| 99 | id uuid primary key, |
| 100 | trip_id uuid not null references trip(id) on delete cascade, |
| 101 | dish_slug varchar(120) not null references dish(slug), |
| 102 | position smallint not null, |
| 103 | unique(trip_id, dish_slug), |
| 104 | unique(trip_id, position) |
| 105 | ); |
| 106 | |
| 107 | create table taste_challenge ( |
| 108 | id uuid primary key, |
| 109 | owner_id uuid not null references app_user(id), |
| 110 | title varchar(100) not null, |
| 111 | destination_code varchar(2) not null references destination(code), |
| 112 | join_code varchar(8) not null unique, |
| 113 | starts_on date not null, |
| 114 | ends_on date not null, |
| 115 | created_at timestamp with time zone not null, |
| 116 | constraint chk_challenge_dates check (ends_on >= starts_on) |
| 117 | ); |
| 118 | |
| 119 | create table challenge_participant ( |
| 120 | id uuid primary key, |
| 121 | challenge_id uuid not null references taste_challenge(id) on delete cascade, |
| 122 | user_id uuid not null references app_user(id) on delete cascade, |
| 123 | joined_at timestamp with time zone not null, |
| 124 | unique(challenge_id, user_id) |
| 125 | ); |
| 126 | |
| 127 | create index idx_challenge_participant_user on challenge_participant(user_id); |
| 128 | |