openletter/database.sql at master · joncle/openletter · GitHub

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
create database if not exists `openletter`;

use `openletter`;

create table if not exists `metadata` (
    `id` bigint primary key auto_increment,
    `name` varchar(255) not null,
    `val` varchar(255),

    index index_metadata_on_name (`name`)
) character set utf8mb4 collate utf8mb4_unicode_ci;

create table if not exists `users` (
    `id` bigint primary key auto_increment,
    `username` varchar(255) not null,
    `email` varchar(255) not null,
    `password` varchar(255) not null,
    `site_admin` tinyint(1) not null default 0,
    `created_at` datetime not null default current_timestamp,
    `updated_at` datetime not null default current_timestamp,

    constraint unique_users_email unique (`email`),
    index index_users_on_email (`email`)
) character set utf8mb4 collate utf8mb4_unicode_ci;

create table if not exists `signatories` (
    `id` bigint primary key auto_increment,
    `letter` varchar(10) not null default 'main',
    `se_acct_id` bigint,
    `display_name` varchar(255),
    `is_moderator` tinyint(1) not null default 0,
    `is_former_moderator` tinyint(1) not null default 0,
    `created_at` datetime not null default current_timestamp,
    `updated_at` datetime not null default current_timestamp,

    constraint unique_signatories_se_acct_id unique (`se_acct_id`, `letter`),
    index index_signatories_on_letter (`letter`),
    index index_signatories_on_se_acct_id (`se_acct_id`),
    index index_signatories_on_display_name (`display_name`)
) character set utf8mb4 collate utf8mb4_unicode_ci;