Database guide in plain English
A table is a list of records, like a spreadsheet. An ID is a permanent identifier. A reference connects a record to another table. Dates are stored as timestamps. JSON means a structured bundle of related settings or content.
| Table | Purpose | Columns and what they mean |
|---|---|---|
| users | People with accounts | id: person’s permanent ID; email: unique login email; password_hash: protected password proof (never the plain password); created_at: signup time. |
| sessions | Devices that are signed in | token_hash: protected session identifier; user_id: signed-in person; expires_at: when sign-in expires; created_at: when the session began. |
| boards | Owned walls | id: permanent wall ID; owner_id: person who owns and edits it; share_token: permanent secret-looking part of the share URL; name: wall name; visibility: private, shared, or public; settings: background, location, window position, and time of day; playlist: music tracks and selected track; revision: save version that prevents older devices overwriting newer edits; created_at: creation time; updated_at: latest save time. |
| board_items | Things placed on a wall | board_id: containing wall; id: object ID; type: picture, text, lamp, plant, folder, etc.; x / y: horizontal and vertical position; width / height: size; z_index: front-to-back order; content: text, appearance, picture crop/fit, light behavior, or folder pages; asset_id: uploaded picture used by the object, if any. |
| assets | Uploaded pictures | id: image ID; board_id: wall that owns the image; uploaded_by: uploader; mime: image format (validated WebP); bytes: actual image file; width / height: picture dimensions; created_at: upload time. |
| board_members | Reserved for future sharing roles | board_id: wall; user_id: person; role: viewer or editor. Not used to grant access now. Editing is owner-only. |
| board_votes | Explore upvotes | board_id: public wall; user_id: voter; created_at: when cast. One vote per user per wall. |
| rate_limits | Abuse protection | key: protected account/IP/action identifier; count: recent attempts; resets_at: when the limit resets. |
| migrations / schema_migrations | Database upgrade history | name: applied upgrade filename; applied_at: when it ran. Postgres uses schema_migrations. |
Per-object lifecycles (APIs, actors, cascades): DATA_FLOWS.md.
How records connect
One person owns many walls. A wall contains many objects and uploaded pictures. An object can reference a picture. Sessions belong to people. Deleting a wall removes its objects and images together. Making a wall private blocks visitors from both the wall and its uploaded pictures.
There is no separate NFC table: the sticker stores the wall’s permanent website URL. Changing a wall’s content or visibility does not change that URL.
The authoritative schema is server/migrations/001_initial.sql; the migrations bookkeeping table is created by server/database.js.
Supabase Auth migration (002)
users.supabase_id links a verified Supabase identity to the existing application user, preserving owned board URLs. sessions.provider distinguishes native Supabase from the isolated local-test adapter. access_token, refresh_token, and token_expires_at keep provider sessions server-side; recovery marks the recovery UI state. These are Supabase session credentials, not custom password-reset tokens. Supabase generates and verifies all reset tokens and sends the email. Protect database files and backups as credential-bearing data. Password changes invalidate all application sessions.