Database

Database

Felines uses Supabase (PostgreSQL) with Row Level Security enabled on all 26 tables. No table has an "allow all" policy — every policy scopes access to a specific role, a specific relationship (auth.uid() = created_by, a caretaker link, etc.), or an explicit column grant.

---

Tables

Table	Purpose	Public read
colonies	Colony records with dual location (exact + two blur levels)	Blurred columns only
cats	Individual named cats per colony	Yes
cat_notes	Observations about a specific cat — immutable once posted	Yes
caretakers	User-colony links, no hierarchy	Yes
reports	9 report types, anonymous or authenticated	Authenticated only
report_confirmations	Atomic confirmations, prevents duplicates	No
timeline_events	Collaborative colony timeline	Yes
feedings	Feeding and water check-ins	Yes
knowledge_progress	Reading progress per user	Own user only
flags	Content/pin flagging (false-pin reasons feed the ban system)	No
colony_followers	Colony followers (infrastructure for future notify_followers use)	Yes
neutering_requests	Castration requests with urgency and status	Yes
help_requests	General help requests (food, foster home, transport, medication)	Yes
colony_verifications	Community verifications (3 needed, never unverifies)	Yes
colony_stories	Community stories wall — logged-in visibility only	Authenticated only
story_reactions	Reactions to stories	Yes
resource_posts	Resource exchange posts (30-day expiry)	Yes
resource_post_interests	Interest tracking on resource posts	Own user only
community_contacts	Animal protection contact directory (14 real Natal/RN contacts)	Yes
profiles	User profiles — public fields + protected streak/ban columns	Public fields only
notifications	Per-user notification inbox, 10 distinct types	Own user only
action_thanks	Thanks for a specific timeline action	Yes
thanks	Generic caretaker-level thanks, feeds the 🙏 badge	Yes
caretaker_certifications	"Cuidador Preparado" certification — one per user	Yes
care_reminders	Recurring care reminders (feeding/water/health/shelter/custom)	Own caretaker only
suggested_colonies	Auto-detected colony suggestions from sighting clusters	Yes

---

Location Privacy Design

The colonies table stores coordinates at three precision levels:

latitude                  -- exact, RLS protected
longitude                 -- exact, RLS protected
latitude_blurred          -- ~500m offset, public (anon)
longitude_blurred         -- ~500m offset, public (anon)
latitude_blurred_near     -- ~100m offset, authenticated only
longitude_blurred_near    -- ~100m offset, authenticated only

Why: Cats have been poisoned after exact locations were shared online. This is a physical safety decision, not just data privacy — see Security for the full reasoning.

Access levels:

• Anonymous: latitude_blurred/longitude_blurred (~500m offset)
• Authenticated, not a caretaker: latitude_blurred_near/longitude_blurred_near (~100m offset)
• Linked caretaker or creator: exact via get_colony_exact_location() RPC only

The identical column-vs-function pattern is reused for reports.latitude/longitude, since a report's exact location could otherwise leak a colony's near-exact position.

---

RPCs (Secure Functions)

All critical RPCs use SECURITY DEFINER and were individually reviewed for two failure modes: accepting anonymous calls they shouldn't, and trusting caller-supplied input that reaches somewhere another user will see it. Full parameter signatures are in API Reference.

RPC	Purpose	Protection
get_colony_exact_location	Exact coordinates for caretakers	Re-validates caretaker/creator link server-side, every call
confirm_report	Report confirmation	Atomic, blocks self-confirmation and duplicate confirmation
mark_cat_seen_today	Updates a cat's last-seen timestamp	Authenticated only
thank_action	Thanks a specific timeline action	Blocks self-thanking
record_daily_visit / record_care_streak	Streak tracking	Idempotent per day; anon access re-revoked after a live-tested drift (migration 0075)
notify_caretakers / notify_nearby_caretakers	Caretaker notifications	Fixed server-side message template, no caller-supplied free text
is_user_banned	Ban-status check used inside INSERT policies	SECURITY DEFINER, no public grant needed on the underlying columns
handle_false_pin_threshold	Trigger function on flags insert	Removes a colony after 3 false-pin flags, escalates the creator's ban
check_colony_verification	Community verification	Blocks self-verification by creator/caretaker
earn_caretaker_certification	"Cuidador Preparado" badge	ON CONFLICT DO NOTHING — one-time, cannot be re-earned

---

Migrations Overview

86 migrations total (0001 to 0086), applied in order.

Range	Content
0001–0039	Initial schema, RLS policies, RPCs, storage
0040–0043	Robustness features (streak, health index, verification)
0044–0045	Fix: RPCs accepting anon key (critical security)
0046–0064	Features: stories, resources, contacts, glossary, help requests
0065	Fix: notification RPCs open to spam/phishing injection
0066	Fix: streaks publicly readable
0067	Fix: no field size limits in database
0068	Fix: thank_action without self-block
0069	Fix: knowledge_progress without unique constraint
0070	Fix: flags without deduplication
0071	Seed: 14 real contacts in Natal/RN
0072–0073	Feature: contacts edit policy, resource exchange + public contact
0074	Feature: care reminders
0075	Fix: record_daily_visit anon grant drift, re-revoked
0076	One-time test-data cleanup (pre-submission)
0077	Fix: colony stories restricted to logged-in visibility
0078	Fix: notify_caretakers rate-limited at the database layer (circuit breaker)
0079	Fix: coordinate range CHECK constraints on colonies/reports
0080	Fix: profiles.display_name length constraint
0081	Feature: per-notification dismiss (delete-own policy)
0082	Feature: 3-false-pin-flag ban system (colony removal + 1-month/permanent ban)
0083	One-time full data wipe, preserving login accounts only
0084	Fix: missing select(removed_at) grant on colonies was silently hiding every colony from the map
0085	Fix: notify_caretakers/notify_nearby_caretakers ignored the site's language toggle, always notifying in Portuguese
0086	Fix: drops the stale pre-0085 3-parameter overloads of notify_caretakers/notify_nearby_caretakers, which left the RPCs ambiguous for some call shapes

To apply migrations, run each .sql file in the Supabase SQL Editor in numerical order. A few files near the end are one-time maintenance scripts rather than schema changes — each says so in its own header comment.