This is an automated email from the ASF dual-hosted git repository. gstein pushed a commit to branch trunk in repository https://gitbox.apache.org/repos/asf/steve.git
commit 797b6d83c21bff425482b9fbe085f18f4c7eee5b Author: Greg Stein <[email protected]> AuthorDate: Mon Sep 22 18:31:09 2025 -0500 Revamp schema for multiple elections in one sqlite database. Rather than one election per database, we're placing an entire install's elections into one. This enables a user to easily query for "all elections I can vote in" (eg. for a dashboard). In the previous schema, the set of voters for the single election is defined as the rows in the "person" databse. With multiple elections, then we create a "mayvote" table to specify which Issues a Person may vote upon (where an Issue is related to a specific Election). "elections" table: - add CHECKs for columns: eid, salt, opened_key, closed "issues" table: - add CHECK for the iid column - remove salt column; no longer needed "person" table: - remove salt column; no longer needed New "mayvote" table: - pid/iid create the needed pairing of who may vote on what - salt is used in a couple contexts; add a CHECK to its value "votes" table: - remove person_token and issue_token. Instead, use a single vote_token computed from the pid/iid and a salt. - remove the salt column; the vote's related mayvote.salt is used via key-stretching to construct the cipher key. - rename token to ciphertext for typical naming practive - revise the two indexes into a single one on the vote_token column --- v3/schema.sql | 75 +++++++++++++++++++++++++++-------------------------------- 1 file changed, 34 insertions(+), 41 deletions(-) diff --git a/v3/schema.sql b/v3/schema.sql index d35e256..5ce67b6 100644 --- a/v3/schema.sql +++ b/v3/schema.sql @@ -58,12 +58,17 @@ CREATE TABLE elections ( /* The Election ID; 10 hex characters. We do not use AUTOINCREMENT, so that URLs for Elections cannot be deduced. */ - eid TEXT PRIMARY KEY NOT NULL, + eid TEXT + PRIMARY KEY NOT NULL + CHECK (length(eid) = 10 + AND eid GLOB '[0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f]'), /* Title of this election. */ title TEXT NOT NULL, - /* Who is the owner/creator of this election? */ + /* Who is the owner/creator of this election? + Note: no need to CHECK OWNER_PID as it refers to a foreign table + where its propriety is enforced. */ owner_pid TEXT NOT NULL, /* What authz group is allowed to edit this election? If NULL, @@ -76,21 +81,18 @@ CREATE TABLE elections ( /* A salt value to use for hashing this Election. 16 bytes. This will be NULL until the Election is opened. */ - salt BLOB, + salt BLOB CHECK (salt IS NULL OR length(salt) = 16), /* If this Election has been opened for voting, then we store the OpenedKey here to avoid recomputing. 32 bytes. This will be NULL until the Election is opened. */ - opened_key BLOB, + opened_key BLOB CHECK (opened_key IS NULL OR length(opened_key) = 32), /* Has this election been closed? NULL or 0 for not-closed (see SALT and OPENED_KEY to determine if the election has been opened). 1 for closed (implies it was opened). */ - closed INTEGER, + closed INTEGER CHECK (closed IS NULL OR closed IN (0, 1)), - /* Enforce the primary key as a 10-character (5 byte) hex string. */ - CHECK (length(eid) = 10 - AND eid GLOB '[0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f]'), /* Enforce/declare/document relationships. */ FOREIGN KEY (owner_pid) REFERENCES person(pid) @@ -106,9 +108,14 @@ CREATE TABLE issues ( /* The Issue ID; 10 hex characters. We do not use AUTOINCREMENT, so that URLs for Issues cannot be deduced. */ - iid TEXT PRIMARY KEY NOT NULL, - - /* Which election is this issue associated with? */ + iid TEXT + PRIMARY KEY NOT NULL + CHECK (length(iid) = 10 + AND iid GLOB '[0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f]'), + + /* Which election is this issue associated with? + Note: no need to CHECK EID as it refers to a foreign table where + its propriety is enforced. */ eid TEXT NOT NULL, /* Simple one-line title for this issue. */ @@ -126,14 +133,6 @@ CREATE TABLE issues ( value is JSON-formatted */ kv TEXT, - /* A salt value to use for hashing this Issue. 16 bytes. - This will be NULL until the Election is opened. */ - salt BLOB, - - /* Enforce the primary key as a 10-character (5 byte) hex string. */ - CHECK (length(iid) = 10 - AND iid GLOB '[0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f][0-9a-f]'), - /* Enforce/declare/document relationships. */ FOREIGN KEY (eid) REFERENCES elections(eid) ON DELETE RESTRICT @@ -152,12 +151,8 @@ CREATE TABLE person ( /* Optional human-readable name for this person. */ name TEXT, - /* How to contact this person (ie. to send a ballot link). */ - email TEXT NOT NULL, - - /* A salt value to use for hashing this Person. 16 bytes. - This will be NULL until the Election is opened. */ - salt BLOB + /* How to contact this person (eg. to send a ballot link). */ + email TEXT NOT NULL ) STRICT; @@ -172,13 +167,17 @@ CREATE TABLE mayvote ( /* ... on this Issue. */ iid TEXT NOT NULL, - /* A salt value for hashing this Person/Issue pair's vote. 16 bytes. - This will be NULL until the Election (containing IID) is opened. */ - salt BLOB, + /* A salt value for hashing this Person/Issue pair into a vote_token. + 16 bytes. This will be NULL until the Election (containing IID) + is opened. 16 bytes. */ + salt BLOB CHECK (salt IS NULL OR length(salt) = 16), /* The pair should be unique. */ PRIMARY KEY (pid, iid), + /* Note: no need to check PID/IID columns as they refer to a foreign + table where their propriety is enforced. */ + /* Enforce/declare/document relationships. */ FOREIGN KEY (pid) REFERENCES person(pid) ON DELETE RESTRICT @@ -193,8 +192,8 @@ CREATE TABLE mayvote ( /* --------------------------------------------------------------------- */ /* The registered votes, once the Election has been opened. Note that - duplicates of (person, issue) may occur, as re-voting is allowed. Only - the latest is used. */ + duplicates of (person, issue) may occur (the vote_token will be the + same), as re-voting is allowed. Only the latest is used. */ CREATE TABLE votes ( /* The key is auto-incrementing to provide a record of insert-order, @@ -203,22 +202,16 @@ CREATE TABLE votes ( Note: an integer primary key is an alias for _ROWID_. */ vid INTEGER PRIMARY KEY AUTOINCREMENT, - /* A hashed token representing a single Person. 32 bytes. */ - person_token BLOB NOT NULL, - - /* A hashed token representing an issue. 32 bytes. */ - issue_token BLOB NOT NULL, - - /* A binary value used to salt the token's encryption. 16 bytes. */ - salt BLOB NOT NULL, + /* A hashed-based token (32 bytes) based on a (Person, Issue) pair + from the MAYVOTE table. Used to produce a key for encryption. */ + vote_token BLOB NOT NULL CHECK (length(vote_token) = 32), /* An encrypted form of the vote. */ - token BLOB NOT NULL + ciphertext BLOB NOT NULL ) STRICT; /* ### review queries.yaml to figure out proper indexes */ -CREATE INDEX idx_by_person ON votes (person_token); -CREATE INDEX idx_by_issue ON votes (issue_token); +CREATE INDEX idx_by_vote_token ON votes (vote_token); /* --------------------------------------------------------------------- */
