# Architecture How Pins fits together, and **why** — the reasoning matters more than the diagram, because it's what lets a future session (or the owner) disagree well. Product intent lives in `VALUES.md`; how we work lives in `PLAYBOOK.md`. **Status: designed, not built.** Written 2026-07-16 at kickoff. Nothing here has met a compiler yet, so treat it as a considered starting point, not proven ground. --- ## The shape ``` ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │ Android app │ │ Web / PWA │ │ Wear OS │ │ Chrome ext │ │ React+Cap. │ │ React+Cap. │ │ Kotlin │ │ (later) │ │ ┌──────────┐ │ │ ┌──────────┐ │ │ ┌──────────┐ │ │ │ │ │ SQLite │ │ │ │ SQLite │ │ │ │ queue │ │ │ thin, online │ │ │ (full │ │ │ │ (full │ │ │ │ only │ │ │ only │ │ │ copy) │ │ │ │ copy) │ │ │ └──────────┘ │ │ │ │ └────┬─────┘ │ │ └────┬─────┘ │ └──────┬───────┘ └──────┬───────┘ └──────┼───────┘ └──────┼───────┘ │ │ │ │ │ │ └──────────────────┴────────┬─────────┴──────────────────┘ │ HTTPS, token per device ┌──────▼───────┐ │ Laravel │ sync API + blob store │ MySQL │ the durable copy │ Apache/Pi │ └──────┬───────┘ │ nightly (planned) ┌──────▼───────┐ │ NAS + offsite│ └──────────────┘ ``` ## The one idea everything follows from **Every client holds a complete copy of every pin, and the server is where those copies go to be safe — not where they live.** This is unusual, and it's only available because Pins is single-user. A lifetime of text and links is small: 10,000 pins at ~1 KB each is ~10 MB. That fits on a phone with room to spare, so there is no reason to ever ask the server for a pin you already have. Almost everything good here falls out of that one decision: - **Offline isn't a mode.** There's no "offline support" to build — the app only ever reads local data. The network is for durability and for other devices, never for the current screen. - **Search is local and instant**, on every client, with no server search to build or keep in sync. - **Capture never blocks**, because saving a pin is a local write. This is the concrete form of the tiebreak in `VALUES.md`: longevity and capture speed only appear to conflict, and writing locally first serves both. - **The API stays tiny** — a sync endpoint and a blob endpoint, not a CRUD surface. **The cost, stated honestly:** every client needs a real local database and a sync implementation, which is more up-front work than "app calls API". We're buying that with the one thing this product cannot compromise on, so it's the right trade — but it *is* a trade, and it's why the sync design below deserves the care it gets. **Images are the exception.** Blobs don't replicate everywhere (see below), because photos are the one thing that can outgrow a phone. --- ## Data model ### Identity: UUIDv7, generated on the client Pins get their ID **on the device that creates them**, before the server has ever heard of them. That's forced: an offline client must be able to create a pin, and an auto-increment ID needs a round-trip. Client-side IDs also make sync idempotent — a re-sent pin is recognised, not duplicated. **UUIDv7 specifically, not v4.** v7 is time-ordered, so it sorts by creation and — the part that matters — inserts in roughly ascending order. Random v4 primary keys scatter writes across a B-tree and fragment the index; it's a well-known way to make MySQL slow, and it's free to avoid by picking the right version now. Stored as `BINARY(16)`. > Verify before relying on it: JS side uses the `uuidv7` package (clients mint every ID). The server > only validates. Laravel's `Str::uuid7()` exists in recent versions — check the pinned version > rather than assuming. ### `user_id` from day one, even though there is one user Every table carries `user_id`, hardcoded to 1 for now. It's a column today and a migration-plus-backfill-plus-every-query-rewrite later. Pins might go public (`VALUES.md`); the cost now is a few bytes and about ten minutes, and the cost later is a weekend and a chance to leak one user's pins to another. Cheap insurance against a plausible future. ### Tables ```sql pins id BINARY(16) PK -- UUIDv7, minted by the client user_id BIGINT -- always 1 for now (see above) type ENUM('text','link','image') title VARCHAR(512) NULL body MEDIUMTEXT NULL -- the note, or the link's description url TEXT NULL -- type='link' blob_hash CHAR(64) NULL -- type='image' → blobs.hash created_at DATETIME(3) -- client wall clock, display only updated_at DATETIME(3) -- client wall clock, LWW tiebreak deleted_at DATETIME(3) NULL -- tombstone; never a hard delete sequence BIGINT -- server-assigned, INDEXED — the sync cursor device_id BINARY(16) -- who last wrote it; deterministic LWW tiebreak pin_tags pin_id BINARY(16) tag VARCHAR(64) -- normalised: trimmed, lowercased added_at DATETIME(3) removed_at DATETIME(3) NULL -- tombstone, not a DELETE (see conflicts) sequence BIGINT PRIMARY KEY (pin_id, tag) blobs hash CHAR(64) PK -- SHA-256 of the bytes; content-addressed size BIGINT mime VARCHAR(64) created_at DATETIME(3) devices id BINARY(16) PK user_id BIGINT name VARCHAR(128) -- "OnePlus 13", "Watch", "Desktop Chrome" platform VARCHAR(32) last_seen_at DATETIME(3) created_at DATETIME(3) ``` **Tags are strings on `pin_tags`, not a normalised `tags` table with a foreign key.** A tags table buys referential integrity and cheap renames; it costs a join on every read and an ID to sync for every tag. For a single user with maybe a few hundred distinct tags, the join is the bigger cost and renames are rare. If tag rename/merge ever becomes a real feature, revisit — that's the trigger. **`pins` and `pin_tags` are the same on the client**, in SQLite, minus `user_id` — same columns, same names. That's deliberate: sync code that has to translate between two shapes is sync code with a place to hide bugs. --- ## Sync ### The protocol Two endpoints. Clients **never** CRUD the server — they mutate locally and reconcile. ``` GET /api/v1/sync?since=&limit=500 → { changes: [...], cursor: , more: } POST /api/v1/sync { device_id, changes: [...] } → { applied: [{ id, sequence }], rejected: [{ id, reason }] } ``` That shape isn't an implementation detail — it's the local-first rule made **structural**. There is no endpoint that creates a pin, so no future session can accidentally write an online-only feature against one. The architecture enforces the value instead of relying on discipline. ### `sequence` — the server owns the ordering The server keeps a monotonic per-user counter. Every write gets the next value; clients remember the highest they've seen and ask for everything after it. That's the entire sync state: **one integer per client.** No timestamps to compare, no clock trust, no "what did I miss" logic. > **Gotcha — the sequence gap.** If two writes get sequences 4 and 5, and 5 commits first, a client > pulling at that instant sees 5, stores cursor=5, and **never sees 4 again**. The pin is on the > server, on no client, and nothing reports an error — a silently lost pin, which is the one thing > `VALUES.md` says must never happen. > > Assign the sequence **inside the same transaction as the write**, taking the counter with > `SELECT ... FOR UPDATE`. That serialises assignment, so a committed row is never followed by a > lower uncommitted one. Single-user means contention is irrelevant, so this costs us nothing. > > This is the sharpest edge in the whole design. Any change to sync must answer this question again. ### Conflicts: last-write-wins, per pin Two devices edit the same pin while offline. **Higher `updated_at` wins; ties broken by `device_id`** so every device independently reaches the same answer. Real CRDTs would preserve both edits, and they're the wrong tool here: this is one person, who is not editing the same pin on two devices in the same minute. The realistic conflict is *capture on the phone, tidy up on the desktop, hours apart* — LWW handles that perfectly. CRDTs would be weeks of work and a permanently harder codebase to protect against something that essentially doesn't happen. > **Clock skew is the weakness.** LWW trusts client wall clocks, and a device with a wrong clock can > stomp a newer edit. Mitigation: the server rejects any `updated_at` more than a few minutes in the > future (returns it in `rejected`, and the client re-stamps and retries). Not airtight — a clock > that's *behind* just loses quietly. Accepted for a single user with phones that sync time from the > network; revisit if Pins ever goes multi-user, where it stops being acceptable. ### Tags conflict differently — union, not LWW Tags are a **set**, and per-pin LWW would throw away a concurrent tag add. So `pin_tags` rows carry their own `added_at` / `removed_at` and merge as a set: a tag is present if `added_at` is the later of the two. Adds never lose to an unrelated edit. This is why `removed_at` is a tombstone rather than a `DELETE`: an untagging must be able to propagate to a device that's been offline for a week. A deleted row is indistinguishable from a row that device hasn't seen yet. ### Deletion is always a tombstone `deleted_at`, never `DELETE`. Same reason — and it gives us a trash for free, which `VALUES.md` wants anyway (confirm destructive actions; nothing lost silently). Purging old tombstones is a **deliberate, dry-run-first** operation (`PLAYBOOK.md` §6), never automatic. ### The client side: `dirty`, not an outbox Local rows carry a `dirty` flag. A sync worker drains dirty rows when there's connectivity, clears the flag on acknowledgement, and stores the returned `sequence`. **No separate outbox table.** The obvious design is a queue of pending operations, but it duplicates the pin's state in two places that can disagree — and reconciling a stale queue entry against an edited row is exactly the kind of bug that eats data at 2am. The dirty rows *are* the queue, and they can't drift from themselves. (§5: duplication that would drift is the enemy.) Because conflicts resolve by LWW rather than by replaying operations, **push order doesn't matter** — which is what makes this simplification safe. If ordered operations are ever needed, this decision must be revisited first. --- ## Images and blobs Images are the one thing that breaks full replication: a phone camera can outgrow a Pi, let alone every client. - **Content-addressed by SHA-256.** The hash *is* the identity, so uploads are idempotent, duplicates cost nothing, and a blob is immutable — never a sync conflict, because content can't change. - **Metadata syncs; bytes don't.** The pin appears on every device immediately with its local file; other devices lazily fetch the blob when it's first viewed, and cache it. - **Capture never waits for the upload.** The pin is saved and confirmed locally the instant it's shared; the blob uploads in the background like any other dirty row. - **Stored on the Pi's filesystem, never in MySQL, never in the web root.** `storage/app/blobs///` — fanned out so no directory grows to a hundred thousand entries. Served only through an authenticated Laravel route (`PLAYBOOK.md` §6: don't expose the filesystem). - **Orphans need a GC** — a blob whose last pin is purged. Dry-run first, always. ``` POST /api/v1/blobs (multipart) → { hash } -- idempotent; existing hash is a no-op GET /api/v1/blobs/{hash} → the bytes -- authenticated ``` --- ## Search **SQLite FTS5 on the client, over title + body + url + tags.** Local, instant, offline, and it's the only search there is — full replication means the server never needs a search endpoint. A whole subsystem doesn't get built, and the two implementations that would have drifted apart don't exist. Danish and English share one index. FTS5's default tokenizer doesn't stem, and the Porter stemmer is English-only — so a Danish search for "bøger" won't match "bog". Accepted for now: with prefix matching over a personal corpus you already half-remember, this is a much smaller problem than it sounds. Revisit if it actually annoys you; that's a better signal than guessing today. --- ## Auth **Laravel Sanctum, one token per device**, named and individually revocable, listed in a settings screen. Per-device rather than one shared secret because the watch is the weak link: it's the most losable device, and it will hold a credential to your entire archive. One token per device means a lost watch costs you one revocation, not a rotation across everything you own. HTTPS only, no exceptions — a token in a header over plain HTTP is a token in public. --- ## The clients ### Android + Web — one React + TypeScript codebase Capacitor wraps it for Android; the same build is the PWA (`CLAUDE.md` has the reasoning for React over Kotlin, and PWA over Electron). - **The local SQLite database is the source of truth.** The UI reads from it and never from the network. No mirrored copy of pins in a state store — that's `VALUES.md`'s "single source of truth" applied to runtime state, and it's why there's no Redux here. - **All database access lives behind one `pins` repository module** — a boundary, not an abstraction. Nothing clever, no interface with one implementation (§5: don't pre-abstract at zero). It exists so the risk below is contained to one file if it lands. > **The biggest technical risk in this design is SQLite in the browser.** On Android it's a real > native SQLite and it's boring. On web it's `@capacitor-community/sqlite`'s wasm path persisting > into IndexedDB — a fiddlier, less-travelled road (worker setup, OPFS/COOP-COEP headers, storage > eviction). It may just work; it may eat an evening and still be flaky. > > **Prove it on day one, before building anything on top of it.** Write pins, reload, confirm they > survive. If it fights back, the fallback is a thin remote adapter for web only — web is online > almost always, and Android is where offline capture actually matters. That's a worse product but a > perfectly shippable one, and confining data access to the repository module is what keeps that > retreat cheap. **Don't discover this in week six.** ### The share target — the feature the habit depends on An `intent-filter` in `AndroidManifest.xml` for `ACTION_SEND` (`text/plain`, `image/*`), plus native code to receive the intent and hand it to the web layer. **Stand this up first, before the app has a UI worth sharing to.** Everything else is a nice pin list; this is the reason Pins beats Messenger. Discovering it's awkward late is the failure mode that matters most, and it's the whole reason for §8.2's "never assume a platform primitive works." The community `send-intent` plugin is the obvious start. But `PLAYBOOK.md` §8.2 and §11 both say owning a small thing beats fighting a big one — and this is a small thing, on the critical path, in a language the owner writes. **Try the plugin; if it fights, write our own.** Roughly thirty lines of Kotlin, fully understood, forever. ### Wear OS — later, deliberately small Native Kotlin + Compose (Capacitor can't target Wear OS). Records a voice note, queues it locally, posts **directly to the server** over HTTP with its own token. Not via the Wear Data Layer API, which would make the watch depend on the phone app being installed and awake — a notoriously fiddly dependency for no gain. Direct-to-server makes the watch just another client with a token, the same shape as everything else. It captures and hands off. **It does not get a pin list, search, or tags.** The moment it grows those, it's a second full client in a second language, and the argument for React collapses. Voice notes imply transcription, which is an open question in `VALUES.md`, not a decision. The audio file is a blob like any other; an `audio` pin type is a small addition when it's real. ### Chrome extension — later, and a deliberate deviation The extension is a **thin, online-only client**: it POSTs a capture and shows a confirmation. No local database, no replication. That contradicts local-first, on purpose (`PLAYBOOK.md` §16.2 wants the exception recorded here). A browser extension is online by definition — it captures the page you're looking at, so if the network is down, so is the reason to use it. Replicating a whole archive into an extension's storage would be real work protecting against a case that can't happen. It needs its own tiny capture endpoint, which is the one crack in "no CRUD endpoints"; keep it narrow and keep it honest. --- ## Server Laravel + MySQL on Apache, self-hosted (`README.md` for hosts). - **Migrations are additive and idempotent**, safe to re-run every deploy (`PLAYBOOK.md` §10.2). Sharper than usual here: prod is the only environment and — until the nightly backup exists — the only copy of the data. A destructive migration has nothing to fall back to. - **The API is versioned (`/api/v1/`) from the first line.** Clients update on their own schedule, so an old app *will* eventually meet a new server. When the protocol has to break, the server rejects the old version explicitly and that ships as a **required** release (§2.1) — the version prefix is what makes that a decision instead of a mystery bug. - **The host is temporary; never bake it in.** The Pi and `pins.blommemix.dk` are where this starts, not where it stays (owner, 2026-07-17). So: **no client ever hardcodes the server URL** — it's configuration, per build, with one definition (§5). The same reasoning killed `dk.blommemix.pins` as an app ID: identifiers that outlive their host must not name it. **A host move is a required release** (§2.1): an installed app pointed at a dead hostname is a brick, and it can't be fixed server-side because the client can't reach the server to be told. If the move ever looks likely, ship the new address to clients *before* the old one goes away — a DNS alias kept alive during the transition is the cheap insurance. - **Match the Pi's PHP version.** Syntax-lint before every deploy (§4.2, and `CLAUDE.md` gotchas). - **Any admin surface is authenticated**, and every destructive maintenance action (purge tombstones, GC orphan blobs) is dry-run → confirm → verify (§6). --- ## Shared vocabulary Per `PLAYBOOK.md` §5.1 — reach for these words and these things, don't reinvent them. | Term | Means | | --- | --- | | **Pin** | One saved thing: text, link, or image. The only noun that matters. | | **Tag** | A flat, lowercase string on a pin. No hierarchy, ever. Optional, always. | | **Blob** | Immutable bytes, identified by SHA-256. Images now; audio later. | | **Device** | One installed client with its own token and its own cursor. | | **Cursor** | The highest `sequence` a device has seen. Its entire sync state. | | **Sequence** | Server-assigned monotonic counter. The ordering authority. | | **Tombstone** | `deleted_at` / `removed_at`. Deletion that can propagate. | | **Dirty** | A local row not yet acknowledged by the server. The queue, without a queue. | ## Recorded deviations Deliberate, with reasons (`PLAYBOOK.md` §16.2). Not drift. - **Chrome extension is online-only**, not local-first — see above. The one endpoint that creates a pin directly. - **Prod-only environment** — recorded in `CLAUDE.md`, conditional on backups existing. ## Open questions Not decided. Better here than silently assumed. - **Image size ceiling.** Cap uploads, re-encode, or take what the camera gives? A Pi's disk is finite and 50 MP photos are not small. Leaning: cap + re-encode on device before upload. - **Trash retention** before a purge is offered. 30 days is the obvious default; nothing has earned it yet. - **Tag rename / merge** — not in v1. If it lands, revisit the "tags as strings" decision first. - **Encryption at rest.** The Pi is the owner's, behind his own door, and the offsite backup is going to someone else's disk. That backup is the part worth thinking about, not the Pi. - **Web SQLite viability** — the day-one spike above. This is the one that could reshape the design. - **Audio pin type** for Wear, and whether transcription happens at all (`VALUES.md`).