Database Migrations

Applied 2025-04-19 19:18:41

create initial database

 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
---- 
--
drop table if exists "item";
--
--
drop table if exists "collection";
--
--
drop table if exists "audit_record";
drop table if exists "audit";
--
--
--
--
--
create table if not exists "audit" (
"id" uuid not null,
"app" text not null,
"act" text not null,
"client" text not null,
"server" text not null,
"user" text not null,
"metadata" jsonb not null,
"message" text not null,
"started" timestamp not null default current_timestamp,
"completed" timestamp not null default current_timestamp,
primary key ("id")
);

create index if not exists "audit__act" on "audit" ("act");
create index if not exists "audit__app" on "audit" ("app");
create index if not exists "audit__client" on "audit" ("client");
create index if not exists "audit__server" on "audit" ("server");
create index if not exists "audit__user_id" on "audit" ("user");

create table if not exists "audit_record" (
"id" uuid not null,
"audit_id" uuid not null,
"t" text not null,
"pk" text not null,
"changes" jsonb not null,
"metadata" jsonb not null,
"occurred" timestamp not null default current_timestamp,
foreign key ("audit_id") references "audit" ("id"),
primary key ("id")
);

create index if not exists "audit_record__t" on "audit_record"("t");
create index if not exists "audit_record__pk" on "audit_record"("pk");
create index if not exists "audit_record__changes" on "audit_record"("changes");
create index if not exists "audit_record__metadata" on "audit_record"("metadata");

create index if not exists "audit_record__audit_id_idx" on "audit_record" ("audit_id");
--
--
create table if not exists "collection" (
"id" uuid not null,
"name" text not null,
"created" timestamp not null default current_timestamp,
primary key ("id")
);
--
--
create table if not exists "item" (
"id" uuid not null,
"collection_id" uuid not null,
"name" text not null,
"created" timestamp not null default current_timestamp,
foreign key ("collection_id") references "collection" ("id"),
primary key ("id")
);

create index if not exists "item__collection_id_idx" on "item" ("collection_id");
--
--
--
--
insert into "collection" (
"id", "name", "created"
) values (
'10000000-0000-0000-0000-000000000000', 'Test Collection', current_timestamp
) on conflict do nothing;
--
--
insert into "item" (
"id", "collection_id", "name", "created"
) values (
'10000000-0000-0000-0000-000000000001', '10000000-0000-0000-0000-000000000000', 'Test Item', current_timestamp
) on conflict do nothing;
--
--
--