feat: encrypted records storage model + SQLite layer (ciphertext-only)

Adds the base project setup (requirements, README, gitignore entries)
and a SQLite storage layer that persists only encrypted records:
uuid, ciphertext, nonce, salt, plus a monotonic version counter used
later for tamper/replay detection. Includes a smoke test run.

🤖 Generated with Codebuff
Co-Authored-By: Codebuff <noreply@codebuff.com>

.gitignore

@@ -174,3 +174,10 @@ cython_debug/
 # PyPI configuration file
 .pypirc
 
+
+# project
+venv/
+instance/
+certs/
+__pycache__/
+*.pyc

README.md

@@ -1,3 +1,23 @@
-# crushdb
+# crushdb – verschlüsselte Datensätze zwischen Personen
 
-a secure database for saving transaktions betwen persons
+Sichere Datenbank für Transaktionen zwischen zwei Personen
+(Person A, Person B, Timestamp-Start, Timestamp-Ende, Aktion).
+
+Alle Datensätze werden **im Browser** verschlüsselt
+(XChaCha20-Poly1305, Schlüsselableitung per Argon2id aus der Passphrase).
+Der Server sieht ausschließlich Chiffretext.
+
+## Starten (HTTPS, Dev mit self-signed Zertifikat)
+
+```bash
+python3 -m venv venv
+./venv/bin/pip install -r requirements.txt
+./run.sh
+# → https://127.0.0.1:8443
+```
+
+Ein selbstsigniertes Zertifikat wird bei Bedarf automatisch erzeugt
+(`certs/`). Im Browser auftretende Zertifikatswarnungen in der
+Entwicklung bestätigen.
+
+Siehe `spezification.txt` für die Anforderungen.

backend/storage.py

@@ -0,0 +1,136 @@
+"""SQLite storage layer: stores ONLY ciphertext records.
+
+Record layout (all client-provided fields, opaque to the server):
+  id          INTEGER PRIMARY KEY            server surrogate key
+  uuid        TEXT UNIQUE NOT NULL           client-generated record id
+  ciphertext  BLOB NOT NULL                  XChaCha20-Poly1305 payload (base64 in API)
+  nonce       BLOB NOT NULL                  random 24-byte XChaCha nonce (base64 in API)
+  salt        BLOB NOT NULL                  per-record Argon2id salt (base64 in API)
+  version     INTEGER NOT NULL               monotonic counter, bump on every update
+  created_at  TEXT NOT NULL                  first seen server time (UTC ISO)
+  updated_at  TEXT NOT NULL                  last update server time (UTC ISO)
+
+The server never sees plaintext: no field contains decrypted data and
+nothing is logged except the UUID/version bookkeeping.
+"""
+
+import sqlite3
+from datetime import datetime, timezone
+
+DB_PATH = "instance/crushdb.sqlite3"
+
+
+def _connect():
+    conn = sqlite3.connect(DB_PATH)
+    conn.row_factory = sqlite3.Row
+    conn.execute("PRAGMA foreign_keys = ON")
+    return conn
+
+
+def init_db():
+    """Create tables if they do not exist yet."""
+    import os
+    os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
+    with _connect() as conn:
+        conn.executescript(
+            """
+            CREATE TABLE IF NOT EXISTS records (
+                id         INTEGER PRIMARY KEY AUTOINCREMENT,
+                uuid       TEXT    NOT NULL UNIQUE,
+                ciphertext BLOB    NOT NULL,
+                nonce      BLOB    NOT NULL,
+                salt       BLOB    NOT NULL,
+                version    INTEGER NOT NULL DEFAULT 1,
+                created_at TEXT    NOT NULL,
+                updated_at TEXT    NOT NULL
+            );
+            CREATE INDEX IF NOT EXISTS idx_records_uuid ON records(uuid);
+            """
+        )
+
+
+def _now():
+    return datetime.now(timezone.utc).isoformat()
+
+
+def _row_to_dict(row):
+    if row is None:
+        return None
+    return {
+        "uuid": row["uuid"],
+        "ciphertext": row["ciphertext"],
+        "nonce": row["nonce"],
+        "salt": row["salt"],
+        "version": row["version"],
+        "created_at": row["created_at"],
+        "updated_at": row["updated_at"],
+    }
+
+
+def list_records():
+    with _connect() as conn:
+        rows = conn.execute(
+            "SELECT * FROM records ORDER BY id ASC"
+        ).fetchall()
+    return [_row_to_dict(r) for r in rows]
+
+
+def get_record(uuid):
+    with _connect() as conn:
+        row = conn.execute(
+            "SELECT * FROM records WHERE uuid = ?", (uuid,)
+        ).fetchone()
+    return _row_to_dict(row)
+
+
+def create_record(uuid, ciphertext, nonce, salt):
+    """Insert a new encrypted record. Returns the stored dict with server metadata."""
+    now = _now()
+    with _connect() as conn:
+        cur = conn.execute(
+            """
+            INSERT INTO records (uuid, ciphertext, nonce, salt, version, created_at, updated_at)
+            VALUES (?, ?, ?, ?, 1, ?, ?)
+            """,
+            (uuid, ciphertext, nonce, salt, now, now),
+        )
+        row_id = cur.lastrowid
+        row = conn.execute("SELECT * FROM records WHERE id = ?", (row_id,)).fetchone()
+    return _row_to_dict(row)
+
+
+def update_record(uuid, ciphertext, nonce, salt, expected_version):
+    """Replace ciphertext only if the client's version matches the stored one.
+
+    Returns the updated record dict, or None if:
+      - no record with that uuid exists
+      - expected_version does not match (someone else updated / replay attack)
+    """
+    with _connect() as conn:
+        row = conn.execute(
+            "SELECT version FROM records WHERE uuid = ?", (uuid,)
+        ).fetchone()
+        if row is None or row["version"] != expected_version:
+            return None
+        cur = conn.execute(
+            """
+            UPDATE records
+            SET ciphertext = ?, nonce = ?, salt = ?, version = version + 1, updated_at = ?
+            WHERE uuid = ? AND version = ?
+            """,
+            (ciphertext, nonce, salt, _now(), uuid, expected_version),
+        )
+        if cur.rowcount == 0:
+            return None
+        row = conn.execute("SELECT * FROM records WHERE uuid = ?", (uuid,)).fetchone()
+    return _row_to_dict(row)
+
+
+def delete_record(uuid, expected_version):
+    """Delete a record only if the client's version matches. Returns True on success."""
+    with _connect() as conn:
+        cur = conn.execute(
+            "DELETE FROM records WHERE uuid = ? AND version = ?",
+            (uuid, expected_version),
+        )
+    return cur.rowcount > 0

requirements.txt

@@ -0,0 +1 @@
+flask>=3.0