Security

Last updated 30 August 2026 · Covers the deployed app at stillwaterfishing.app

1. What is being protected

A fishing logbook is more sensitive than it first sounds. It contains where a person goes, when they are reliably there, and precise coordinates of spots they consider worth keeping. The two things worth real effort are: nobody should be able to read a logbook that is not theirs, and nothing should be published that the owner did not choose to publish.

2. Identity

Every row in the database carries a device_id — its scope. As of August 2026 there is one scope that matters, and one that is only kept alive to migrate people off it.

The key_<hash> passphrase scope — a user-invented “sync code” hashed into a bucket id — was removed in August 2026. It was a second identity system with weaker recovery properties sitting beside a real one, and a passphrase that silently reasserted itself whenever a user signed out. Existing rows are reachable only by adopting them into an account.

3. How access is enforced

Enforcement is in the SQL, not in the UI. The relevant guarantees, endpoint by endpoint:

• GET /api/outings requires a scope and filters where device_id = $scope. There is no "list everything" path — a request with no scope is a 400, not an empty filter.
• POST /api/outings upserts with … on conflict (id) do update … where outings.device_id = $scope. Guessing another person's outing id does not let you overwrite it; the update silently matches zero rows.
• DELETE /api/outings deletes where id = $id and device_id = $scope. Ownership is part of the statement.
• GET /api/account, PUT /api/account take no account parameter at all. The row acted on is derived from the verified token, so there is nothing to tamper with.
• POST /api/photo requires a verified session, checks the file's actual magic number rather than its claimed MIME type, caps the size, and chooses the storage path itself under the caller's own user id.

All queries use parameterised tagged templates, so values are never concatenated into SQL.

This design came out of a real finding. In an earlier version GET /api/outings accepted no scope and returned every row in the table — a live IDOR that exposed four separate logbooks including catch coordinates and private notes. It was fixed on 5 July 2026 by making the scope mandatory and pushing the ownership check into every statement. The account tier added on 16 August 2026 builds on that, and upgrades the strongest scope from a capability to a verified identity.

4. The leaderboard

This is the only place Stillwater shows one person's data to another, so it has three independent gates:

1. Opt-in. accounts.leaderboard defaults to false. The query filters on it, so an account that has not opted in cannot appear even by accident.
2. Account rows only. The query joins on account_id, which is set only for authenticated writes. Keyless capability-scoped outings are structurally unable to reach the board.
3. Field allowlist. The SELECT names the columns it publishes, one by one. Notes, catch coordinates, gear, depth, trip times and the conditions snapshot are not in the list, so a future field added to a catch cannot leak by default. Timestamps are truncated to a date before they leave the server, because a precise time plus a named lake is a decent guess at where somebody was standing.

5. The league layer

Leagues are multi-tenant. Every competitive row — seasons, teams, measurement tools, entries, the audit log — carries a league_id, and eligibility lives on league_members, not on the account. “Membership: active” is a fact about a person in a league, so one angler can be an officer of one league and a lapsed member of another.

• No cross-tenant reads. Every authenticated route resolves a league first, checks the caller's membership in it, and filters every query on it. A request for a league you have not joined is refused, not silently narrowed.
• Joining is always code-gated. Visibility and access are separate questions: a league can choose to be publicly listed and readable, but entering fish in it always requires a join code an officer issues and can rotate. Join attempts are rate limited per account.
• Officer bootstrap. Each league has its own officer code, generated at creation and shown only to its officers. Five attempts per account per league per hour.
• Entries are append-only. A correction inserts a new version and links it to the old one; the only writes to an existing row are the superseded_by pointer and the officer status block. Officers change an entry's status, never its facts, and every action is written to an append-only audit log with a reason.
• Verification media is uploaded only with a verified session, validated by magic number rather than a claimed content type, size-capped separately for stills and video, and written to a server-chosen path under the caller's own user id — so no client can pick a path and overwrite another member's evidence.
• Precise coordinates are selected only on the officer path and the owner's own path. Public standings return the waterbody name and nothing else about location. The research export withholds coordinates row by row for every member who has not consented, and drops handles entirely.

What in-app capture does and does not prove. Verification media is recorded through getUserMedia + MediaRecorder inside the app, which an ordinary member on an ordinary phone cannot feed from a saved file — the bar moves from “find any photo” to “defeat the browser”. It is not proof. Where a browser refuses a camera stream the app falls back to a camera-only file input and tags the upload honestly. Every file therefore carries two signals an officer can read on the entry: whether it came from the in-app camera at all, and how long after the file was created it was submitted.

6. The rest of the surface

• Secrets. CLERK_SECRET_KEY, POSTGRES_URL and BLOB_READ_WRITE_TOKEN exist only as Vercel environment variables, are read server-side, and are never returned by any endpoint. /api/config serves the Clerk publishable key, which is public by design. .env* is git-ignored.
• Passwords. Stillwater never sees one. Sign-in, session issuance, resets and MFA are Clerk's responsibility.
• XSS. User-supplied strings are escaped before being interpolated into HTML, and photo references are validated as https: URLs on the way into the database.
• CORS. Cross-origin access is restricted to the project's own deployment origins. Same-origin app calls are unaffected.
• Location. Device GPS is requested one fix at a time, only on an explicit tap. There is no watchPosition and no background tracking, and a fix is never sent to a Stillwater server.
• Photo metadata. Images are re-encoded through a canvas in the browser before upload, which discards all EXIF — including the GPS tag phones embed. The upload endpoint therefore receives an image with no embedded location.
• Destructive actions. Clearing the logbook requires typing CLEAR; deleting an outing requires confirming which one.

7. Known weaknesses

Every system has these. Publishing them is more useful than implying they do not exist.

8. Rate limiting

Every data endpoint is throttled. The limits are set well above what real use looks like, so you should never meet one:

The logbook endpoint gets the edge rule specifically because it is the only one where a request with no session can reach real data — by presenting a keyless capability scope. A throttle there has to be impossible to bypass, so it sits in front of the application rather than inside it. Exceeding a limit returns 429 with a Retry-After header; nothing is deleted or locked.

9. Reporting a vulnerability

Email arothe2007@gmail.com with enough detail to reproduce it. Please do not access, modify, or delete data belonging to anyone else while testing — use two accounts of your own. There is no bounty; there is a genuine thank-you and a fast fix.