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
|
create extension if not exists pgcrypto;
create extension if not exists citext;
create schema if not exists secd;
create table if not exists secd.realm (
realm_id bigserial primary key
, created_at timestamptz not null
);
create table if not exists secd.realm_data (
realm_data_id bigserial primary key
, realm_id bigint not null references secd.realm(realm_id)
, email_provider jsonb not null
, sms_provider jsonb not null
, created_at timestamptz not null
, deleted_at timestamptz
);
create table if not exists secd.identity (
identity_id bigserial primary key
, identity_public_id uuid not null
, data jsonb -- some things are dervied, others are not
, created_at timestamptz not null
, updated_at timestamptz not null
, deleted_at timestamptz
, unique(identity_public_id)
);
create table if not exists secd.credential (
credential_id bigserial primary key
, credential_public_id uuid not null
, identity_id bigint not null references secd.identity(identity_id)
, type text not null-- e.g. password, oidc, totop, lookup_secret, webauthn, ...
, data jsonb not null
, version integer not null
, created_at timestamptz not null
, revoked_at timestamptz
, deleted_at timestamptz
);
create table if not exists secd.address (
address_id bigserial primary key
, address_public_id uuid not null
, type text not null
, value text not null
, created_at timestamptz not null
, unique(value, type)
);
create table if not exists secd.address_validation (
address_validation_id bigserial primary key
, address_validation_public_id uuid not null
, identity_id bigint references secd.identity(identity_id)
, address_id bigint not null references secd.address(address_id)
, method text not null -- e.g. email, sms, voice, oidc
, token_hash bytea
, code_hash bytea
, attempts integer not null
, created_at timestamptz not null
, expires_at timestamptz not null
, revoked_at timestamptz
, validated_at timestamptz
, unique(address_validation_public_id)
);
create table if not exists secd.session (
session_id bigserial primary key
, identity_id bigint not null references secd.identity(identity_id)
, token_hash bytea not null
, created_at timestamptz not null
, expired_at timestamptz not null
, revoked_at timestamptz
, unique(token_hash)
);
create table if not exists secd.message (
message_id bigserial primary key
, address_id bigint not null references secd.address(address_id)
, subject text
, body text
, template text not null
, template_vars jsonb not null
, created_at timestamptz not null
, sent_at timestamptz
);
|
