Data object flows
Per-entity create → read → update → delete behavior for data the app persists today. This complements:
- ARCHITECTURE.md — deploy and HTTP overview
- FRONTEND_FLOWS.md — screen-level UX contracts
- DATABASE_GUIDE.md — plain-English column glossary
- BACKEND_OPERATIONS.md — ops limits and backups
Authorization is enforced in Node (accounts.js, walls.js, uploads.js, discover.js), not Supabase RLS.
Legend: Actor = who can trigger the flow. API = HTTP entry points. Cascades follow foreign keys in server/migrations/.
---
Guest draft (browser only)
Not a database table. Stored in localStorage under keys managed by public/guest.js.
| Operation | Actor | Behavior |
|---|---|---|
| Create / update | Anonymous visitor | Edit wall in browser; draft JSON kept locally (digital-walls-guest-drafts-v1). |
| Read | Same device | Reload restores draft from localStorage. |
| Delete | User or app | Cleared after successful claim, or when draft is blank and discarded. |
| Claim → account | Signed-in owner | claimGuestDraft() creates a server board (POST /api/boards), uploads embedded images (POST /api/boards/:id/assets), then saves state (PUT /api/boards/:id). Intent stored in digital-walls-guest-claim-v1. |
See FRONTEND_FLOWS.md for retention during signup/login failure.
---
users
App account row. Password proof for production users lives in Supabase Auth; password_hash is empty when supabase_id is set.
| Operation | Actor | API / trigger | DB effect |
|---|---|---|---|
| Create | New signup | POST /api/signup → Supabase signup → establishProviderSession | INSERT users with new UUID, email, supabase_id, empty password_hash |
| Create (link) | Existing email, no supabase_id | Same flow after Supabase confirms | UPDATE users SET supabase_id=… on matching email |
| Create (tests only) | Local test server | POST /api/signup with ALLOW_LOCAL_AUTH_FOR_TESTS=1 | INSERT with scrypt password_hash, no supabase_id |
| Read | Session holder | GET /api/session, current() on other routes | SELECT id, email (via session + Supabase user match) |
| Update | — | No profile API | Email changes would require Supabase dashboard / future feature |
| Delete | — | Not exposed in UI | DELETE users cascades: sessions, boards (and their items, assets, votes), board_members, board_votes |
Rules: Supabase identity must have email_confirmed_at before app user is linked (providerAccount). Duplicate email with different supabase_id → 409. Account routes call requireDurable() → 503 on Vercel without Postgres.
---
sessions
Server-side login for the browser cookie. Stores hashed app session token and (for Supabase) provider tokens.
| Operation | Actor | API / trigger | DB effect |
|---|---|---|---|
| Create | After successful auth | Signup/login, POST /api/auth/confirm, POST /api/auth/session, recovery confirm | INSERT sessions with token_hash, 30-day expires_at, provider, optional Supabase tokens, recovery flag |
| Read | Any request with cookie | session(req) | SELECT by token_hash where not expired |
| Update | Automatic | Supabase access token near expiry in current() | UPDATE sessions refresh tokens on same row |
| Delete | User | POST /api/logout | DELETE row for current cookie; Supabase logout best-effort |
| Delete | Password change | PUT /api/password after recovery | DELETE FROM sessions WHERE user_id=? (all devices) |
| Delete | Invalid Supabase token | current() on 401/403 from Auth | DELETE current session row |
| Delete (tests) | Login | Local auth path | Expired rows purged; new session inserted |
Cookie: __Host-dw_session (production) or dw_session (dev), HttpOnly, SameSite=Lax, 30 days.
Read path for “who is logged in”: cookie → session row → (Supabase) refresh if needed → getUser → match users.id + supabase_id.
---
boards
Owned wall (board). Identified internally by id; shared by permanent share_token in URLs.
| Operation | Actor | API | DB effect |
|---|---|---|---|
| Create | Owner | POST /api/boards | INSERT boards — default name, private, revision 1, default settings/playlist JSON; new share_token |
| Read (list) | Owner | GET /api/boards | SELECT metadata for owner_id |
| Read (one) | Owner, or visitor with link | GET /api/boards/:idOrShareToken | Full serialize: settings, playlist, items; canEdit / mode from ownership |
| Update | Owner only | PUT /api/boards/:id | Transaction: revision check → replace all board_items → update board fields → revision+1; stale revision → 409 |
| Delete | Owner | DELETE /api/boards/:id | Transaction: delete items, delete board (assets and votes cascade via FK) |
Access matrix (`walls.access`):
| visibility | Owner | Anonymous / other user |
|---|---|---|
private | read + edit | no access (403) |
shared | read + edit | read via id or share token |
public | read + edit | read via id or share token; also listed on Explore |
Limits: 1000 boards per owner; create rate limit per user. share_token and board id are stable across renames and visibility changes.
Nested data (not separate tables): settings (wallConfig), playlist — updated only through board PUT, validated in validateState().
---
board_items
Canvas objects on a 1200×800 wall. No standalone CRUD API.
| Operation | Actor | API | DB effect |
|---|---|---|---|
| Create | Owner | Part of PUT /api/boards/:id | Rows inserted after full delete of prior items for that board |
| Read | Anyone who can read board | GET /api/boards/:id | SELECT ordered by z_index, id; merged into item JSON |
| Update | Owner | Same PUT | Full replace: DELETE all items for board, re-INSERT from payload |
| Delete | Owner | Omit from save payload on PUT | Row not re-inserted; orphaned assets GC’d (see assets) |
Rules: Each item’s asset_id must reference an assets row on the same board. Item content JSON holds type-specific fields (text, folder pages, playlist UI state, etc.). Types enforced by DB check constraint (includes living on migrated DBs).
---
assets
WebP image bytes stored in the database.
| Operation | Actor | API | DB effect |
|---|---|---|---|
| Create | Owner | POST /api/boards/:id/assets (raw body, ≤10 MB) | Sharp decode/resize → INSERT assets |
| Read | Board reader | GET /api/assets/:id | SELECT bytes; preceded by walls.access(board_id) |
| Update | — | No in-place update | Replace by new upload + item reference on save |
| Delete | Owner (indirect) | Board PUT GC, or board DELETE | GC: delete assets on board with no referencing board_items and created_at older than 1 hour; board delete cascades all assets |
Limits: 100 MB total WebP per board; 60 uploads/user/hour (rate limit). Private board images require owner session; shared/public allow read when board is readable.
---
board_votes
Explore upvotes — at most one row per (board_id, user_id).
| Operation | Actor | API | DB effect |
|---|---|---|---|
| Create | Signed-in user | POST /api/explore/:boardId/vote | INSERT if board visibility = public and not already voted |
| Read | Anyone | GET /api/explore | Aggregated vote_count and voted flag per public board |
| Update | — | N/A | — |
| Delete | Voter | DELETE /api/explore/:boardId/vote | DELETE row for user+board |
| Delete (cascade) | — | Owner deletes board or user deleted | FK cascade removes votes |
Rules: Voting on non-public boards → 404. Duplicate vote → 409. Explore list only includes visibility = 'public'.
---
board_members
Schema only for future sharing roles.
| Operation | Actor | API | DB effect |
|---|---|---|---|
| Any | — | None in app | No reads or writes from application code |
Editing remains owner-only. See BACKEND_OPERATIONS.md.
---
rate_limits
Ephemeral counters for abuse protection (not user-facing).
| Operation | Actor | Trigger | DB effect |
|---|---|---|---|
| Create / update | Server | Before auth, upload, vote, create, etc. | Upsert row: increment count, set resets_at; expired keys deleted in same transaction |
| Read | Server | After increment | Compare count to caller’s max |
| Delete | Server | Expiry sweep on each hit() | DELETE WHERE resets_at < now |
Keys are opaque strings chosen per action (e.g. auth:{ip}, upload:{userId}, vote:{userId}). Exceeding max → 429.
---
migrations / schema_migrations
Bookkeeping for applied SQL files — not business data.
| Store | Table | Written when |
|---|---|---|
| SQLite | migrations | Each file in server/migrations/*.sql applied at boot |
| Postgres | schema_migrations | Each file in server/migrations/postgres/*.sql inside advisory-locked transaction |
No API. See ARCHITECTURE.md §3.
---
End-to-end diagrams
Owner save (items + board metadata)
PUT /api/boards/:id { revision, visibility, state }
→ walls.access(edit)
→ revision match?
→ DELETE board_items (board_id)
→ INSERT board_items (each item, optional asset_id)
→ DELETE orphaned assets (>1h, unreferenced)
→ UPDATE boards (name, visibility, settings, playlist, revision++)Share link read
GET /?board=:share_token
→ GET /api/boards/:share_token
→ walls.access(read) — private blocks non-owner
→ GET /api/assets/:id for each referenced image
→ walls.access on asset.board_idPublic explore + vote
GET /api/explore?sort=votes|recent
→ SELECT boards WHERE visibility = 'public'
POST /api/explore/:boardId/vote (session required)
→ assert public
→ INSERT board_votes---
Coverage notes
| Topic | Documented here | Also see |
|---|---|---|
| Supabase email confirm / recovery | Session create paths only | SUPABASE_AUTH.md |
| Editor autosave / 409 UX | Board update | FRONTEND_FLOWS.md |
| Aspirational entities (NFC table, share_links, etc.) | Not implemented | DATA_MODEL.md |