structure.sql 8.64 KB
Newer Older
Florian Azizian's avatar
Florian Azizian committed
1
2
3
4
5
6
7
8
9
10
11
-- core/sql/structure/core.postgresql.sql

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;

12
13
CREATE EXTENSION IF NOT EXISTS unaccent;

14

15
16
DROP TABLE IF EXISTS adr_attachments;
CREATE TABLE adr_attachments
Florian Azizian's avatar
Florian Azizian committed
17
18
(
  id serial NOT NULL,
19
20
21
22
23
24
25
  attachment_id INTEGER NOT NULL,
  type character varying(32) NOT NULL,
  path character varying(255) NOT NULL,
  filename character varying(255) NOT NULL,
  fingerprint character varying(255) NOT NULL,
  CONSTRAINT adr_attachments_pkey PRIMARY KEY (id),
  CONSTRAINT adr_attachments_unique_key UNIQUE (attachment_id, type)
Damien's avatar
Damien committed
26
27
)
WITH (OIDS=FALSE);
Florian Azizian's avatar
Florian Azizian committed
28

29
30
DROP TABLE IF EXISTS adr_main_documents;
CREATE TABLE adr_main_documents
Damien's avatar
Damien committed
31
32
(
  id serial NOT NULL,
33
34
35
36
37
38
39
  main_document_id INTEGER NOT NULL,
  type character varying(32) NOT NULL,
  path character varying(255) NOT NULL,
  filename character varying(255) NOT NULL,
  fingerprint character varying(255) NOT NULL,
  CONSTRAINT adr_main_documents_pkey PRIMARY KEY (id),
  CONSTRAINT adr_main_documents_unique_key UNIQUE (main_document_id, type)
Damien's avatar
Damien committed
40
41
42
)
WITH (OIDS=FALSE);

43
44
DROP TABLE IF EXISTS attachments;
CREATE TABLE attachments
Damien's avatar
Damien committed
45
46
(
  id serial NOT NULL,
47
48
49
50
51
52
  main_document_id INTEGER NOT NULL,
  title text NOT NULL,
  reference CHARACTER VARYING(64),
  creation_date timestamp without time zone NOT NULL DEFAULT NOW(),
  modification_date timestamp without time zone DEFAULT NOW(),
  CONSTRAINT attachments_pkey PRIMARY KEY (id)
Damien's avatar
Damien committed
53
54
55
)
WITH (OIDS=FALSE);

56
57
DROP TABLE IF EXISTS configurations;
CREATE TABLE configurations
Damien's avatar
Damien committed
58
(
59
60
id serial NOT NULL,
identifier CHARACTER VARYING (64) NOT NULL,
61
label text NOT NULL,
Damien's avatar
Damien committed
62
value jsonb DEFAULT '{}' NOT NULL,
63
CONSTRAINT configuration_pkey PRIMARY KEY (id)
Damien's avatar
Damien committed
64
65
66
)
WITH (OIDS=FALSE);

67
68
DROP TABLE IF EXISTS docservers;
CREATE TABLE docservers
Florian Azizian's avatar
Florian Azizian committed
69
(
Damien's avatar
Damien committed
70
  id serial NOT NULL,
71
72
73
74
75
76
77
78
  type character varying(32) NOT NULL,
  label character varying(255),
  is_readonly character(1) NOT NULL DEFAULT 'N'::bpchar,
  size_limit_number bigint NOT NULL DEFAULT (0)::bigint,
  actual_size_number bigint NOT NULL DEFAULT (0)::bigint,
  path character varying(255) NOT NULL,
  CONSTRAINT docservers_pkey PRIMARY KEY (id),
  CONSTRAINT docservers_type_key UNIQUE (type)
Florian Azizian's avatar
Florian Azizian committed
79
80
81
)
WITH (OIDS=FALSE);

82
83
DROP TABLE IF EXISTS emails;
CREATE TABLE emails
Damien's avatar
Damien committed
84
85
(
  id serial NOT NULL,
86
87
88
89
90
  user_id INTEGER NOT NULL,
  sender CHARACTER VARYING(128) NOT NULL,
  recipients json DEFAULT '[]' NOT NULL,
  cc json DEFAULT '[]' NOT NULL,
  cci json DEFAULT '[]' NOT NULL,
Damien's avatar
Damien committed
91
  subject CHARACTER VARYING(256),
92
93
94
95
96
97
98
  body text,
  document json,
  is_html boolean NOT NULL DEFAULT TRUE,
  status CHARACTER VARYING(16) NOT NULL,
  creation_date timestamp without time zone NOT NULL,
  send_date timestamp without time zone,
CONSTRAINT emails_pkey PRIMARY KEY (id)
Damien's avatar
Damien committed
99
100
101
)
WITH (OIDS=FALSE);

102
103
104
105
106
107
108
109
DROP TABLE IF EXISTS external_signatory_book;
CREATE TABLE external_signatory_book
(
    id serial NOT NULL,
    label character varying(255) NOT NULL,
    type character varying(128) NOT NULL,
    connection_data jsonb DEFAULT '{}',
    otp_code jsonb DEFAULT '{}',
110
    message_content jsonb DEFAULT '{}',
111
112
113
114
    CONSTRAINT external_signatory_book_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

115
116
DROP TABLE IF EXISTS groups;
CREATE TABLE groups
Florian Azizian's avatar
Florian Azizian committed
117
(
Florian Azizian's avatar
Florian Azizian committed
118
  id serial NOT NULL,
119
120
  label character varying(128) NOT NULL,
  CONSTRAINT groups_pkey PRIMARY KEY (id)
Florian Azizian's avatar
Florian Azizian committed
121
122
)
WITH (OIDS=FALSE);
123

Damien's avatar
Damien committed
124
125
126
127
128
129
130
131
132
133
134
DROP TABLE IF EXISTS groups_privileges;
CREATE TABLE groups_privileges
(
  id serial NOT NULL,
  group_id INTEGER NOT NULL,
  privilege character varying(128) NOT NULL,
  CONSTRAINT groups_privileges_pkey PRIMARY KEY (id),
  CONSTRAINT groups_privileges_unique_key UNIQUE (group_id, privilege)
)
WITH (OIDS=FALSE);

135
136
137
138
DROP TABLE IF EXISTS history;
CREATE TABLE history
(
  id serial NOT NULL,
Damien's avatar
Damien committed
139
140
141
142
  code CHARACTER VARYING(2) NOT NULL,
  object_type CHARACTER VARYING(128) NOT NULL,
  object_id  CHARACTER VARYING(32) NOT NULL,
  type CHARACTER VARYING(64) NOT NULL,
Damien's avatar
Damien committed
143
  user_id INTEGER NOT NULL,
144
  "user" text NOT NULL,
Damien's avatar
Damien committed
145
146
147
148
  date TIMESTAMP without TIME ZONE NOT NULL,
  message text NOT NULL,
  data jsonb NOT NULL DEFAULT '{}',
  ip CHARACTER VARYING(64) NOT NULL,
149
150
151
  CONSTRAINT history_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
Damien's avatar
Damien committed
152

153
154
155
156
157
158
159
160
161
DROP TABLE IF EXISTS main_documents;
CREATE TABLE main_documents
(
  id serial NOT NULL,
  title text NOT NULL,
  reference CHARACTER VARYING(64),
  description text,
  sender text NOT NULL,
  deadline timestamp without time zone,
162
163
  notes jsonb,
  link_id text,
164
165
166
  metadata jsonb NOT NULL DEFAULT '{}',
  creation_date timestamp without time zone NOT NULL DEFAULT NOW(),
  modification_date timestamp without time zone DEFAULT NOW(),
167
  typist INTEGER,
168
  status CHARACTER VARYING(10),
169
  digital_signature_transaction_id text,
Damien's avatar
Damien committed
170
  mailing_id text,
171
172
173
174
175
176
177
178
179
180
181
182
183
184
  CONSTRAINT main_documents_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

DROP TABLE IF EXISTS password_history;
CREATE TABLE password_history
(
  id serial,
  user_id INTEGER NOT NULL,
  password character varying(255) NOT NULL,
  CONSTRAINT password_history_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

Damien's avatar
Damien committed
185
186
187
188
189
DROP TABLE IF EXISTS password_rules;
CREATE TABLE password_rules
(
  id serial,
  label character varying(64) NOT NULL,
Damien's avatar
Damien committed
190
  "value" INTEGER NOT NULL,
Damien's avatar
Damien committed
191
192
193
194
195
196
  enabled boolean DEFAULT FALSE NOT NULL,
  CONSTRAINT password_rules_pkey PRIMARY KEY (id),
  CONSTRAINT password_rules_label_key UNIQUE (label)
)
WITH (OIDS=FALSE);

197
198
DROP TABLE IF EXISTS signatures;
CREATE TABLE signatures
Damien's avatar
Damien committed
199
(
200
  id serial NOT NULL,
Damien's avatar
Damien committed
201
  user_id INTEGER NOT NULL,
202
203
204
  path character varying(255) NOT NULL,
  filename character varying(255) NOT NULL,
  fingerprint character varying(255) NOT NULL,
205
  substituted boolean DEFAULT FALSE NOT NULL,
Damien's avatar
Damien committed
206
  external_application CHARACTER VARYING(255),
207
  CONSTRAINT signatures_pkey PRIMARY KEY (id)
Damien's avatar
Damien committed
208
209
)
WITH (OIDS=FALSE);
Damien's avatar
Damien committed
210

211
212
213
214
215
216
217
218
219
DROP TABLE IF EXISTS users;
CREATE TABLE users
(
  id serial NOT NULL,
  login character varying(128) NOT NULL,
  email character varying(128) NOT NULL,
  "password" character varying(255) NOT NULL,
  firstname character varying(128) NOT NULL,
  lastname character varying(128) NOT NULL,
Damien's avatar
Damien committed
220
  phone character varying(128),
221
  picture text,
Damien's avatar
Damien committed
222
  "isRest" boolean DEFAULT FALSE NOT NULL,
Damien's avatar
Damien committed
223
  preferences jsonb NOT NULL DEFAULT '{"lang" : "fr", "writingMode" : "direct", "writingSize" : 1, "writingColor" : "#000000", "notifications" : true}',
224
  substitute INTEGER DEFAULT NULL,
Damien's avatar
Damien committed
225
  refresh_token jsonb NOT NULL DEFAULT '[]',
Damien's avatar
Damien committed
226
  reset_token text DEFAULT NULL,
227
228
229
  password_modification_date timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
  failed_authentication INTEGER DEFAULT 0,
  locked_until TIMESTAMP without time zone,
230
  signature_modes jsonb DEFAULT '["stamp"]',
231
  x509_fingerprint text,
232
233
234
235
236
237
238
239
240
241
242
243
244
  CONSTRAINT users_pkey PRIMARY KEY (id),
  CONSTRAINT users_login_key UNIQUE (login)
)
WITH (OIDS=FALSE);

DROP TABLE IF EXISTS users_groups;
CREATE TABLE users_groups
(
    id serial NOT NULL,
    group_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    CONSTRAINT users_groups_pkey PRIMARY KEY (id),
    CONSTRAINT users_groups_unique_key UNIQUE (group_id, user_id)
Damien's avatar
Damien committed
245
246
)
WITH (OIDS=FALSE);
247
248
249
250
251

DROP TABLE IF EXISTS workflows;
CREATE TABLE workflows
(
    id serial NOT NULL,
252
    user_id INTEGER,
253
254
    main_document_id INTEGER NOT NULL,
    mode CHARACTER VARYING(16) NOT NULL,
Damien's avatar
Damien committed
255
    signature_mode CHARACTER VARYING(64) NOT NULL,
256
    signature_positions jsonb DEFAULT '[]',
257
    date_positions jsonb DEFAULT '[]',
258
259
260
261
    "order" INTEGER NOT NULL,
    status CHARACTER VARYING(16) DEFAULT NULL,
    note text DEFAULT NULL,
    process_date timestamp without time zone DEFAULT NULL,
262
    digital_signature_id text,
263
264
265
    CONSTRAINT workflow_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);
266

267
268
269
270
271
DROP TABLE IF EXISTS workflows_external_informations;
CREATE TABLE workflows_external_informations
(
    id serial NOT NULL,
    workflow_id INTEGER NOT NULL,
272
    external_signatory_book_id INTEGER NOT NULL,
273
274
275
276
277
278
279
280
281
    firstname character varying(128) NOT NULL,
    lastname character varying(128) NOT NULL,
    email character varying(128),
    phone character varying(128),
    informations jsonb DEFAULT '{}',
    CONSTRAINT workflows_external_informations_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
DROP TABLE IF EXISTS workflow_templates;
CREATE TABLE workflow_templates
(
    id SERIAL NOT NULL,
    title text NOT NULL,
    owner INTEGER NOT NULL,
    CONSTRAINT workflow_templates_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

DROP TABLE IF EXISTS workflow_templates_items;
CREATE TABLE workflow_templates_items
(
    id SERIAL NOT NULL,
    workflow_template_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    mode CHARACTER VARYING(64) NOT NULL,
    signature_mode CHARACTER VARYING(64) NOT NULL,
300
    "order" INTEGER NOT NULL,
301
302
303
    CONSTRAINT workflow_templates_items_pkey PRIMARY KEY (id)
)
WITH (OIDS=FALSE);