Guide: keep a knowledge base in sync

"""Sync SOPCompass SOPs into a local SQLite database.

Run it on a schedule (every 15 minutes is plenty). Pass --full once a week
to also remove SOPs that were deleted or stopped being shared.
"""
import json
import os
import sqlite3
import sys
import time

import requests

BASE = "https://app.sopcompass.com/api/v1"
session = requests.Session()
session.headers["Authorization"] = f"Bearer {os.environ['SOPCOMPASS_API_KEY']}"

db = sqlite3.connect("sopcompass.db")
db.execute("create table if not exists sops (id text primary key, updated_at text, archived integer, doc text)")
db.execute("create table if not exists state (k text primary key, v text)")


def call(path, params=None):
    for attempt in range(6):
        res = session.get(f"{BASE}{path}", params=params, timeout=30)
        if res.status_code == 429:
            time.sleep(int(res.headers.get("Retry-After", "5")))
            continue
        if res.status_code >= 500:
            time.sleep(min(30, 2 ** attempt))
            continue
        body = res.json()
        if not res.ok:
            raise RuntimeError(f"{res.status_code} {body['error']['code']}: {body['error']['message']}")
        return body
    raise RuntimeError(f"gave up on {path}")


def list_sops(updated_since):
    params = {"include_archived": "true", "limit": 100}
    if updated_since:
        params["updated_since"] = updated_since
    while True:
        page = call("/sops", params)
        yield from page["data"]
        if not page["has_more"]:
            return
        params["cursor"] = page["next_cursor"]


def run(full):
    row = db.execute("select v from state where k = 'checkpoint'").fetchone()
    checkpoint = None if full or not row else row[0]
    newest = checkpoint
    seen = set()

    for item in list_sops(checkpoint):
        seen.add(item["id"])
        mine = db.execute("select updated_at from sops where id = ?", (item["id"],)).fetchone()
        if mine is None or mine[0] < item["updated_at"]:
            sop = call(f"/sops/{item['id']}")["data"]
            db.execute(
                "insert into sops (id, updated_at, archived, doc) values (?, ?, ?, ?) "
                "on conflict(id) do update set updated_at = excluded.updated_at, "
                "archived = excluded.archived, doc = excluded.doc",
                (sop["id"], sop["updated_at"], sop["archived_at"] is not None, json.dumps(sop)),
            )
        if newest is None or item["updated_at"] > newest:
            newest = item["updated_at"]

    if full:
        # Anything we hold that a full pass didn't return is gone: deleted,
        # unshared, or archived in a library shared in from another workspace.
        for (sop_id,) in db.execute("select id from sops").fetchall():
            if sop_id not in seen:
                db.execute("delete from sops where id = ?", (sop_id,))

    if newest:
        db.execute("insert into state (k, v) values ('checkpoint', ?) "
                   "on conflict(k) do update set v = excluded.v", (newest,))
    db.commit()


if __name__ == "__main__":
    run(full="--full" in sys.argv)