Skip to content

Database

Jump to bottom
Luara Oliveira edited this page Jul 3, 2026 · 4 revisions

Database

Felines uses Supabase (PostgreSQL) with Row Level Security enabled on all 17 tables. No table has an "allow all" policy.

Tables

Table Purpose Public read
colonies Colony records with dual location (exact + blurred) Blurred columns only
cats Individual named cats per colony Yes
caretakers User-colony links 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 flagging No
colony_followers Colony followers Yes
neutering_requests Neutering requests with urgency Yes
colony_verifications Community verifications (3 needed) Yes
stories Community stories wall Yes
resources Resource exchange posts Yes
resource_post_interests Interest tracking on posts Own user only
community_contacts Animal protection contact directory Yes
profiles User profiles with streak and public contact Public fields only

Location Privacy Design

The colonies table stores coordinates twice:

latitude          DECIMAL  -- exact, RLS protected
longitude         DECIMAL  -- exact, RLS protected
latitude_blurred  DECIMAL  -- ~600m offset, public
longitude_blurred DECIMAL  -- ~600m offset, public

Why: Cats have been poisoned after exact locations were shared online. This is a physical safety decision, not just data privacy.

Access levels:

  • Anonymous: blurred (~600m offset)
  • Authenticated: less blurred (~110m offset)
  • Linked caretaker: exact via RPC only (server-side validation)

RPCs (Secure Functions)

All critical RPCs use SECURITY DEFINER and reject the anon key. This was found to be missing and fixed in migrations 0044/0045 after live testing.

get_colony_exact_location

Returns exact coordinates only if the caller is a linked caretaker of that colony.

get_colony_exact_location(p_colony_id UUID)
RETURNS TABLE(latitude DECIMAL, longitude DECIMAL)

Access: Authenticated + linked caretaker only
Rejects: Anon key, authenticated non-caretakers

confirm_report

Atomically increments report confirmations. Blocks self-confirmation and duplicate confirmation.

confirm_report(p_report_id UUID)
RETURNS BOOLEAN

Access: Authenticated only
Blocks: Self-confirmation, duplicate from same user

mark_cat_seen

Updates the last_seen timestamp for a specific cat.

mark_cat_seen(p_cat_id UUID)
RETURNS BOOLEAN

Access: Authenticated only

thank_action

Records a thank-you between caretakers. Blocks self-thanking.

thank_action(p_target_user_id UUID, p_colony_id UUID)
RETURNS BOOLEAN

Access: Authenticated only
Blocks: Self-thanking

record_daily_visit

Updates caretaker streak. Idempotent — safe to call multiple times in the same day.

record_daily_visit(p_colony_id UUID)
RETURNS BOOLEAN

Access: Authenticated only
Behavior: Idempotent per day

notify_caretakers

Sends notification to caretakers of a colony. Text is fixed server-side — no user input reaches the notification content (prevents spam/phishing injection).

notify_caretakers(p_colony_id UUID, p_event_type TEXT)
RETURNS VOID

Access: Authenticated only
Security: Fixed message templates, no free-text input

Migrations Overview

75 migrations total (0001 to 0075), 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
0065 Fix: notification RPCs open to spam 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 Feature: contact category filter + edit/delete
0073 Feature: resource interests + public contact on profile
0074–0075 Performance and final pre-submission fixes

To apply migrations, run each .sql file in the Supabase SQL Editor in numerical order.

Clone this wiki locally