SCDossierRepServer

Server-side infrastructure for the SC Dossier Community Reputation System.

This project contains the Supabase database schema, Row Level Security (RLS) policies, and Edge Functions that power the anonymous player reputation system in the SC Dossier desktop app.

---

Architecture Overview

SCDossierRepServer/
├── supabase/
│   ├── config.toml                      ← Supabase CLI project config
│   ├── migrations/
│   │   └── 001_initial_schema.sql       ← All 4 tables + RLS policies
│   └── functions/
│       ├── submit-report/
│       │   └── index.ts                 ← Report validation + aggregation
│       ├── check-rate-limit/
│       │   └── index.ts                 ← Server-side rate limit check
│       └── keep-alive/
│           └── index.ts                 ← Ping to prevent free-tier pause
└── scripts/
    └── seed_tags.sql                    ← One-time tag definitions seed

The only connection between this project and the SC Dossier desktop app (SCDossier) is the Supabase HTTP API. No code from this repo is ever imported by the Python client.

---

Self-Hoster Setup Guide

Prerequisites

• A free Supabase account
• Supabase CLI installed (npm install -g supabase or via brew)
• Deno installed (for Edge Function development/testing)

Step 1 — Create a Supabase Project

1. Go to https://supabase.com/dashboard
2. Click New Project
3. Choose a name (e.g., scdossier-rep), set a strong database password, select your region
4. Wait for the project to initialize (~1 minute)

Step 2 — Link the Supabase CLI

supabase login
supabase link --project-ref <your-project-ref>

Your project ref is visible in the Supabase dashboard URL: https://supabase.com/dashboard/project/<project-ref>

Step 3 — Apply the Database Migration

supabase db push

This runs supabase/migrations/001_initial_schema.sql against your Supabase project. It creates all 4 tables with RLS policies and indexes.

Verify in the Supabase Table Editor that the following tables exist:

• players
• reputation_scores
• interaction_reports
• rate_limits

Step 4 — Seed the Tag Definitions (Optional)

The tag-to-category mapping is embedded directly in the Edge Function source. The seed_tags.sql is provided as a human-readable reference and optional lookup table.

If you want the lookup table in your DB:

# Via Supabase SQL Editor (dashboard) or psql
psql -h db.<project-ref>.supabase.co -U postgres -d postgres -f scripts/seed_tags.sql

Step 5 — Set the App Token Secret

The submit-report Edge Function validates a shared secret (X-SCD-App-Token header) to provide a soft barrier against casual abuse.

In the Supabase dashboard:

1. Go to Settings → Edge Functions
2. Add a new secret: APP_TOKEN = <your-chosen-secret-string>

This value must also be set in SCDossier/src/app/constants.py as REP_APP_TOKEN.

Step 6 — Deploy the Edge Functions

supabase functions deploy submit-report
supabase functions deploy check-rate-limit
supabase functions deploy keep-alive

Verify all three functions appear in the Supabase Edge Functions dashboard with status "Active".

Step 7 — Get Your API Credentials

In the Supabase dashboard, go to Settings → API:

• Project URL: https://<project-ref>.supabase.co
• anon public key: starts with eyJ...

Copy both values into SCDossier/src/app/constants.py:

REP_SUPABASE_URL = "https://<project-ref>.supabase.co"
REP_ANON_KEY = "eyJ..."       # anon/public key
REP_APP_TOKEN = "<your-secret>"  # the APP_TOKEN you set in Step 5

Step 8 — Test the Setup

Test keep-alive:

curl https://<project-ref>.supabase.co/functions/v1/keep-alive
# Expected: {"status":"alive"}

Test submit-report:

curl -X POST https://<project-ref>.supabase.co/functions/v1/submit-report \
  -H "Content-Type: application/json" \
  -H "X-SCD-App-Token: <your-secret>" \
  -d '{"handle":"testplayer","tags":["killed_me"],"ip_hash":"abc123deadbeef"}'
# Expected: {"dangerous":{"score":1,"report_count":1},"trustworthy":{"score":0,"report_count":0},...}

Test check-rate-limit:

curl -X POST https://<project-ref>.supabase.co/functions/v1/check-rate-limit \
  -H "Content-Type: application/json" \
  -H "X-SCD-App-Token: <your-secret>" \
  -d '{"handle":"testplayer","ip_hash":"abc123deadbeef"}'
# Expected: {"allowed":true,"reports_used":1,"reports_remaining":1,...}

---

Security Design

Row Level Security (RLS)

Table	Anonymous SELECT	INSERT/UPDATE/DELETE
players	✅ Allowed	❌ Edge Function only
reputation_scores	✅ Allowed	❌ Edge Function only
interaction_reports	❌ Disabled	❌ Edge Function only
rate_limits	❌ Disabled	❌ Edge Function only

The anon key (embedded in the desktop app) can only read players and reputation_scores. All writes go through the Edge Function using the service_role key server-side.

IP Hashing

The client's public IP is SHA-256 hashed on the client machine before being sent to Supabase. Raw IPs are never transmitted or stored. The hash is sufficient for rate limiting (same IP → same hash).

Rate Limiting

• Limit: 2 interaction reports per unique IP hash per player handle per 30-day rolling window
• Two-layer enforcement: app-side check (UI) + server-side check (Edge Function)
• 24-hour cooldown: One report per player per 24 hours (enforced server-side)

App Authenticity Header

All write requests include X-SCD-App-Token: <token>. This is a soft barrier — not cryptographically secure — that prevents casual abuse from raw HTTP clients.

---

Keeping the Free Tier Active

Supabase free-tier projects pause after 7 days of inactivity. The SC Dossier desktop app automatically pings the keep-alive Edge Function at startup (when reputation is enabled). This is sufficient to prevent pausing as long as any user runs the app within a 7-day window.

For guaranteed uptime, add a GitHub Actions cron job:

# .github/workflows/keepalive.yml
name: Supabase Keep-Alive
on:
  schedule:
    - cron: '0 12 */5 * *'  # Every 5 days at noon UTC
jobs:
  ping:
    runs-on: ubuntu-latest
    steps:
      - run: curl ${{ secrets.SUPABASE_KEEPALIVE_URL }}

---

Database Schema Reference

See supabase/migrations/001_initial_schema.sql for the full schema. Tables:

• players — canonical player records (handle, created_at, last_updated, total_reports)
• reputation_scores — aggregated per-category scores (score, report_count per category per player)
• interaction_reports — individual submissions (tags array, ip_hash, submitted_at)
• rate_limits — rolling 30-day window tracking (ip_hash, player_handle, report_count, window_start)

Tag Definitions Reference

Tag ID	Label	Category	Points
killed_me	They Killed Me	dangerous	1
killed_us	They Killed Us All	dangerous	2
ambushed	Set an Ambush / Trap	dangerous	1
griefer	Griefed / Harassed Me	dangerous	1
scammed	Scammed Me	shady	2
lied	Lied / Deceived Me	shady	1
manipulated	Manipulated / Lured Me	shady	1
pirate_act	Acted Like a Pirate	pirate	1
pirate_confirmed	Confirmed Pirate	pirate	2
elusive	Hard to Track / Elusive	elusive	1
escaped	Escaped Every Time	elusive	1
trustworthy	Trustworthy / Reliable	trustworthy	2
helpful	Helped Me Out	trustworthy	1
fair_fight	Honorable Fighter	trustworthy	1
friendly	Friendly Encounter	trustworthy	1