# Design ## Goals and constraints | Decision | Choice | | ------------- | --------------------------------------------------------------------------------------------------------------------------- | | Direction | One-way, SharePoint → ACC | | Latency | Poll every ~3 min (configurable); "Run now" for immediate sync | | Operations | Create, update (new ACC version), rename, move, delete | | Scope | Many projects per server; each project is a 1:1 pair of SharePoint root and ACC root | | ACC ownership | Only touch ACC items this tool created; hand-added ACC content is left alone | | Ignored files | Temp/lock/system files are skipped silently (not issues) | | Excluded | Admins can untick folders per project (all sync by default); excluding deletes the tool's ACC copies, re-including restores | | Retries | 3 attempts across runs, then surface as an issue | | Files | Small/medium documents; no large Revit models | | Hosting | One Docker container (Portainer), SQLite on a volume | | Credentials | One Microsoft app registration + one APS app, shared by all projects | | UI access | Viewing and "Run now" are open (behind VPN). Adding, editing or resetting projects requires the single admin login from env | ## Architecture The sync logic is plain TypeScript with no SvelteKit imports. SvelteKit hosts it: it starts the scheduler from the `init` hook and serves the status UI. The core depends only on interfaces (ports); SharePoint, ACC and SQLite are adapters plugged in at one composition root. ``` ┌──────────────── SvelteKit (adapter-node) ────────────────┐ │ routes/ (status UI, Run now, admin) hooks.server.ts │ │ │ (init → start) │ │ ▼ │ │ │ SyncCoordinator ◄── scheduler tick ────┘ │ │ (per-project lock + cooldown) │ │ │ │ │ ▼ │ │ SyncEngine ── IgnorePolicy │ │ ┌──────┼───────────────┐ │ │ ▼ ▼ ▼ │ │ ChangeSource Repositories DocumentTarget │ │ (Graph) (SQLite) (APS / ACC) │ └──────────────────────────────────────────────────────────┘ ``` ### Each run has two phases 1. **Ingest.** Read changes from SharePoint since the project's saved cursor and write them to `items` as the _desired_ state (name, parent, content tag, deleted flag). Save the new cursor in the same transaction. Nothing touches ACC in this phase. 2. **Reconcile.** For every item whose desired state differs from its _synced_ state, work out the operation and apply it to ACC. On success, copy desired → synced. On failure, increment `attempt_count` and record the error. This split gives us several things without extra code: - **Retries.** Failed items still differ, so the next run picks them up again. No separate retry queue is needed. - **Crash safety.** If the container dies mid-run, the cursor is already saved and the pending work is still in the database. - **Inferred renames and moves.** Graph delta only reports "this item now looks like X". The operation comes from comparing that to what we last synced: | Desired vs synced | Operation | | ------------------- | ------------------------------- | | no target id yet | create (folder) / upload (file) | | `deleted` | delete | | name differs | rename | | parent differs | move | | content tag differs | upload new version | A single change can need more than one of these, for example a move plus a rename. **Order within reconcile:** folder creates and moves from shallowest to deepest, then files, then deletes. That way a destination folder always exists before anything moves into it. ### Scope and delta queries Graph delta for SharePoint only works at the **drive root**, not on subfolders. So each project runs delta on its whole drive and keeps only items under the project's root folder. It checks this with the parent chain stored in `items`. An item whose new parent is outside the root counts as deleted. That covers "removed from the root = deleted in ACC". **Safety rule:** if the project's root folder is deleted, or is missing from a full listing, the project is paused with a project-level issue and **nothing is deleted**. That is almost always a mistake, not an intent to wipe ACC. Moving or renaming the root within the same library is harmless, because projects track it by ID. **Restored folders:** a folder delete cascades "deleted" to everything stored under it. If a deleted folder later reappears, its contents may not be reported one by one, so ingest asks for one follow-up **full listing** in the same run, which brings them back. **Excluded folders:** an admin can untick folders on the project's admin page; their IDs are stored on the project. Ingest still tracks everything under them, so `desired` stays accurate. Between ingest and reconcile, each run sets `excluded` on every item that is, or sits under, an excluded folder (by SharePoint's current layout, so moves in and out count). Reconcile treats excluded like deleted: the folder's ACC copy is deleted with one call, which covers its contents. Unticking it again clears the flag, and the tombstoned ACC items are restored. Saving the choice updates the flags straight away, so the status page shows the change before the next run. ## Interfaces (ports) The source of truth is [`src/lib/server/sync/core/ports.ts`](../src/lib/server/sync/core/ports.ts): - `ChangeSource` (SharePoint): `getChanges(root, cursor)` returns changes, the next cursor and whether it was a full listing. `download(root, id)` returns the file content. - `DocumentTarget` (ACC): `createFolder`, `uploadFile`, `uploadVersion`, `rename`, `move`, `delete`, `restore`. - `IgnorePolicy`: decides which names are skipped. Repositories are split by consumer (interface segregation): - `ProjectRepository`: create, pause/resume, first-synced and last-run stamps, reset - `ItemRepository`: load a project's items, commit an ingest with its cursor (atomically), save reconcile results - `RunRepository`: start/finish runs, record events, list history, prune old history Adapters throw `ServiceUnavailableError` for outages (network, auth, throttling that outlasted retries). The run stops early and no item is charged an attempt; any other error counts against the item that hit it. Both fallback strategies, move-by-copy and restore-by-reupload, live **inside the ACC adapter**. They are ACC-specific details, so the engine never branches on them. ### Coordinator `SyncCoordinator` is the only way a run starts. It holds one in-process lock per project. `requestManualRun(projectId)` ("Run now") returns one of: - `started` - `already-running` (the page just shows the run in progress) - `cooldown` with `retryAfterSeconds` (the last manual request for this project was under 30 s ago) - `unavailable` with a reason (unknown, disabled or paused project) `runScheduledPass()` is what the timer calls. An `IntervalScheduler` drives it, and the history-pruning job, without ever overlapping a task with itself. The scheduler goes through enabled projects one at a time, and one failing project never blocks the others. Graph and APS clients share one HTTP layer that handles `429`/`Retry-After` and backoff, because rate limits apply per app, not per project. ## Data model (SQLite via Drizzle) ``` projects id, name, enabled, paused_reason sp_site_id, sp_drive_id, sp_root_item_id, sp_display_path acc_hub_id, acc_project_id, acc_root_folder_id, acc_display_path excluded_folder_ids -- JSON array of SharePoint folder IDs delta_cursor, first_synced_at, last_run_at, created_at items PK (project_id, source_id) -- desired (from SharePoint) parent_source_id, name, is_folder, content_tag, source_modified_at, size, deleted -- synced (what ACC has) target_id, synced_parent_source_id, synced_name, synced_content_tag, synced_deleted, synced_at -- bookkeeping excluded -- in or under a folder an admin excluded status ('synced' | 'pending' | 'failed' | 'deleted' | 'excluded') attempt_count, last_error, last_attempt_at runs id, project_id, trigger ('schedule' | 'manual'), started_at, finished_at outcome ('success' | 'partial' | 'failed'), error created, updated, renamed, moved, deleted, restored, failed -- counts run_events id, run_id, project_id, source_id, path, action, message, at -- action: created | updated | renamed | moved | deleted | restored | failed ``` The synced parent is stored as a SharePoint ID, so "was it moved?" is a plain comparison with the desired parent. The ACC folder to move into is looked up from the parent item when needed. - IDs are stored, not paths, so renaming a root folder doesn't break a project. Display paths are shown in the UI only. - **Issues** = items with `status = 'failed'`, plus a project-level issue when it is paused or its latest run failed outright (bad auth, root missing). - A nightly job deletes `runs` and `run_events` older than `HISTORY_RETENTION_DAYS`. `items` is current state, so it is never pruned. - After a failed item has used up its attempts, it is only retried when its content or location changes again, or when someone clicks retry. - Deleted items keep their `target_id` (a tombstone). That lets a SharePoint restore revive the same ACC item instead of creating a new one. - A project's roots are locked once `first_synced_at` is set. Changing them requires an explicit **reset**, which clears the project's items and cursor. ## UI | Route | Who | What | | --------------------------------------------- | ------ | ---------------------------------------------------------------------------------------------------------------------- | | `/` | anyone | Projects list: status, last run, open issue count | | `/projects/[id]` | anyone | File tree with status badges and last update time, an issues panel and a "Run now" button | | `/projects/[id]/history` | anyone | Runs from the last 7 days, expandable to per-file events | | `/admin/login` | — | Single admin login | | `/admin/projects/new`, `/admin/projects/[id]` | admin | Add/edit project (paste SharePoint URL and pick ACC folder; server resolves and checks the IDs), enable/disable, reset | - The status pages read only from SQLite. They never call SharePoint or ACC. - While a run is active, the project page refreshes its data every few seconds. ## Configuration (env) ``` GRAPH_TENANT_ID, GRAPH_CLIENT_ID, GRAPH_CLIENT_SECRET APS_CLIENT_ID, APS_CLIENT_SECRET ADMIN_USERNAME, ADMIN_PASSWORD, SESSION_SECRET DATABASE_PATH=/data/sync.db POLL_INTERVAL_SECONDS=180 MANUAL_RUN_COOLDOWN_SECONDS=30 MAX_ATTEMPTS=3 HISTORY_RETENTION_DAYS=7 ``` Env vars are declared and validated with `zod` in `src/env.ts` (SvelteKit's `defineEnvVars`); the server refuses to start on bad config. Only the variables in use so far are declared; the Graph and APS ones get added along with the adapters that read them. Run exactly **one** replica, because the locks live in-process. `SYNC_BACKEND` picks the SharePoint/ACC connection: - **`live`**: the real Graph/APS adapters. Until those exist, a `NotConnectedBackend` stands in. Runs fail as an outage (no attempts charged) and the add-project form explains why. - **`simulated`**: `FakeDrive`/`FakeAcc` behind a `SimulatedBackend` that resolves any pasted path, seeds sample files and makes random edits. The database is in-memory in this mode, so the fake services and the sync state never disagree. `ADMIN_USERNAME`/`ADMIN_PASSWORD` turn the admin pages on; `SESSION_SECRET` keeps logins valid across restarts. Two settings live outside `src/env.ts`: - **`ORIGIN`** is read at build time (`paths.origin` in `vite.config.ts`). SvelteKit 3 fixes the app's address at build time, and adapter-node otherwise assumes HTTPS. So over plain HTTP, form posts fail the CSRF check, and the session cookie is marked `Secure`, unless `ORIGIN` is set. Behind an HTTPS reverse proxy, leave it unset. - **`TZ`** is the container's time zone. Times are formatted on the server first, so it should match the users' zone. ### Deployment - `Dockerfile`: a multi-stage build on `node:24-bookworm-slim`. The runtime stage holds `build/`, production `node_modules` (`better-sqlite3`, `zod`; everything else is bundled) and `drizzle/`, runs as `node`, and keeps the database at `/data/sync.db`. - No CI: images are built on a developer's machine with `npm run image:publish` (`scripts/publish-image.ts`). It refuses uncommitted changes, runs lint, type check and tests, builds, and pushes to the Gitea registry tagged with the short commit hash and then `latest`. It reads `ORIGIN` from the shell, never from `.env`. Portainer only pulls and runs the image. - `docker-compose.yml` is the Portainer stack: one service using the published image with `pull_policy: always` (a redeploy never reuses a stale image, and `IMAGE` pins a commit tag for rollback), the `sync-data` volume, required settings enforced with `${VAR:?}`, and `stop_grace_period: 2m` so a run in progress can finish. Its `build:` section is for local `docker compose up --build`. - `GET /healthz` backs the image's `HEALTHCHECK`. - On startup, runs left without an outcome (the process was killed mid-run) are closed as failed. The next run carries on from the saved item state. - Operations are covered in [runbook.md](runbook.md). ### Code conventions - **Imports:** never `../`. Anything outside the current folder goes through the `#lib/...` alias (`package.json` `imports`). Every local import carries its `.ts` extension, which SvelteKit 3's tsconfig requires. - **Internal links:** always built with `resolve()` from `$app/paths` (enforced by lint). ## Source layout ``` src/env.ts # env var declarations + validation src/hooks.server.ts # init → startApp(config); admin route guard src/lib/server/ container.ts # composition root: the only place adapters are chosen app.ts # holds the running services (survives dev reloads) forms.ts logger.ts sync/ core/ # pure TS, no SvelteKit imports model.ts ports.ts errors.ts ingest.ts # SharePoint changes → desired state plan.ts # desired vs synced → action, status, work order reconciler.ts # applies actions to ACC engine.ts # one run of one project tree.ts ignore.ts coordinator.ts # per-project lock + manual cooldown scheduler.ts # non-overlapping interval tasks project-service.ts # admin operations: create/edit/reset/delete, retry testing/ # harness: real engine + in-memory SQLite + fakes status/ # read models for the UI: summaries, issues, file tree auth/ # admin credentials, signed session tokens, cookie helpers adapters/ sqlite/ # Drizzle schema, migrations runner, repositories simulated/ # FakeDrive, FakeAcc, SimulatedBackend (demo + tests) not-connected.ts # placeholder for live mode until Graph/APS exist graph/ # (todo) GraphChangeSource + SourceRootResolver, auth (msal-node) aps/ # (todo) ApsDocumentTarget + TargetRootResolver, upload flow http/ # (todo) shared fetch wrapper: retry, backoff, Retry-After src/lib/components/ # Badge, FileTree, RunNowButton, ProjectFields, TimeAgo, AutoRefresh src/lib/styles/app.css # design tokens + shared styles src/routes/ # projects, project status + history, admin, healthz drizzle/ # generated SQL migrations (npm run db:generate) Dockerfile docker-compose.yml scripts/publish-image.ts # checks, builds and pushes the image (npm run image:publish) ``` ## Open questions (resolved by spikes) 1. **Move a file in ACC natively?** The APS docs show folder moves via `PATCH folders` (changing the parent). Single-file moves are not clearly documented. If not supported, the fallback is copy then delete, which may lose ACC version history. 2. **Revive a deleted ACC item?** Can we add a version to, or restore, a deleted item through the API so that history and links are kept? If not, the fallback is to upload a new item. 3. **App-only auth for ACC writes:** whether some ACC Docs operations need an `x-user-id` (a service user that is a member of each project). 4. **Graph delta with a subfolder root:** confirm the drive-wide filtering approach and how folder deletes report their children. ## Build order 0. **Access requests:** start now; they're the slowest part. See [access-setup.md](access-setup.md). 1. **Spikes** against a sandbox ACC project and test SharePoint site. Answer the open questions above and record the results here. 2. **Scaffold (done):** SvelteKit 3 + adapter-node, TypeScript strict, Drizzle + better-sqlite3, zod, Vitest, ESLint/Prettier. (Dockerfile moves to step 9.) 3. **Core (done):** ports, IgnorePolicy, ingest, planner, reconciler and SyncEngine, tested against FakeDrive/FakeAcc. 4. **SQLite repositories and migrations (done).** 5. **GraphChangeSource** + SharePoint URL resolver. 6. **ApsDocumentTarget** + ACC folder URL resolver, including the fallbacks chosen in step 1. 7. **Wiring (done):** coordinator, scheduler, composition root, `init` hook, history pruning, simulated mode. 8. **UI (done):** projects list, project status (title block, issues with retry, file tree), 7-day history with per-run detail, Run now with cooldown, admin login, add/edit/resume/reset/remove. 9. **Ship (done):** Docker image, Portainer stack file, publish script, runbook. The image was tested locally. The first publish and Portainer deploy are still to do.