oauth2gw-0001.sql (8200B)
1 -- Everything in one big transaction 2 BEGIN; 3 4 -- Check patch versioning is in place. 5 SELECT _v.register_patch('oauth2gw-0001', NULL, NULL); 6 7 CREATE SCHEMA oauth2gw; 8 COMMENT ON SCHEMA oauth2gw IS 'oauth2 gateway data'; 9 10 SET search_path TO oauth2gw; 11 12 CREATE TABLE IF NOT EXISTS clients ( 13 id UUID PRIMARY KEY DEFAULT gen_random_uuid(), 14 client_id VARCHAR(255) UNIQUE NOT NULL, 15 secret_hash VARCHAR(255) NOT NULL, 16 webhook_url TEXT NOT NULL, 17 verifier_url TEXT NOT NULL, 18 verifier_management_api_path VARCHAR(255) DEFAULT '/management/api/verifications', 19 redirect_uri TEXT, 20 accepted_issuer_dids TEXT, 21 created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, 22 updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP 23 ); 24 COMMENT ON TABLE clients 25 IS 'Clients using the oauth2gw for selective VC disclosure'; 26 COMMENT ON COLUMN clients.client_id 27 IS 'ID used for client identification before oauth2 gateway'; 28 COMMENT ON COLUMN clients.secret_hash 29 IS 'hash of shared secret used for client authentication before oauth2 gateway'; 30 COMMENT ON COLUMN clients.redirect_uri 31 IS 'Default OAuth2 redirect URI for this client'; 32 COMMENT ON COLUMN clients.accepted_issuer_dids 33 IS 'Comma-separated list of accepted DID issuers for credential verification'; 34 COMMENT ON COLUMN clients.webhook_url 35 IS 'Client URL where oauth2 gateway will callback'; 36 COMMENT ON COLUMN clients.verifier_url 37 IS 'Client URL where oauth2 gateway will callback'; 38 COMMENT ON COLUMN clients.verifier_management_api_path 39 IS 'Swiyu verifier api endpoint to create verification requests'; 40 41 CREATE INDEX IF NOT EXISTS idx_clients_client_id ON clients(client_id); 42 43 CREATE TABLE verification_sessions ( 44 id UUID PRIMARY KEY DEFAULT gen_random_uuid(), 45 client_id UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE, 46 nonce VARCHAR(255) UNIQUE NOT NULL, 47 scope TEXT NOT NULL, 48 redirect_uri TEXT, 49 state TEXT, 50 verification_url TEXT, 51 verification_deeplink TEXT, 52 request_id VARCHAR(255), 53 verifier_nonce VARCHAR(255), 54 verifiable_credential JSONB, 55 status VARCHAR(50) NOT NULL DEFAULT 'pending', 56 created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, 57 authorized_at TIMESTAMPTZ, 58 verified_at TIMESTAMPTZ, 59 completed_at TIMESTAMPTZ, 60 failed_at TIMESTAMPTZ, 61 expires_at TIMESTAMPTZ NOT NULL, 62 63 CONSTRAINT verification_sessions_status_check 64 CHECK (status IN ('pending', 'authorized', 'verified', 'completed', 65 'expired', 'failed')) 66 ); 67 68 CREATE INDEX IF NOT EXISTS 69 idx_verification_sessions_nonce ON verification_sessions(nonce); 70 CREATE INDEX IF NOT EXISTS 71 idx_verification_sessions_request_id ON verification_sessions(request_id); 72 CREATE INDEX IF NOT EXISTS 73 idx_verification_sessions_status ON verification_sessions(status); 74 CREATE INDEX IF NOT EXISTS 75 idx_verification_sessions_expires_at ON verification_sessions(expires_at); 76 77 COMMENT ON COLUMN verification_sessions.nonce 78 IS 'Cryptographically secure 256-bit random value used as OAuth2 authorization code'; 79 COMMENT ON COLUMN verification_sessions.scope 80 IS 'Space-delimited requested verification attributes (e.g., "first_name last_name")'; 81 COMMENT ON COLUMN verification_sessions.redirect_uri 82 IS 'OAuth2 redirect_uri from /authorize request where authorization code will be sent'; 83 COMMENT ON COLUMN verification_sessions.state 84 IS 'OAuth2 state parameter from /authorize request for CSRF protection'; 85 COMMENT ON COLUMN verification_sessions.verification_url 86 IS 'URL for user wallet to complete verification (populated after /authorize)'; 87 COMMENT ON COLUMN verification_sessions.request_id 88 IS 'Swiyu Verifier request ID for tracking the OID4VP session'; 89 COMMENT ON COLUMN verification_sessions.verifier_nonce 90 IS 'Nonce returned by verifier for replay protection'; 91 COMMENT ON COLUMN verification_sessions.status 92 IS 'pending: created via /setup, authorized: /authorize called, verified: verification complete, completed: Client retrieved VC, expired: session timeout, failed: error occurred'; 93 94 CREATE TABLE IF NOT EXISTS access_tokens ( 95 id UUID PRIMARY KEY DEFAULT gen_random_uuid(), 96 session_id UUID NOT NULL REFERENCES verification_sessions(id) ON DELETE CASCADE, 97 token VARCHAR(255) UNIQUE NOT NULL, 98 token_type VARCHAR(50) NOT NULL DEFAULT 'Bearer', 99 expires_at TIMESTAMPTZ NOT NULL, 100 created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, 101 revoked BOOLEAN DEFAULT FALSE, 102 revoked_at TIMESTAMPTZ 103 ); 104 105 CREATE INDEX idx_access_tokens_token ON access_tokens(token); 106 CREATE INDEX idx_access_tokens_session_id ON access_tokens(session_id); 107 CREATE INDEX idx_access_tokens_expires_at ON access_tokens(expires_at); 108 CREATE INDEX idx_access_tokens_revoked ON access_tokens(revoked); 109 110 COMMENT ON COLUMN access_tokens.token 111 IS 'Bearer token value (256-bit random, base64-encoded)'; 112 COMMENT ON COLUMN access_tokens.token_type 113 IS 'OAuth2 token type (always Bearer)'; 114 COMMENT ON COLUMN access_tokens.revoked 115 IS 'Whether token has been explicitly revoked before expiration'; 116 117 CREATE TABLE IF NOT EXISTS authorization_codes ( 118 id UUID PRIMARY KEY DEFAULT gen_random_uuid(), 119 session_id UUID NOT NULL REFERENCES verification_sessions(id) ON DELETE CASCADE, 120 code VARCHAR(255) UNIQUE NOT NULL, 121 expires_at TIMESTAMPTZ NOT NULL, 122 used BOOLEAN DEFAULT FALSE, 123 used_at TIMESTAMPTZ, 124 created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP 125 ); 126 127 CREATE INDEX idx_authorization_codes_code ON authorization_codes(code); 128 CREATE INDEX idx_authorization_codes_session_id ON authorization_codes(session_id); 129 CREATE INDEX idx_authorization_codes_expires_at ON authorization_codes(expires_at); 130 CREATE INDEX idx_authorization_codes_used ON authorization_codes(used); 131 132 COMMENT ON TABLE authorization_codes 133 IS 'OAuth2 authorization codes issued after verification completion'; 134 COMMENT ON COLUMN authorization_codes.code 135 IS 'Authorization code value (256-bit random, base64-encoded)'; 136 COMMENT ON COLUMN authorization_codes.used 137 IS 'Whether code has been exchanged for an access token'; 138 139 CREATE TABLE IF NOT EXISTS notification_pending_webhooks ( 140 webhook_pending_serial BIGSERIAL PRIMARY KEY, 141 session_id UUID NOT NULL REFERENCES verification_sessions(id) ON DELETE CASCADE, 142 client_id UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE, 143 next_attempt INT8 NOT NULL DEFAULT 0, 144 retries INT4 NOT NULL DEFAULT 0, 145 url TEXT NOT NULL, 146 http_method TEXT NOT NULL DEFAULT 'POST', 147 header TEXT, 148 body TEXT NOT NULL 149 ); 150 151 CREATE INDEX idx_notification_pending_webhooks_next_attempt 152 ON notification_pending_webhooks(next_attempt); 153 CREATE INDEX idx_notification_pending_webhooks_session_id 154 ON notification_pending_webhooks(session_id); 155 156 COMMENT ON TABLE notification_pending_webhooks 157 IS 'Pending client notifications to be sent by background worker'; 158 COMMENT ON COLUMN notification_pending_webhooks.next_attempt 159 IS 'Unix timestamp when to attempt sending (0 = execute now, max value = never retry)'; 160 COMMENT ON COLUMN notification_pending_webhooks.retries 161 IS 'Number of failed delivery attempts'; 162 COMMENT ON COLUMN notification_pending_webhooks.url 163 IS 'Client webhook URL to POST notification'; 164 COMMENT ON COLUMN notification_pending_webhooks.http_method 165 IS 'HTTP method for webhook (always POST for notifications)'; 166 COMMENT ON COLUMN notification_pending_webhooks.header 167 IS 'Newline-separated HTTP headers for the webhook request'; 168 COMMENT ON COLUMN notification_pending_webhooks.body 169 IS 'JSON body to send (stringified, contains nonce, status, code, verification_id)'; 170 171 -- Trigger function to notify webhook worker when new webhooks are queued 172 CREATE OR REPLACE FUNCTION notify_webhook_pending() 173 RETURNS TRIGGER AS $$ 174 BEGIN 175 -- Notify the webhook worker daemon 176 PERFORM pg_notify('oauth2gw_webhook_pending', NEW.webhook_pending_serial::TEXT); 177 RETURN NEW; 178 END; 179 $$ LANGUAGE plpgsql; 180 181 CREATE TRIGGER trigger_webhook_pending 182 AFTER INSERT ON notification_pending_webhooks 183 FOR EACH ROW 184 EXECUTE FUNCTION notify_webhook_pending(); 185 186 COMMENT ON FUNCTION notify_webhook_pending() 187 IS 'Sends PostgreSQL NOTIFY to wake up webhook worker when new webhooks are queued'; 188 189 -- Complete transaction 190 COMMIT;