AI App Builder Connected to Live Databases: PostgreSQL, Snowflake & Supabase

import { sql } from '@/lib/db'
import { CohortRetentionChart } from '@/components/CohortRetentionChart'

export const revalidate = 300 // Revalidate cache every 5 minutes

interface CohortData {
  cohort_month: string
  initial_users: number
  retention_m1: number
  retention_m3: number
}

export default async function CohortAnalyticsPage() {
  // Parameter-safe SQL query compiled by Orbit against verified schema
  const cohorts = await sql<CohortData[]>`
    SELECT 
      DATE_TRUNC('month', created_at)::date AS cohort_month,
      COUNT(id) AS initial_users,
      ROUND(COUNT(CASE WHEN last_active >= created_at + INTERVAL '30 days' THEN 1 END) * 100.0 / COUNT(id), 1) AS retention_m1,
      ROUND(COUNT(CASE WHEN last_active >= created_at + INTERVAL '90 days' THEN 1 END) * 100.0 / COUNT(id), 1) AS retention_m3
    FROM users
    WHERE created_at >= NOW() - INTERVAL '12 months'
    GROUP BY cohort_month
    ORDER BY cohort_month DESC;
  `

  return (
    <div className="p-8 max-w-7xl mx-auto space-y-6">
      <h1 className="text-2xl font-bold tracking-tight">12-Month Cohort Retention</h1>
      <CohortRetentionChart data={cohorts} />
    </div>
  )
}