delibrium-postgrest/delibrium.schema.sql at master · Delibrium/delibrium-postgrest · 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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
create schema if not exists delibrium;
create schema if not exists delibrium_secure;

create table if not exists delibrium.community (
    id         bigserial     primary key,
    created_at timestamptz   not null default now(),
    changed_at timestamptz   not null default now(),
    name       text,
    config     jsonb
);

create table if not exists delibrium_secure.user_login (
    id                 bigserial     primary key,
    community_id          bigint        references delibrium.community (id),
    created_at         timestamptz   not null default now(),
    changed_at         timestamptz   not null default now(),
    session_count  int default 0,
    login              text          not null,
    password           text          not null
);

----------
--  user
----------

create table if not exists delibrium.users (
    id            bigserial         primary key,
    community_id     bigint            references delibrium.community (id),
    created_by    bigint            references delibrium.users (id),
    created_at    timestamptz       not null default now(),
    changed_by    bigint            references delibrium.users (id),
    changed_at    timestamptz       not null default now(),
    user_login_id bigint references delibrium_secure.user_login (id),
    first_name    text              not null,
    last_name     text              not null,
    email         text
);

alter table delibrium.community add column created_by bigint references delibrium.users (id);
alter table delibrium_secure.user_login add column delibrium_user_id bigint references delibrium.users (id);
alter table delibrium_secure.user_login add column created_by bigint references delibrium.users (id);
alter table delibrium_secure.user_login add column changed_by bigint references delibrium.users (id);

-------------
--  Idea
-------------

create table if not exists delibrium.idea_space (
    id          bigserial   primary key,
    community_id bigint        references delibrium.community (id),
    created_by  bigint      not null references delibrium.users (id),
    created_at  timestamptz not null default now(),
    changed_by  bigint      not null references delibrium.users (id),
    changed_at  timestamptz not null default now(),
    title       text        not null,
    description text        not null,
    slug        text
);

create type delibrium.phase as enum
    ('edit_topics', 'feasibility', 'vote', 'finished');

create table if not exists delibrium.topic (
    id          bigserial   primary key,
    community_id   bigint      references delibrium.community (id),
    created_by  bigint      not null references delibrium.users (id),
    created_at  timestamptz not null default now(),
    changed_by  bigint      not null references delibrium.users (id),
    changed_at  timestamptz not null default now(),
    title       text        not null,
    description text        not null,
    image       text        not null,
    idea_space  bigint      references delibrium.idea_space (id),  -- 'null' == 'communityspace'
    phase       delibrium.phase  not null
);

create table if not exists delibrium.category (
    id          bigserial   primary key,
    community_id   bigint      references delibrium.community (id),
    name        text        not null,
    description text
);

create table if not exists delibrium.feasible (
    id         bigserial   primary key,
    community_id  bigint      references delibrium.community (id),
    created_by bigint      not null references delibrium.users (id),
    created_at timestamptz not null default now(),
    changed_by bigint      not null references delibrium.users (id),
    changed_at timestamptz not null default now(),
    val        bool        not null,
    reason     text
);

create table if not exists delibrium.idea (
    id          bigserial   primary key,
    community_id   bigint      references delibrium.community (id),
    created_by  bigint      not null references delibrium.users (id),
    created_at  timestamptz not null default now(),
    changed_by  bigint      not null references delibrium.users (id),
    changed_at  timestamptz not null default now(),
    title       text        not null,
    description text        not null,
    category    bigint      references delibrium.category (id),
    idea_space  bigint      references delibrium.idea_space (id),  -- 'null' == 'communityspace'
    topic       bigint      references delibrium.topic (id),
    feasible    bigint      references delibrium.feasible (id)
);

create table if not exists delibrium.idea_like (
    community_id bigint        references delibrium.community (id),
    idea        bigint      not null references delibrium.idea (id),
    created_by  bigint      not null references delibrium.users (id),
    created_at  timestamptz not null default now(),
    changed_by  bigint      not null references delibrium.users (id),
    changed_at  timestamptz not null default now()
);

create type delibrium.idea_vote_value as enum
    ('yes', 'no');

create table if not exists delibrium.idea_vote (
    community_id bigint                references delibrium.community (id),
    idea       bigint               not null references delibrium.idea (id),
    created_by bigint               not null references delibrium.users (id),
    created_at timestamptz          not null default now(),
    changed_by bigint               not null references delibrium.users (id),
    changed_at timestamptz          not null default now(),
    val        delibrium.idea_vote_value not null
);


----------------------------------------------------------------------
-- comment

create table if not exists delibrium.comment (
    community_id      bigint      references delibrium.community (id),
    id             bigserial   primary key,
    created_by     bigint      not null references delibrium.users (id),
    created_at     timestamptz not null default now(),
    changed_by     bigint      not null references delibrium.users (id),
    changed_at     timestamptz not null default now(),
    text           text        not null,
    parent_comment bigint      references delibrium.comment (id),
    parent_idea    bigint      references delibrium.idea (id)
);

create type delibrium.up_down as enum
    ('up', 'down');

create table if not exists delibrium.comment_vote (
    community_id bigint            references delibrium.community (id),
    comment    bigint           not null references delibrium.comment (id),
    created_by bigint           not null references delibrium.users (id),
    created_at timestamptz      not null default now(),
    changed_by bigint           not null references delibrium.users (id),
    changed_at timestamptz      not null default now(),
    val        delibrium.up_down     not null
);


----------------------------------------------------------------------
-- idea space

create table if not exists delibrium.community_class (
    community_id bigint        references delibrium.community (id),
    id          bigserial   primary key,
    created_by  bigint      not null references delibrium.users (id),
    created_at  timestamptz not null default now(),
    changed_by  bigint      not null references delibrium.users (id),
    changed_at  timestamptz not null default now(),
    class_name  text        not null,
    community_year text        not null
);

create type delibrium.group_id as enum
    ('student', 'class_guest', 'community_guest', 'moderator', 'principal', 'community_admin', 'admin');

create table if not exists delibrium.user_group (
    community_id    bigint         references delibrium.community (id),
    user_id      bigint         not null references delibrium.users (id) on delete cascade,
    group_id     delibrium.group_id  not null,
    community_class bigint         references delibrium.community_class (id),
    unique (user_id, group_id, community_class)
);

create table if not exists delibrium.delegation (
    id                 bigserial   primary key,
    community_id bigint   references  delibrium.community (id),
    created_by         bigint      not null references delibrium.users (id),
    created_at         timestamptz not null default now(),
    changed_by         bigint      not null references delibrium.users (id),
    changed_at         timestamptz not null default now(),
    context_idea_space bigint      references delibrium.idea_space (id),
        -- 'null' == 'communityspace'
    context_topic      bigint      references delibrium.topic (id),
    context_idea       bigint      references delibrium.idea (id),
    from_user          bigint      not null references delibrium.users (id),
    to_user            bigint      not null references delibrium.users (id)
    -- fixme: constraint: at most one of context_idea_space,
    -- context_topic, context_idea is not null.
);