250 lines
10 KiB
Python
250 lines
10 KiB
Python
|
"""SQLite data layer for ApprovalFlow."""
|
||
|
|
import json
|
||
|
|
import os
|
||
|
|
import secrets
|
||
|
|
import sqlite3
|
||
|
|
from datetime import datetime, timezone
|
||
|
|
|
||
|
|
from werkzeug.security import generate_password_hash
|
||
|
|
|
||
|
|
DB_PATH = os.environ.get("DATABASE_PATH", os.path.join(os.path.dirname(__file__), "data", "approvalflow.db"))
|
||
|
|
ATTACH_DIR = os.path.join(os.path.dirname(DB_PATH), "attachments")
|
||
|
|
EMAIL_IMG_DIR = os.path.join(os.path.dirname(DB_PATH), "email_images")
|
||
|
|
|
||
|
|
MAX_EMAIL_IMAGE_BYTES = 2 * 1024 * 1024 # 2 MB per email image
|
||
|
|
EMAIL_IMAGE_TYPES = {".png": "png", ".jpg": "jpeg", ".jpeg": "jpeg", ".gif": "gif"}
|
||
|
|
|
||
|
|
MAX_ATTACHMENT_BYTES = 15 * 1024 * 1024 # 15 MB
|
||
|
|
|
||
|
|
|
||
|
|
def attachment_path(stored_name):
|
||
|
|
return os.path.join(ATTACH_DIR, stored_name)
|
||
|
|
|
||
|
|
SCHEMA = """
|
||
|
|
CREATE TABLE IF NOT EXISTS users (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
username TEXT UNIQUE NOT NULL,
|
||
|
|
password_hash TEXT NOT NULL,
|
||
|
|
email TEXT DEFAULT '',
|
||
|
|
first_name TEXT NOT NULL DEFAULT '',
|
||
|
|
last_name TEXT NOT NULL DEFAULT '',
|
||
|
|
unit_location TEXT NOT NULL DEFAULT '',
|
||
|
|
mfa_secret TEXT NOT NULL DEFAULT '', -- empty = MFA disabled
|
||
|
|
is_admin INTEGER NOT NULL DEFAULT 0,
|
||
|
|
created_at TEXT NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS workflows (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
name TEXT UNIQUE NOT NULL,
|
||
|
|
slug TEXT UNIQUE NOT NULL,
|
||
|
|
description TEXT DEFAULT '',
|
||
|
|
approvers TEXT NOT NULL DEFAULT '[]', -- JSON list, in approval order
|
||
|
|
knowledge TEXT NOT NULL DEFAULT '[]', -- JSON list: informed of every event, never approve
|
||
|
|
active INTEGER NOT NULL DEFAULT 1,
|
||
|
|
-- personalization (templates support {placeholders})
|
||
|
|
approval_subject TEXT NOT NULL,
|
||
|
|
approval_body TEXT NOT NULL,
|
||
|
|
approved_subject TEXT NOT NULL,
|
||
|
|
approved_body TEXT NOT NULL,
|
||
|
|
denied_subject TEXT NOT NULL,
|
||
|
|
denied_body TEXT NOT NULL,
|
||
|
|
created_at TEXT NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS user_workflows (
|
||
|
|
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
|
|
workflow_id INTEGER NOT NULL REFERENCES workflows(id) ON DELETE CASCADE,
|
||
|
|
PRIMARY KEY (user_id, workflow_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS requests (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
workflow_id INTEGER NOT NULL REFERENCES workflows(id),
|
||
|
|
source TEXT NOT NULL DEFAULT 'web', -- requests are created on the site
|
||
|
|
requester TEXT NOT NULL, -- email address (or username for web)
|
||
|
|
subject TEXT NOT NULL,
|
||
|
|
body TEXT NOT NULL,
|
||
|
|
participants TEXT NOT NULL DEFAULT '[]', -- JSON list of everyone involved so far
|
||
|
|
status TEXT NOT NULL DEFAULT 'pending', -- pending / approved / denied
|
||
|
|
current_step INTEGER NOT NULL DEFAULT 0, -- index into the workflow's approver chain
|
||
|
|
token TEXT UNIQUE NOT NULL, -- secret for the CURRENT step's approve/deny links
|
||
|
|
decided_by TEXT DEFAULT '',
|
||
|
|
deny_reason TEXT NOT NULL DEFAULT '',
|
||
|
|
attachment TEXT NOT NULL DEFAULT '[]', -- JSON list of {"stored": ..., "name": ...}
|
||
|
|
created_at TEXT NOT NULL,
|
||
|
|
decided_at TEXT
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS decisions (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
request_id INTEGER NOT NULL REFERENCES requests(id) ON DELETE CASCADE,
|
||
|
|
step INTEGER NOT NULL, -- 1-based position in the chain
|
||
|
|
decided_by TEXT NOT NULL,
|
||
|
|
decision TEXT NOT NULL, -- approved / denied
|
||
|
|
at TEXT NOT NULL
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS request_log (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
request_id INTEGER REFERENCES requests(id) ON DELETE CASCADE,
|
||
|
|
at TEXT NOT NULL,
|
||
|
|
event TEXT NOT NULL,
|
||
|
|
detail TEXT DEFAULT ''
|
||
|
|
);
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS mail_log (
|
||
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
||
|
|
at TEXT NOT NULL,
|
||
|
|
request_id INTEGER, -- related request, if any
|
||
|
|
recipients TEXT NOT NULL,
|
||
|
|
subject TEXT NOT NULL,
|
||
|
|
status TEXT NOT NULL, -- sent / error / dev
|
||
|
|
detail TEXT DEFAULT '', -- SMTP error message, if any
|
||
|
|
sent_copy TEXT DEFAULT '' -- result of saving to the Sent folder
|
||
|
|
);
|
||
|
|
"""
|
||
|
|
|
||
|
|
DEFAULT_TEMPLATES = {
|
||
|
|
"approval_subject": "[{workflow}] Approval needed ({step}/{total_steps}): {subject}",
|
||
|
|
"approval_body": (
|
||
|
|
"A request needs your decision (approval step {step} of {total_steps}).\n\n"
|
||
|
|
"Workflow: {workflow}\n"
|
||
|
|
"From: {requester}\n"
|
||
|
|
"Created: {created_at}\n"
|
||
|
|
"Subject: {subject}\n\n"
|
||
|
|
"Message:\n{body}\n\n"
|
||
|
|
"Approve: {approve_url}\n"
|
||
|
|
"Deny: {deny_url}\n\n"
|
||
|
|
"If you approve, the request moves on to the next approver.\n"
|
||
|
|
"If you deny, the workflow stops and everyone involved is notified.\n"
|
||
|
|
),
|
||
|
|
"approved_subject": "[{workflow}] Approved: {subject}",
|
||
|
|
"approved_body": (
|
||
|
|
"The following request was APPROVED.\n\n"
|
||
|
|
"Workflow: {workflow}\n"
|
||
|
|
"From: {requester}\n"
|
||
|
|
"Created: {created_at}\n"
|
||
|
|
"Subject: {subject}\n\n"
|
||
|
|
"Message:\n{body}\n\n"
|
||
|
|
"Summary:\n{summary}\n"
|
||
|
|
),
|
||
|
|
"denied_subject": "[{workflow}] Denied: {subject}",
|
||
|
|
"denied_body": (
|
||
|
|
"The following request was DENIED by {decided_by}.\n\n"
|
||
|
|
"Workflow: {workflow}\n"
|
||
|
|
"From: {requester}\n"
|
||
|
|
"Created: {created_at}\n"
|
||
|
|
"Subject: {subject}\n\n"
|
||
|
|
"Message:\n{body}\n\n"
|
||
|
|
"Reason for denial:\n{deny_reason}\n"
|
||
|
|
),
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
def now():
|
||
|
|
return datetime.now(timezone.utc).strftime("%Y-%m-%d %H:%M:%S UTC")
|
||
|
|
|
||
|
|
|
||
|
|
def get_db():
|
||
|
|
os.makedirs(os.path.dirname(DB_PATH), exist_ok=True)
|
||
|
|
con = sqlite3.connect(DB_PATH, timeout=15)
|
||
|
|
con.row_factory = sqlite3.Row
|
||
|
|
con.execute("PRAGMA foreign_keys = ON")
|
||
|
|
con.execute("PRAGMA journal_mode = WAL")
|
||
|
|
con.execute("PRAGMA busy_timeout = 15000")
|
||
|
|
return con
|
||
|
|
|
||
|
|
|
||
|
|
def _migrate(con):
|
||
|
|
"""Upgrade a database created by the single-approver version, if present."""
|
||
|
|
cols = {r["name"] for r in con.execute("PRAGMA table_info(workflows)")}
|
||
|
|
if "approver_email" in cols and "approvers" not in cols:
|
||
|
|
con.execute("ALTER TABLE workflows ADD COLUMN approvers TEXT NOT NULL DEFAULT '[]'")
|
||
|
|
for w in con.execute("SELECT id, approver_email FROM workflows").fetchall():
|
||
|
|
con.execute("UPDATE workflows SET approvers=? WHERE id=?",
|
||
|
|
(json.dumps([w["approver_email"]]), w["id"]))
|
||
|
|
print("[migrate] Converted single approver_email to approver chains.")
|
||
|
|
rcols = {r["name"] for r in con.execute("PRAGMA table_info(requests)")}
|
||
|
|
if rcols and "current_step" not in rcols:
|
||
|
|
con.execute("ALTER TABLE requests ADD COLUMN current_step INTEGER NOT NULL DEFAULT 0")
|
||
|
|
print("[migrate] Added current_step to requests.")
|
||
|
|
if rcols and "deny_reason" not in rcols:
|
||
|
|
con.execute("ALTER TABLE requests ADD COLUMN deny_reason TEXT NOT NULL DEFAULT ''")
|
||
|
|
print("[migrate] Added deny_reason to requests.")
|
||
|
|
if rcols and "attachment" not in rcols:
|
||
|
|
con.execute("ALTER TABLE requests ADD COLUMN attachment TEXT NOT NULL DEFAULT ''")
|
||
|
|
print("[migrate] Added attachment to requests.")
|
||
|
|
ucols = {r["name"] for r in con.execute("PRAGMA table_info(users)")}
|
||
|
|
if ucols and "mfa_secret" not in ucols:
|
||
|
|
con.execute("ALTER TABLE users ADD COLUMN mfa_secret TEXT NOT NULL DEFAULT ''")
|
||
|
|
print("[migrate] Added mfa_secret to users.")
|
||
|
|
for col in ("first_name", "last_name", "unit_location"):
|
||
|
|
if ucols and col not in ucols:
|
||
|
|
con.execute(f"ALTER TABLE users ADD COLUMN {col} TEXT NOT NULL DEFAULT ''")
|
||
|
|
print(f"[migrate] Added {col} to users.")
|
||
|
|
wcols = {r["name"] for r in con.execute("PRAGMA table_info(workflows)")}
|
||
|
|
if wcols and "knowledge" not in wcols:
|
||
|
|
con.execute("ALTER TABLE workflows ADD COLUMN knowledge TEXT NOT NULL DEFAULT '[]'")
|
||
|
|
print("[migrate] Added knowledge chain to workflows.")
|
||
|
|
# single-attachment values -> JSON list format
|
||
|
|
if rcols and "attachment" in rcols:
|
||
|
|
for row in con.execute("SELECT id, attachment FROM requests "
|
||
|
|
"WHERE attachment != '' AND attachment NOT LIKE '[%'").fetchall():
|
||
|
|
stored = row["attachment"]
|
||
|
|
entry = [{"stored": stored, "name": stored.split("_", 1)[-1]}]
|
||
|
|
con.execute("UPDATE requests SET attachment=? WHERE id=?",
|
||
|
|
(json.dumps(entry), row["id"]))
|
||
|
|
print(f"[migrate] Converted attachment of request #{row['id']} to list format.")
|
||
|
|
con.execute("UPDATE requests SET attachment='[]' WHERE attachment=''")
|
||
|
|
con.commit()
|
||
|
|
|
||
|
|
|
||
|
|
def init_db():
|
||
|
|
con = get_db()
|
||
|
|
_migrate(con)
|
||
|
|
con.executescript(SCHEMA)
|
||
|
|
# bootstrap admin user on first run
|
||
|
|
if con.execute("SELECT COUNT(*) c FROM users").fetchone()["c"] == 0:
|
||
|
|
username = os.environ.get("ADMIN_USERNAME", "admin")
|
||
|
|
password = os.environ.get("ADMIN_PASSWORD", "changeme")
|
||
|
|
con.execute(
|
||
|
|
"INSERT INTO users (username, password_hash, is_admin, created_at) VALUES (?,?,1,?)",
|
||
|
|
(username, generate_password_hash(password), now()),
|
||
|
|
)
|
||
|
|
print(f"[init] Created admin user '{username}'. Change the password immediately.")
|
||
|
|
con.commit()
|
||
|
|
con.close()
|
||
|
|
|
||
|
|
|
||
|
|
def log(con, request_id, event, detail=""):
|
||
|
|
con.execute(
|
||
|
|
"INSERT INTO request_log (request_id, at, event, detail) VALUES (?,?,?,?)",
|
||
|
|
(request_id, now(), event, detail),
|
||
|
|
)
|
||
|
|
|
||
|
|
|
||
|
|
def add_participants(con, request_id, emails):
|
||
|
|
"""Add people to the 'involved so far' list of a request."""
|
||
|
|
row = con.execute("SELECT participants FROM requests WHERE id=?", (request_id,)).fetchone()
|
||
|
|
current = json.loads(row["participants"])
|
||
|
|
for e in emails:
|
||
|
|
e = (e or "").strip().lower()
|
||
|
|
if e and e not in current:
|
||
|
|
current.append(e)
|
||
|
|
con.execute("UPDATE requests SET participants=? WHERE id=?", (json.dumps(current), request_id))
|
||
|
|
return current
|
||
|
|
|
||
|
|
|
||
|
|
def create_request(con, workflow_id, source, requester, subject, body, extra_participants=()):
|
||
|
|
token = secrets.token_urlsafe(32)
|
||
|
|
cur = con.execute(
|
||
|
|
"INSERT INTO requests (workflow_id, source, requester, subject, body, token, created_at) "
|
||
|
|
"VALUES (?,?,?,?,?,?,?)",
|
||
|
|
(workflow_id, source, requester, subject, body, token, now()),
|
||
|
|
)
|
||
|
|
rid = cur.lastrowid
|
||
|
|
add_participants(con, rid, [requester, *extra_participants])
|
||
|
|
log(con, rid, "received", f"Request received via {source} from {requester}")
|
||
|
|
return rid, token
|