kych

commit af2ae1aeb862616d123486e127192e2e076ba1d1
parent cad695fbc4e542010abae60650a286be82a82809
Author: Henrique Chan Carvalho Machado <henriqueccmachado@tecnico.ulisboa.pt>
Date:   Wed,  5 Nov 2025 00:04:54 +0100

oauth2_gateway: db: remove useless comments from SQL setup, add failed_at session timestamp

Diffstat:
M
oauth2_gateway/migrations/oauth2gw-0001.sql
 | 
31
+++++++++++++------------------

1 file changed, 13 insertions(+), 18 deletions(-)
diff --git a/oauth2_gateway/migrations/oauth2gw-0001.sql b/oauth2_gateway/migrations/oauth2gw-0001.sql
@@ -1,14 +1,8 @@
 -- OAuth2 Gateway Initial Schema
 -- This migration creates the core tables for the OAuth2 Gateway service
 BEGIN;
-
--- Register this patch with the depesz versioning system
 SELECT _v.register_patch('oauth2gw-0001', NULL, NULL);
-
--- Create application schema
 CREATE SCHEMA oauth2gw;
-
--- Set search path for this migration
 SET search_path TO oauth2gw;
 
 -- ============================================================================
@@ -18,7 +12,7 @@ SET search_path TO oauth2gw;
 CREATE TABLE clients (
     id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
     client_id VARCHAR(255) UNIQUE NOT NULL,
-    client_secret VARCHAR(255) NOT NULL,  -- Should be hashed in production
+    client_secret VARCHAR(255) NOT NULL,
     notification_url TEXT NOT NULL,       -- Client's webhook URL for notifications
     verifier_base_url TEXT NOT NULL,      -- Swiyu Verifier URL for this client
     verifier_management_api_path VARCHAR(255) DEFAULT '/management/api/verifications',
@@ -43,18 +37,19 @@ CREATE TABLE verification_sessions (
     scope TEXT NOT NULL,                 -- Requested verification scope (space-delimited attributes)
 
     -- Swiyu Verifier data (populated after /authorize)
-    verification_url TEXT,               -- URL/QR code for user to scan
-    request_id VARCHAR(255),             -- Swiyu's verification request ID
+    verification_url TEXT,
+    request_id VARCHAR(255),
 
     -- Session status tracking
     status VARCHAR(50) NOT NULL DEFAULT 'pending',
 
     -- Timestamps
     created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
-    authorized_at TIMESTAMPTZ,           -- When /authorize was called
-    verified_at TIMESTAMPTZ,             -- When Swiyu webhook confirmed verification
-    completed_at TIMESTAMPTZ,            -- When Client retrieved the VC via /info
-    expires_at TIMESTAMPTZ NOT NULL,     -- Session expiration (e.g., 15 minutes from creation)
+    authorized_at TIMESTAMPTZ,
+    verified_at TIMESTAMPTZ,
+    completed_at TIMESTAMPTZ,
+    failed_at TIMESTAMPTZ,
+    expires_at TIMESTAMPTZ NOT NULL,
 
     CONSTRAINT verification_sessions_status_check
         CHECK (status IN ('pending', 'authorized', 'verified', 'completed', 'expired', 'failed'))
@@ -77,9 +72,9 @@ COMMENT ON COLUMN verification_sessions.status IS 'pending: created via /setup, 
 CREATE TABLE access_tokens (
     id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
     session_id UUID NOT NULL REFERENCES verification_sessions(id) ON DELETE CASCADE,
-    token VARCHAR(255) UNIQUE NOT NULL,  -- The access_token value (cryptographically secure)
+    token VARCHAR(255) UNIQUE NOT NULL,
     token_type VARCHAR(50) NOT NULL DEFAULT 'Bearer',
-    expires_at TIMESTAMPTZ NOT NULL,     -- Token expiration (e.g., 1 hour from issuance)
+    expires_at TIMESTAMPTZ NOT NULL,
     created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
     revoked BOOLEAN DEFAULT FALSE,
     revoked_at TIMESTAMPTZ
@@ -99,10 +94,10 @@ COMMENT ON COLUMN access_tokens.token IS 'Bearer token value (256-bit random, ba
 -- ============================================================================
 CREATE TABLE webhook_logs (
     id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-    request_id VARCHAR(255),             -- Swiyu's request_id from webhook path
+    request_id VARCHAR(255),
     session_id UUID REFERENCES verification_sessions(id) ON DELETE SET NULL,
-    payload JSONB NOT NULL,              -- Full webhook payload
-    status_code INTEGER,                 -- HTTP status code of our response
+    payload JSONB NOT NULL,
+    status_code INTEGER,
     processed BOOLEAN DEFAULT FALSE,
     error_message TEXT,
     received_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,