Coverage for downtify/db/migrations/schema/auth.py: 100%
2 statements
« prev ^ index » next coverage.py v7.17.4, created at 2026-11-21 22:18 +0000
« prev ^ index » next coverage.py v7.17.4, created at 2026-11-21 22:18 +0000
1"""Postgres DDL for the accounts and sign-in tables.
3Mirrors the final SQLite schema built by :class:`downtify.auth.AuthStore`
4and :class:`downtify.users.UserStore`.
5"""
7from __future__ import annotations
9DDL: list[str] = [
10 """
11 CREATE TABLE IF NOT EXISTS users (
12 id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
13 username TEXT NOT NULL,
14 password_hash TEXT NOT NULL,
15 role TEXT NOT NULL DEFAULT 'user',
16 default_password BIGINT NOT NULL DEFAULT 0,
17 prefs_json TEXT NOT NULL DEFAULT '{}',
18 created_at TEXT NOT NULL,
19 last_login_at TEXT,
20 last_login_ip TEXT NOT NULL DEFAULT ''
21 )
22 """,
23 """
24 CREATE UNIQUE INDEX IF NOT EXISTS users_username_lower
25 ON users (lower(username))
26 """,
27 """
28 CREATE TABLE IF NOT EXISTS auth_config (
29 key TEXT PRIMARY KEY,
30 value TEXT NOT NULL
31 )
32 """,
33 """
34 CREATE TABLE IF NOT EXISTS auth_oidc_identities (
35 issuer TEXT NOT NULL,
36 subject TEXT NOT NULL,
37 user_id BIGINT NOT NULL,
38 PRIMARY KEY (issuer, subject),
39 UNIQUE (user_id)
40 )
41 """,
42 """
43 CREATE TABLE IF NOT EXISTS auth_devices (
44 id TEXT PRIMARY KEY,
45 name TEXT NOT NULL,
46 platform TEXT NOT NULL DEFAULT '',
47 token_hash TEXT NOT NULL,
48 created_at TEXT NOT NULL,
49 last_seen_at TEXT,
50 last_ip TEXT NOT NULL DEFAULT '',
51 revoked_at TEXT,
52 user_id BIGINT NOT NULL DEFAULT 0
53 )
54 """,
55 """
56 CREATE TABLE IF NOT EXISTS auth_sessions (
57 id_hash TEXT PRIMARY KEY,
58 created_at TEXT NOT NULL,
59 expires_at TEXT NOT NULL,
60 touched_at TEXT NOT NULL,
61 ip TEXT NOT NULL DEFAULT '',
62 user_agent TEXT NOT NULL DEFAULT '',
63 user_id BIGINT NOT NULL DEFAULT 0
64 )
65 """,
66]