0000_init.up.sql (3448B)
1 CREATE TABLE IF NOT EXISTS signer_store_metadata ( 2 singleton_id INTEGER PRIMARY KEY CHECK (singleton_id = 1), 3 store_version INTEGER NOT NULL, 4 signer_identity_id TEXT, 5 signer_identity_public_key_hex TEXT, 6 signer_identity_json TEXT, 7 updated_at TEXT NOT NULL DEFAULT (datetime('now')) 8 ); 9 10 INSERT OR IGNORE INTO signer_store_metadata (singleton_id, store_version) 11 VALUES (1, 1); 12 13 CREATE TABLE IF NOT EXISTS signer_connection ( 14 connection_id TEXT PRIMARY KEY, 15 client_public_key_hex TEXT NOT NULL, 16 signer_identity_id TEXT NOT NULL, 17 signer_identity_public_key_hex TEXT NOT NULL, 18 signer_identity_json TEXT NOT NULL, 19 user_identity_id TEXT NOT NULL, 20 user_identity_public_key_hex TEXT NOT NULL, 21 user_identity_json TEXT NOT NULL, 22 connect_secret_hash_algorithm TEXT, 23 connect_secret_hash_digest_hex TEXT, 24 connect_secret_consumed_at_unix INTEGER, 25 requested_permissions_json TEXT NOT NULL, 26 approval_requirement TEXT NOT NULL, 27 approval_state TEXT NOT NULL, 28 auth_state TEXT NOT NULL, 29 status TEXT NOT NULL, 30 status_reason TEXT, 31 created_at_unix INTEGER NOT NULL, 32 updated_at_unix INTEGER NOT NULL, 33 last_authenticated_at_unix INTEGER, 34 last_request_at_unix INTEGER 35 ); 36 37 CREATE INDEX IF NOT EXISTS signer_connection_client_public_key_idx 38 ON signer_connection (client_public_key_hex); 39 40 CREATE INDEX IF NOT EXISTS signer_connection_user_identity_idx 41 ON signer_connection (user_identity_id); 42 43 CREATE INDEX IF NOT EXISTS signer_connection_connect_secret_digest_idx 44 ON signer_connection (connect_secret_hash_digest_hex) 45 WHERE connect_secret_hash_digest_hex IS NOT NULL; 46 47 CREATE INDEX IF NOT EXISTS signer_connection_status_idx 48 ON signer_connection (status); 49 50 CREATE TABLE IF NOT EXISTS signer_connection_permission_grant ( 51 connection_id TEXT NOT NULL REFERENCES signer_connection (connection_id) ON DELETE CASCADE, 52 permission TEXT NOT NULL, 53 granted_at_unix INTEGER NOT NULL, 54 PRIMARY KEY (connection_id, permission) 55 ); 56 57 CREATE INDEX IF NOT EXISTS signer_connection_permission_grant_permission_idx 58 ON signer_connection_permission_grant (permission); 59 60 CREATE TABLE IF NOT EXISTS signer_connection_relay ( 61 connection_id TEXT NOT NULL REFERENCES signer_connection (connection_id) ON DELETE CASCADE, 62 ordinal INTEGER NOT NULL, 63 relay_url TEXT NOT NULL, 64 PRIMARY KEY (connection_id, ordinal), 65 UNIQUE (connection_id, relay_url) 66 ); 67 68 CREATE INDEX IF NOT EXISTS signer_connection_relay_url_idx 69 ON signer_connection_relay (relay_url); 70 71 CREATE TABLE IF NOT EXISTS signer_connection_auth_challenge ( 72 connection_id TEXT PRIMARY KEY REFERENCES signer_connection (connection_id) ON DELETE CASCADE, 73 auth_url TEXT NOT NULL, 74 required_at_unix INTEGER NOT NULL, 75 authorized_at_unix INTEGER 76 ); 77 78 CREATE TABLE IF NOT EXISTS signer_connection_pending_request ( 79 connection_id TEXT PRIMARY KEY REFERENCES signer_connection (connection_id) ON DELETE CASCADE, 80 request_message_json TEXT NOT NULL, 81 created_at_unix INTEGER NOT NULL 82 ); 83 84 CREATE TABLE IF NOT EXISTS signer_request_audit ( 85 request_id TEXT PRIMARY KEY, 86 connection_id TEXT NOT NULL REFERENCES signer_connection (connection_id) ON DELETE CASCADE, 87 method TEXT NOT NULL, 88 decision TEXT NOT NULL, 89 message TEXT, 90 created_at_unix INTEGER NOT NULL 91 ); 92 93 CREATE INDEX IF NOT EXISTS signer_request_audit_connection_id_idx 94 ON signer_request_audit (connection_id); 95 96 CREATE INDEX IF NOT EXISTS signer_request_audit_created_at_idx 97 ON signer_request_audit (created_at_unix);