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 table if not exists realm (
realm_id integer primary key
, created_at integer not null
);
create table if not exists realm_data (
realm_data_id integer primary key
, realm_id integer not null references realm(realm_id)
, email_provider text not null
, sms_provider text not null
, created_at integer not null
, deleted_at integer
);
create table if not exists identity (
identity_id integer primary key
, identity_public_id uuid not null
, data text -- we do not prescribe JSON or any other serialization format
, created_at integer not null
, updated_at integer not null
, deleted_at integer
, unique(identity_public_id)
);
create table if not exists credential (
credential_id integer primary key
, credential_public_id uuid not null
, identity_id integer not null references identity(identity_id)
, partial_key text
, type text not null-- e.g. password, oidc, totop, lookup_secret, webauthn, ...
, data text not null
, created_at integer not null
, revoked_at integer
, deleted_at integer
);
create unique index if not exists credential_passphrase_type_key_ix
on credential (partial_key)
where type = 'Passphrase';
create table if not exists address (
address_id integer primary key
, address_public_id uuid not null
, type text not null
, value text not null
, created_at integer not null
, unique(value, type)
);
create table if not exists address_validation (
address_validation_id integer primary key
, address_validation_public_id uuid not null
, identity_id integer references identity(identity_id)
, address_id integer not null references address(address_id)
, method text not null -- e.g. email, sms, voice, oidc
, token_hash blob
, code_hash blob
, attempts integer not null
, created_at integer not null
, expires_at integer not null
, revoked_at integer
, validated_at integer
, unique(address_validation_public_id)
);
create table if not exists session (
session_id integer primary key
, identity_id integer not null references identity(identity_id)
, token_hash blob not null
, created_at integer not null
, expired_at integer not null
, revoked_at integer
, unique(token_hash)
);
create table if not exists message (
message_id integer primary key
, address_id integer not null references address(address_id)
, subject text
, body text
, template text not null
, template_vars text not null
, created_at integer not null
, sent_at integer
);
|
