m/paliad#76. The export button no longer pokes out of the tabs nav with a
non-tab styling — instead it lives inside a new "Verwaltung" tab (last in
the project tab list) as a normal section with heading, description, and a
plain btn-secondary trigger. Same gate as before (canExportProject).
Archive co-locates in the same tab as a pointer to the Edit-modal danger
zone: click "Bearbeiten öffnen" → modal opens scrolled to the archive
button. Single source of truth for the destructive action stays in the
modal; the Verwaltung pointer just gives it discoverability.
If neither sub-section is visible to the caller (no export entitlement,
not global_admin), the Verwaltung tab hides itself — an empty tab is
worse UX than no tab.
2026-05-25 13:33:14 +02:00
6 changed files with 112 additions and 753 deletions
Status: READY FOR REVIEW — no code yet, awaiting head/m go-no-go on §11 material picks.
---
## 0. TL;DR
Paliad already ships a per-scope data export (`/api/me/export`, `/api/projects/{id}/export`) built on `ExportService` with sheet-registry + xlsx + JSON + CSV bundling and a `system_audit_log` audit chain. Slice 3 (`org` scope, async) was the last unbuilt slice of t-paliad-214 — and that is exactly what "Backup Mode" wants, **plus** Slice 4 (scheduler). This design folds them together:
- **`org` scope of `ExportService`** — new `WriteOrg` + `orgSheetQueries(...)` registry. No new service.
- **On-demand admin trigger** — `POST /api/admin/backups/run` from `/admin/backups`, gated by the existing `adminGate(users, ...)` middleware.
- **Nightly scheduled trigger** — new `BackupScheduler` goroutine modelled on `ReminderService` (top-of-hour ticker, fires at 03:00 UTC).
- **Storage** — open. (R from issue) Supabase Storage bucket `paliad-backups` with 90-day lifecycle; (R from prior design) local disk `PALIAD_EXPORT_DIR`. **Material pick — escalated.** Defaulting to **Supabase Storage** in this draft because m's issue text named it explicitly; m's prior pick favoured local disk to avoid object-store provisioning. See §11 Q1.
- **Format** — open. (R from issue) single `.xlsx`; (R from prior design + shipped infra) `.zip` of xlsx + JSON + CSV. **Material pick — escalated.** Defaulting to **`.zip`** in this draft because (a) the writer abstraction already produces a zip, (b) JSON twin is the no-lock-in promise, (c) "Admin Excel" framing is satisfied by the xlsx-inside-the-zip. See §11 Q2.
- **`paliadin_turns`** — open. (R from issue) "no redaction for admin"; (R from t-paliad-214 m decision) "never include in org export." **Material pick — escalated**, but defaulting to **EXCLUDE** because the prior decision was explicit, structural, and the most-sensitive-PII rationale still holds. See §11 Q3.
- **Audit chain** — write to `paliad.system_audit_log` with `event_type ∈ {backup_created, backup_downloaded, backup_failed}`. No new audit infra.
Three slices: **A** (on-demand + workbook) ships the whole pipeline against the existing audit chain. **B** (scheduler + storage) adds nightly automation and the chosen artifact store. **C** (admin UI polish) makes `/admin/backups` a proper catalog browser.
-`internal/handlers/export.go` — 290 lines. `GET /api/me/export` (personal), `GET /api/projects/{id}/export?direct_only=0|1` (project). Audit row written **before** the artifact generation; patched on success/failure.
-`paliad.system_audit_log` (mig 102, shipped) — generic `event_type` text + `metadata jsonb`; `scope ∈ {org, project, personal}`; `actor_id` FK to `paliad.users` with `ON DELETE SET NULL`, `actor_email` captured at write time. RLS: self-read + admin-read. **Already supports `scope='org'`** — backup writes land here trivially.
-`github.com/xuri/excelize/v2 v2.10.1` — already in `go.mod`. No new xlsx dependency needed.
- Reference design doc: `docs/design-paliad-data-export-2026-05-19.md` — exhaustive (~600 lines), with m's Q-by-Q decisions captured in §12.
-`adminGate(users, gateOnboarded(h))` — the standard admin gate (10+ admin routes; e.g. `handlers.go:567-598`). Just wire the new routes through it.
### 1.2 What's NOT shipped
- **No `WriteOrg`** in `ExportService`. No `orgSheetQueries(...)` registry. The org scope is declared in the `ExportScope*` constants but no handler/writer references it yet.
- **No `PALIAD_EXPORT_DIR`** env var. No `/var/lib/paliad/exports/` mount on Dokploy. Greenfield on storage.
- **No Supabase Storage Go client** in `go.mod`. No `STORAGE_*` env vars. The Go side talks Postgres-only today; Supabase Storage would be a new HTTP-client dependency.
- **No backup scheduler.** The only in-process scheduler is `ReminderService` (`internal/services/reminder_service.go`) — top-of-hour aligned ticker with startup catch-up. Same shape works for backups.
- **No `paliad.backups`** table. The system_audit_log row alone is too sparse to back a "list past backups + click to download" UI; a dedicated catalog table is wanted.
### 1.3 New `paliad.*` tables since t-paliad-214 design (mig 103-120)
The org-scope sheet registry must enumerate these or fall behind reality. Mostly straightforward additions:
The coder enumerates `information_schema.tables WHERE table_schema='paliad'` at registry-build time and reconciles with the static list, raising a warning when the live schema introduces a table the registry doesn't know about (forces a registry update on every new migration touching paliad data).
### 1.4 Live row counts (informational)
Per the t-paliad-214 design's premise check (2026-05-19), full-org content was **<600user-contentrows**+~1000referencerows.Totalxlsxaftercompression:~100KB.Adailysnapshotatthissizeischeaptostore,cheaptotransfer,andwellbelowanystorage-lifecyclethreshold.
### 1.5 Conclusion
**No new service abstraction is needed.**`ExportService`istherighthome;extenditwith`WriteOrg`andasmallhandler+scheduler+catalogtablearoundit.
-- No INSERT/UPDATE/DELETE policies: all writes go through the Go service path
-- under the migration-runner role (the same role that writes to
-- system_audit_log).
COMMENTONTABLEpaliad.backupsIS
'Catalog of org-scope backup runs. One row per scheduled or on-demand backup. status transitions: running → done | failed. storage_uri resolves through the ArtifactStore interface (local file:// or supabase://). audit_id links to system_audit_log; the catalog row is duplicate-with-richer-shape, the audit row is the trust signal.';
```
**Why a separate catalog and not just the audit row?**
-Cleanupgoroutine:dailyscanof`paliad.backups WHERE finished_at < now() - INTERVAL '90 days' AND deleted_at IS NULL`;calls`store.Delete`;sets`deleted_at`.(ForSupabaseStorage,thisisredundantwithbucket-sidelifecyclerules—buthavingpaliadassertownershipkeepsthecatalogaccurateevenifabucketruleismisconfigured.)
- **Backup retention by count** (e.g. "keep last 30") — v1 is age-based only via the retention env var.
- **Export-as-email-attachment** — would require attaching ~150 KB to a daily mail; out of v1.
- **Public signed URLs that bypass cookie auth** — the download endpoint requires the admin cookie; no anon download surface in v1.
- **A second audit chain table** — `paliad.system_audit_log` is the only chain. The `paliad.backups` catalog is operational metadata, not audit.
---
## 11. Material picks for head → m
Most of the issue's R answers are non-material and the design defaults to them silently. Four are material and get escalated. The design's draft picks (above) reflect the inventor's best guess; m's calls supersede.
### Q1 — Storage backend: Supabase Storage (issue R) vs. local disk (prior design pick)?
**Inventor draft pick: Supabase Storage** (matches issue R; m named it explicitly).
Trade-offs:
- **Supabase Storage** — new HTTP-client dep (~200 LoC), but no provisioning (the youpc Supabase already has Storage available). Lifecycle rules are bucket-side. Signed-URL downloads bypass paliad → less bandwidth on the Dokploy host. Backups survive a Dokploy host loss.
- **Local disk** — zero external dep, simpler code. Tied to the Dokploy compose volume; backup is lost if the host is lost (which is exactly the disaster a backup defends against). Cleanup is Go-side, slightly more code.
Material because: the artifact-store choice has a real disaster-recovery implication (the backup of paliad should not live on the same host as paliad). And the prior t-paliad-214 design's "no MinIO provisioning" rationale doesn't apply — the youpc Supabase is already provisioned.
### Q2 — Bundle format: single `.xlsx` (issue R) vs. `.zip` (shipped infra)?
- **Single `.xlsx`** — matches m's "Admin Excel" framing literally. One file, double-click to open. Loses the JSON twin (Excel-independent re-ingest) — for org scope this re-introduces the lock-in problem the personal/project exports were designed to avoid.
- **`.zip` bundle** — preserves no-lock-in. Slight UX friction: admins unzip first to get the xlsx. The .xlsx inside still satisfies "Admin Excel" — m gets the workbook by clicking through one zip layer.
Material because: the no-lock-in promise was a load-bearing rationale in t-paliad-214 §1. Cutting it for the admin scope only is a real product decision, not a default.
### Q3 — `paliadin_turns` (+ `paliadin_aichat_conversation`) in the backup: include (issue R) or exclude (prior m decision)?
**Inventor draft pick: EXCLUDE** (matches prior m decision from t-paliad-214 Q5 addendum).
Reasoning: the prior m decision was structural ("the `paliadin_turns` row drops from the org-scope sheet table entirely — no `?include=paliadin_turns` query param") with a precedent-setting rationale ("the *moment* Paliadin opens beyond owner-only, the AI conversation history per user is the most sensitive personal data we carry"). That rationale still holds — `paliadin_aichat_conversation` (mig 118) extends the conversation history surface.
Issue R says "nothing for the admin role; this is an internal backup, not a sharable artifact." That argument is reasonable but undercuts the prior precedent. The conflict is real; flagging it.
Two flavours of "include":
- **All AI conversation tables included as-is** — full backup. Convenient. Most leaky.
- **Tables included but with `assistant_response` / message-body columns dropped** — keeps the metadata (who talked to Paliadin when), drops the content. Middle ground.
### Q4 — Scheduled backup time: nightly 03:00 UTC (draft) or a different hour / cadence?
**Inventor draft pick: nightly 03:00 UTC.**
Reasoning: 03:00 UTC = 04:00/05:00 Europe/Berlin (winter/summer), well outside business hours for every paliad office (Munich, Düsseldorf, Amsterdam, London, Paris, Milan, Hamburg). Low write traffic → snapshot-tx contention is minimal. Daily cadence is the issue's stated cadence.
Material if m wants weekly + monthly + on-status-change (the t-paliad-214 §5.2 sketch). Defaulting to daily keeps Slice B small; multi-cadence belongs in a follow-up.
The hour is `PALIAD_BACKUP_HOUR_UTC` (default 3), m-tunable per deploy.
---
## 11. Open questions for m (FLAG only — no escalation)
These are coder-time choices that don't move the design. Listed for the coder shift to resolve in implementation chat with m.
- Exact bucket name if Q1=supabase (`paliad-backups`? `paliad-{firm-slug}-backups`?).
- Whether the on-demand button rate-limits (e.g. "only one running backup at a time"). Default: yes, the runner rejects on overlap with a friendly 409.
- Whether the `/admin/backups` row-click navigates to a backup-detail page (showing per-sheet row counts, warnings, the originating audit row). Default: no detail page in v1; the table columns are sufficient.
- Whether the README.txt also embeds a one-line list of every sheet name. Default: yes (it's two lines of code and very useful for "what tables were in scope on this date").
- Whether `submission_drafts.body_md` (the actual draft pleading text) is included verbatim or dropped. Default: included — admins are by definition allowed to see this. Flag to m so the precedent is recorded.
---
## 12. References
-`docs/design-paliad-data-export-2026-05-19.md` — the prior design this extends (Slices 1-2 SHIPPED, Slice 3-4 deferred until this design).
-`internal/services/export_service.go` — the writer abstraction we extend with `WriteOrg`.
-`internal/services/reminder_service.go` — the in-process scheduler template for `BackupScheduler`.
-`internal/db/migrations/102_system_audit_log.up.sql` — the audit chain we reuse.
-`internal/handlers/handlers.go:567-598` — the existing admin-gate block; new `/admin/backups*` routes register alongside.
- m/paliad#77 — the originating ask.
- m/paliad#214 — the prior data-export task (Slices 1-2 shipped).
---
**END OF DESIGN. Status: READY FOR REVIEW pending §11 Q1-Q4 material picks.**
Inventor parks here. Head's `mai-head` skill gates the coder shift; this draft uses the issue's R defaults everywhere except where they conflict with prior shipped m decisions (Q1, Q2, Q3) or where the issue text named no default (Q4).
"projects.detail.submissions.empty":"Es sind aktuell keine Schriftsatzvorlagen hinterlegt.",
"projects.detail.submissions.empty.no_proceeding":"Für dieses Projekt ist noch kein Verfahrenstyp gesetzt — der Katalog unten zeigt trotzdem alle Vorlagen.",
/* Verwaltung tab — rare admin actions (export, archive) live here as
stacked sections. No accent, no oversized buttons (t-paliad-245). */
.settings-section{
margin-bottom:2rem;
}
.settings-section:last-child{
margin-bottom:0;
}
.settings-section.tool-subtitle{
margin-bottom:0.75rem;
}
.entity-events{
list-style:none;
padding:0;
Reference in New Issue
Block a user
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking a user.
