hoelee
4693c9d684
- w4-body.js: ONE-DIRECTIONAL sync (NocoDB status = SOT, never reverse);
drift matrix: missing user/role (provision w/ username lookup to avoid
duplicates), dangling refs (adopt/recreate), disabled<->status, canonical
base_path '/' + permission 16384, scopes vs calculateEffectiveGrants,
username drift detect+log_only; verify-after-repair on every change;
password never pushed on repair (方案 C); dry-run via ?dryRun=true
- gen_w4.js: daily 03:00 cron + webhook alist-w4-reconcile; API-import-safe
(UUID node ids, no pinData, webhookId)
- ReconcileLogs table mktsb7le6fqr2u1 (add_reconcilelogs_table.py, idempotent,
v2 API); FK column nc_jitu___Customers_id for bt-link writes (v2 ignores
link column itself)
- test_w4_reconcile.js: 34 assertions / 12 mock worlds, ALL PASSED
- live-verified against production: dry-run (3 drifts + 1 error found),
real repair (3 repaired, verify all passed, 4 log rows), idempotent rerun
- NocoDB 2026.08.x pitfalls documented (v1 col endpoints gone, row delete
via body [{Id}]); n8n API import/activation pitfalls (activeVersionId)
- docs: W4-DESIGN.md new; DATABASE_SCHEMA 8 tables; N8N_WORKFLOWS, SETUP,
PROJECT_STATE updated
118 lines
4.5 KiB
Python
118 lines
4.5 KiB
Python
#!/usr/bin/env python3
|
|
"""Ensure the ReconcileLogs table exists in AlistAccess (idempotent, NON-destructive).
|
|
|
|
Unlike create_nocodb_schema.py (which drops + recreates the base), this script
|
|
only creates/mends the ReconcileLogs table + columns. Safe to re-run.
|
|
Uses v2 meta endpoints only — verified on NocoDB 2026.08.1 (v1 column GET/PATCH
|
|
endpoints are GONE on this version; column id is fetched from the table meta).
|
|
|
|
Credentials from env (NOCODB_URL / NOCODB_TOKEN) — never commit real tokens.
|
|
|
|
Usage:
|
|
NOCODB_TOKEN=... python scripts/add_reconcilelogs_table.py
|
|
"""
|
|
import json, os, urllib.request, urllib.error
|
|
|
|
NOCODB_URL = os.environ.get("NOCODB_URL", "https://nocodb.hoelee.com")
|
|
NOCODB_TOKEN = os.environ.get("NOCODB_TOKEN", "CHANGE_ME_nocodb_token")
|
|
BASE_ID = os.environ.get("NOCODB_BASE_ID", "p3lzkresswyowqg")
|
|
CUSTOMERS_TID = os.environ.get("NOCODB_TABLE_CUSTOMERS", "maqb9zgvza2te3k")
|
|
TABLE_TITLE = os.environ.get("RECONCILELOGS_TITLE", "ReconcileLogs")
|
|
|
|
DRIFT_OPTIONS = [
|
|
"user_missing", "role_missing", "dangling_user_ref", "dangling_role_ref",
|
|
"user_disabled_drift", "scopes_drift", "base_path_drift",
|
|
"permission_drift", "role_binding_drift", "username_drift",
|
|
"verify_failed", "reconcile_error",
|
|
]
|
|
RESULT_OPTIONS = ["repaired", "log_only", "failed", "skipped"]
|
|
|
|
|
|
def req(method, path, body=None):
|
|
r = urllib.request.Request(f"{NOCODB_URL}{path}", method=method)
|
|
r.add_header("xc-token", NOCODB_TOKEN)
|
|
if body is not None:
|
|
r.add_header("Content-Type", "application/json")
|
|
r.data = json.dumps(body).encode()
|
|
try:
|
|
with urllib.request.urlopen(r, timeout=30) as resp:
|
|
return json.loads(resp.read().decode() or "null")
|
|
except urllib.error.HTTPError as e:
|
|
raise RuntimeError(f"{method} {path} -> HTTP {e.code}: {e.read().decode()[:200]}")
|
|
|
|
|
|
def table_meta(tid):
|
|
return req("GET", f"/api/v2/meta/tables/{tid}")
|
|
|
|
|
|
def col_by_title(tid, title):
|
|
meta = table_meta(tid)
|
|
return next((c for c in (meta.get("columns") or []) if c.get("title") == title), None)
|
|
|
|
|
|
def ensure_column(tid, title, uidt, **extra):
|
|
c = col_by_title(tid, title)
|
|
if not c:
|
|
req("POST", f"/api/v2/meta/tables/{tid}/columns",
|
|
{"title": title, "uidt": uidt, **extra})
|
|
c = col_by_title(tid, title)
|
|
if not c:
|
|
raise RuntimeError(f"column {title} not found after create")
|
|
print(f" + {title} ({uidt}) -> {c['id']}")
|
|
return c
|
|
|
|
|
|
def ensure_ss_options(tid, title, options):
|
|
c = ensure_column(tid, title, "SingleSelect")
|
|
cur = ((c.get("colOptions") or {}).get("options") or [])
|
|
cur_titles = [o.get("title") for o in cur]
|
|
if cur_titles == options:
|
|
print(f" = {title} options OK")
|
|
return
|
|
req("PATCH", f"/api/v2/meta/columns/{c['id']}", {
|
|
"colOptions": {"options": [{"title": o, "order": i + 1}
|
|
for i, o in enumerate(options)]},
|
|
"dtxp": ",".join(options),
|
|
})
|
|
print(f" ~ {title} options set")
|
|
|
|
|
|
def main():
|
|
tables = req("GET", f"/api/v2/meta/bases/{BASE_ID}/tables")["list"]
|
|
existing = next((t for t in tables if t["title"] == TABLE_TITLE), None)
|
|
if existing:
|
|
tid = existing["id"]
|
|
print(f"{TABLE_TITLE} exists: {tid}")
|
|
else:
|
|
t = req("POST", f"/api/v2/meta/bases/{BASE_ID}/tables", {
|
|
"title": TABLE_TITLE, "table_name": TABLE_TITLE, "type": "table",
|
|
"columns": [],
|
|
})
|
|
tid = t["id"]
|
|
print(f"{TABLE_TITLE} created: {tid}")
|
|
|
|
ensure_column(tid, "run_id", "SingleLineText")
|
|
ensure_column(tid, "details", "LongText")
|
|
ensure_column(tid, "action", "SingleLineText")
|
|
ensure_column(tid, "timestamp", "DateTime")
|
|
ensure_ss_options(tid, "drift_type", DRIFT_OPTIONS)
|
|
ensure_ss_options(tid, "result", RESULT_OPTIONS)
|
|
|
|
if not col_by_title(tid, "customer"):
|
|
req("POST", f"/api/v1/db/meta/tables/{tid}/columns", {
|
|
"title": "customer", "uidt": "LinkToAnotherRecord",
|
|
"parentId": CUSTOMERS_TID, "childId": tid, "type": "bt",
|
|
})
|
|
c = col_by_title(tid, "customer")
|
|
print(f" + customer (bt link) -> {c['id']}")
|
|
fk = col_by_title(tid, "nc_jitu___Customers_id")
|
|
if not fk:
|
|
raise RuntimeError("FK column nc_jitu___Customers_id missing — bt link create failed")
|
|
|
|
print(f"\nDONE. table={tid} fk_column={fk['id']} ({fk['title']})")
|
|
print("=> add to n8n env: NOCODB_TABLE_RECONCILELOGS=" + tid
|
|
+ " NOCODB_FK_RECONCILELOG_CUSTOMER=" + fk["title"])
|
|
|
|
|
|
if __name__ == "__main__":
|
|
main() |