-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathdatabase-schema.drawio
More file actions
151 lines (151 loc) · 22.3 KB
/
Copy pathdatabase-schema.drawio
File metadata and controls
151 lines (151 loc) · 22.3 KB
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
<mxfile host="65bd71144e">
<diagram id="erd-gateway" name="PostgreSQL ERD">
<mxGraphModel dx="2175" dy="1583" grid="1" gridSize="10" guides="1" tooltips="1" connect="1" arrows="1" fold="1" page="1" pageScale="1" pageWidth="1600" pageHeight="1200" math="0" shadow="0">
<root>
<mxCell id="0"/>
<mxCell id="1" parent="0"/>
<mxCell id="workspaces" value="<b>workspaces</b><hr><b>id:</b>&nbsp;<span style="white-space: pre;">	</span><span style="color: rgb(0, 0, 0);">PK&nbsp;</span>UUID DEFAULT gen_random_uuid()<br>name: VARCHAR(255) NOT NULL<br>slug:&nbsp; TEXT NOT NULL UNIQUE<br>owner_id: FK -&gt; users (id)<br>status: TEXT (active | suspended)<br>is_private: BOOL , default: true<br>hashed_pin_code: TEXT<br>icon_key: VARCHAR(64)<br><br><div>created_at, updated_at TIMESTAMPTZ</div>" style="rounded=0;whiteSpace=wrap;html=1;align=left;verticalAlign=top;fillColor=#dae8fc;strokeColor=#6c8ebf;fontSize=11;spacing=6;" parent="1" vertex="1">
<mxGeometry x="340" y="70" width="380" height="200" as="geometry"/>
</mxCell>
<mxCell id="users" value="<b>users</b><hr><b>id:&nbsp;</b>PK UUID<br>email: TEXT NOT NULL UNIQUE<br>password:&nbsp; TEXT - Hashed<br>first_name: TEXT<br>last_name: TEXT - NULL<div><div><div><font color="#000000">status: active | suspended | blocked<br></font><div>created_at, updated_at</div></div></div></div>" style="rounded=0;whiteSpace=wrap;html=1;align=left;verticalAlign=top;fillColor=#d5e8d4;strokeColor=#82b366;fontSize=11;spacing=6;" parent="1" vertex="1">
<mxGeometry x="-80" y="20" width="290" height="140" as="geometry"/>
</mxCell>
<mxCell id="4" style="edgeStyle=orthogonalEdgeStyle;html=1;entryX=1;entryY=0.5;entryDx=0;entryDy=0;rounded=1;endArrow=ERone;endFill=0;startArrow=ERmany;startFill=0;strokeWidth=2;" parent="1" source="workspace_members" target="3" edge="1">
<mxGeometry relative="1" as="geometry"/>
</mxCell>
<mxCell id="workspace_members" value="<b>workspace_members</b><hr>PK (<b>workspace_id</b>, <b>user_id</b>)<br>FK workspace_id → workspaces(id) CASCADE<br>FK user_id → users(id) CASCADE<br>FK <b>role_id</b> → roles(id) RESTRICT<br>joined_at TIMESTAMPTZ" style="rounded=0;whiteSpace=wrap;html=1;align=left;verticalAlign=top;fillColor=#fff2cc;strokeColor=#d6b656;fontSize=11;spacing=6;" parent="1" vertex="1">
<mxGeometry x="110" y="310" width="280" height="120" as="geometry"/>
</mxCell>
<mxCell id="invitations" value="<b>invitations</b><hr><b>id: </b>PK&nbsp;UUID<br>FK workspace_id → workspaces CASCADE<br>email, role, token_hash<br>expires_at, status (pending | accepted | revoked)<br>created_at<br>UNIQUE partial idx: (workspace_id, email) WHERE pending" style="rounded=0;whiteSpace=wrap;html=1;align=left;verticalAlign=top;fillColor=#fff2cc;strokeColor=#d6b656;fontSize=11;spacing=6;" parent="1" vertex="1">
<mxGeometry x="-400" y="160" width="300" height="150" as="geometry"/>
</mxCell>
<mxCell id="workspace_channels" value="<b>workspace_channels</b><hr>PK (workspace_id, channel_type)<br>FK workspace_id → workspaces CASCADE<br>channel_type ( email | sms | push | chat )<br>enabled BOOLEAN" style="rounded=0;whiteSpace=wrap;html=1;align=left;verticalAlign=top;fillColor=#e1d5e7;strokeColor=#9673a6;fontSize=11;spacing=6;" parent="1" vertex="1">
<mxGeometry x="-260" y="520" width="260" height="110" as="geometry"/>
</mxCell>
<mxCell id="integrations" value="<b>integrations</b><hr><b>id:</b>&nbsp;<span style="color: rgb(0, 0, 0);">PK&nbsp;</span>UUID<br>FK workspace_id → workspaces CASCADE<br>FK provider_id&nbsp;<span style="color: rgb(0, 0, 0);">→ providers RESTRICTED&nbsp;</span><div><font color="#000000">channel_type<br></font>encrypted_config BYTEA<br>status ( connected | error | disconnected )<br>is_default: BOOLEAN</div><div><br>UNIQUE (workspace_id, provider_id)<br>UNIQUE partial: one default per (workspace_id, channel_type)</div>" style="rounded=0;whiteSpace=wrap;html=1;align=left;verticalAlign=top;fillColor=#f8cecc;strokeColor=#b85450;fontSize=11;spacing=6;" parent="1" vertex="1">
<mxGeometry x="345" y="-235" width="360" height="190" as="geometry"/>
</mxCell>
<mxCell id="api_keys" value="<b>api_keys</b><hr>PK <b>id</b> UUID<br>FK workspace_id → workspaces CASCADE<br>client_id UNIQUE,&nbsp;<div>client_secret_hash<br>name,&nbsp;</div><div>is_active,&nbsp;</div><div>last_used_at<br>created_at,&nbsp;</div><div>expires_at</div>" style="rounded=0;whiteSpace=wrap;html=1;align=left;verticalAlign=top;fillColor=#f8cecc;strokeColor=#b85450;fontSize=11;spacing=6;" parent="1" vertex="1">
<mxGeometry x="800" y="20" width="260" height="160" as="geometry"/>
</mxCell>
<mxCell id="message_request_logs" value="<b>message_request_logs</b><hr>PK <b>id</b> UUID<br>FK workspace_id → workspaces CASCADE<br>FK <b>api_key_id</b> → api_keys(id) SET NULL<br>channel_type,&nbsp;<div><b>provider_name</b> VARCHAR(64)<br>http_method,&nbsp;</div><div>status_code,&nbsp;</div><div>endpoint<br>request_id,&nbsp;</div><div>duration_ms,&nbsp;</div><div>error_message<br>created_at</div><div><br>idx:&nbsp;</div><div>(workspace_id, created_at DESC);&nbsp;</div><div><span style="color: rgb(63, 63, 63); background-color: transparent;">(api_key_id);&nbsp;</span></div><div><span style="color: rgb(63, 63, 63); background-color: transparent;">(workspace_id, api_key_id, created_at)</span></div>" style="rounded=0;whiteSpace=wrap;html=1;align=left;verticalAlign=top;fillColor=#ffe6cc;strokeColor=#d79b00;fontSize=11;spacing=6;" parent="1" vertex="1">
<mxGeometry x="1140" y="170" width="320" height="275" as="geometry"/>
</mxCell>
<mxCell id="templates" value="<b>templates</b><hr>PK <b>id</b> UUID<br>FK workspace_id → workspaces CASCADE<br>name,&nbsp;<div><b>unique_key</b>&nbsp;</div><div>UNIQUE (workspace_id, unique_key)<br>channel_type,&nbsp;</div><div>subject,&nbsp;</div><div>content TEXT&nbsp;<br><b>category</b> VARCHAR(64) — portal<br>is_active,&nbsp;</div><div>is_default,&nbsp;</div><div>timestamps</div>" style="rounded=0;whiteSpace=wrap;html=1;align=left;verticalAlign=top;fillColor=#d5e8d4;strokeColor=#82b366;fontSize=11;spacing=6;" parent="1" vertex="1">
<mxGeometry x="90" y="620" width="260" height="190" as="geometry"/>
</mxCell>
<mxCell id="template_components" value="<b>template_components</b><hr>PK <b>id</b> UUID<br>FK workspace_id → workspaces CASCADE<br>kind (header | footer | snippet),&nbsp;<div>name<br>content TEXT<br><b>metadata</b> JSONB — portal<br><br></div><div>UNIQUE (workspace_id, kind, name)</div>" style="rounded=0;whiteSpace=wrap;html=1;align=left;verticalAlign=top;fillColor=#d5e8d4;strokeColor=#82b366;fontSize=11;spacing=6;" parent="1" vertex="1">
<mxGeometry x="400" y="630" width="280" height="160" as="geometry"/>
</mxCell>
<mxCell id="workspace_settings" value="<b>workspace_settings</b><hr>PK <b>id</b> UUID<br>FK workspace_id → workspaces CASCADE<br>key,&nbsp;<div>value TEXT&nbsp;</div><div><br></div><div><span style="color: rgb(63, 63, 63); background-color: transparent;">UNIQUE (workspace_id, key)&nbsp;</span></div><div><span style="color: rgb(63, 63, 63); background-color: transparent;">timestamps</span></div>" style="rounded=0;whiteSpace=wrap;html=1;align=left;verticalAlign=top;fillColor=#e1d5e7;strokeColor=#9673a6;fontSize=11;spacing=6;" parent="1" vertex="1">
<mxGeometry x="720" y="680" width="240" height="130" as="geometry"/>
</mxCell>
<mxCell id="workspace_access_audits" value="<b>workspace_access_audits</b><hr>PK <b>id</b> UUID<br>FK workspace_id → workspaces CASCADE<br>FK actor_user_id → users(id) SET NULL<br>event VARCHAR(64),&nbsp;<div>ip_address<br>metadata JSONB,&nbsp;</div><div>created_at</div><div><br>idx (workspace_id, created_at DESC)</div>" style="rounded=0;whiteSpace=wrap;html=1;align=left;verticalAlign=top;fillColor=#f5f5f5;strokeColor=#666666;fontSize=11;spacing=6;" parent="1" vertex="1">
<mxGeometry x="990" y="540" width="300" height="160" as="geometry"/>
</mxCell>
<mxCell id="e_wm_wks" style="edgeStyle=orthogonalEdgeStyle;rounded=1;orthogonalLoop=1;jettySize=auto;html=1;endArrow=ERmany;startArrow=ERone;endFill=0;startFill=0;exitX=0.2;exitY=1;exitDx=0;exitDy=0;entryX=1;entryY=0.4;entryDx=0;entryDy=0;curved=0;strokeWidth=2;" parent="1" source="workspaces" target="workspace_members" edge="1">
<mxGeometry relative="1" as="geometry"/>
</mxCell>
<mxCell id="e_wm_usr" style="edgeStyle=orthogonalEdgeStyle;rounded=1;orthogonalLoop=1;jettySize=auto;html=1;endArrow=ERmany;startArrow=ERone;endFill=0;startFill=0;exitX=0.5;exitY=1;exitDx=0;exitDy=0;entryX=0;entryY=0.4;entryDx=0;entryDy=0;curved=0;strokeWidth=2;" parent="1" source="users" target="workspace_members" edge="1">
<mxGeometry relative="1" as="geometry"/>
</mxCell>
<mxCell id="e_inv_wks" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;startArrow=ERone;endFill=0;startFill=0;exitX=0;exitY=0.8;exitDx=0;exitDy=0;entryX=1;entryY=0.35;entryDx=0;entryDy=0;curved=0;strokeWidth=2;" parent="1" source="workspaces" target="invitations" edge="1">
<mxGeometry relative="1" as="geometry"/>
</mxCell>
<mxCell id="e_wch_wks" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;startArrow=ERone;endFill=0;startFill=0;entryX=0.5;entryY=0;entryDx=0;entryDy=0;curved=0;strokeWidth=2;" parent="1" target="workspace_channels" edge="1">
<mxGeometry relative="1" as="geometry">
<Array as="points">
<mxPoint x="440" y="271"/>
<mxPoint x="440" y="470"/>
<mxPoint x="-130" y="470"/>
</Array>
<mxPoint x="440" y="271" as="sourcePoint"/>
</mxGeometry>
</mxCell>
<mxCell id="e_int_wks" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;startArrow=ERone;endFill=0;startFill=0;exitX=0.25;exitY=0;exitDx=0;exitDy=0;entryX=0.25;entryY=1;entryDx=0;entryDy=0;strokeWidth=2;curved=0;" parent="1" source="workspaces" target="integrations" edge="1">
<mxGeometry relative="1" as="geometry"/>
</mxCell>
<mxCell id="e_key_wks" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;startArrow=ERone;endFill=0;startFill=0;exitX=1;exitY=0.55;exitDx=0;exitDy=0;entryX=0;entryY=0.4;entryDx=0;entryDy=0;strokeWidth=2;curved=0;" parent="1" source="workspaces" target="api_keys" edge="1">
<mxGeometry relative="1" as="geometry"/>
</mxCell>
<mxCell id="e_log_wks" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;startArrow=ERone;endFill=0;startFill=0;entryX=0.5;entryY=1;entryDx=0;entryDy=0;exitX=0.849;exitY=0.995;exitDx=0;exitDy=0;exitPerimeter=0;strokeWidth=2;curved=0;" parent="1" source="workspaces" target="message_request_logs" edge="1">
<mxGeometry relative="1" as="geometry">
<Array as="points">
<mxPoint x="663" y="500"/>
<mxPoint x="1300" y="500"/>
</Array>
<mxPoint x="540" y="310" as="sourcePoint"/>
</mxGeometry>
</mxCell>
<mxCell id="e_log_key" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;startArrow=ERone;endFill=0;startFill=0;dashed=1;exitX=1;exitY=0.5;exitDx=0;exitDy=0;entryX=0.5;entryY=0;entryDx=0;entryDy=0;strokeWidth=2;curved=0;" parent="1" source="api_keys" target="message_request_logs" edge="1">
<mxGeometry relative="1" as="geometry">
<Array as="points">
<mxPoint x="1300" y="100"/>
</Array>
</mxGeometry>
</mxCell>
<mxCell id="e_tpl_wks" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;startArrow=ERone;endFill=0;startFill=0;entryX=0.25;entryY=0;entryDx=0;entryDy=0;strokeWidth=2;curved=0;" parent="1" target="templates" edge="1">
<mxGeometry relative="1" as="geometry">
<Array as="points">
<mxPoint x="480" y="269"/>
<mxPoint x="480" y="520"/>
<mxPoint x="155" y="520"/>
</Array>
<mxPoint x="480" y="269" as="sourcePoint"/>
</mxGeometry>
</mxCell>
<mxCell id="e_tcomp_wks" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;startArrow=ERone;endFill=0;startFill=0;entryX=0.445;entryY=-0.008;entryDx=0;entryDy=0;exitX=0.5;exitY=1;exitDx=0;exitDy=0;strokeWidth=2;curved=0;entryPerimeter=0;" parent="1" target="template_components" edge="1">
<mxGeometry relative="1" as="geometry">
<Array as="points">
<mxPoint x="525" y="629"/>
</Array>
<mxPoint x="525" y="270" as="sourcePoint"/>
<mxPoint x="535" y="630" as="targetPoint"/>
</mxGeometry>
</mxCell>
<mxCell id="e_wset_wks" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;startArrow=ERone;endFill=0;startFill=0;entryX=0.25;entryY=0;entryDx=0;entryDy=0;curved=0;strokeWidth=2;" parent="1" target="workspace_settings" edge="1">
<mxGeometry relative="1" as="geometry">
<Array as="points">
<mxPoint x="580" y="520"/>
<mxPoint x="780" y="520"/>
</Array>
<mxPoint x="780" y="660" as="targetPoint"/>
<mxPoint x="580" y="270" as="sourcePoint"/>
</mxGeometry>
</mxCell>
<mxCell id="e_aud_wks" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;startArrow=ERone;endFill=0;startFill=0;exitX=1;exitY=0.95;exitDx=0;exitDy=0;strokeWidth=2;curved=0;entryX=0.25;entryY=0;entryDx=0;entryDy=0;" parent="1" source="workspaces" target="workspace_access_audits" edge="1">
<mxGeometry relative="1" as="geometry">
<Array as="points">
<mxPoint x="1065" y="260"/>
</Array>
<mxPoint x="1030" y="650" as="targetPoint"/>
</mxGeometry>
</mxCell>
<mxCell id="e_aud_usr" style="edgeStyle=orthogonalEdgeStyle;rounded=1;html=1;endArrow=ERmany;startArrow=ERone;endFill=0;startFill=0;dashed=1;exitX=0.379;exitY=0.997;exitDx=0;exitDy=0;strokeWidth=2;curved=0;exitPerimeter=0;" parent="1" edge="1" source="users">
<mxGeometry relative="1" as="geometry">
<Array as="points">
<mxPoint x="30" y="570"/>
</Array>
<mxPoint x="30" y="170" as="sourcePoint"/>
<mxPoint x="990" y="570" as="targetPoint"/>
</mxGeometry>
</mxCell>
<mxCell id="3" value="<b>roles</b><hr><b>id:&nbsp;</b><span style="color: rgb(0, 0, 0);">PK&nbsp;-</span>&nbsp;UUID<br>name: TEXT unique<br>display_name: TEXT<br>created_at" style="rounded=0;whiteSpace=wrap;html=1;align=left;verticalAlign=top;fillColor=#d5e8d4;strokeColor=#82b366;fontSize=11;spacing=6;" parent="1" vertex="1">
<mxGeometry x="-300" y="320" width="200" height="100" as="geometry"/>
</mxCell>
<mxCell id="7" style="edgeStyle=orthogonalEdgeStyle;html=1;entryX=0;entryY=0.5;entryDx=0;entryDy=0;strokeWidth=2;endArrow=ERmany;endFill=0;startArrow=ERzeroToOne;startFill=0;" parent="1" source="6" target="integrations" edge="1">
<mxGeometry relative="1" as="geometry"/>
</mxCell>
<mxCell id="9" style="edgeStyle=orthogonalEdgeStyle;html=1;entryX=0.5;entryY=1;entryDx=0;entryDy=0;strokeWidth=2;endArrow=ERoneToMany;endFill=0;startArrow=ERone;startFill=0;" parent="1" source="6" target="8" edge="1">
<mxGeometry relative="1" as="geometry"/>
</mxCell>
<mxCell id="6" value="<b>providers</b><hr><b>id:&nbsp;</b>PK UUID<br>name: TEXT NOT NULL&nbsp;<br>channel_type:&nbsp; email | sms | push | chat<br>status: active | not_supported | blocked<br><br><div><div><div><font color="#000000"><br></font><div>created_at, updated_at</div></div></div></div>" style="rounded=0;whiteSpace=wrap;html=1;align=left;verticalAlign=top;fillColor=#d5e8d4;strokeColor=#82b366;fontSize=11;spacing=6;" parent="1" vertex="1">
<mxGeometry x="-80" y="-210" width="290" height="140" as="geometry"/>
</mxCell>
<mxCell id="8" value="<span style="white-space-collapse: preserve;"><b>provider_config_fields</b></span><br><hr><b>id:&nbsp;</b>PK UUID<br>provider_id:&nbsp; FK UUID&nbsp;&nbsp;<span style="scrollbar-color: rgb(226, 226, 226) rgb(251, 251, 251); color: rgb(0, 0, 0);">→ providers RESTRICTED&nbsp;</span><br>key TEXT NOT NULL&nbsp; &nbsp;<span style="white-space-collapse: preserve;">-- "api_key", "domain", "from_email"</span><div><font color="#000000">label TEXT NOT NULL&nbsp;&nbsp;</font><span style="color: rgb(63, 63, 63); white-space-collapse: preserve; background-color: transparent;">-- "API Key", "Sending Domain"</span></div><div><font color="#000000">description TEXT NULL&nbsp;</font><span style="color: rgb(63, 63, 63); white-space-collapse: preserve; background-color: transparent;">- shown as help text in Portal UI</span></div><div><font color="#000000">field_type TEXT NOT NULL [text, password, email, url, boolean, textarea, select]</font></div><div><font color="#000000"><font color="#000000">required BOOLEAN</font></font></div><div><font color="#000000"><font color="#000000"><font color="#000000">default_value TEXT&nbsp;<span style="white-space-collapse: preserve;">-- NULL means no default</span><br style="scrollbar-color: rgb(226, 226, 226) rgb(251, 251, 251); color: rgb(63, 63, 63);"></font></font></font><div><div><div><font color="#000000">options JSONB&nbsp;</font><span style="color: rgb(63, 63, 63); white-space-collapse: preserve; background-color: transparent;">-- for field_type='select': ["tls","ssl","none"]</span></div><div><font color="#000000"><span style="white-space-collapse: preserve;">sort_order SMALLINT NOT NULL DEFAULT 0,</span><span style="white-space-collapse: preserve;"></span></font></div><div><font color="#000000"><span style="white-space-collapse: preserve;"><br></span></font></div><div><font color="#000000"><span style="white-space-collapse: preserve;">UNIQUE (provider_id, key)</span></font></div><div><div>created_at, updated_at</div></div></div></div></div>" style="rounded=0;whiteSpace=wrap;html=1;align=left;verticalAlign=top;fillColor=#d5e8d4;strokeColor=#82b366;fontSize=11;spacing=6;" parent="1" vertex="1">
<mxGeometry x="-90" y="-490" width="460" height="230" as="geometry"/>
</mxCell>
</root>
</mxGraphModel>
</diagram>
</mxfile>