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. The system supports 9 reputation categories and 49 interaction tags, protected by a 15-layer anti-abuse system with live countdown timers and detailed logging.

Architecture Overview

SCDossierRepServer/
├── supabase/
│   ├── config.toml                      ← Supabase CLI project config
│   ├── migrations/
│   │   ├── 001_initial_schema.sql       ← Core 4 tables + RLS policies
│   │   ├── 20260527000000_*.sql         ← Service role grants
│   │   ├── 20260629160707_*.sql         ← reporter_orgs column
│   │   ├── 20260629170000_*.sql         ← player_handle column
│   │   ├── 20260629200000_*.sql         ← Player disposition support
│   │   ├── 20260630100000_*.sql         ← Reporter reputation tracking
│   │   ├── 20260630101000_*.sql         ← Mutual report pair detection
│   │   ├── 20260630102000_*.sql         ← Score decay tracking
│   │   ├── 20260630103000_*.sql         ← Disposition state
│   │   ├── 20260630104000_*.sql         ← Org roster cache
│   │   ├── 20260630105000_*.sql         ← Daily backups
│   │   ├── 20260630106000_*.sql         ← Reporter handle column
│   │   ├── 20260630107000_*.sql         ← Reporter handle to reputation
│   │   ├── 20260630220000_*.sql         ← Performance composite indexes
│   │   └── 20260630220001_*.sql         ← RLS on new tables + constraints
│   └── functions/
│       ├── submit-report/
│       │   └── index.ts                 ← Report validation + aggregation
│       ├── check-rate-limit/
│       │   └── index.ts                 ← Server-side rate limit check
│       ├── register-handle/
│       │   └── index.ts                 ← Reporter handle registration
│       ├── score-decay/
│       │   └── index.ts                 ← Daily score decay for stale reports
│       ├── daily-backup/
│       │   └── index.ts                 ← Daily DB backup to FTP
│       └── 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.

Edge Functions

Function	Method	Purpose	Auth
`submit-report`	POST	Submit an interaction report with tags, update reputation scores, detect abuse patterns	`X-SCD-App-Token` header
`check-rate-limit`	POST	Check if a reporter has remaining report capacity for a given player	`X-SCD-App-Token` header
`register-handle`	POST	Register a reporter handle with their IP hash for reputation tracking	`X-SCD-App-Token` header
`score-decay`	GET/POST	Daily cron: decay stale reputation scores (2%/month after 6 months inactive)	Service role (pg_cron)
`daily-backup`	GET/POST	Daily cron: export all tables as gzipped JSON, upload to FTP	Service role (pg_cron)
`keep-alive`	GET	Lightweight ping to prevent Supabase free-tier project pause	None

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

Go to https://supabase.com/dashboard
Click New Project
Choose a name (e.g., scdossier-rep), set a strong database password, select your region
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 Migrations

supabase db push

This runs all migration files in supabase/migrations/ against your Supabase project. It creates all tables with RLS policies, indexes, and constraints.

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

players
reputation_scores
interaction_reports
rate_limits
reporter_reputation
mutual_report_pairs
score_decay_tracking
disposition_state
org_roster_cache
daily_backups

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 and check-rate-limit Edge Functions validate a shared secret (X-SCD-App-Token header) to provide a soft barrier against casual abuse.

In the Supabase dashboard:

Go to Settings → Edge Functions
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 — Set Optional Secrets (for daily-backup)

If you want automated FTP backups, add these secrets in Settings → Edge Functions:

Secret	Description
`FTP_HOST`	FTP server hostname (default: `ftpupload.net`)
`FTP_USER`	FTP username
`FTP_PASS`	FTP password
`FTP_PORT`	FTP port (default: `21`)
`FTP_PATH`	Remote directory path (default: `/scdossier-supabasedb-backups`)

Step 7 — Deploy the Edge Functions

supabase functions deploy submit-report
supabase functions deploy check-rate-limit
supabase functions deploy register-handle
supabase functions deploy score-decay
supabase functions deploy daily-backup
supabase functions deploy keep-alive

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

Step 8 — Set Up Cron Jobs (optional, for score-decay and daily-backup)

To run score-decay and daily-backup automatically, create pg_cron jobs via the Supabase SQL Editor:

-- Daily score decay at 03:00 UTC
SELECT cron.schedule('score-decay', '0 3 * * *', 
  $$ SELECT net.http_post(url := current_setting('app.settings.supabase_url') || '/functions/v1/score-decay', headers := jsonb_build_object('Authorization', 'Bearer ' || current_setting('app.settings.service_role_key'))) $$);

-- Daily backup at 03:30 UTC
SELECT cron.schedule('daily-backup', '30 3 * * *', 
  $$ SELECT net.http_post(url := current_setting('app.settings.supabase_url') || '/functions/v1/daily-backup', headers := jsonb_build_object('Authorization', 'Bearer ' || current_setting('app.settings.service_role_key'))) $$);

Step 9 — 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 10 — 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","orgs":["test-org"],"reporter_handle":"reporter1","activity_timestamp":"2026-06-30T12:00:00Z"}'
# 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
`reporter_reputation`	Disabled	Edge Function only
`mutual_report_pairs`	Disabled	Edge Function only
`score_decay_tracking`	Disabled	Edge Function only
`disposition_state`	Disabled	Edge Function only
`org_roster_cache`	Disabled	Edge Function only
`daily_backups`	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 Functions 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

30-day limit: 2 interaction reports per unique IP hash per player handle per 30-day rolling window
24-hour cooldown: One report per player per 24 hours per IP hash
IP velocity: Max 5 reports total per IP hash per hour
Org cooldown: Max 6 reporters from shared orgs per player per 24 hours
Two-layer enforcement: app-side check (UI) + server-side check (Edge Function)

Abuse Prevention

Mutual report detection: If player A reports player B and player B reports player A within 24 hours, the weight is reduced
Org mutual normalization: If 5+ org members report the same player, weight is reduced instead of blocking
Reporter reputation: First-time reporters start at 0.5 weight, increasing by 0.05 per report up to 1.0
Disposition notoriety: If a player accumulates 5+ hostile reports, friendly reports are temporarily blocked (14 days)

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, hostile_count, friendly_count)
reputation_scores — aggregated per-category scores (score, report_count per category per player)
interaction_reports — individual submissions (tags array, ip_hash, submitted_at, reporter_orgs, reporter_handle, score_weight, disposition)
rate_limits — rolling 30-day window tracking (ip_hash, player_handle, report_count, window_start)
reporter_reputation — per-reporter trust weight tracking (ip_hash, reporter_handle, report_count, trusted_weight)
mutual_report_pairs — detected mutual/org retaliation pairs (reporter_a_hash, reporter_b_hash, pair_type, weight_applied)
score_decay_tracking — tracks categories eligible for score decay (current_score, decay_active, last_adjustment)
disposition_state — tracks hostile notoriety cooldowns per player (friendly_cooldown_expires, last_disposition_report)
org_roster_cache — cached org membership data for org-based cooldown checks
daily_backups — backup records with status, file path, checksum, and duration

Tag Definitions Reference (49 tags, 9 categories)

⚔ DANGEROUS (Combat Threat)

Tag ID	Label	Points
`killed_me`	Killed Me	1
`killed_us`	Killed My Crew	2
`ambushed`	Ambushed / Camped Me	1
`griefer`	Griefed / Harassed Me	1
`spawn_killed`	Spawn Killed Me	2
`team_killed`	Team Killed (Friendly Fire)	1
`combat_logged`	Combat Logged vs Me	2
`stalked`	Stalked / Followed Me	1

🎭 SHADY (Deception / Theft)

Tag ID	Label	Points
`scammed`	Scammed Me	2
`lied`	Lied / Deceived Me	1
`manipulated`	Manipulated / Lured Me	1
`stole_cargo`	Stole My Cargo/Ship	2
`fake_trade`	Fake Trade / Bait Switch	2
`fake_auction`	Fake Auction / Price Fix	2
`hacked_terminal`	Hacked My Terminal/Ship	1

☠ PIRACY (Hostile PvP / Robbery)

Tag ID	Label	Points
`pirate_act`	Demanded Cargo / Tolls	1
`pirate_confirmed`	Confirmed Pirate	2
`ship_jacked`	Jacked My Ship	2
`cargo_hold_raided`	Raided My Cargo Hold	1
`blockaded`	Blockaded / Camped Port	2
`extortion`	Extorted / Shakedown	2

👻 ELUSIVE (Hard to Catch)

Tag ID	Label	Points
`elusive`	Hard to Track / Elusive	1
`escaped`	Escaped Every Time	1
`quantum_dodged`	Quantum Dodged / Logged	1
`stealthy`	Stealthy / Ghost	1
`hushed`	Used HUD Hacks / Glitches	2

✓ TRUSTWORTHY (Reliable / Honest)

Tag ID	Label	Points
`trustworthy`	Trustworthy / Reliable	2
`fair_fight`	Honorable Fighter	1
`kept_word`	Kept Their Word	2
`honest_trade`	Honest Trader	1

⭐ COMPETENT (Skilled / Good at Game)

Tag ID	Label	Points
`skilled_pilot`	Skilled Pilot	1
`good_leader`	Good Squad Leader	2
`great_in_combat`	Great in Combat	1
`mining_partner`	Good Mining/Trade Partner	1
`revived_me`	Revived / Healed Me	1

😊 POSITIVE (General Good Vibes)

Tag ID	Label	Points
`generous`	Gave Free Stuff / Repaired	1
`helpful`	Helped Me Out	1
`encouraging`	Encouraging / Supportive	1
`calm_under_pressure`	Calm Under Pressure	1
`problem_solver`	Solved a Problem For Me	2

🤝 FRIENDLY (Social / Welcoming)

Tag ID	Label	Points
`friendly`	Friendly Encounter	1
`welcoming`	Welcoming to New Players	2
`good_conversation`	Good Conversation	1
`made_me_laugh`	Made Me Laugh	1
`social_butterfly`	Social / Knows Everyone	1

🤮 TOXIC (Bad Attitude / Disruptive)

Tag ID	Label	Points
`toxic_chat`	Toxic Voice/Text Chat	1
`grief_teammate`	Griefed Own Team	2
`afk_leech`	AFK / Leeching	1
`false_reports`	False Reported Others	2

Name		Name	Last commit message	Last commit date
scripts		scripts
supabase		supabase
.gitignore		.gitignore
README.md		README.md

Navigation Menu

Repository files navigation

SCDossierRepServer

Architecture Overview

Edge Functions

Self-Hoster Setup Guide

Prerequisites

Step 1 — Create a Supabase Project

Step 2 — Link the Supabase CLI

Step 3 — Apply the Database Migrations

Step 4 — Seed the Tag Definitions (Optional)

Step 5 — Set the App Token Secret

Step 6 — Set Optional Secrets (for daily-backup)

Step 7 — Deploy the Edge Functions

Step 8 — Set Up Cron Jobs (optional, for score-decay and daily-backup)

Step 9 — Get Your API Credentials

Step 10 — Test the Setup

Security Design

Row Level Security (RLS)

IP Hashing

Rate Limiting

Abuse Prevention

App Authenticity Header

Keeping the Free Tier Active

Database Schema Reference

Tag Definitions Reference (49 tags, 9 categories)

⚔ DANGEROUS (Combat Threat)

🎭 SHADY (Deception / Theft)

☠ PIRACY (Hostile PvP / Robbery)

👻 ELUSIVE (Hard to Catch)

✓ TRUSTWORTHY (Reliable / Honest)

⭐ COMPETENT (Skilled / Good at Game)

😊 POSITIVE (General Good Vibes)

🤝 FRIENDLY (Social / Welcoming)

🤮 TOXIC (Bad Attitude / Disruptive)

About

Releases

Packages

Contributors

Languages

Latest commit

History

Folders and files

Repository files navigation

SCDossierRepServer

Architecture Overview

Edge Functions

Self-Hoster Setup Guide

Prerequisites

Step 1 — Create a Supabase Project

Step 2 — Link the Supabase CLI

Step 3 — Apply the Database Migrations

Step 4 — Seed the Tag Definitions (Optional)

Step 5 — Set the App Token Secret

Step 6 — Set Optional Secrets (for daily-backup)

Step 7 — Deploy the Edge Functions

Step 8 — Set Up Cron Jobs (optional, for score-decay and daily-backup)

Step 9 — Get Your API Credentials

Step 10 — Test the Setup

Security Design

Row Level Security (RLS)

IP Hashing

Rate Limiting

Abuse Prevention

App Authenticity Header

Keeping the Free Tier Active

Database Schema Reference

Tag Definitions Reference (49 tags, 9 categories)

⚔ DANGEROUS (Combat Threat)

🎭 SHADY (Deception / Theft)

☠ PIRACY (Hostile PvP / Robbery)

👻 ELUSIVE (Hard to Catch)

✓ TRUSTWORTHY (Reliable / Honest)

⭐ COMPETENT (Skilled / Good at Game)

😊 POSITIVE (General Good Vibes)

🤝 FRIENDLY (Social / Welcoming)

🤮 TOXIC (Bad Attitude / Disruptive)

About

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages