my own indieAuth provider!
indiko.dunkirk.sh/docs
indieauth
oauth2-server
1-- Full schema for indiko
2CREATE TABLE IF NOT EXISTS users (
3 id INTEGER PRIMARY KEY AUTOINCREMENT,
4 username TEXT NOT NULL UNIQUE,
5 name TEXT NOT NULL,
6 email TEXT,
7 photo TEXT,
8 url TEXT,
9 status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'suspended', 'inactive')),
10 role TEXT NOT NULL DEFAULT 'user',
11 is_admin INTEGER NOT NULL DEFAULT 0,
12 created_at INTEGER NOT NULL DEFAULT (strftime('%s','now'))
13);
14
15CREATE TABLE IF NOT EXISTS credentials (
16 id INTEGER PRIMARY KEY AUTOINCREMENT,
17 user_id INTEGER NOT NULL,
18 credential_id BLOB NOT NULL UNIQUE,
19 public_key BLOB NOT NULL,
20 counter INTEGER NOT NULL DEFAULT 0,
21 created_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),
22 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
23);
24
25CREATE TABLE IF NOT EXISTS sessions (
26 id INTEGER PRIMARY KEY AUTOINCREMENT,
27 token TEXT NOT NULL UNIQUE,
28 user_id INTEGER NOT NULL,
29 expires_at INTEGER NOT NULL,
30 created_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),
31 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
32);
33
34CREATE TABLE IF NOT EXISTS challenges (
35 id INTEGER PRIMARY KEY AUTOINCREMENT,
36 challenge TEXT NOT NULL UNIQUE,
37 username TEXT NOT NULL,
38 type TEXT NOT NULL CHECK(type IN ('registration', 'authentication')),
39 expires_at INTEGER NOT NULL,
40 created_at INTEGER NOT NULL DEFAULT (strftime('%s','now'))
41);
42
43CREATE TABLE IF NOT EXISTS invites (
44 id INTEGER PRIMARY KEY AUTOINCREMENT,
45 code TEXT NOT NULL UNIQUE,
46 created_by INTEGER NOT NULL,
47 used INTEGER NOT NULL DEFAULT 0,
48 used_by INTEGER,
49 used_at INTEGER,
50 max_uses INTEGER DEFAULT 1,
51 current_uses INTEGER NOT NULL DEFAULT 0,
52 expires_at INTEGER,
53 note TEXT,
54 message TEXT,
55 created_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),
56 FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE,
57 FOREIGN KEY (used_by) REFERENCES users(id) ON DELETE SET NULL
58);
59
60CREATE TABLE IF NOT EXISTS apps (
61 id INTEGER PRIMARY KEY AUTOINCREMENT,
62 client_id TEXT NOT NULL UNIQUE,
63 redirect_uris TEXT NOT NULL,
64 name TEXT,
65 logo_url TEXT,
66 description TEXT,
67 is_preregistered INTEGER NOT NULL DEFAULT 0,
68 client_secret_hash TEXT,
69 available_roles TEXT,
70 default_role TEXT,
71 first_seen INTEGER NOT NULL DEFAULT (strftime('%s','now')),
72 last_used INTEGER NOT NULL DEFAULT (strftime('%s','now'))
73);
74
75CREATE TABLE IF NOT EXISTS permissions (
76 id INTEGER PRIMARY KEY AUTOINCREMENT,
77 user_id INTEGER NOT NULL,
78 client_id TEXT NOT NULL,
79 scopes TEXT NOT NULL,
80 role TEXT,
81 granted_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),
82 last_used INTEGER NOT NULL DEFAULT (strftime('%s','now')),
83 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
84 FOREIGN KEY (client_id) REFERENCES apps(client_id) ON DELETE CASCADE,
85 UNIQUE(user_id, client_id)
86);
87
88CREATE TABLE IF NOT EXISTS authcodes (
89 id INTEGER PRIMARY KEY AUTOINCREMENT,
90 code TEXT NOT NULL UNIQUE,
91 user_id INTEGER NOT NULL,
92 client_id TEXT NOT NULL,
93 redirect_uri TEXT NOT NULL,
94 scopes TEXT NOT NULL,
95 code_challenge TEXT NOT NULL,
96 code_challenge_method TEXT NOT NULL DEFAULT 'S256',
97 expires_at INTEGER NOT NULL,
98 used INTEGER NOT NULL DEFAULT 0,
99 created_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),
100 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
101);
102
103CREATE TABLE IF NOT EXISTS invite_roles (
104 id INTEGER PRIMARY KEY AUTOINCREMENT,
105 invite_id INTEGER NOT NULL,
106 app_id INTEGER NOT NULL,
107 role TEXT NOT NULL,
108 created_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),
109 FOREIGN KEY (invite_id) REFERENCES invites(id) ON DELETE CASCADE,
110 FOREIGN KEY (app_id) REFERENCES apps(id) ON DELETE CASCADE,
111 UNIQUE(invite_id, app_id)
112);
113
114CREATE TABLE IF NOT EXISTS invite_uses (
115 id INTEGER PRIMARY KEY AUTOINCREMENT,
116 invite_id INTEGER NOT NULL,
117 user_id INTEGER NOT NULL,
118 used_at INTEGER NOT NULL DEFAULT (strftime('%s','now')),
119 FOREIGN KEY (invite_id) REFERENCES invites(id) ON DELETE CASCADE,
120 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
121);
122
123CREATE INDEX IF NOT EXISTS idx_sessions_token ON sessions(token);
124CREATE INDEX IF NOT EXISTS idx_sessions_expires_at ON sessions(expires_at);
125CREATE INDEX IF NOT EXISTS idx_challenges_challenge ON challenges(challenge);
126CREATE INDEX IF NOT EXISTS idx_challenges_expires_at ON challenges(expires_at);
127CREATE INDEX IF NOT EXISTS idx_credentials_user_id ON credentials(user_id);
128CREATE INDEX IF NOT EXISTS idx_invites_code ON invites(code);
129CREATE INDEX IF NOT EXISTS idx_apps_client_id ON apps(client_id);
130CREATE INDEX IF NOT EXISTS idx_permissions_user_id ON permissions(user_id);
131CREATE INDEX IF NOT EXISTS idx_permissions_client_id ON permissions(client_id);
132CREATE INDEX IF NOT EXISTS idx_authcodes_code ON authcodes(code);
133CREATE INDEX IF NOT EXISTS idx_authcodes_expires_at ON authcodes(expires_at);
134CREATE INDEX IF NOT EXISTS idx_invite_roles_invite_id ON invite_roles(invite_id);
135CREATE INDEX IF NOT EXISTS idx_invite_roles_app_id ON invite_roles(app_id);
136CREATE INDEX IF NOT EXISTS idx_invite_uses_invite_id ON invite_uses(invite_id);
137CREATE INDEX IF NOT EXISTS idx_invite_uses_user_id ON invite_uses(user_id);