oauth2gw-0001.sql (5940B)
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 verifier_url TEXT NOT NULL, 17 verifier_management_api_path VARCHAR(255) DEFAULT '/management/api/verifications', 18 redirect_uri TEXT NOT NULL, 19 accepted_issuer_dids TEXT, 20 created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, 21 updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP 22 ); 23 COMMENT ON TABLE clients 24 IS 'Clients using the oauth2gw for selective VC disclosure'; 25 COMMENT ON COLUMN clients.client_id 26 IS 'ID used for client identification before oauth2 gateway'; 27 COMMENT ON COLUMN clients.secret_hash 28 IS 'hash of shared secret used for client authentication before oauth2 gateway'; 29 COMMENT ON COLUMN clients.redirect_uri 30 IS 'Default OAuth2 redirect URI for this client'; 31 COMMENT ON COLUMN clients.accepted_issuer_dids 32 IS 'Comma-separated list of accepted DID issuers for credential verification'; 33 COMMENT ON COLUMN clients.verifier_url 34 IS 'Base URL of the Swiyu verifier'; 35 COMMENT ON COLUMN clients.verifier_management_api_path 36 IS 'Swiyu verifier api endpoint to create verification requests'; 37 38 CREATE INDEX IF NOT EXISTS idx_clients_client_id ON clients(client_id); 39 40 CREATE TABLE verification_sessions ( 41 id UUID PRIMARY KEY DEFAULT gen_random_uuid(), 42 client_id UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE, 43 nonce VARCHAR(255) UNIQUE NOT NULL, 44 scope TEXT NOT NULL, 45 redirect_uri TEXT, 46 state TEXT, 47 verification_url TEXT, 48 verification_deeplink TEXT, 49 request_id VARCHAR(255), 50 verifier_nonce VARCHAR(255), 51 verifiable_credential JSONB, 52 status VARCHAR(50) NOT NULL DEFAULT 'pending', 53 created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, 54 authorized_at TIMESTAMPTZ, 55 verified_at TIMESTAMPTZ, 56 completed_at TIMESTAMPTZ, 57 failed_at TIMESTAMPTZ, 58 expires_at TIMESTAMPTZ NOT NULL, 59 60 CONSTRAINT verification_sessions_status_check 61 CHECK (status IN ('pending', 'authorized', 'verified', 'completed', 62 'expired', 'failed')) 63 ); 64 65 CREATE INDEX IF NOT EXISTS 66 idx_verification_sessions_nonce ON verification_sessions(nonce); 67 CREATE INDEX IF NOT EXISTS 68 idx_verification_sessions_request_id ON verification_sessions(request_id); 69 CREATE INDEX IF NOT EXISTS 70 idx_verification_sessions_status ON verification_sessions(status); 71 CREATE INDEX IF NOT EXISTS 72 idx_verification_sessions_expires_at ON verification_sessions(expires_at); 73 74 COMMENT ON COLUMN verification_sessions.nonce 75 IS 'Cryptographically secure 256-bit random value used as OAuth2 authorization code'; 76 COMMENT ON COLUMN verification_sessions.scope 77 IS 'Space-delimited requested verification attributes (e.g., "first_name last_name")'; 78 COMMENT ON COLUMN verification_sessions.redirect_uri 79 IS 'OAuth2 redirect_uri from /authorize request where authorization code will be sent'; 80 COMMENT ON COLUMN verification_sessions.state 81 IS 'OAuth2 state parameter from /authorize request for CSRF protection'; 82 COMMENT ON COLUMN verification_sessions.verification_url 83 IS 'URL for user wallet to complete verification (populated after /authorize)'; 84 COMMENT ON COLUMN verification_sessions.request_id 85 IS 'Swiyu Verifier request ID for tracking the OID4VP session'; 86 COMMENT ON COLUMN verification_sessions.verifier_nonce 87 IS 'Nonce returned by verifier for replay protection'; 88 COMMENT ON COLUMN verification_sessions.status 89 IS 'pending: created via /setup, authorized: /authorize called, verified: verification complete, completed: Client retrieved VC, expired: session timeout, failed: error occurred'; 90 91 CREATE TABLE IF NOT EXISTS access_tokens ( 92 id UUID PRIMARY KEY DEFAULT gen_random_uuid(), 93 session_id UUID NOT NULL REFERENCES verification_sessions(id) ON DELETE CASCADE, 94 token VARCHAR(255) UNIQUE NOT NULL, 95 token_type VARCHAR(50) NOT NULL DEFAULT 'Bearer', 96 expires_at TIMESTAMPTZ NOT NULL, 97 created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, 98 revoked BOOLEAN DEFAULT FALSE, 99 revoked_at TIMESTAMPTZ 100 ); 101 102 CREATE INDEX idx_access_tokens_token ON access_tokens(token); 103 CREATE INDEX idx_access_tokens_session_id ON access_tokens(session_id); 104 CREATE INDEX idx_access_tokens_expires_at ON access_tokens(expires_at); 105 CREATE INDEX idx_access_tokens_revoked ON access_tokens(revoked); 106 107 COMMENT ON COLUMN access_tokens.token 108 IS 'Bearer token value (256-bit random, base64-encoded)'; 109 COMMENT ON COLUMN access_tokens.token_type 110 IS 'OAuth2 token type (always Bearer)'; 111 COMMENT ON COLUMN access_tokens.revoked 112 IS 'Whether token has been explicitly revoked before expiration'; 113 114 CREATE TABLE IF NOT EXISTS authorization_codes ( 115 id UUID PRIMARY KEY DEFAULT gen_random_uuid(), 116 session_id UUID NOT NULL REFERENCES verification_sessions(id) ON DELETE CASCADE, 117 code VARCHAR(255) UNIQUE NOT NULL, 118 expires_at TIMESTAMPTZ NOT NULL, 119 used BOOLEAN DEFAULT FALSE, 120 used_at TIMESTAMPTZ, 121 created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP 122 ); 123 124 CREATE INDEX idx_authorization_codes_code ON authorization_codes(code); 125 CREATE INDEX idx_authorization_codes_session_id ON authorization_codes(session_id); 126 CREATE INDEX idx_authorization_codes_expires_at ON authorization_codes(expires_at); 127 CREATE INDEX idx_authorization_codes_used ON authorization_codes(used); 128 129 COMMENT ON TABLE authorization_codes 130 IS 'OAuth2 authorization codes issued after verification completion'; 131 COMMENT ON COLUMN authorization_codes.code 132 IS 'Authorization code value (256-bit random, base64-encoded)'; 133 COMMENT ON COLUMN authorization_codes.used 134 IS 'Whether code has been exchanged for an access token'; 135 136 -- Complete transaction 137 COMMIT;