Build Product Analytics in Lovable

Implement the ingest Edge Function at supabase/functions/ingest/index.ts.

The function accepts POST with body {api_key: string, events: [{name, user_id?, session_id?, properties?, occurred_at?}]}. It should:
1. Validate the API key: hash the provided api_key with SHA-256 (use Web Crypto API available in Deno), look up api_keys.key_hash = hashed, is_active = true. If not found, return 401 {error: 'Invalid API key'}. Update last_used_at on success.
2. Batch INSERT all events in one statement using the track_event RPC or a direct INSERT (SECURITY DEFINER context).
3. Return 200 {received: events.length}.
4. Rate limit: maintain a key in memory (or a postgres table api_key_usage) — reject if >1000 events per minute per key with 429.

In /settings, add the 'Get Started' section: a copy-pasteable JS snippet:
```
// Add to your app
const analytics = {
  track(name, properties = {}) {
    fetch('https://your-app.lovable.app/api/ingest', {
      method: 'POST',
      headers: {'Content-Type': 'application/json'},
      body: JSON.stringify({api_key: 'YOUR_API_KEY', events: [{name, properties, occurred_at: new Date().toISOString()}]})
    });
  }
};

analytics.track('page_view', {path: window.location.pathname});
analytics.track('button_click', {button: 'sign_up_cta'});
```

Replace 'your-app.lovable.app' with the actual deployed URL and 'YOUR_API_KEY' with a freshly generated API key from /settings.

Implement the funnel calculation in FunnelBuilder.tsx and a new Postgres RPC `calculate_funnel`.

The RPC signature: `calculate_funnel(p_steps text[], p_start_at timestamptz, p_end_at timestamptz, p_window_days int DEFAULT 7)` returns table(step_index int, step_name text, user_count bigint, conversion_rate numeric).

The RPC implements a sequential funnel: for step 1, count DISTINCT user_id where name = p_steps[1] and occurred_at between p_start_at and p_end_at. For step 2, count DISTINCT user_id who appear in step 1 AND have name = p_steps[2] occurring within p_window_days of their step 1 event. Repeat for each step. Use CTEs:

WITH step1 AS (SELECT DISTINCT user_id, MIN(occurred_at) as first_at FROM events WHERE name = p_steps[1] AND occurred_at BETWEEN p_start_at AND p_end_at GROUP BY user_id),
step2 AS (SELECT DISTINCT e.user_id FROM events e JOIN step1 s ON s.user_id = e.user_id WHERE e.name = p_steps[2] AND e.occurred_at BETWEEN s.first_at AND s.first_at + make_interval(days => p_window_days))...

In FunnelBuilder, replace the mock data with a supabase.rpc('calculate_funnel', {p_steps, p_start_at, p_end_at}) call. Show each step as a horizontal bar (width = conversion_rate %). Show absolute user count and % below each bar. Show drop-off % between steps in a rose-500 label.

Implement the cohort retention grid in CohortGrid.tsx and a new Postgres RPC `calculate_retention`.

The RPC: `calculate_retention(p_signup_event text DEFAULT 'sign_up', p_return_event text DEFAULT 'page_view', p_weeks int DEFAULT 8)` returns table(cohort_week date, week_number int, retained_users bigint, cohort_size bigint, retention_rate numeric).

Implementation: group users by the week of their first p_signup_event occurrence (cohort_week). For each cohort, count users who also triggered p_return_event exactly N weeks later. Return one row per (cohort_week, week_number) pair.

In CohortGrid.tsx, render a table where:
- Rows = cohort weeks (most recent at top)
- Columns = Week 0 (baseline = 100%), Week 1, Week 2, ... Week N
- Each cell = retention_rate formatted as X.X%
- Cell background color: interpolate from emerald-100 (100%) to transparent (0%) based on retention_rate value
- Row header = cohort week date + cohort_size users
- Click a cell to see the user list (query raw events for those users in that week)

Grant EXECUTE on calculate_retention to authenticated.

Add a BigQuery export pipeline for when your events table grows past 100K rows/month.

Create a new Edge Function at supabase/functions/export-to-warehouse/index.ts. The function:
1. Queries events where occurred_at > (SELECT MAX(exported_at) FROM warehouse_sync_log). Create a new table `warehouse_sync_log` (last_exported_event_id bigint, exported_at timestamptz, rows_exported int).
2. Streams events in batches of 1000 to BigQuery using the BigQuery REST API (POST to https://bigquery.googleapis.com/bigquery/v2/projects/{project}/datasets/{dataset}/tables/{table}/insertAll). Authenticate with a service account key stored as GOOGLE_SERVICE_ACCOUNT_JSON in Cloud Secrets.
3. After successful export, DELETES events older than 30 days from Postgres (keeping only a rolling 30-day window in Postgres for fast queries). Inserts a warehouse_sync_log row.
4. Returns {exported_rows, deleted_rows}.

Schedule this function via Cloud tab → Edge Functions → export-to-warehouse → Schedule to run hourly.

In /settings, add a 'Warehouse' tab showing the last sync timestamp and row count from warehouse_sync_log, with a 'Sync now' button that calls the function on demand.

Before running this prompt: create a BigQuery dataset in Google Cloud Console, create a service account with BigQuery Data Editor role, download the JSON key, and paste it into Cloud Secrets as GOOGLE_SERVICE_ACCOUNT_JSON.

Add an 'Ask anything' natural-language query input to /dashboards.

In /dashboards, add a text input with placeholder 'Ask: What were the top events last week?' and a Submit button. On submit, call the Lovable AI connector (Cloud tab → AI → Gemini 3 Flash, which is the default model) with the user's question and this system context:

'You are a SQL analyst for a product analytics database. The main table is `events` with columns: id (bigserial), user_id (uuid), session_id (text), name (text), properties (jsonb), occurred_at (timestamptz). Generate a read-only SELECT SQL query answering the user's question. Return ONLY the SQL with no explanation or markdown fencing. Always include an ORDER BY and LIMIT 100.'

Pass the generated SQL to a new Edge Function `run-query` that:
1. Validates the SQL is a SELECT statement (reject UPDATE/DELETE/DROP with 400).
2. Executes via supabase.rpc or a prepared statement against the events table.
3. Returns up to 100 rows as JSON.

The /dashboards/:id page renders the result as either a DataTable (if columns > 3) or a Recharts BarChart (if 2 columns: name + count). Add a 'Save to dashboard' button that saves the query + result type to dashboards.layout as a named card.

Add alerting when a key metric drops. In /dashboards, let users define alert rules: 'Alert if [event_name] count drops more than [X]% week-over-week.'

Create an `alert_rules` table: id (uuid pk), name (text), event_name (text), threshold_pct (int not null), channel (text check in ('slack','email')), destination (text — Slack webhook URL or email address), is_active (bool default true), owner_id (uuid references auth.users(id)).

Create a scheduled Edge Function at supabase/functions/check-alerts/index.ts running every hour:
1. Fetch all active alert_rules.
2. For each rule, call event_count_by_day for the past 7 days and the 7 days before that.
3. Compare this week's total to last week's total. If the drop percentage > threshold_pct, fire the alert.
4. For Slack: POST {text: 'Alert: [event_name] is down X% WoW ([this week] vs [last week])'} to destination (a Slack Incoming Webhook URL).
5. For email: call Resend with the same message to destination.
6. Record each fired alert in an `alert_history` table (rule_id, fired_at, this_week_count, last_week_count, drop_pct) to avoid re-alerting on the same drop.

In /dashboards, add an 'Alerts' tab with the alert_rules list and a 'Create alert' form.