Under the Hood: Database

Sqlite database

Passwords uses a flat file SQLite database. SQLite is fully open‑source, and even more permissive than most open‑source projects. According to the official SQLite website, the entire source code is released into the public domain, meaning anyone can use it for any purpose without restriction. The project's website is https://sqlite.org/index.html.

Database Schema

Here's the complete database schema for Passwords to create an empty database for this version of Passwords.

CREATE TABLE application (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category_id INTEGER,
    application_name TEXT,
    data TEXT,
    created INTEGER,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
);

-- application_tokens: add FK with cascade
CREATE TABLE application_tokens (
    application_id INTEGER NOT NULL,
    token_hash TEXT NOT NULL,
    FOREIGN KEY(application_id) REFERENCES application(id) ON DELETE CASCADE
);

-- application_views: add FK with cascade (keeps existing PK)
CREATE TABLE application_views (
    application_id INTEGER PRIMARY KEY,
    view_count INTEGER DEFAULT 0,
    dt INTEGER,
    user TEXT,
    host TEXT,
    FOREIGN KEY(application_id) REFERENCES application(id) ON DELETE CASCADE
);

-- application_views_audit: add FK with cascade
CREATE TABLE application_views_audit (
    audit_id INTEGER PRIMARY KEY AUTOINCREMENT,
    application_id INTEGER NOT NULL,
    dt INTEGER,
    user TEXT,
    host TEXT,
    action TEXT,
    audit_time INTEGER,
    FOREIGN KEY(application_id) REFERENCES application(id) ON DELETE CASCADE
);

CREATE TABLE note (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category_id INTEGER,
    note_title TEXT,
    data TEXT,
    created INTEGER,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
);

CREATE TABLE credit (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category_id INTEGER,
    card_name TEXT,
    data TEXT,
    created INTEGER,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
);

CREATE TABLE file (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category_id INTEGER,
    file_name TEXT,
    data BLOB,
    created INTEGER,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT
);

CREATE TABLE note_tokens (
    note_id INTEGER NOT NULL,
    token_hash TEXT NOT NULL,
    FOREIGN KEY(note_id) REFERENCES note(id) ON DELETE CASCADE
);

CREATE TABLE credit_tokens (
    credit_id INTEGER NOT NULL,
    token_hash TEXT NOT NULL,
    FOREIGN KEY(credit_id) REFERENCES credit(id) ON DELETE CASCADE
);

CREATE TABLE file_tokens (
    file_id INTEGER NOT NULL,
    token_hash TEXT NOT NULL,
    FOREIGN KEY(file_id) REFERENCES file(id) ON DELETE CASCADE
);

CREATE TABLE note_views (
    note_id INTEGER PRIMARY KEY,
    view_count INTEGER DEFAULT 0,
    dt INTEGER,
    user TEXT,
    host TEXT,
    FOREIGN KEY(note_id) REFERENCES note(id) ON DELETE CASCADE
);

CREATE TABLE credit_views (
    credit_id INTEGER PRIMARY KEY,
    view_count INTEGER DEFAULT 0,
    dt INTEGER,
    user TEXT,
    host TEXT,
    FOREIGN KEY(credit_id) REFERENCES credit(id) ON DELETE CASCADE
);

CREATE TABLE file_views (
    file_id INTEGER PRIMARY KEY,
    view_count INTEGER DEFAULT 0,
    dt INTEGER,
    user TEXT,
    host TEXT,
    FOREIGN KEY(file_id) REFERENCES file(id) ON DELETE CASCADE
);

CREATE TABLE note_views_audit (
    audit_id INTEGER PRIMARY KEY AUTOINCREMENT,
    note_id INTEGER NOT NULL,
    dt INTEGER,
    user TEXT,
    host TEXT,
    action TEXT,
    audit_time INTEGER,
    FOREIGN KEY(note_id) REFERENCES note(id) ON DELETE CASCADE
);

CREATE TABLE credit_views_audit (
    audit_id INTEGER PRIMARY KEY AUTOINCREMENT,
    credit_id INTEGER NOT NULL,
    dt INTEGER,
    user TEXT,
    host TEXT,
    action TEXT,
    audit_time INTEGER,
    FOREIGN KEY(credit_id) REFERENCES credit(id) ON DELETE CASCADE
);

CREATE TABLE file_views_audit (
    audit_id INTEGER PRIMARY KEY AUTOINCREMENT,
    file_id INTEGER NOT NULL,
    dt INTEGER,
    user TEXT,
    host TEXT,
    action TEXT,
    audit_time INTEGER,
    FOREIGN KEY(file_id) REFERENCES file(id) ON DELETE CASCADE
);
CREATE TABLE app_info (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL
);

CREATE TABLE categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    parent_id INTEGER DEFAULT NULL,
    text TEXT NOT NULL,
    UNIQUE(parent_id, text),
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE CASCADE
);

CREATE TABLE favourite (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    application_id INTEGER NOT NULL,
    username TEXT NOT NULL,
    FOREIGN KEY(application_id) REFERENCES application(id) ON DELETE CASCADE,
    UNIQUE(application_id, username)
    FOREIGN KEY(application_id) REFERENCES application(id) ON DELETE CASCADE
);

CREATE TABLE keys (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    label TEXT NOT NULL UNIQUE,
    key TEXT NOT NULL UNIQUE
);

CREATE INDEX idx_note_category ON note(category_id);
CREATE INDEX idx_credit_category ON credit(category_id);
CREATE INDEX idx_file_category ON file(category_id);
CREATE INDEX idx_note_created ON note(created);
CREATE INDEX idx_credit_created ON credit(created);
CREATE INDEX idx_file_created ON file(created);
CREATE INDEX idx_favourite_username ON favourite(username);
CREATE INDEX idx_favourite_application ON favourite(application_id);

CREATE TRIGGER application_views_insert_audit
AFTER INSERT ON application_views
BEGIN
    INSERT INTO application_views_audit(application_id, dt, user, host, action, audit_time)
    VALUES (NEW.application_id, NEW.dt, NEW.user, NEW.host, 'ACCESSED', strftime('%s','now'));
END;

CREATE TRIGGER application_views_update_audit
AFTER UPDATE ON application_views
BEGIN
    INSERT INTO application_views_audit(application_id, dt, user, host, action, audit_time)
    VALUES (NEW.application_id, NEW.dt, NEW.user, NEW.host, 'ACCESSED', strftime('%s','now'));
END;

CREATE TRIGGER note_views_insert_audit
AFTER INSERT ON note_views
BEGIN
    INSERT INTO note_views_audit(note_id, dt, user, host, action, audit_time)
    VALUES (NEW.note_id, NEW.dt, NEW.user, NEW.host, 'ACCESSED', strftime('%s','now'));
END;

CREATE TRIGGER note_views_update_audit
AFTER UPDATE ON note_views
BEGIN
    INSERT INTO note_views_audit(note_id, dt, user, host, action, audit_time)
    VALUES (NEW.note_id, NEW.dt, NEW.user, NEW.host, 'ACCESSED', strftime('%s','now'));
END;

CREATE TRIGGER credit_views_insert_audit
AFTER INSERT ON credit_views
BEGIN
    INSERT INTO credit_views_audit(credit_id, dt, user, host, action, audit_time)
    VALUES (NEW.credit_id, NEW.dt, NEW.user, NEW.host, 'ACCESSED', strftime('%s','now'));
END;

CREATE TRIGGER credit_views_update_audit
AFTER UPDATE ON credit_views
BEGIN
    INSERT INTO credit_views_audit(credit_id, dt, user, host, action, audit_time)
    VALUES (NEW.credit_id, NEW.dt, NEW.user, NEW.host, 'ACCESSED', strftime('%s','now'));
END;

CREATE TRIGGER file_views_insert_audit
AFTER INSERT ON file_views
BEGIN
    INSERT INTO file_views_audit(file_id, dt, user, host, action, audit_time)
    VALUES (NEW.file_id, NEW.dt, NEW.user, NEW.host, 'ACCESSED', strftime('%s','now'));
END;

CREATE TRIGGER file_views_update_audit
AFTER UPDATE ON file_views
BEGIN
    INSERT INTO file_views_audit(file_id, dt, user, host, action, audit_time)
    VALUES (NEW.file_id, NEW.dt, NEW.user, NEW.host, 'ACCESSED', strftime('%s','now'));
END;

CREATE VIEW all_objects AS
SELECT 'application' AS type, id, category_id, created
FROM application
UNION ALL
SELECT 'note' AS type, id, category_id, created
FROM note
UNION ALL
SELECT 'credit' AS type, id, category_id, created
FROM credit
UNION ALL
SELECT 'file' AS type, id, category_id, created
FROM file;

INSERT OR IGNORE INTO app_info (key, value)
VALUES ('app_signature', 'Asteraceae');

INSERT OR IGNORE INTO app_info (key, value)
VALUES ('schema_version', '1');

After the database has been created it is recommended to enable Write-Ahead Logging (WAL) by running the following command:

sqlite3 <database> "PRAGMA journal_mode=WAL;"