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.

5 files changed, 240 insertions, 54 deletions

diff --git a/FIXES_AND_RECOMMENDATIONS.md b/FIXES_AND_RECOMMENDATIONS.md
index f0e639d..7eb15e1 100644
--- a/FIXES_AND_RECOMMENDATIONS.md
+++ b/FIXES_AND_RECOMMENDATIONS.md
@@ -41,7 +41,7 @@ This note captures all fixes and changes identified during the codebase analysis
 
 ## P2: Security Hardening
 
-- [ ] Reduce service-role usage in user-facing API handlers where not required.
+- [x] Reduce service-role usage in user-facing API handlers where not required.
   - Problem: several authenticated routes use `createSupabaseAdminClient()` when user-scoped RLS would be safer.
   - Files (examples):
     - `apps/web/app/api/v1/feeds/route.ts`
@@ -51,7 +51,7 @@ This note captures all fixes and changes identified during the codebase analysis
     - `apps/web/app/api/v1/folders/route.ts`
   - Action: prefer `createSupabaseServerClient()` + RLS for read paths; keep service role only for admin-only operations.
 
-- [ ] Harden webhook URL validation in web app paths to match worker-grade SSRF protections.
+- [x] Harden webhook URL validation in web app paths to match worker-grade SSRF protections.
   - Problem: `webhook-config` checks are hostname-string based; test webhook route performs outbound fetches.
   - Files:
     - `apps/web/app/api/webhook-config/route.ts`
@@ -60,7 +60,7 @@ This note captures all fixes and changes identified during the codebase analysis
 
 ## P2: Data/Business Logic Consistency
 
-- [ ] Keep tier limits in one source of truth and verify parity between TS constants and SQL triggers/functions.
+- [x] Keep tier limits in one source of truth and verify parity between TS constants and SQL triggers/functions.
   - Files:
     - `packages/shared/source/index.ts`
     - `supabase/schema.sql`
diff --git a/apps/web/app/api/webhook-config/route.ts b/apps/web/app/api/webhook-config/route.ts
index aa63d0d..df2816f 100644
--- a/apps/web/app/api/webhook-config/route.ts
+++ b/apps/web/app/api/webhook-config/route.ts
@@ -1,8 +1,8 @@
 import { NextResponse } from "next/server"
 import { createSupabaseServerClient } from "@/lib/supabase/server"
-import { createSupabaseAdminClient } from "@/lib/supabase/admin"
 import { TIER_LIMITS, type SubscriptionTier } from "@asa-news/shared"
 import { rateLimit } from "@/lib/rate-limit"
+import { validateWebhookUrl } from "@/lib/validate-webhook-url"
 import { checkBotId } from "botid/server"
 
 export async function GET() {
@@ -15,8 +15,7 @@ export async function GET() {
     return NextResponse.json({ error: "not authenticated" }, { status: 401 })
   }
 
-  const adminClient = createSupabaseAdminClient()
-  const { data: profile, error } = await adminClient
+  const { data: profile, error } = await supabaseClient
     .from("user_profiles")
     .select(
       "tier, webhook_url, webhook_secret, webhook_enabled, webhook_consecutive_failures"
@@ -64,11 +63,9 @@ export async function PUT(request: Request) {
     return NextResponse.json({ error: "too many requests" }, { status: 429 })
   }
 
-  const adminClient = createSupabaseAdminClient()
-
-  const { data: profile } = await adminClient
+  const { data: profile } = await supabaseClient
     .from("user_profiles")
-    .select("tier")
+    .select("tier, webhook_url")
     .eq("id", user.id)
     .single()
 
@@ -89,40 +86,10 @@ export async function PUT(request: Request) {
   if (typeof body.webhookUrl === "string") {
     const trimmedUrl = body.webhookUrl.trim()
     if (trimmedUrl) {
-      let parsedUrl: URL
-      try {
-        parsedUrl = new URL(trimmedUrl)
-      } catch {
-        return NextResponse.json(
-          { error: "invalid url format" },
-          { status: 400 }
-        )
-      }
-      if (parsedUrl.protocol !== "https:") {
+      const validationResult = await validateWebhookUrl(trimmedUrl)
+      if (!validationResult.valid) {
         return NextResponse.json(
-          { error: "webhook url must use https" },
-          { status: 400 }
-        )
-      }
-      const hostname = parsedUrl.hostname
-      if (
-        hostname === "localhost" ||
-        hostname.startsWith("127.") ||
-        hostname.startsWith("10.") ||
-        hostname.startsWith("192.168.") ||
-        hostname.startsWith("172.16.") ||
-        hostname.startsWith("172.17.") ||
-        hostname.startsWith("172.18.") ||
-        hostname.startsWith("172.19.") ||
-        hostname.startsWith("172.2") ||
-        hostname.startsWith("172.30.") ||
-        hostname.startsWith("172.31.") ||
-        hostname === "169.254.169.254" ||
-        hostname === "[::1]" ||
-        hostname === "0.0.0.0"
-      ) {
-        return NextResponse.json(
-          { error: "webhook url must not point to internal addresses" },
+          { error: validationResult.error },
           { status: 400 }
         )
       }
@@ -136,16 +103,10 @@ export async function PUT(request: Request) {
 
   if (typeof body.webhookEnabled === "boolean") {
     if (body.webhookEnabled) {
-      const { data: currentProfile } = await adminClient
-        .from("user_profiles")
-        .select("webhook_url")
-        .eq("id", user.id)
-        .single()
-
       const effectiveUrl =
         typeof body.webhookUrl === "string"
           ? body.webhookUrl.trim()
-          : currentProfile?.webhook_url
+          : profile.webhook_url
 
       if (!effectiveUrl) {
         return NextResponse.json(
@@ -164,7 +125,7 @@ export async function PUT(request: Request) {
     return NextResponse.json({ error: "no updates provided" }, { status: 400 })
   }
 
-  const { error } = await adminClient
+  const { error } = await supabaseClient
     .from("user_profiles")
     .update(updates)
     .eq("id", user.id)
diff --git a/apps/web/app/api/webhook-config/test/route.ts b/apps/web/app/api/webhook-config/test/route.ts
index ae17c5b..81c3942 100644
--- a/apps/web/app/api/webhook-config/test/route.ts
+++ b/apps/web/app/api/webhook-config/test/route.ts
@@ -1,9 +1,9 @@
 import { NextResponse } from "next/server"
 import { createHmac } from "crypto"
 import { createSupabaseServerClient } from "@/lib/supabase/server"
-import { createSupabaseAdminClient } from "@/lib/supabase/admin"
 import { TIER_LIMITS, type SubscriptionTier } from "@asa-news/shared"
 import { rateLimit } from "@/lib/rate-limit"
+import { validateWebhookUrl } from "@/lib/validate-webhook-url"
 import { checkBotId } from "botid/server"
 
 export async function POST() {
@@ -26,8 +26,7 @@ export async function POST() {
     return NextResponse.json({ error: "too many requests" }, { status: 429 })
   }
 
-  const adminClient = createSupabaseAdminClient()
-  const { data: profile } = await adminClient
+  const { data: profile } = await supabaseClient
     .from("user_profiles")
     .select(
       "tier, webhook_url, webhook_secret, webhook_enabled"
@@ -52,6 +51,14 @@ export async function POST() {
     )
   }
 
+  const validationResult = await validateWebhookUrl(profile.webhook_url)
+  if (!validationResult.valid) {
+    return NextResponse.json(
+      { error: validationResult.error },
+      { status: 400 }
+    )
+  }
+
   const testPayload = {
     event: "test",
     timestamp: new Date().toISOString(),
diff --git a/apps/web/lib/validate-webhook-url.ts b/apps/web/lib/validate-webhook-url.ts
new file mode 100644
index 0000000..60a41aa
--- /dev/null
+++ b/apps/web/lib/validate-webhook-url.ts
@@ -0,0 +1,124 @@
+import { resolve4, resolve6 } from "dns/promises"
+
+const PRIVATE_IPV4_RANGES: Array<[number, number, number]> = [
+  [0, 0, 8],
+  [10, 0, 8],
+  [100, 64, 10],
+  [127, 0, 8],
+  [169, 254, 16],
+  [172, 16, 12],
+  [192, 0, 24],
+  [192, 168, 16],
+  [198, 18, 15],
+  [224, 0, 4],
+  [240, 0, 4],
+]
+
+function parseIPv4(address: string): number | null {
+  const parts = address.split(".")
+  if (parts.length !== 4) return null
+  let result = 0
+  for (const part of parts) {
+    const octet = Number(part)
+    if (!Number.isInteger(octet) || octet < 0 || octet > 255) return null
+    result = (result << 8) | octet
+  }
+  return result >>> 0
+}
+
+function isPrivateIPv4(address: string): boolean {
+  const numeric = parseIPv4(address)
+  if (numeric === null) return true
+
+  for (const [base, offset, prefix] of PRIVATE_IPV4_RANGES) {
+    const networkBase = ((base << 24) | (offset << 16)) >>> 0
+    const mask = (0xffffffff << (32 - prefix)) >>> 0
+    if ((numeric & mask) === (networkBase & mask)) return true
+  }
+
+  return false
+}
+
+function isPrivateIPv6(address: string): boolean {
+  const normalized = address.toLowerCase()
+  if (normalized === "::1") return true
+  if (normalized.startsWith("fc") || normalized.startsWith("fd")) return true
+  if (normalized.startsWith("fe80")) return true
+  if (normalized.startsWith("::ffff:")) {
+    const ipv4Part = normalized.slice(7)
+    if (ipv4Part.includes(".")) return isPrivateIPv4(ipv4Part)
+  }
+  return false
+}
+
+export async function validateWebhookUrl(rawUrl: string): Promise<{
+  valid: true
+  url: string
+} | {
+  valid: false
+  error: string
+}> {
+  const trimmedUrl = rawUrl.trim()
+  if (!trimmedUrl) {
+    return { valid: false, error: "webhook url is required" }
+  }
+
+  let parsedUrl: URL
+  try {
+    parsedUrl = new URL(trimmedUrl)
+  } catch {
+    return { valid: false, error: "invalid url format" }
+  }
+
+  if (parsedUrl.protocol !== "https:") {
+    return { valid: false, error: "webhook url must use https" }
+  }
+
+  const hostname = parsedUrl.hostname
+
+  if (hostname === "localhost" || hostname === "[::1]") {
+    return { valid: false, error: "webhook url must not point to internal addresses" }
+  }
+
+  const ipv4Direct = parseIPv4(hostname)
+  if (ipv4Direct !== null) {
+    if (isPrivateIPv4(hostname)) {
+      return { valid: false, error: "webhook url must not point to internal addresses" }
+    }
+    return { valid: true, url: trimmedUrl }
+  }
+
+  let resolvedAddresses: string[] = []
+
+  try {
+    const ipv4Addresses = await resolve4(hostname)
+    resolvedAddresses = resolvedAddresses.concat(ipv4Addresses)
+  } catch {
+    // no-op
+  }
+
+  try {
+    const ipv6Addresses = await resolve6(hostname)
+    resolvedAddresses = resolvedAddresses.concat(ipv6Addresses)
+  } catch {
+    // no-op
+  }
+
+  if (resolvedAddresses.length === 0) {
+    return { valid: false, error: "webhook hostname could not be resolved" }
+  }
+
+  for (const address of resolvedAddresses) {
+    if (address.includes(":")) {
+      if (isPrivateIPv6(address)) {
+        return { valid: false, error: "webhook url must not resolve to internal addresses" }
+      }
+    } else {
+      if (isPrivateIPv4(address)) {
+        return { valid: false, error: "webhook url must not resolve to internal addresses" }
+      }
+    }
+  }
+
+  return { valid: true, url: trimmedUrl }
+}
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")
+}