T2T-AI-X

/

copilot_work_3

Like 0

Follow

T2T AI-X 4

POST /api/tools and POST /api/ratings had no abuse protection:

• Anyone could flood the directory with submissions.
• Anyone could inflate ratings by sending random UUIDs as voterId, because the server never checked them.
• The GitHub URL was only format-checked, so it could point to a missing or private repo, and duplicates were allowed.

Changes

Schema and migration (infra/bigquery_schema.sql, src/lib/tool-types.ts)

• Added nullable tools.status (pending | approved | rejected) and tools.repo_key (lowercase owner/repo).
• A NULL status is treated as approved, so existing rows stay visible until the backfill runs.
• The migration SQL is at the bottom of the schema file (commented out).

GitHub verification and duplicates (src/lib/github.ts, src/lib/tools.ts, src/app/api/tools/route.ts)

• parseGithubRepoUrl is shared by validation and duplicate detection. It normalizes case, .git and a trailing slash.
• verifyRepo calls the GitHub API with a 5s timeout. A 404, a private repo or a disabled repo gives 422 on repoUrl. If
  GitHub is unavailable (rate limit, 5xx, timeout), the submission is kept as pending instead of being rejected.
• Duplicates return 409. In BigQuery the check is inside the INSERT ... WHERE NOT EXISTS, and it also covers rows
  written before repo_key existed.

Rate limiting (src/lib/rate-limit.ts)

• Per-IP, in-memory fixed window: 3 submissions per hour and 30 ratings per hour. Returns 429 with Retry-After.
• It runs inside the route handlers rather than proxy.ts, because the Next 16 docs advise against relying on module state in the proxy file.

Moderation (src/app/api/admin/tools, src/lib/admin-auth.ts)

• New submissions are stored as pending. Only approved tools appear in the list, the detail page, the sitemap and the rating endpoint.
• GET /api/admin/tools?status=pending and POST /api/admin/tools/[id] with {action: approve | reject}. Both need Authorization: Bearer $ADMIN_TOKEN and return 404 when ADMIN_TOKEN is unset.
• The submit form now says the submission is awaiting review, and no longer adds it to the list.

Voter integrity (src/lib/voter.ts, src/app/api/ratings/route.ts, src/app/tool-rating.tsx)

• The voter ID now comes from an HMAC-signed, HttpOnly cookie issued by the server. The voterId in the request body is no longer accepted, and the client no longer uses localStorage.

Tests and docs

• Added vitest (npm test) with 19 unit tests for URL parsing, GitHub verification, the rate limiter, the voter token and the demo-store moderation and duplicate flow.
• Updated .env.example and the README (secrets, migration, moderation curl examples).

Running the database migration

Run this once per environment, before deploying this version. It only applies to an existing tools table. A fresh dataset gets the new columns from the CREATE TABLE at the top of infra/bigquery_schema.sql.

Use an account with roles/bigquery.dataEditor on the dataset and roles/bigquery.jobUser on the project. The runtime service account is not meant for this.

P=YOUR_PROJECT_ID; D=YOUR_DATASET

bq query --project_id=$P --use_legacy_sql=false
"ALTER TABLE `$P.$D.tools` ADD COLUMN IF NOT EXISTS repo_key STRING"
bq query --project_id=$P --use_legacy_sql=false
"ALTER TABLE `$P.$D.tools` ADD COLUMN IF NOT EXISTS status STRING"
bq query --project_id=$P --use_legacy_sql=false
"UPDATE `$P.$D.tools`
SET status = COALESCE(status, 'approved'),
repo_key = LOWER(REGEXP_EXTRACT(repo_url, r'^https://github\.com/([^/]+/[^/]+?)(?:\.git)?/?\$'))
WHERE TRUE"

Or paste the three commented statements from the bottom of infra/bigquery_schema.sql into the BigQuery console, after replacing the placeholders and removing the -- prefixes.

Check it worked:

SELECT status, COUNT(*) AS n, COUNTIF(repo_key IS NULL) AS missing_key
FROM YOUR_PROJECT_ID.YOUR_DATASET.tools GROUP BY status;

Expect every existing row to be approved with missing_key = 0. Rows whose repo_url is not a plain GitHub URL would keep a NULL repo_key. The app still catches those as duplicates through the URL comparison in the insert.

The statements are safe to re-run. The old app version keeps working against the new columns, because it ignores them.

Deployment notes

1. Run the migration above before deploying.
2. Create these Secret Manager secrets and grant the runtime service account secretmanager.secretAccessor:
   • VOTER_SECRET (required in production; the ratings endpoint fails without it)
   • ADMIN_TOKEN
   • GITHUB_TOKEN (optional; raises the GitHub API limit from 60 per hour)
3. Follow-up: put Cloud Armor in front of Cloud Run for a hard rate limit.

Known limits

• The rate limiter is per Cloud Run instance and resets on cold start, so it is best-effort.
• BigQuery cannot enforce uniqueness, so two exactly simultaneous submissions of one repo could both succeed.
• Clearing cookies gives a new voter identity. The IP rate limit is the second layer.
• Whether a repo is actually about supply chains is not checked automatically. A moderator decides.
• A submission whose repo is not found is rejected with 422 and not stored, so rejected is only set by a moderator.

Test plan

• npm run typecheck, npm run lint, npm test and npm run build pass.
• Demo mode, run by hand with curl:
  • A new submission returns 201 and is not listed.
  • The same repo with different casing and .git returns 409.
  • The admin API returns 401 without the token, and approve works with it.
  • Rating a pending tool returns 404. Rating an approved tool returns 200 and sets the chainkit-voter cookie.
  • After 3 submissions from one IP, the next ones return 429.
• Not done: a not-found repo returning 422 against the live GitHub API (covered by unit tests with mocked fetch).
• Not done: the BigQuery path. Run the migration on a test dataset, then repeat the submit, approve and duplicate checks.