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

1"""Postgres DDL for the accounts and sign-in tables. 

2 

3Mirrors the final SQLite schema built by :class:`downtify.auth.AuthStore` 

4and :class:`downtify.users.UserStore`. 

5""" 

6 

7from __future__ import annotations 

8 

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]