You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@steve.apache.org by gs...@apache.org on 2025/09/23 00:31:14 UTC

(steve) branch trunk updated (1471936 -> b06d730)

This is an automated email from the ASF dual-hosted git repository.

gstein pushed a change to branch trunk
in repository https://gitbox.apache.org/repos/asf/steve.git


    from 1471936  Specify ID format for Elections and Issues
     new 797b6d8  Revamp schema for multiple elections in one sqlite database.
     new 279f20c  fix comment on mayvote.salt
     new 927a855  Track the changes/scheme-redesign for a multi-election database.
     new 963f131  Update the coverage test for the new schema and API.
     new b06d730  Remove a couple debug print() calls.

The 5 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails.  The revisions
listed as "add" were already present in the repository and have only
been added to this reference.


Summary of changes:
 v3/schema.sql             |  76 +++++++++----------
 v3/steve/crypto.py        |  48 +++++++-----
 v3/steve/election.py      | 187 +++++++++++++++++++++++++++-------------------
 v3/test/check_coverage.py |   5 ++
 4 files changed, 179 insertions(+), 137 deletions(-)


(steve) 03/05: Track the changes/scheme-redesign for a multi-election database.

Posted by gs...@apache.org.
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 927a8555d71ee9f5d00090f67b4bdb1003818e9e
Author: Greg Stein <gs...@gmail.com>
AuthorDate: Mon Sep 22 18:49:23 2025 -0500

    Track the changes/scheme-redesign for a multi-election database.
    
    Many queries and APIs need to change to track the schema changes. A
    new entrypoints for managing the mayvoter table.
    
    crypto.py:
    - remove the generalized gen_token() function; we only have/need
      gen_vote_token() now
    - create_vote() takes a vote_token rather than person/issue tokens. It
      also takes a salt in order to create an encryption key from the
      (high-entropy) vote_token. Only returns the ciphertext now.
    - added _b64_vote_key() to build an encryption key with the
      appropriate key-stretching and b64 encode/decode.
      Note: comments in here refer to a future change.
    - decrypt_votestring() is adjusted for the new vote_token and
      associated salt and uses _b64_vote_key()
    
    election.py:
    - in __init__() adjust the cursors needed for interaction with the
      database:
      - salting only occurs in "mayvote". Not "persons" nor "issues"
      - c_add_issue, c_add_person takes one less param (the old salt)
      - adjust c_add_vote to match new columns in "voters" table
      - two new cursors for adding rows to the "mayvote" table
      - q_metadata, q_issues now require an eid to find the rows for a
        given election
      - q_get_mayvote to get the salt for a given pid/iid pair
      - q_tally replaces q_by_issue to fetch all Persons that may be
        voting in a given Election. Then vote_tokens are computed and
        searches through "votes" will occur.
      - new m_find_isseues to find all issues that a given Person may vote
        on within a given Election.
      - new m_has_voted to check the "votes" table for any possible votes
        by Person on an Issue.
      - new m_recent_vote to select the most-recent votestring for a
        Person on an Issue.
      - new m_all_issues to select all the Person/Issue pairings for all
        issues within a given Election.
    - gather_election_data(): pass EID to the queries for data
    - add_salts(): update the SALT column in "mayvote", dropping salts for
      the "persons" and "issues" tables.
    - get_metadata(), is_tampered(), get_state(): pass EID to the q_metadata
      query.
    - add_issue(), add_person(): no more SALT column. remove insertion param
    - list_issues(): query now needs which EID is being queried for issues
    - new add_voter() to add rows into the "mayvote" table for the Issues
      that a given Person may vote upon (one specific, or all issues in
      this Election)
    - add_vote(): revise algorithm away from issue_token and person_token
      to compute a single vote_token (which is a primary key to find
      votes), then to use that to generate an encryption key. Record the
      resulting ciphertext.
    - tally_issue(): revise code to find all people eligible to vote on
      the given issue, then compute a vote_token, then look for any votes
      performed for that pair. Remove dedup code since we only select the
      most-recent vote via SQL.
    - has_voted_upon(): EID for q_metadata. Change algorithm to find all
      Issues (in this Election) that this person is eligible to vote upon.
      Construct a vote_token and see if at least 1 vote has been cast for
      the Issue.
---
 v3/steve/crypto.py   |  48 ++++++++-----
 v3/steve/election.py | 189 ++++++++++++++++++++++++++++++---------------------
 2 files changed, 141 insertions(+), 96 deletions(-)

diff --git a/v3/steve/crypto.py b/v3/steve/crypto.py
index 4ea0baa..cb63c84 100644
--- a/v3/steve/crypto.py
+++ b/v3/steve/crypto.py
@@ -25,6 +25,9 @@ import passlib.hash  # note that .argon2 is proxy in this pkg
 import passlib.utils  # for the RNG, to create Salt values
 
 import cryptography.fernet
+from cryptography.hazmat.primitives import hashes
+from cryptography.hazmat.primitives.kdf import hkdf
+
 
 # All salt values will be 16 bytes in length. After base64 encoding, they
 # will be represented with 22 characters.
@@ -41,32 +44,41 @@ def gen_opened_key(edata: bytes, salt: bytes) -> bytes:
     return _hash(edata, salt)
 
 
-def gen_token(opened_key: bytes, value: str, salt: bytes) -> bytes:
+def gen_vote_token(opened_key: bytes, pid: str, iid: str, salt: bytes) -> bytes:
     "Generate a person or issue token."
-    return _hash(opened_key + value.encode(), salt)
+    return _hash(opened_key + pid.encode() + iid.encode(), salt)
+
+
+def _b64_vote_key(vote_token: bytes, salt: bytes) -> str:
+    "Key-stretch the vote_token. (ref: PBKDF)"
 
+    ### still using Fernet now, but will switch soon. Leaving comments.
+    keymaker = hkdf.HKDF(
+        algorithm=hashes.SHA256(),
+        length=32,  # 32-byte key for XChaCha20-Poly1305
+        salt=salt,
+        info=b"xchacha20_key"
+    )
+    vote_key = keymaker.derive(vote_token)
+    return base64.urlsafe_b64encode(vote_key)
 
-### fix return type, to be a tuple
-def create_vote(person_token: bytes,
-                issue_token: bytes,
-                votestring: str) -> bytes:
-    "Create a vote tuple, to record the VOTESTRING."
-    salt = gen_salt()
-    key = _hash(person_token + issue_token, salt)
-    b64key = base64.urlsafe_b64encode(key)
+
+def create_vote(vote_token: bytes, salt: bytes, votestring: str) -> bytes:
+    "Encrypt VOTESTRING using the VOTE_TOKEN and SALT."
+
+    b64key = _b64_vote_key(vote_token, salt)
     f = cryptography.fernet.Fernet(b64key)
-    return salt, f.encrypt(votestring.encode())
+    return f.encrypt(votestring.encode())
 
 
-def decrypt_votestring(person_token: bytes,
-                       issue_token: bytes,
+def decrypt_votestring(vote_token: bytes,
                        salt: bytes,
-                       token: bytes) -> str:
-    "Decrypt TOKEN into a VOTESTRING."
-    key = _hash(person_token + issue_token, salt)
-    b64key = base64.urlsafe_b64encode(key)
+                       ciphertext: bytes) -> str:
+    "Decrypt CIPHERTEXT into a VOTESTRING."
+
+    b64key = _b64_vote_key(vote_token, salt)
     f = cryptography.fernet.Fernet(b64key)
-    return f.decrypt(token).decode()
+    return f.decrypt(ciphertext).decode()
 
 
 def _hash(data: bytes, salt: bytes) -> bytes:
diff --git a/v3/steve/election.py b/v3/steve/election.py
index d76d883..b607edb 100644
--- a/v3/steve/election.py
+++ b/v3/steve/election.py
@@ -41,10 +41,8 @@ class Election:
         self.eid = eid
 
         # Construct cursors for all operations.
-        self.c_salt_issue = self.db.add_statement(
-            'UPDATE ISSUES SET salt = ? WHERE _ROWID_ = ?')
-        self.c_salt_person = self.db.add_statement(
-            'UPDATE PERSON SET salt = ? WHERE _ROWID_ = ?')
+        self.c_salt_mayvote = self.db.add_statement(
+            'UPDATE mayvote SET salt = ? WHERE _ROWID_ = ?')
         self.c_open = self.db.add_statement(
             'UPDATE ELECTIONS SET salt = ?, opened_key = ?'
             ' WHERE eid = ?')
@@ -52,7 +50,7 @@ class Election:
             'UPDATE ELECTIONS SET closed = 1'
             ' WHERE eid = ?')
         self.c_add_issue = self.db.add_statement(
-            '''INSERT INTO ISSUES VALUES (?, ?, ?, ?, ?, ?, ?)
+            '''INSERT INTO ISSUES VALUES (?, ?, ?, ?, ?, ?)
                ON CONFLICT DO UPDATE SET
                  title=excluded.title,
                  description=excluded.description,
@@ -60,7 +58,7 @@ class Election:
                  kv=excluded.kv
             ''')
         self.c_add_person = self.db.add_statement(
-            '''INSERT INTO PERSON VALUES (?, ?, ?, ?)
+            '''INSERT INTO PERSON VALUES (?, ?, ?)
                ON CONFLICT DO UPDATE SET
                  name=excluded.name,
                  email=excluded.email
@@ -70,27 +68,55 @@ class Election:
         self.c_delete_person = self.db.add_statement(
             'DELETE FROM PERSON WHERE pid = ?')
         self.c_add_vote = self.db.add_statement(
-            'INSERT INTO VOTES VALUES (NULL, ?, ?, ?, ?)')
-        self.c_has_voted = self.db.add_statement(
-            '''SELECT 1 FROM VOTES
-               WHERE person_token = ? AND issue_token = ?
-               LIMIT 1
-            ''')
+            'INSERT INTO VOTES VALUES (NULL, ?, ?)')
+        self.c_add_mayvote = self.db.add_statement(
+            'INSERT INTO mayvote (pid, iid, salt) VALUES (?, ?, NULL)')
+        self.c_add_mayvote_all = self.db.add_statement(
+            'INSERT INTO mayvote (pid, iid, salt)'
+            ' SELECT ?, iid, NULL FROM issues WHERE eid = ?')
 
         # Cursors for running queries.
-        ### need to add eid to the ISSUES queries; likely ELECTIONS
         self.q_metadata = self.db.add_query('elections',
-            'SELECT * FROM ELECTIONS')
+            'SELECT * FROM ELECTIONS WHERE eid = ?')
         self.q_issues = self.db.add_query('issues',
-            'SELECT * FROM ISSUES ORDER BY iid')
+            'SELECT * FROM ISSUES WHERE eid = ? ORDER BY iid')
         self.q_person = self.db.add_query('person',
             'SELECT * FROM PERSON ORDER BY pid')
         self.q_get_issue = self.db.add_query('issues',
             'SELECT * FROM ISSUES WHERE iid = ?')
         self.q_get_person = self.db.add_query('person',
             'SELECT * FROM PERSON WHERE pid = ?')
-        self.q_by_issue = self.db.add_query('votes',
-            'SELECT * FROM VOTES WHERE issue_token = ? ORDER BY _ROWID_')
+        self.q_get_mayvote = self.db.add_query('mayvote',
+            'SELECT * FROM MAYVOTE WHERE pid = ? AND iid = ?')
+        self.q_tally = self.db.add_query('mayvote',
+            'SELECT * FROM MAYVOTE WHERE iid = ?')
+
+        # Manual queries: these queries do not follow the 'SELECT * '
+        # pattern, so we cannot use .add_query() and will not have
+        # attribute access to the row results while running the query.
+        self.m_find_issues = self.db.add_statement(
+            '''SELECT m.*
+               FROM mayvote m
+               JOIN issues i ON m.iid = i.iid
+               WHERE m.pid = ? AND i.eid = ?
+            ''')
+        self.m_has_voted = self.db.add_statement(
+            '''SELECT 1 FROM VOTES
+               WHERE vote_token = ?
+               LIMIT 1
+            ''')
+        self.m_recent_vote = self.db.add_statement(
+            '''SELECT ciphertext FROM VOTES
+               WHERE vote_token = ?
+               ORDER BY _ROWID_ DESC
+               LIMIT 1
+            ''')
+        self.m_all_issues = self.db.add_statement(
+            '''SELECT m._ROWID_
+               FROM mayvote m
+               JOIN issues i ON m.iid = i.iid
+               WHERE i.eid = ?;
+            ''')
 
     def open(self):
 
@@ -119,10 +145,10 @@ class Election:
 
         # NOTE: all assembly of rows must use a repeatable ordering.
 
-        md = self.q_metadata.first_row()
+        md = self.q_metadata.first_row((self.eid,))
         mdata = md.eid + md.title
 
-        self.q_issues.perform()
+        self.q_issues.perform((self.eid,))
         # Use an f-string to render "None" if a column is NULL.
         idata = ''.join(f'{i.iid}{i.title}{i.description}{i.type}{i.kv}'
                         for i in self.q_issues.fetchall())
@@ -143,34 +169,29 @@ class Election:
         self.c_close.perform((self.eid,))
 
     def add_salts(self):
-        "Set the SALT column in the ISSUES and PERSON tables."
+        "Set the SALT column in the MAYVOTE table."
 
         # The Election should be editable.
         assert self.is_editable()
 
-        cur = self.db.conn.cursor()
-
-        def for_table(table, mod_cursor):
-            "Use MOD_CURSOR to salt each row of TABLE."
+        # Use M_ALL_ISSUES to iterate over all Person/Issue mappings
+        # in this Election (specified by EID).
+        self.m_all_issues.execute('BEGIN TRANSACTION')
+        self.m_all_issues.perform((self.eid,))
+        for mayvote in self.m_all_issues.fetchall():
+            # MAYVOTE is a 1-tuple: _ROWID_
 
-            # Fetch all ROWID values now, to avoid two cursors
-            # attempting to work on TABLE at the same time.
-            cur.execute(f'SELECT _ROWID_ FROM {table}')
-            ids = cur.fetchall()
+            # Use a distinct cursor to insert the SALT value.
+            salt = crypto.gen_salt()
+            #print('ROW will use:', table, r[0], salt)
+            self.c_salt_mayvote.perform((salt, mayvote[0]))
 
-            # Now, add a salt to every row.
-            for r in ids:
-                salt = crypto.gen_salt()
-                print('ROW will use:', table, r[0], salt)
-                mod_cursor.perform((salt, r[0]))
-
-        for_table('issues', self.c_salt_issue)
-        for_table('person', self.c_salt_person)
+        self.m_all_issues.execute('COMMIT')
 
     def get_metadata(self):
         "Return basic metadata about this Election."
 
-        md = self.q_metadata.first_row()
+        md = self.q_metadata.first_row((self.eid,))
         # NOTE: do not return the SALT
         # note: likely: never return opened_key
 
@@ -193,7 +214,7 @@ class Election:
         # If we ADD, then SALT will be NULL. If we UPDATE, then it will not
         # be touched (it should be NULL).
         self.c_add_issue.perform((iid, eid, title, description, vtype,
-                                  self.kv2json(kv), None))
+                                  self.kv2json(kv)))
 
     def delete_issue(self, iid):
         "Delete the Issue designated by IID."
@@ -208,7 +229,7 @@ class Election:
             # NOTE: the SALT column is omitted. It should never be exposed.
             return row[:4] + (self.json2kv(row.kv),)
 
-        self.q_issues.perform()
+        self.q_issues.perform((self.eid,))
         return [ extract_issue(row) for row in self.q_issues.fetchall() ]
 
     def get_person(self, pid):
@@ -224,7 +245,7 @@ class Election:
 
         # If we ADD, then SALT will be NULL. If we UPDATE, then it will not
         # be touched (it should be NULL).
-        self.c_add_person.perform((pid, name, email, None))
+        self.c_add_person.perform((pid, name, email,))
 
     def delete_person(self, pid):
         "Delete the Person designated by PID."
@@ -239,27 +260,31 @@ class Election:
         self.q_person.perform()
         return [ row[:3] for row in self.q_person.fetchall() ]
 
-    def add_vote(self, pid, iid, votestring):
+    def add_voter(self, pid: str, iid: str | None = None) -> None:
+        "Add PID (Person) to Issue IID, or to all Issues (None)."
+
+        if iid:
+            self.c_add_mayvote.perform((pid, iid,))
+        else:
+            self.c_add_mayvote_all.perform((pid, self.eid,))
+
+    def add_vote(self, pid: str, iid: str, votestring: str):
         "Add VOTESTRING as the (latest) vote by PID for IID."
 
         # The Election should be open.
         assert self.is_open()
 
-        md = self.q_metadata.first_row()
-        person = self.q_get_person.first_row((pid,))
-        issue = self.q_get_issue.first_row((iid,))
+        md = self.q_metadata.first_row((self.eid,))
 
         ### validate VOTESTRING for ISSUE.TYPE voting
 
-        person_token = crypto.gen_token(md.opened_key, pid, person.salt)
-        #print('PERSON:', pid, person.salt, person_token)
-        issue_token = crypto.gen_token(md.opened_key, iid, issue.salt)
-        #print('ISSUE:', iid, issue.salt, issue_token)
+        mayvote = self.q_get_mayvote.first_row((pid, iid,))
+        vote_token = crypto.gen_vote_token(md.opened_key, pid, iid, mayvote.salt)
 
-        salt, token = crypto.create_vote(person_token, issue_token, votestring)
-        #print('SALT:', salt)
-        #print('TOKEN:', token)
-        self.c_add_vote.perform((person_token, issue_token, salt, token))
+        # Pass MAYVOTE.SALT for PBKDF.
+        ciphertext = crypto.create_vote(vote_token, mayvote.salt, votestring)
+
+        self.c_add_vote.perform((vote_token, ciphertext))
 
     def tally_issue(self, iid):
         """Return the results for a given ISSUE-ID.
@@ -275,25 +300,37 @@ class Election:
         # The Election should be closed.
         assert self.is_closed()
 
-        md = self.q_metadata.first_row()
+        md = self.q_metadata.first_row((self.eid,))
+
+        # Need the issue TYPE
         issue = self.q_get_issue.first_row((iid,))
-        issue_token = crypto.gen_token(md.opened_key, iid, issue.salt)
 
-        # Use this dict to retain "most recent" votes.
-        dedup = { }  # (PERSON_TOKEN, ISSUE_TOKEN) : VOTESTRING
+        # Accumulate all MOST-RECENT votes for Issue IID.
+        votes = [ ]
+
+        # Use mayvote to determine all potential voters for Issue IID.
+        self.q_tally.perform((iid,))
+        for mayvote in self.q_tally.fetchall():
+            # Each row is: PID, IID, SALT
 
-        self.q_by_issue.perform((issue_token,))
-        for row in self.q_by_issue.fetchall():
+            # For the given Person PID found, compute a VOTE_TOKEN.
+            vote_token = crypto.gen_vote_token(
+                md.opened_key, mayvote.pid, iid, mayvote.salt,
+            )
+
+            # We don't need/want all columns, so only pick CIPHERTEXT.
+            row = self.m_recent_vote.first_row((vote_token,))
             votestring = crypto.decrypt_votestring(
-                row.person_token, issue_token, row.salt, row.token)
-            dedup[row.person_token, row.issue_token] = votestring
+                vote_token, mayvote.salt, row[0],
+            )
+            votes.append(votestring)
 
-        # Make sure the votes are not in database-order.
+        # Make sure the votes are NOT in database-order.
         # Note: we are not returning the votes, so this may be
         #  superfluous. But it certainly should not hurt.
-        votes = list(dedup.values())
         crypto.shuffle(votes)  # in-place
 
+        print('VOTES:', votes)
         # Perform the tally, and return the results.
         m = vtypes.vtype_module(issue.type)
         return m.tally(votes, self.json2kv(issue.kv))
@@ -304,25 +341,21 @@ class Election:
         # The Election should be open.
         assert self.is_open()
 
-        md = self.q_metadata.first_row()
-        person = self.q_get_person.first_row((pid,))
-        person_token = crypto.gen_token(md.opened_key, pid, person.salt)
+        md = self.q_metadata.first_row((self.eid,))
 
         voted_upon = { }
 
-        self.q_issues.perform()
-        for issue in self.q_issues.fetchall():
-            issue_token = crypto.gen_token(md.opened_key,
-                                           issue.iid,
-                                           issue.salt)
+        self.m_find_issues.perform((pid, self.eid,))
+        for row in self.m_find_issues.fetchall():
+            # Query is mayvote.* ... so ROW is: PID, IID, SALT
+            vote_token = crypto.gen_vote_token(
+                md.opened_key, pid, row[1], row[2]
+            )
 
-            # Is any vote present?
-            self.c_has_voted.perform((person_token, issue_token))
-            row = self.c_has_voted.fetchone()
-            _ = self.c_has_voted.fetchall()  # should be empty (LIMIT 1)
+            # Is any vote present? (wicked fast)
+            voted = self.m_has_voted.first_row((vote_token,))
 
-            #print('HAS-VOTED:', row, '||', person_token, issue_token)
-            voted_upon[issue.iid] = row is not None
+            voted_upon[row[1]] = (voted is not None)
 
         return voted_upon
 
@@ -331,7 +364,7 @@ class Election:
         # The Election should be open.
         assert self.is_open()
 
-        md = self.q_metadata.first_row()
+        md = self.q_metadata.first_row((self.eid,))
 
         # Compute an opened_key based on the current data.
         edata = self.gather_election_data()
@@ -359,7 +392,7 @@ class Election:
     def get_state(self):
         "Derive our election state from the METADATA table."
 
-        md = self.q_metadata.first_row()
+        md = self.q_metadata.first_row((self.eid,))
         if md.closed == 1:
             assert md.salt is not None and md.opened_key is not None
             return self.S_CLOSED


(steve) 05/05: Remove a couple debug print() calls.

Posted by gs...@apache.org.
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 b06d7306251998b2af1ce58f668ee858d1a64290
Author: Greg Stein <gs...@gmail.com>
AuthorDate: Mon Sep 22 19:31:01 2025 -0500

    Remove a couple debug print() calls.
---
 v3/steve/election.py | 2 --
 1 file changed, 2 deletions(-)

diff --git a/v3/steve/election.py b/v3/steve/election.py
index b607edb..0d00c3a 100644
--- a/v3/steve/election.py
+++ b/v3/steve/election.py
@@ -183,7 +183,6 @@ class Election:
 
             # Use a distinct cursor to insert the SALT value.
             salt = crypto.gen_salt()
-            #print('ROW will use:', table, r[0], salt)
             self.c_salt_mayvote.perform((salt, mayvote[0]))
 
         self.m_all_issues.execute('COMMIT')
@@ -330,7 +329,6 @@ class Election:
         #  superfluous. But it certainly should not hurt.
         crypto.shuffle(votes)  # in-place
 
-        print('VOTES:', votes)
         # Perform the tally, and return the results.
         m = vtypes.vtype_module(issue.type)
         return m.tally(votes, self.json2kv(issue.kv))


(steve) 01/05: Revamp schema for multiple elections in one sqlite database.

Posted by gs...@apache.org.
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 <gs...@gmail.com>
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);
 
 /* --------------------------------------------------------------------- */


(steve) 02/05: fix comment on mayvote.salt

Posted by gs...@apache.org.
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 279f20cee46d41abd71fc7e828a41ef6113f72dd
Author: Greg Stein <gs...@gmail.com>
AuthorDate: Mon Sep 22 18:49:06 2025 -0500

    fix comment on mayvote.salt
---
 v3/schema.sql | 3 ++-
 1 file changed, 2 insertions(+), 1 deletion(-)

diff --git a/v3/schema.sql b/v3/schema.sql
index 5ce67b6..2d12bc5 100644
--- a/v3/schema.sql
+++ b/v3/schema.sql
@@ -168,7 +168,8 @@ CREATE TABLE mayvote (
     iid  TEXT NOT NULL,
 
     /* A salt value for hashing this Person/Issue pair into a vote_token.
-       16 bytes. This will be NULL until the Election (containing IID)
+       Also used via key-stretching to create an encryption key for the
+       vote values. This will be NULL until the Election (containing IID)
        is opened.  16 bytes.  */
     salt  BLOB  CHECK (salt IS NULL OR length(salt) = 16),
 


(steve) 04/05: Update the coverage test for the new schema and API.

Posted by gs...@apache.org.
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 963f1318deeb6c75cabb1b305b12cd4b21d3fbad
Author: Greg Stein <gs...@gmail.com>
AuthorDate: Mon Sep 22 19:21:54 2025 -0500

    Update the coverage test for the new schema and API.
    
    * use add_voter() to fill in the "mayvote" table.
---
 v3/test/check_coverage.py | 5 +++++
 1 file changed, 5 insertions(+)

diff --git a/v3/test/check_coverage.py b/v3/test/check_coverage.py
index 3d50b86..042b2bc 100755
--- a/v3/test/check_coverage.py
+++ b/v3/test/check_coverage.py
@@ -92,6 +92,11 @@ def touch_every_line():
     e.delete_issue(i3)
     _ = e.get_issue(i1)
 
+    # Alice and Bob can vote on all issues. Carlos only on i1.
+    e.add_voter('alice')
+    e.add_voter('bob')
+    e.add_voter('carlos', i1)
+
     e.open()
     _ = e.get_metadata()  # while OPEN
     e.add_vote('alice', i1, 'y')