public
anord
read
Ledger
Why work hard when you can work easier?
Languages
Repository composition by tracked source files.
TypeScript
86%
CSS
10%
SQL
3%
Shell
1%
HTML
0%
Trace
apps/api/src/db/migrations/001_init.sql
Trace helps you understand code history line by line. See who changed each line, when it changed, and which commit introduced it.
Author
Date
Commit
Line
Code
1
CREATE TABLE IF NOT EXISTS roles (
2
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
3
key TEXT NOT NULL UNIQUE,
4
name TEXT NOT NULL,
5
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
6
);
8
CREATE TABLE IF NOT EXISTS users (
9
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
10
email TEXT NOT NULL UNIQUE,
11
password_hash TEXT,
12
display_name TEXT NOT NULL,
13
primary_role_id UUID NOT NULL REFERENCES roles(id),
14
is_active BOOLEAN NOT NULL DEFAULT true,
15
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
16
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
17
);
19
CREATE TABLE IF NOT EXISTS groups (
20
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
21
name TEXT NOT NULL UNIQUE,
22
description TEXT,
23
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
24
);
26
CREATE TABLE IF NOT EXISTS group_memberships (
27
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
28
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
29
group_id UUID NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
30
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
31
UNIQUE(user_id, group_id)
32
);
34
CREATE TABLE IF NOT EXISTS spaces (
35
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
36
name TEXT NOT NULL,
37
key TEXT NOT NULL UNIQUE,
38
description TEXT,
39
visibility TEXT NOT NULL CHECK (visibility IN ('public', 'internal', 'restricted')),
40
created_by_user_id UUID REFERENCES users(id),
41
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
42
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
43
);
45
CREATE TABLE IF NOT EXISTS pages (
46
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
47
space_id UUID NOT NULL REFERENCES spaces(id) ON DELETE CASCADE,
48
parent_page_id UUID REFERENCES pages(id) ON DELETE SET NULL,
49
title TEXT NOT NULL,
50
slug TEXT NOT NULL UNIQUE,
51
excerpt TEXT,
52
visibility TEXT NOT NULL CHECK (visibility IN ('public', 'internal', 'restricted')),
53
state TEXT NOT NULL CHECK (state IN ('draft', 'published')),
54
is_public BOOLEAN NOT NULL DEFAULT false,
55
current_revision_id UUID,
56
owner_user_id UUID REFERENCES users(id),
57
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
58
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
59
);
61
CREATE TABLE IF NOT EXISTS page_revisions (
62
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
63
page_id UUID NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
64
title TEXT NOT NULL,
65
slug TEXT NOT NULL,
66
excerpt TEXT,
67
visibility TEXT NOT NULL CHECK (visibility IN ('public', 'internal', 'restricted')),
68
state TEXT NOT NULL CHECK (state IN ('draft', 'published')),
69
body_markdown TEXT NOT NULL,
70
frontmatter JSONB NOT NULL DEFAULT '{}'::jsonb,
71
tag_snapshot JSONB NOT NULL DEFAULT '[]'::jsonb,
72
edited_by_user_id UUID REFERENCES users(id),
73
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
74
);
76
ALTER TABLE pages
77
ADD CONSTRAINT pages_current_revision_fk
78
FOREIGN KEY (current_revision_id) REFERENCES page_revisions(id) ON DELETE SET NULL;
80
CREATE TABLE IF NOT EXISTS page_permissions (
81
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
82
page_id UUID NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
83
role_key TEXT REFERENCES roles(key) ON DELETE CASCADE,
84
group_id UUID REFERENCES groups(id) ON DELETE CASCADE,
85
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
86
CHECK (role_key IS NOT NULL OR group_id IS NOT NULL)
87
);
89
CREATE TABLE IF NOT EXISTS tags (
90
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
91
name TEXT NOT NULL UNIQUE,
92
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
93
);
95
CREATE TABLE IF NOT EXISTS page_tags (
96
page_id UUID NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
97
tag_id UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
98
PRIMARY KEY (page_id, tag_id)
99
);
101
CREATE TABLE IF NOT EXISTS attachments (
102
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
103
page_id UUID REFERENCES pages(id) ON DELETE SET NULL,
104
file_name TEXT NOT NULL,
105
content_type TEXT NOT NULL,
106
storage_path TEXT NOT NULL,
107
size_bytes BIGINT NOT NULL,
108
created_by_user_id UUID REFERENCES users(id),
109
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
110
);
112
CREATE TABLE IF NOT EXISTS feedback (
113
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
114
page_id UUID NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
115
page_revision_id UUID REFERENCES page_revisions(id) ON DELETE SET NULL,
116
user_id UUID REFERENCES users(id) ON DELETE SET NULL,
117
helpful BOOLEAN NOT NULL,
118
comment TEXT,
119
is_reviewed BOOLEAN NOT NULL DEFAULT false,
120
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
121
);
123
CREATE TABLE IF NOT EXISTS searches (
124
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
125
user_id UUID REFERENCES users(id) ON DELETE SET NULL,
126
query TEXT NOT NULL,
127
visible_scope TEXT NOT NULL,
128
results_count INT NOT NULL DEFAULT 0,
129
resulted_in_support_action BOOLEAN NOT NULL DEFAULT false,
130
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
131
);
133
CREATE TABLE IF NOT EXISTS search_results (
134
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
135
search_id UUID NOT NULL REFERENCES searches(id) ON DELETE CASCADE,
136
page_id UUID REFERENCES pages(id) ON DELETE SET NULL,
137
position INT NOT NULL,
138
clicked BOOLEAN NOT NULL DEFAULT false,
139
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
140
);
142
CREATE TABLE IF NOT EXISTS webhooks (
143
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
144
name TEXT NOT NULL,
145
target_url TEXT NOT NULL,
146
signing_secret TEXT NOT NULL,
147
is_active BOOLEAN NOT NULL DEFAULT true,
148
events JSONB NOT NULL DEFAULT '[]'::jsonb,
149
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
150
);
152
CREATE TABLE IF NOT EXISTS webhook_deliveries (
153
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
154
webhook_id UUID NOT NULL REFERENCES webhooks(id) ON DELETE CASCADE,
155
event_name TEXT NOT NULL,
156
payload JSONB NOT NULL,
157
response_status INT,
158
response_body TEXT,
159
delivered_at TIMESTAMPTZ,
160
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
161
);
163
CREATE TABLE IF NOT EXISTS integrations (
164
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
165
provider TEXT NOT NULL,
166
name TEXT NOT NULL,
167
config JSONB NOT NULL DEFAULT '{}'::jsonb,
168
is_enabled BOOLEAN NOT NULL DEFAULT false,
169
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
170
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
171
);
173
CREATE TABLE IF NOT EXISTS integration_sync_jobs (
174
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
175
integration_id UUID NOT NULL REFERENCES integrations(id) ON DELETE CASCADE,
176
source_identifier TEXT NOT NULL,
177
status TEXT NOT NULL DEFAULT 'pending',
178
metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
179
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
180
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
181
);
183
CREATE TABLE IF NOT EXISTS smtp_settings (
184
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
185
host TEXT,
186
port INT NOT NULL DEFAULT 587,
187
username TEXT,
188
password_encrypted TEXT,
189
from_email TEXT NOT NULL,
190
from_name TEXT NOT NULL,
191
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
192
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
193
);
195
CREATE TABLE IF NOT EXISTS branding_settings (
196
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
197
site_name TEXT NOT NULL DEFAULT 'Ledger',
198
logo_url TEXT,
199
brand_color TEXT NOT NULL DEFAULT '#245cff',
200
footer_text TEXT,
201
public_knowledge_base_enabled BOOLEAN NOT NULL DEFAULT true,
202
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
203
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
204
);
206
CREATE TABLE IF NOT EXISTS ai_settings (
207
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
208
provider TEXT NOT NULL DEFAULT 'none',
209
model TEXT NOT NULL DEFAULT '',
210
encrypted_api_key TEXT,
211
is_enabled BOOLEAN NOT NULL DEFAULT false,
212
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
213
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
214
);
216
CREATE TABLE IF NOT EXISTS audit_logs (
217
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
218
actor_user_id UUID REFERENCES users(id) ON DELETE SET NULL,
219
action TEXT NOT NULL,
220
resource_type TEXT NOT NULL,
221
resource_id TEXT NOT NULL,
222
metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
223
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
224
);
226
CREATE INDEX IF NOT EXISTS idx_pages_space_id ON pages(space_id);
227
CREATE INDEX IF NOT EXISTS idx_pages_parent_page_id ON pages(parent_page_id);
228
CREATE INDEX IF NOT EXISTS idx_pages_visibility_state ON pages(visibility, state);
229
CREATE INDEX IF NOT EXISTS idx_page_revisions_page_id ON page_revisions(page_id);
230
CREATE INDEX IF NOT EXISTS idx_feedback_page_id ON feedback(page_id);
231
CREATE INDEX IF NOT EXISTS idx_searches_query ON searches(query);
232
CREATE INDEX IF NOT EXISTS idx_webhook_deliveries_webhook_id ON webhook_deliveries(webhook_id);
233
CREATE INDEX IF NOT EXISTS idx_integration_sync_jobs_integration_id ON integration_sync_jobs(integration_id);
235
CREATE INDEX IF NOT EXISTS idx_page_revisions_search
236
ON page_revisions
237
USING GIN (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body_markdown, '')));