Supabase (PostgreSQL). All tables use user_id TEXT (device UUID from AsyncStorage). No auth layer. Migrations live in src/data/migrations/.
One row per walk session. Created on walk start, updated on end.
| Column | Type | Notes |
|---|---|---|
id | UUID (PK) | gen_random_uuid() |
user_id | TEXT | Device UUID |
started_at | TIMESTAMPTZ | Walk start |
ended_at | TIMESTAMPTZ | Walk end (null if active) |
distance_km | NUMERIC | Total distance walked |
duration_min | INTEGER | Duration in minutes |
route_polyline | TEXT | Encoded polyline of walk path |
quest_id | UUID | FK to user_quests (nullable) |
Every snap photo taken during a walk. AI classifies themes for collection badge progress.
| Column | Type | Notes |
|---|---|---|
id | UUID (PK) | gen_random_uuid() |
user_id | TEXT | Device UUID |
walk_id | UUID | FK to walks |
photo_url | TEXT | Supabase Storage URL |
latitude | DOUBLE PRECISION | Snap location |
longitude | DOUBLE PRECISION | Snap location |
created_at | TIMESTAMPTZ | Snap timestamp |
is_public | BOOLEAN | Published to public map |
display_name | TEXT | User display name (for public map) |
caption | TEXT | User caption |
save_count | INTEGER | Number of bookmarks |
published_at | TIMESTAMPTZ | When published to map |
classified_themes | TEXT[] | AI-classified photo themes for collection badges (e.g. cat, door, tile) |
Curated quest definitions. Three types: wander, collect, visit.
| Column | Type | Notes |
|---|---|---|
id | UUID (PK) | gen_random_uuid() |
title | TEXT | Quest title |
quest_type | TEXT | wander / collect / visit |
description | TEXT | Long description (3-5 sentences) |
short_description | TEXT | One-liner for card preview |
intention | TEXT | Quest intention/prompt (single line shown on card) |
neighbourhood | TEXT | Target neighbourhood |
tags | TEXT[] | Content tags (e.g. architecture, street art) |
estimated_duration_min | INTEGER | Estimated walk time |
estimated_distance_km | NUMERIC | Estimated distance |
locations | JSONB | Array of {name, lat, lng, neighbourhood} for visit quests |
target_count | INTEGER | Snap target for collect quests (e.g. 5) |
verification_method | TEXT | snap / proximity / none |
detection_themes | TEXT[] | Themes for collect quest AI classification (e.g. cat, tile) |
day_types | TEXT[] | Weekday/weekend eligibility (e.g. {weekday, weekend}) |
ai_generated | BOOLEAN | True if LLM-generated (not curated) |
Quest instances accepted by users. Tracks progress and completion.
| Column | Type | Notes |
|---|---|---|
id | UUID (PK) | gen_random_uuid() |
user_id | TEXT | Device UUID |
quest_id | UUID | FK to quest_library (nullable for LLM quests) |
quest_data | JSONB | Full quest snapshot at accept time |
status | TEXT | active / completed / abandoned |
progress | JSONB | Progress state (snaps counted, locations visited) |
detection_themes | TEXT[] | Copied from quest on accept (for ambient SNAP matching) |
accepted_at | TIMESTAMPTZ | When quest was accepted |
completed_at | TIMESTAMPTZ | When quest was completed |
One personalised quest per user per day, generated by cron from curated POIs or live Gemini search.
| Column | Type | Notes |
|---|---|---|
id | UUID (PK) | gen_random_uuid() |
user_id | TEXT | Device UUID |
active_date | DATE | Quest date |
quest_data | JSONB | Full quest JSON |
route_chosen | TEXT | curated / live |
reasoning | TEXT | LLM reasoning for quest selection |
created_at | TIMESTAMPTZ |
Bookmarked public map snaps.
| Column | Type | Notes |
|---|---|---|
id | UUID (PK) | gen_random_uuid() |
user_id | TEXT | Device UUID |
photo_id | UUID | FK to photo_snaps (CASCADE) |
saved_at | TIMESTAMPTZ |
Shareable collection map links. Each row generates a public token URL for a user's collection quest photos.
| Column | Type | Notes |
|---|---|---|
id | UUID (PK) | gen_random_uuid() |
token | UUID (UNIQUE) | Public share token |
user_id | TEXT | Device UUID |
quest_title | TEXT | Quest title for display |
detection_theme | TEXT | Theme filter for photos |
created_at | TIMESTAMPTZ |
Taste memory dimensions. Updated by walk behaviour and manual edits.
| Column | Type | Notes |
|---|---|---|
user_id | TEXT (PK) | Device UUID |
dimensions | JSONB | Taste dimension scores (architecture, nature, food, etc.) |
seeded_from | TEXT | Onboarding source |
updated_at | TIMESTAMPTZ |
Expo push tokens per device.
| Column | Type | Notes |
|---|---|---|
id | UUID (PK) | gen_random_uuid() |
user_id | TEXT | Device UUID |
push_token | TEXT | Expo push token |
platform | TEXT | ios / android |
created_at | TIMESTAMPTZ |
Per-user notification settings with quiet hours.
| Column | Type | Notes |
|---|---|---|
user_id | TEXT (PK) | Device UUID |
enabled | BOOLEAN | Master toggle (default true) |
quiet_hours_start | INTEGER | Hour (0-23), default 22 |
quiet_hours_end | INTEGER | Hour (0-23), default 8 |
streak_nudges | BOOLEAN | Default true |
comeback_nudges | BOOLEAN | Default true |
weather_nudges | BOOLEAN | Default true |
event_nudges | BOOLEAN | Default true |
quest_nudges | BOOLEAN | Default true |
updated_at | TIMESTAMPTZ |
Record of every push notification sent.
| Column | Type | Notes |
|---|---|---|
id | UUID (PK) | gen_random_uuid() |
user_id | TEXT | Device UUID |
notification_type | TEXT | streak / comeback / weather / event / quest |
title | TEXT | Notification title |
body | TEXT | Notification body |
sent_at | TIMESTAMPTZ | |
opened_at | TIMESTAMPTZ | When user tapped notification |
Tracks daily app opens for streak and comeback detection.
| Column | Type | Notes |
|---|---|---|
user_id | TEXT | Composite PK with active_date |
active_date | DATE | Composite PK |
opened_at | TIMESTAMPTZ | First open of the day |