challenger

commit e3516be513762e5d3bb44b1a1a8e868dcc1d061a
parent 65a8d78bc9b0db5e09305cef5e3abc50eb412328
Author: Christian Grothoff <grothoff@gnunet.org>
Date:   Sun, 19 Jul 2026 23:42:54 +0200

clean up DB migration

Diffstat:
M
src/challengerdb/challenger-0004.sql
 | 
37
++++++++++++++++++++-----------------

1 file changed, 20 insertions(+), 17 deletions(-)
diff --git a/src/challengerdb/challenger-0004.sql b/src/challengerdb/challenger-0004.sql
@@ -22,31 +22,34 @@ SELECT _v.register_patch('challenger-0004', NULL, NULL);
 
 SET search_path TO challenger;
 
--- The nonce is the primary lookup key for validations and must always exist.
+-- need to drop this before converting UNIQUE to PK
 ALTER TABLE validations
-  ALTER COLUMN nonce SET NOT NULL;
+  DROP CONSTRAINT validations_client_serial_id_fkey;
 
--- The C code reads these counters through non-nullable uint32 result specs.
+-- convert UNIQUE index to PK
+ALTER TABLE clients
+  ADD CONSTRAINT clients_serial_pkey PRIMARY KEY (client_serial_id),
+  DROP CONSTRAINT clients_client_serial_id_key;
+
+-- Set non-NULLable where the C code reads
+-- fields through non-nullable result specs,
+-- add missing pK, and re-add foreign key constraint
 ALTER TABLE validations
+  ALTER COLUMN nonce SET NOT NULL,
   ALTER COLUMN address_attempts_left SET NOT NULL,
   ALTER COLUMN pin_transmissions_left SET NOT NULL,
-  ALTER COLUMN auth_attempts_left SET NOT NULL;
-
--- Deleting a client should remove its ephemeral validations rather than raise
--- a foreign-key violation that the admin tool surfaces as an internal error.
--- (The default constraint name for the FK declared inline in challenger-0001
--- is validations_client_serial_id_fkey.)
-ALTER TABLE validations
-  DROP CONSTRAINT validations_client_serial_id_fkey,
+  ALTER COLUMN auth_attempts_left SET NOT NULL,
+  ADD CONSTRAINT validation_serial_pkey PRIMARY KEY (validation_serial_id),
   ADD CONSTRAINT validations_client_serial_id_fkey
     FOREIGN KEY (client_serial_id)
-    REFERENCES clients (client_serial_id)
+    REFERENCES clients(client_serial_id)
     ON DELETE CASCADE;
 
--- These should really be primary keys
-ALTER TABLE validations
-  ADD CONSTRAINT validation_serial_pkey PRIMARY KEY (validation_serial_id);
-ALTER TABLE clients
-  ADD CONSTRAINT client_serial_pkey PRIMARY KEY (client_serial_id);
+-- Add missing index for FK cascade drop
+CREATE INDEX validations_by_client
+  ON validations(client_serial_id);
+COMMENT ON INDEX validations_by_client
+  IS 'Index needed for foreign key constraint validations_client_serial_id_fkey';
+
 
 COMMIT;