PostgreSQL tables, key fields, and the row‑level security policies that enforce who can read and write what. RLS is the primary authorisation surface — it must be specified, not improvised.

7.1 — Tables

• profiles
• artworks
• artwork_categories
• artwork_tags
• artwork_tag_links
• artists
• content_packs
• content_pack_artworks
• palette_translations
• user_artwork_progress
• artwork_downloads
• entitlements
• purchase_receipts
• moderation_reviews

7.2 — profiles

One row per authenticated user. Created automatically on first sign‑in.

Field	Type	Notes
id	uuid	= auth.uid(). Row exists for every user, including anonymous.
is_anonymous	boolean	True until the user signs in / signs up. See §7.8.
role	text	Enum: user (default) · content_reviewer · admin. Set only via direct DB access — never via a client API.
display_name	text	Optional.
locale	text	BCP‑47.
child_mode	boolean	Disables behavioural analytics and ads.
audience	text	self · child · both. Captured at onboarding.
cultural_themes	text[]	Multi‑select from §9.7 taxonomy. Drives library ranking.
accessibility	jsonb	always_show_numbers, reduce_motion, pattern_fills.
onboarding_completed_at	timestamptz	Null until the founder’s‑note screen is reached.
created_at	timestamptz

7.3 — artworks

Field	Type	Notes
id, slug, title, description	—	Slug is the storage key.
category_id, artist_id	uuid
difficulty	text	easy · medium · hard.
age_rating	text	e.g. 4+, 9+.
region_count, palette_size, estimated_minutes	int	Performance hints.
thumbnail_url, preview_url, manifest_path	text	Storage paths.
storage_bucket, version, checksum	—	Asset identity.
is_premium, is_published, published_at	—	Catalogue gating.
cultural_review_status	text	pending · approved · changes_requested.
created_at, updated_at	—

7.4 — user_artwork_progress

One row per (user_id, artwork_id). completed_regions is a bitset — one bit per region indexed by position — stored as bytea. JSON storage is rejected from day one for size reasons.

Field	Type	Notes
id, user_id, artwork_id	—
artwork_version	int	Version this progress applies to.
completed_regions	bytea	Bitset. ceil(regionCount / 8) bytes.
completed_count	int	Denormalised; recomputable.
completion_percent	numeric	0–100.
last_selected_colour_id	smallint
started_at, last_played_at, completed_at, updated_at	timestamptz

Conflict resolution

Completion is monotonic — a region cannot become uncompleted. On sync, the server merges by computing the union of the local and remote bitsets. This is simpler than last‑write‑wins, requires no clock comparison, and is correct for the domain.

7.5 — entitlements

Field	Type	Notes
id, user_id, entitlement_type	—	subscription · pack.
product_id, platform	—	apple · google.
active, expires_at	—
family_shared	boolean	True when granted via Apple Family Sharing.
granting_user_id	uuid	Original purchaser when family‑shared.
created_at, updated_at	—

7.6 — purchase_receipts

Append‑only audit log of every receipt validation, including renewal notifications from Apple ASSN v2 and Google RTDN.

7.7 — Row‑level security policies

RLS is enabled on every user‑scoped table. The default deny is the safety net; explicit policies grant the narrowest possible access. The role claim is read from auth.jwt() ->> 'role', which InsForge populates from profiles.role at sign‑in.

-- artworks: anyone (including anonymous) can read published rows;
-- reviewers and admins can also read drafts.
ALTER TABLE artworks ENABLE ROW LEVEL SECURITY;
CREATE POLICY artworks_read_published ON artworks
  FOR SELECT USING (is_published = true);
CREATE POLICY artworks_read_drafts ON artworks
  FOR SELECT USING (auth.jwt() ->> 'role' IN ('admin', 'content_reviewer'));
CREATE POLICY artworks_write_admin ON artworks
  FOR ALL USING (auth.jwt() ->> 'role' = 'admin')
  WITH CHECK (auth.jwt() ->> 'role' = 'admin');

-- user_artwork_progress: users see and write only their own.
-- Anonymous users are full first-class citizens here.
ALTER TABLE user_artwork_progress ENABLE ROW LEVEL SECURITY;
CREATE POLICY progress_own_read ON user_artwork_progress
  FOR SELECT USING (auth.uid() = user_id);
CREATE POLICY progress_own_write ON user_artwork_progress
  FOR INSERT WITH CHECK (auth.uid() = user_id);
CREATE POLICY progress_own_update ON user_artwork_progress
  FOR UPDATE USING (auth.uid() = user_id);

-- entitlements: read-own only; writes go through validate-purchase function.
-- Anonymous users have no rows here by construction.
ALTER TABLE entitlements ENABLE ROW LEVEL SECURITY;
CREATE POLICY entitlements_read_own ON entitlements
  FOR SELECT USING (auth.uid() = user_id);

-- moderation_reviews: reviewers and admins only.
ALTER TABLE moderation_reviews ENABLE ROW LEVEL SECURITY;
CREATE POLICY reviews_rw_reviewer ON moderation_reviews
  FOR ALL USING (auth.jwt() ->> 'role' IN ('admin', 'content_reviewer'))
  WITH CHECK (auth.jwt() ->> 'role' IN ('admin', 'content_reviewer'));

-- profiles: users see and update their own; role and is_anonymous
-- cannot be modified through this path.
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
CREATE POLICY profiles_own_read ON profiles
  FOR SELECT USING (auth.uid() = id);
CREATE POLICY profiles_own_update ON profiles
  FOR UPDATE USING (auth.uid() = id)
  WITH CHECK (
    auth.uid() = id
    AND role         = (SELECT role         FROM profiles WHERE id = auth.uid())
    AND is_anonymous = (SELECT is_anonymous FROM profiles WHERE id = auth.uid())
  );

Write paths that need to bypass RLS — receipt validation, admin publishing, anonymous→authenticated promotion — run inside InsForge functions authenticated with the service role. Service‑role keys are never embedded in the client.

7.8 — Role & identity model

Every user on Ochre & Soul has exactly one profiles row and exactly one of the role values below. The split between anonymous and authenticated users is orthogonal to the role.

Roles

Role	Default?	Can do	Set by
user	Yes	Read published artworks, manage own progress and entitlements, purchase packs, complete onboarding.	Implicit on profile creation.
content_reviewer	No	All of user plus: read draft artworks, read/write moderation_reviews, update cultural_review_status on draft artworks via the admin function surface.	Direct SQL by ops only. No self‑service.
admin	No	All of content_reviewer plus: full admin function surface (§8.5), unpublish, asset upload URLs, role assignment via SQL.	Direct SQL by ops only. No client API exposes role assignment.

Anonymous vs authenticated

• Every install calls auth.signInAnonymously() on first launch. A profiles row is created with is_anonymous = true and role = 'user'.
• Anonymous users can: browse the library, download free artworks, save and sync progress, complete onboarding, customise preferences.
• Anonymous users cannot: purchase packs, hold entitlements, restore purchases, or have their progress survive a reinstall. The paywall is the only place that prompts sign‑up.
• On sign‑up or sign‑in, the promote-anonymous function flips is_anonymous = false on the existing row. The auth.uid() does not change, so all progress, preferences, and the local database stay intact with no client‑side merge required.
• If the user signs into an existing authenticated account from a device that has anonymous progress, the function unions the bitsets across both rows (per §7.4) and deletes the orphan anonymous row.

Trust boundaries

• The client never sees the service‑role key. Sensitive operations (validate purchase, admin publish, promote anonymous, role assignment) are server functions.
• Admin function entry points check auth.jwt() ->> 'role' = 'admin' as their first action; reviewer functions accept either admin or content_reviewer.
• Role escalation is a manual ops process logged separately; the application surface offers no path to it.