This document describes the SQLite database schema for an election management system. The schema supports creating and managing elections, defining issues to vote on, tracking eligible voters, and securely recording votes. It emphasizes security through cryptographic fields (e.g., salts, hashed tokens, encrypted votes) and enforces data integrity with constraints like foreign keys and strict typing.
The database consists of five tables:
election: Stores election metadata, including ownership and cryptographic data.issue: Defines specific issues within an election, with voting mechanisms and metadata.person: Tracks individuals involved in elections (e.g., voters, owners).mayvote: Specifies which persons are eligible to vote on specific issues.vote: Records encrypted votes, supporting re-voting by tracking insertion order.All tables use SQLite’s STRICT mode to enforce type constraints. The schema avoids AUTOINCREMENT for IDs (except in vote) to prevent predictable URLs, using 10-character hexadecimal IDs instead.
electionStores metadata for each election.
Columns:
eid (TEXT, PRIMARY KEY): A 10-character hexadecimal election ID (e.g., “1a2b3c4d5e”). Not auto-incremented to prevent URL deduction. Must match the pattern [0-9a-f]{10}.title (TEXT, NOT NULL): The election’s title.owner_pid (TEXT, NOT NULL): The ID of the person who created/owns the election. References person(pid).authz (TEXT): Specifies an LDAP group (e.g., group name or identifier) allowed to edit the election. If NULL, only owner_pid can edit. The exact format is TBD but will likely map to LDAP-based persons.salt (BLOB): A 16-byte salt generated using secrets.token_bytes() in Python, set when the election is opened. NULL if not opened.opened_key (BLOB): A 32-byte hash (using Argon2) derived from election data to detect tampering, set when the election is opened. NULL if not opened.closed (INTEGER): Indicates election status: NULL or 0 for not closed, 1 for closed (implies opened). Must be NULL, 0, or 1.Constraints:
owner_pid references person(pid) with RESTRICT on delete and NO ACTION on update.eid format, salt (16 bytes or NULL), opened_key (32 bytes or NULL), and closed values.issueRepresents issues (questions or proposals) within an election.
Columns:
iid (TEXT, PRIMARY KEY): A 10-character hexadecimal issue ID (e.g., “a1b2c3d4e5”). Must match [0-9a-f]{10}.eid (TEXT, NOT NULL): The election ID this issue belongs to. References election(eid).title (TEXT, NOT NULL): A one-line title for the issue.description (TEXT): An optional detailed description of the issue.type (TEXT, NOT NULL): The voting mechanism. Currently supports:yna: Yes/No/Abstain voting.stv: Single Transferable Vote. Additional types may be added in the future.kv (TEXT): JSON-formatted key-value pairs for issue-specific data, varying by type. For example, stv issues may include a "candidates" key listing candidate names or IDs.Constraints:
eid references election(eid) with RESTRICT on delete and NO ACTION on update.iid format.Indexes:
idx_issue_eid: Index on eid for efficient lookups of issues by election.personStores information about individuals (voters, owners).
Columns:
pid (TEXT, PRIMARY KEY): A unique person ID (e.g., LDAP username).name (TEXT): An optional human-readable name.email (TEXT, NOT NULL): Contact email for sending ballot links.Constraints:
mayvoteDefines which persons are eligible to vote on specific issues.
Columns:
pid (TEXT, NOT NULL): The person ID. References person(pid).iid (TEXT, NOT NULL): The issue ID. References issue(iid).salt (BLOB): A 16-byte salt generated using secrets.token_bytes(), used for generating a vote token and encryption key. NULL until the election is opened.Constraints:
(pid, iid) ensures a person can be eligible for an issue only once.pid references person(pid), iid references issue(iid), both with RESTRICT on delete and NO ACTION on update.salt is 16 bytes or NULL.voteRecords votes cast for issues, supporting re-voting.
Columns:
vid (INTEGER, PRIMARY KEY, AUTOINCREMENT): Auto-incrementing ID to track insertion order (aliases _ROWID_).vote_token (BLOB, NOT NULL): A 32-byte hash (using Argon2) of a person-issue pair (from mayvote), used for encryption key derivation.ciphertext (BLOB, NOT NULL): The encrypted vote data, currently using Fernet encryption, with plans to transition to XChaCha20-Poly1305.Constraints:
vote_token is 32 bytes.vote_token is derived from mayvote data.Indexes:
idx_by_vote_token: Index on vote_token for efficient vote lookups.salt (16 bytes) in election and mayvote uses secrets.token_bytes().opened_key (32 bytes) in election uses Argon2 to hash election data for tamper detection.vote_token (32 bytes) in vote uses Argon2 to hash person-issue pairs.ciphertext in vote uses Fernet encryption (to be replaced with XChaCha20-Poly1305).eid, iid) use 10-character hex strings to prevent URL guessing.vote table allows multiple votes per person-issue pair. The latest vote is identified using MAX(vid), leveraging the auto-incrementing vid. Older votes are retained for auditing.issue table supports multiple voting mechanisms (yna, stv) with extensible JSON metadata (kv). For example, stv issues may specify candidates in kv.RESTRICT prevent deletion of referenced records, and STRICT mode enforces type safety.salt and opened_key. Eligible voters (in mayvote) can vote on issues.closed = 1), enabling tallying.owner_pid) can monitor and edit the election at all stages, including viewing voter eligibility and vote counts.authz field’s format is TBD but will likely use LDAP group identifiers to define editing permissions. Validation occurs outside the schema (e.g., via LDAP integration).queries.yaml file, which maps query names to SQL statements for all database interactions. This file provides insight into common operations (e.g., vote tallying, eligibility checks). Specific queries are not included here but can be analyzed for index optimization.ciphertext encryption will transition from Fernet to XChaCha20-Poly1305. Additional issue types may be added beyond yna and stv.