-
Notifications
You must be signed in to change notification settings - Fork 19
/
0089.sql
47 lines (45 loc) · 1.67 KB
/
0089.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
CREATE TABLE barcodes.campaigns (
campaign_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
title varchar NOT NULL UNIQUE,
instructions text,
header_image varchar,
permitted_countries text,
language_key varchar,
accepting_participants boolean DEFAULT FALSE,
language_key_alt varchar,
title_alt varchar,
instructions_alt text
);
CREATE TABLE barcodes.interested_users (
interested_user_id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
campaign_id uuid NOT NULL,
acquisition_source varchar,
first_name varchar NOT NULL,
last_name varchar NOT NULL,
email varchar NOT NULL,
phone varchar,
address_1 varchar,
address_2 varchar,
city varchar,
state varchar,
postal_code varchar,
country varchar,
latitude double precision,
longitude double precision,
confirm_consent boolean not null DEFAULT FALSE,
ip_address varchar,
creation_timestamp timestamp not null DEFAULT NOW(),
update_timestamp timestamp,
address_checked boolean not null DEFAULT FALSE,
address_valid boolean not null DEFAULT FALSE,
converted_to_account boolean not null DEFAULT FALSE,
converted_to_account_timestamp timestamp,
CONSTRAINT fk_campaign_id FOREIGN KEY ( campaign_id ) REFERENCES barcodes.campaigns ( campaign_id )
);
CREATE TABLE barcodes.campaigns_projects (
campaign_id uuid NOT NULL,
project_id bigint NOT NULL,
CONSTRAINT campaign_project_pkey PRIMARY KEY ( campaign_id, project_id ),
CONSTRAINT fk_campaign_id FOREIGN KEY ( campaign_id ) REFERENCES barcodes.campaigns( campaign_id ),
CONSTRAINT fk_project_id FOREIGN KEY ( project_id ) REFERENCES barcodes.project( project_id )
);