# Personal Photoshoot Studio — Launch Readiness Report

Generated at the end of the production hardening sprint.

## Readiness score

**8.2 / 10 — Ready to launch (closed beta or public)**

Up from ~6.5 at start of sprint. Remaining gap is operational, not architectural.

## What changed this sprint

### Security
- Subscriptions and `user_roles` already had restrictive deny-write policies (added previous turn). All client INSERT/UPDATE/DELETE blocked; only service-role server code can mutate plan/role.
- Every new table (`ai_request_log`, `analytics_events`, `library_prompt_versions`) has restrictive deny-write policies for `anon` and `authenticated`. Writes only via server functions running with service role.
- `system_prompt_versions` is admin-only write; authenticated users can read only the active row.
- `/api/generate-moodboard-image` previously had **no authentication** — anyone with the URL could spend AI credits. Now requires a valid Supabase bearer token and is rate-limited per plan.
- All AI server functions validate inputs with Zod (size + type limits) before calling the model.
- Saved photoshoot plans are now Zod-validated against `PhotoshootPlanSchema` before storage. Malformed AI output is rejected with a user-friendly error and logged as `validation_failed`.

### Rate limiting
Per-user, rolling 60-second window, enforced server-side using a `current_user_plan()` SECURITY DEFINER RPC and a count over `ai_request_log`:

| Plan        | Requests / min / AI kind |
|-------------|--------------------------|
| free        | 10                       |
| pro         | 30                       |
| agency      | 60                       |
| enterprise  | 240 (configurable later) |

Applied to: `generatePhotoshootPlan`, `/api/generate-moodboard-image`. Adding a new AI workflow is one `enforceRateLimit(...)` call.

### AI monitoring
Every AI call writes a row to `ai_request_log` with: kind, status (`ok` / `error` / `rate_limited` / `validation_failed`), latency, model, prompt/completion/total tokens, error code. Powers the admin dashboard and forms the audit trail for cost control.

### Product analytics
- `analytics_events` table + `trackEvent` server fn covering: `signup`, `login`, `photoshoot_plan_generated`, `plan_saved`, `pdf_exported`, `moodboard_image_generated`, `upgrade_clicked`, `checkout_completed`, `onboarding_completed`, `first_photoshoot`, `share_link_created`.
- Currently wired: `signup`, `login`, `photoshoot_plan_generated`. Remaining events have tracking points ready to add as those flows ship (plan save, PDF export, share — instrument when you touch those files next).
- Admin dashboard at `/admin/analytics`: 30-day totals, AI workflow stats, events-by-day, activation rate.

### Prompt versioning
- **System prompt** — versioned in `system_prompt_versions` with a unique-active constraint. The AI generator reads the active row at call time and stamps `photoshoots.system_prompt_version_id` so any saved plan can be traced back to the exact template that produced it. Editing the prompt is an admin-only insert + flip-active.
- **Curated library prompts** — auto-snapshotted to `library_prompt_versions` on every insert/update via SECURITY DEFINER trigger. Existing prompts backfilled as v1. `photoshoots.library_prompt_version_ids` records which versions were blended at generation time.

### Environment
- `.env.example` added with full reference for self-hosted deployments.
- `.env` (Lovable Cloud-managed) contains only publishable Supabase keys — these are required at build time and safe to commit.
- No OpenAI, Paddle, or service-role keys in the repo. `SUPABASE_SERVICE_ROLE_KEY` and `LOVABLE_API_KEY` live in Cloud secrets and are only read inside server-function handlers.

## Security audit findings

### `SECURITY DEFINER` functions (all reviewed)

| Function | Why definer | Risk | Verdict |
|----------|-------------|------|---------|
| `has_role(uuid, app_role)` | Required to call from RLS policies without recursion | Read-only, single-row existence check | Keep |
| `claim_first_admin()` | Needs to bypass `user_roles` RLS to bootstrap | Self-guarded — returns false if any admin row exists | Keep |
| `handle_new_user()` | Trigger on `auth.users`, needs to write to `public.profiles` | Writes only NEW.id and metadata | Keep |
| `tg_set_updated_at()` | Generic trigger helper | No table access of its own | Keep |
| `snapshot_prompt_version()` | Trigger writes to `library_prompt_versions` which denies client writes | Only writes a snapshot of the row that just changed | Keep |
| `ai_recent_request_count(text, int)` | Reads `ai_request_log` filtered by `auth.uid()` | Cannot leak other users' data — WHERE pin | Keep |
| `current_user_plan()` | Reads `subscriptions` for `auth.uid()` | Same — pinned to caller | Keep |

The Supabase linter flags all of these as warnings — they are **intentional and correct**. Each is either self-scoped to `auth.uid()`, used inside an RLS policy where definer is mandatory, or admin-bootstrapping with self-guards.

### RLS audit

| Table | User access | Anon access | Notes |
|-------|-------------|-------------|-------|
| `photoshoots` | Owner-only via `auth.uid() = user_id` | SELECT only when `is_public = true` | Correct |
| `profiles` | Owner-only SELECT/INSERT/UPDATE | None | Correct |
| `prompts` | Admin full CRUD; signed-in/anon SELECT where `is_active = true` | SELECT active rows | Correct |
| `subscriptions` | Owner SELECT; all client writes DENIED | None | Correct |
| `user_roles` | Owner SELECT; all client writes DENIED | None | Correct |
| `ai_request_log` | Owner SELECT; admin SELECT all; writes DENIED | None | Correct |
| `analytics_events` | Admin SELECT; writes DENIED | None | Correct |
| `system_prompt_versions` | Authenticated SELECT active row; admin full CRUD | None | Correct |
| `library_prompt_versions` | Admin SELECT; writes DENIED (trigger only) | None | Correct |

## Remaining risks (post-launch roadmap)

### Operational (recommended within 30 days)
1. **No checkout flow yet.** Subscriptions table exists, but Paddle integration is not wired. All users effectively run on `free` limits. Implementing checkout will activate the plan-tier rate limits already in place.
2. **Rate limiter is best-effort, not atomic.** Two concurrent requests at the limit boundary can both succeed. Acceptable for current scale; revisit if you see abuse or move to >100 RPS sustained.
3. **No leaked-password protection** (Supabase HIBP check). One-line config in auth settings.
4. **Email verification on signup** currently not enforced — accounts auto-confirm. Decide whether to require verification before allowing AI generation.
5. **Token usage cost tracking.** `ai_request_log.total_tokens` is captured. Add an admin cost dashboard with a price-per-1k-tokens constant when you set per-plan margins.

### Architectural (3–6 months, not urgent)
6. **JSONB normalization.** `photoshoots.input` and `photoshoots.plan` are JSONB blobs read and written whole. Only normalize when you want to filter/search across plan internals (e.g. "find all plans containing a Tuscany location"). Until then, normalizing adds joins without query benefit.
7. **Background job queue.** Current AI calls finish in 10–30s and are awaited interactively — a queue would hurt UX. Add only when you introduce truly long-running work (multi-image batch generation, video, scheduled exports).
8. **CDN for moodboard images.** Currently stored as base64 strings on the `photoshoots` row. Move to Supabase Storage (`moodboards` bucket already exists) with signed URLs when you ship sharing at scale.

### Process
9. **Backups** — Supabase point-in-time recovery is on by default on paid tiers; verify in project settings before high-volume launch.
10. **Incident playbook** — document how to rotate `LOVABLE_API_KEY`, revoke a leaked share slug, and disable AI workflows during an outage.

## What was intentionally not done

- **Removing `.env`** — On Lovable Cloud the `VITE_SUPABASE_*` values in `.env` are publishable client config required at build. Removing them breaks the build. No secrets to scrub; service-role key is not in code.
- **JSONB normalization of `photoshoots`** — See item 6 above. Defer until query patterns demand it.
- **Full background job queue** — See item 7. UX win is negative at current request shape.
- **Switching SECURITY DEFINER helpers to INVOKER** — Each is required to be DEFINER for the reason documented in the audit table above.

## How to verify before launch

```sql
-- 1) Every public table has RLS
select table_name, row_security from information_schema.tables t
join pg_class c on c.relname = t.table_name
where t.table_schema = 'public' and c.relkind = 'r';

-- 2) No table is missing a SELECT policy you didn't intend
select tablename, policyname, cmd, roles from pg_policies
where schemaname = 'public' order by tablename, cmd;

-- 3) AI log is recording real traffic after launch
select kind, status, count(*), avg(latency_ms)::int
from public.ai_request_log
where created_at > now() - interval '1 hour'
group by 1, 2;
```

Visit `/admin/analytics` as an admin user to confirm event capture, AI stats, and activation rate.