Homestay: Two-Sided Marketplace

Architecture Design Document

TL;DR

"The takeaway is that Homestay is a production Supabase and Lovable architecture blueprint generated by Zalcro. It uses PostgreSQL Row-Level Security policies and a GiST exclusion constraint to mathematically prevent double-bookings and safely route escrow funds via Stripe Connect."

Homestay is a two-sided short-term rental marketplace where hosts list homes or rooms and guests search, book, and pay for overnight stays. It runs on Supabase (PostgreSQL, Auth, Edge Functions, Storage) with a React frontend scaffolded in Lovable and Stripe Connect handling all payment routing. The two most important architectural choices are Supabase Row-Level Security policies that enforce data isolation and ownership at the database level, and a Stripe Connect escrow model that holds guest funds and releases them to hosts only after check-in via a Supabase Edge Function.

Executive Summary

Homestay is built as a Supabase-backed REST/RPC API consumed by a React single-page application. The frontend is scaffolded and iterated component-by-component in Lovable, using Plan mode analysis before any execution step — particularly for RLS policy changes, which are the primary security and data-isolation mechanism. All server-side business logic runs in Supabase Edge Functions (Deno runtime), which connect directly to the Supabase PostgreSQL database and call external services like Stripe.

The core business logic is split into two domains: Listing Management (host-side CRUD, calendar rules, pricing overrides, photo ordering) and Booking & Payments (guest-side search, reservation flow, Stripe Connect fund routing). These domains are implemented as Edge Functions, with Supabase's auto-generated REST API handling straightforward CRUD operations where RLS alone is sufficient to enforce access control.

The atomic availability lock — the hardest business rule in the system — is implemented natively at the database level using a PostgreSQL GiST exclusion constraint. This structurally rejects overlapping reservations, making double-bookings mathematically impossible. This prevents double bookings at the database level while keeping the locking logic server-side and out of reach of client manipulation.

The escrow model is enforced via Stripe: a PaymentIntent is created and captured at booking time, held in the platform's Stripe account, and transferred to the host's connected account after check-in by a scheduled Edge Function. Host cancellations trigger an unconditional full refund. Guest-side service fees are captured as Stripe's application fee on the charge.

CCPA compliance is handled via a soft-delete + PII-scrubbing Edge Function. PCI DSS scope is SAQ A — no raw card data ever touches the application or Edge Function layer.

The system targets hundreds of users with cost optimization as the primary constraint. Three environments (dev, staging, production) map to separate Supabase projects deployed via the Supabase CLI and GitHub Actions.

System Overview

graph TD
    subgraph client["Client Layer"]
        browser["Web Browser"]
        react["React 18 SPA"]
        lovable["Lovable Scaffold"]
    end

    subgraph supabase["Supabase Platform"]
        subgraph sbAuth["Auth & API"]
            gotrue["GoTrue Auth"]
            postgrest["PostgREST API"]
        end
        subgraph sbData["Data Layer"]
            pg["PostgreSQL 15"]
            vault["Supabase Vault"]
            pgcron["pg_cron Scheduler"]
        end
        subgraph sbStorage["Storage"]
            storage["Supabase Storage"]
        end
        subgraph sbFn["Edge Functions"]
            bookFn["booking-create"]
            payoutFn["payout-scheduler"]
            webhookFn["stripe-webhook"]
            ccpaFn["ccpa-scrubber"]
            adminFn["admin"]
            otherFn["host-onboarding / cancel / approve / expire / photo-confirm"]
        end
    end

    subgraph external["External Services"]
        stripe["Stripe Connect"]
        resend["Resend Email"]
    end

    subgraph deploy["CI/CD"]
        gh["GitHub Actions"]
        sbcli["Supabase CLI"]
    end

    browser --> react
    lovable --> react
    react --> gotrue
    react --> postgrest
    react --> bookFn
    react --> adminFn
    react --> otherFn
    postgrest --> pg
    gotrue --> pg
    pgcron --> payoutFn
    pgcron --> ccpaFn
    pgcron --> otherFn
    bookFn --> pg
    bookFn --> vault
    bookFn --> stripe
    bookFn --> resend
    payoutFn --> pg
    payoutFn --> vault
    payoutFn --> stripe
    payoutFn --> resend
    webhookFn --> pg
    webhookFn --> vault
    webhookFn --> stripe
    ccpaFn --> pg
    ccpaFn --> storage
    ccpaFn --> resend
    adminFn --> pg
    adminFn --> stripe
    otherFn --> pg
    otherFn --> vault
    otherFn --> stripe
    otherFn --> resend
    otherFn --> storage
    stripe --> webhookFn
    ccpaFn --> vault
    gh --> sbcli
    sbcli --> supabase

The system has four logical tiers:

1. Client layer — A React SPA scaffolded in Lovable. It communicates with Supabase using the Supabase JS client (v2) for auth, auto-generated table APIs, and Edge Function invocations. The Lovable development workflow uses Plan mode for RLS-sensitive changes before any code generation is executed. No direct Stripe API calls from the browser — only Stripe.js for card element rendering.

2. Supabase platform — The central backend. Supabase provides PostgreSQL 15 (the database), GoTrue (auth/JWT issuance), PostgREST (auto-generated REST API over tables), Realtime (optional, not used in v1), Storage (listing photos), and Edge Functions (Deno 1.x runtime for server-side business logic).

3. Edge Function layer — Deno-based serverless functions hosted on Supabase's infrastructure. They run with a service-role key scoped to server-side operations, bypassing RLS where needed for privileged actions (webhook processing, scheduled payouts, CCPA scrubbing). Exposed via Supabase's built-in function routing.

4. External integrations — Stripe Connect (payments, payouts, webhooks), Resend (transactional email), and Supabase Storage (listing photos, CCPA data exports).

Data flows directionally: a guest's search request hits PostgREST directly from the Supabase JS client, filtered by date/location/guest-count queries. A booking request hits a dedicated Edge Function that opens a serializable transaction, attempts to insert the booking row (blocked by a unique partial index on overlapping confirmed bookings), calls Stripe, and commits. If Stripe fails, the transaction rolls back and no booking record is created.

Architectural Constraints Summary

Lovable as the scaffolding tool drives the component-by-component development approach. Lovable's Plan mode is used before any execution step involving RLS policy changes — RLS policies silently deny data access if misconfigured, making pre-execution analysis essential. Each Lovable prompt targets a single, scoped component (a page, a form, a data hook) to minimize unintended side effects.

Supabase replaces AWS as the entire backend platform. This eliminates Lambda, API Gateway, RDS Proxy, SSM Parameter Store, SES, CloudWatch, and SAM. Supabase provides equivalent functionality — PostgreSQL (database), GoTrue (auth), PostgREST (API), Edge Functions (compute), Storage (object storage), and Vault (secrets) — as a managed service, dramatically reducing infrastructure surface area for an early-stage product.

RLS as the primary data isolation mechanism is the Supabase-idiomatic replacement for the previous architecture's Lambda Authorizer + role checks. Every table has RLS enabled. Policies are defined per operation (SELECT, INSERT, UPDATE, DELETE) and per role (authenticated user, service role). Because RLS is enforced at the database layer, it protects data even if application-layer logic has a bug — this is a stronger isolation guarantee than application-level role checks alone.

Atomic availability locking is handled by a PostgreSQL GiST exclusion constraint. Because Supabase natively supports the btree_gist extension, we can prevent date overlaps directly on the table using the && operator on a date range. This keeps the invariant database-native and removes the need for complex serializable RPC workarounds.

CCPA and PCI DSS constraints are unchanged in intent. PCI scope is SAQ A (Stripe.js, no card data in the application). CCPA deletion is a PII scrub via Edge Function, not a row delete, to preserve the 3-year financial retention requirement.

Cost optimization at small scale means Supabase's free or Pro tier is sufficient at launch. Edge Functions have no per-invocation infrastructure cost beyond Supabase's included limits. Supabase Storage replaces S3. Resend replaces SES. The total external service bill at hundreds-of-users scale is materially lower than the previous AWS-only architecture.

Output format is a Phased Lovable Prompt Pack, not a DAG Implementation Plan. Each phase maps to a set of Lovable prompts that scaffold a coherent slice of the product.

Component Architecture

Supabase Auth (GoTrue)

Handles user registration, login, token refresh, and JWT issuance. Users register with email and password. GoTrue issues short-lived JWTs (default 1-hour expiry) and manages refresh tokens. The JWT contains the user's UUID and role claim. No custom identity provider — GoTrue manages credentials natively.

On registration, a database trigger creates a corresponding row in the public profiles table with a default guest role. Host onboarding is a separate flow (Stripe Connect) that sets host_onboarding_status on the profile. The Supabase JS client handles token refresh transparently on the frontend.

PostgREST (Auto-Generated API)

Supabase exposes a REST API over every table in the public schema, filtered by the authenticated user's JWT. RLS policies on each table determine what rows each user can read or write. PostgREST is used for:

• Guest: browsing listings, viewing booking history
• Host: reading their own listings, availability rules, pricing overrides
• Admin: all reads (via service-role key in the Admin edge function)

PostgREST is not used for operations that require transactional guarantees (booking creation, payment processing) — those go through Edge Functions.

Supabase Edge Functions

Deno 1.x functions deployed alongside the Supabase project. They use the Supabase service-role client internally for privileged database access, bypassing RLS for server-controlled operations. All Edge Functions validate the caller's JWT before performing user-scoped operations. Edge Functions are organized by domain:

booking-create Edge Function: The most critical function. Handles the full instant-book and manual-approval reservation flows. It validates the availability rules (blocked dates, min stay, check-in day bitmask) and attempts a direct INSERT into the bookings table using the service-role client. The insert is protected by the GiST exclusion constraint. If Postgres raises a constraint violation, it returns a structured conflict error. On success, the Edge Function calls Stripe to create and capture the PaymentIntent, then updates the booking status to confirmed. On Stripe failure, it calls a compensating PostgreSQL function to cancel the pending booking row. Sends confirmation email via Resend on success.

Availability rule evaluation logic: The attempt_booking Postgres function checks rules in sequence: (1) all requested nights fall outside any blocked date range for the listing, (2) the stay length meets or exceeds the listing's minimum-stay value, (3) the check-in day-of-week is present in the listing's allowed check-in days bitmask. All three must pass before the insert is attempted.

Pricing calculation logic: The Edge Function calculates the charge amount by iterating each calendar night in the requested range. For each night, it looks up applicable overrides in priority order: (1) a seasonal override if the date falls within a defined season range stored in pricing_overrides, (2) a weekend override if the date is Friday or Saturday, (3) the listing's base nightly rate. The sum of all per-night rates plus the guest service fee (a fixed percentage stored in Supabase Vault, default 12%) forms the total charge amount passed to Stripe.

booking-approve / booking-decline Edge Function: Host-triggered approval for manual-approval listings. On approve, captures the Stripe PaymentIntent in requires_capture state and moves the booking to confirmed. On decline or timeout (checked by the booking-expire scheduled function), cancels the PaymentIntent and moves the booking to declined.

booking-cancel Edge Function: Handles both host-initiated and guest-initiated cancellations. Host-initiated: unconditional full Stripe refund, booking moved to cancelled_by_host, guest notified via Resend. Guest-initiated: applies the listing's cancellation policy enum (flexible, moderate, strict), calculates refund amount based on days until check-in, calls Stripe refund API, updates booking status.

payout-scheduler Edge Function (scheduled): Triggered by Supabase's pg_cron extension on an hourly schedule. Queries for confirmed bookings where check_in_date < NOW() and payout_status = pending. For each, calls Stripe to create a Transfer from the platform account to the host's connected Stripe account. Updates payout_status to completed and records the Stripe Transfer ID. Idempotent — checks payout_status before calling Stripe.

stripe-webhook Edge Function: Receives Stripe webhook events at a public endpoint. Validates the Stripe webhook signature using the signing secret from Supabase Vault before processing. Handles: payment_intent.succeeded (recovery path — confirms booking if not already confirmed), payment_intent.payment_failed (moves booking to payment_failed), transfer.failed (logs error, sends operator alert via Resend), account.updated (updates host's Stripe verification status on the profile). Uses the stripe_webhook_events table for idempotency — if the Stripe event ID already exists, short-circuits and returns success.

ccpa-scrubber Edge Function (scheduled): Triggered by pg_cron nightly at 02:00 UTC. Finds users with deletion_status = pending and deletion_requested_at older than 30 days. Scrubs all PII fields (name, email, phone, avatar URL) on the profile row, updating deletion_status = completed. Processes users in batches of 50 with a 100ms sleep between batches. Each user scrub is committed individually — a failure on one user does not roll back others. Also handles CCPA data export requests: assembles the user's data from the database, writes a JSON export file to Supabase Storage, and emails a time-limited signed URL to the user via Resend.

host-onboarding Edge Function: Generates a Stripe Connect hosted onboarding URL for a user becoming a host. On Stripe redirect callback, exchanges the authorization code for a connected account ID and stores it on the profile. Sets host_onboarding_status = pending_verification.

admin Edge Function: Internal endpoints gated by a role check against the admin or support claim in the JWT. Capabilities: view all users, listings, and bookings (using service-role database access); manually trigger a payout retry for a payout_status = failed booking; flag a listing as inactive; view hosts by verification status. Support agents are blocked from write operations by an explicit role check within the function.

listing-photo-confirm Edge Function: After a host uploads a photo directly to Supabase Storage via a signed upload URL, this function confirms the upload by creating a row in listing_photos with the storage object path and display order. Validates that the storage object exists before writing the database row. Supabase Storage lifecycle rules handle orphaned objects (uploaded but never confirmed) after 24 hours via an object expiry policy on the pending/ path prefix.

booking-expire Edge Function (scheduled): Triggered by pg_cron every 15 minutes. Queries for pending_approval bookings older than 24 hours and cancels them — cancels the Stripe PaymentIntent and moves the booking to declined. Guest receives an automated email via Resend.

Row-Level Security Policies

RLS is enabled on every table. The primary policies are:

profiles: Users can SELECT and UPDATE their own row (auth.uid() = id). Service role bypasses for admin and scrubber access. No user can DELETE their own profile row — deletion is handled by the CCPA scrubber Edge Function via service role.

listings: Authenticated users can SELECT active listings (status = active). Hosts can SELECT, INSERT, UPDATE, and DELETE their own listings (auth.uid() = host_user_id). DELETE is a soft delete (status set to inactive) enforced by an INSTEAD OF DELETE trigger rather than a true row deletion.

bookings: Guests can SELECT their own bookings (auth.uid() = guest_user_id). Hosts can SELECT bookings for their own listings (auth.uid() = listing.host_user_id via a join policy). Guests can INSERT bookings only through the booking-create Edge Function (which uses service role) — direct PostgREST INSERT on bookings is denied for all roles to force the transactional Edge Function path. Updates are only via Edge Functions (service role).

listing_photos, listing_amenities, pricing_overrides, availability_rules: Hosts can SELECT, INSERT, UPDATE, DELETE their own records (linked through listing_id → listings.host_user_id). Authenticated users can SELECT photos and amenities for active listings.

stripe_webhook_events, ccpa_requests: No direct client access. Service role only.

Lovable Scaffolding Approach

The frontend is built component-by-component in Lovable. Each prompt targets a single screen or feature slice. Before any prompt that generates database queries or RLS-sensitive operations, Plan mode is used to review the proposed data access pattern and confirm it aligns with the RLS policy. The Phased Lovable Prompt Pack (the output format of this architecture) structures prompts in dependency order: auth flows first, then listing browsing, then booking, then host management, then admin.

Data Architecture

Core Tables (Data Contracts)

Entity	Attribute	Type / Constraints	Business Rule & Access Control
profiles	roles	Array	RLS: Users can SELECT/UPDATE own row. Deletion restricted to CCPA scrubber.
listings	status	Enum (draft, active, inactive)	RLS: Public read for active. Host INSERT/UPDATE. Soft-delete via INSTEAD OF DELETE trigger.
availability_rules	rule_type	Enum	Validates blocked ranges, min stay, and check-in days before booking execution.
bookings	date_range	tsrange	GiST Exclusion Constraint (&&): Structurally rejects overlapping time blocks.
bookings	status	Enum	State machine: pending → confirmed → cancelled_by_host. RLS: Service-role execution only.
stripe_webhook_events	stripe_event_id	String (Unique)	Idempotency log to prevent duplicate processing. Purged 90 days post-creation.
ccpa_requests	request_type	Enum (deletion, export)	Audit log for data lifecycle. Triggered by client, executed by nightly Edge Function.

Atomic Availability Lock

The double-booking prevention mechanism is enforced directly at the database layer using a GiST Exclusion Constraint:

CREATE EXTENSION IF NOT EXISTS btree_gist;
ALTER TABLE bookings ADD CONSTRAINT no_overlapping_bookings
  EXCLUDE USING gist (listing_id WITH =, tsrange(check_in_date, check_out_date) WITH &&)
  WHERE (status IN ('pending', 'pending_approval', 'confirmed'));

This structural invariant guarantees that concurrent booking attempts for the same dates mathematically cannot succeed, regardless of whether they originate from an Edge Function, PostgREST, or the Supabase dashboard.

### Data Lifecycle

Bookings are never hard-deleted. Declined and expired bookings are retained for audit. Confirmed bookings are retained for 3 years per policy; beyond 3 years, a yearly Edge Function exports anonymized booking records to Supabase Storage and deletes them from the database. Refresh tokens are managed by GoTrue natively. Stripe webhook event log rows older than 90 days are purged nightly. Listing photos whose Storage objects are orphaned are cleaned up by a Supabase Storage lifecycle rule (24-hour expiry on the `pending/` prefix). User PII is scrubbed 30 days after a deletion request, but booking rows are retained with a null user reference.

### Indexing Strategy
- `bookings(listing_id, check_in_date, check_out_date)` — overlap queries and availability checks
- `bookings(guest_user_id)` — guest booking history
- `listings(location, max_guests, base_price_cents)` — search filter composite
- `availability_rules(listing_id)` — rule lookup per listing
- `pricing_overrides(listing_id)` — override lookup per listing
- `stripe_webhook_events(stripe_event_id)` — unique index for idempotency
- `profiles(deletion_status)` — nightly scrubber query

### Backup and Retention
Supabase managed backups: daily backups with 7-day retention on the Pro tier, point-in-time recovery (PITR) enabled on the Production project. Dev and staging projects use daily backups only. Supabase Storage versioning is not natively available — photo objects are treated as immutable (delete and re-upload for replacement). Database data is retained for 3 years per policy; archived booking rows past 3 years are exported to a Supabase Storage archive bucket and then deleted from the database.

## Integration Architecture

### Stripe Connect

**Purpose:** Payment processing, escrow, host onboarding, fund routing, and refunds.

**Auth:** The platform's Stripe secret key and webhook signing secret are stored in Supabase Vault (encrypted at rest) and retrieved by Edge Functions at invocation time. Host connected accounts are created via Stripe Connect's hosted onboarding (OAuth flow): the `host-onboarding` Edge Function generates the Stripe Connect onboarding URL, the host completes onboarding on Stripe's hosted page, and Stripe redirects back to the platform. The Edge Function exchanges the authorization code for the connected account ID and stores it on the profile.

**Operations performed:**
- Create and capture a PaymentIntent (charge guest at booking, with application fee for service fee capture) — performed by `booking-create`
- Create a PaymentIntent in `requires_capture` state (manual approval flow) — performed by `booking-create`
- Capture a PaymentIntent (on host approval) — performed by `booking-approve`
- Cancel a PaymentIntent (on decline or expiry) — performed by `booking-decline` and `booking-expire`
- Create a Refund (on cancellation) — performed by `booking-cancel`
- Create a Transfer (host payout post check-in) — performed by `payout-scheduler`
- Create a Connect onboarding link — performed by `host-onboarding`
- Retrieve Account status — performed by `stripe-webhook` on `account.updated` events

**Failure behavior:** If PaymentIntent creation fails during booking, the Edge Function calls the compensating Postgres function to cancel the pending booking row and returns a payment failure message to the guest. If a Transfer fails during payout, the `payout-scheduler` marks `payout_status = failed`, logs the error, and sends an operator alert via Resend. The operator retries via the `admin` Edge Function's payout retry endpoint. Stripe webhook delivery failures: Stripe retries for up to 3 days. Processing errors return a non-2xx response to Stripe to trigger retry. Stripe Connect onboarding failures leave the host in `host_onboarding_status = pending_verification` — they cannot publish a listing until `account.updated` confirms verification.

### Resend

**Purpose:** Transactional email — booking confirmation, cancellation, payout notification, CCPA data export link, host onboarding follow-up.

**Auth:** Resend API key stored in Supabase Vault, retrieved by Edge Functions at invocation time. Emails sent from a verified domain configured in Resend.

**Failure behavior:** Resend errors are logged by the Edge Function. Email delivery failure does not roll back a booking or payout — it is a non-critical side effect. Persistent failures are surfaced via the Edge Function's error log in the Supabase dashboard.

### Supabase Storage

**Purpose:** Listing photo storage and CCPA data export file staging.

**Auth:** Hosts generate a signed upload URL via the Supabase JS client (authenticated) pointing to a `pending/{listing_id}/{uuid}` path. The `listing-photo-confirm` Edge Function validates the upload and moves the object to the `photos/{listing_id}/{uuid}` path. The Storage bucket is private. The React frontend retrieves photos via signed read URLs generated by the Supabase JS client for authenticated users, or via public bucket policy for listing-detail pages accessible to unauthenticated browsers (search results). CCPA export files are written by the `ccpa-scrubber` Edge Function using the service-role client and emailed as time-limited signed URLs (1-hour expiry) via Resend.

**Failure behavior:** Signed URL generation is a local Supabase SDK operation and cannot fail in isolation. If the client upload fails, no database record is written — the client retries the upload independently. Orphaned `pending/` objects expire via Supabase Storage lifecycle policy after 24 hours.

### Supabase Vault

**Purpose:** Secrets storage — Stripe secret key, Stripe webhook signing secret, Resend API key, service fee percentage (default `12`).

**Auth:** Edge Functions access Vault secrets via the Supabase service-role client's Vault API. No secrets are stored in Edge Function environment variables or source code.

### pg_cron (Supabase Scheduled Jobs)

**Purpose:** Triggers the `payout-scheduler` (hourly), `booking-expire` (every 15 minutes), and `ccpa-scrubber` (nightly at 02:00 UTC) Edge Functions on their respective schedules.

**Auth:** pg_cron runs within the PostgreSQL instance with superuser privileges. It invokes Edge Functions via an authenticated HTTP call using the service-role key.

**Failure behavior:** If a scheduled invocation fails, pg_cron logs the failure in the `cron.job_run_details` table. Failed runs do not auto-retry — the next scheduled run picks up where the last one left off. Idempotency in each scheduled function ensures re-runs are safe.

## Security Architecture

### Input Validation
All Edge Function request bodies are validated using Zod schemas (v3.23) before reaching any business logic. Validation rejects unexpected fields, enforces type constraints, sanitizes string inputs (trim whitespace, enforce max lengths), and checks date range logic (check-out must be after check-in, max stay 365 nights) before any database operation. Validation errors return a structured error response without exposing internal schema details. PostgREST requests are validated implicitly by column type constraints and RLS policy predicates.

### Authentication and Authorization
GoTrue issues JWTs on login. The Supabase JS client attaches the JWT to every request (PostgREST and Edge Functions). Edge Functions validate the JWT using the Supabase JWT secret before performing any user-scoped operation. RLS policies enforce row-level authorization at the database layer for all PostgREST operations — a misconfigured application-layer check cannot bypass RLS. Role-based access for admin/support operations is enforced inside the `admin` Edge Function by checking the `roles` array on the caller's profile, fetched via service-role client.

### Row-Level Security as the Primary Isolation Boundary
Every table in the `public` schema has `ALTER TABLE ... ENABLE ROW LEVEL SECURITY`. Policies are defined per operation per table. The service-role key (used only in Edge Functions) bypasses RLS — this is intentional and necessary for privileged operations (payout, scrubbing, webhook processing). The anon key (used by unauthenticated browser sessions) has access only to public listing data via explicit SELECT policies on the `listings` and `listing_photos` tables.

### Secrets Management
No secrets in source code, Edge Function environment variables, or Lovable's project configuration. All secrets are stored in Supabase Vault. Edge Functions retrieve secrets at invocation time via the Vault API. The service-role key (used to bootstrap Vault access) is stored only in GitHub Actions secrets and the Supabase project settings — never committed to the repository.

### PCI DSS (SAQ A)
No card data touches any Edge Function or database table. Stripe.js on the React frontend collects card details in Stripe-hosted iframes. The backend handles only Stripe entity references (PaymentIntent IDs, Transfer IDs). The `stripe-webhook` Edge Function validates the Stripe signature before processing any event. This keeps the platform in SAQ A scope.

### CCPA
Data deletion is a first-class flow with a 30-day grace period. The `do_not_sell` flag is enforced by the Edge Function before any data is included in a CCPA export. User data export (right-to-know) is provided via a secure, time-limited (1-hour) Supabase Storage signed URL emailed to the verified address. All CCPA requests are logged in the `ccpa_requests` table for audit purposes.

### Web-Facing Security
- **HTTPS enforcement:** Supabase API endpoints and Edge Function URLs are HTTPS-only. The React SPA is hosted on a CDN (Lovable's default hosting or a custom domain via Netlify/Vercel) with HTTPS enforced and HTTP redirected.
- **CORS:** Edge Functions set explicit `Access-Control-Allow-Origin` headers matching the production frontend domain. No wildcard CORS in staging or production.
- **Security headers:** The frontend hosting layer (Lovable's CDN / Netlify / Vercel) is configured with `Strict-Transport-Security`, `X-Content-Type-Options: nosniff`, `X-Frame-Options: DENY`, `Content-Security-Policy` (restricting scripts to the CDN origin and the Stripe.js domain `js.stripe.com`), and `Referrer-Policy: strict-origin-when-cross-origin`.
- **SQL injection:** All database queries in Edge Functions use the Supabase client's parameterized query interface. No string interpolation in SQL. PostgREST uses parameterized queries natively.
- **Rate limiting:** Supabase's built-in rate limiting applies to auth endpoints. Edge Functions implement request-level rate limiting by checking a simple counter in the database for high-sensitivity endpoints (booking creation: maximum 10 attempts per user per minute).

### Error Handling Policy
Internal error details (Postgres error codes, Stripe error objects, stack traces) are logged server-side via Supabase Edge Function logs and never returned to the client. Client-facing error responses contain only a human-readable message and a stable error code string. The Lovable-generated frontend maps error codes to appropriate UI states.

### Lovable Plan Mode for RLS
Any Lovable prompt that involves querying a table, inserting data, or changing a data access pattern must be run through Plan mode first. Plan mode surfaces the proposed data access pattern, which is reviewed against the RLS policy for that table before execution. This prevents Lovable from generating client-side queries that silently return empty results due to RLS denials — a failure mode that is difficult to debug after the fact.

## Deployment Architecture

### Environments
Three Supabase projects: `homestay-dev`, `homestay-staging`, `homestay-prod`. Each project is an isolated Supabase environment with its own PostgreSQL database, auth configuration, Storage buckets, Edge Functions, and Vault secrets. Environment-specific configuration (Stripe keys, Resend API key, service fee percentage) is stored in each project's Vault independently.

### Infrastructure as Code
Supabase database schema, RLS policies, pg_cron jobs, and Edge Functions are managed via the **Supabase CLI** (v1.x). Migration files define all schema changes. The CLI's `supabase db push` command applies migrations to the target environment. Edge Functions are deployed via `supabase functions deploy`. No separate IaC tool (no SAM, no Terraform) — the Supabase CLI is the single source of truth for all infrastructure.

### CI/CD Pipeline (GitHub Actions)
Three workflows:

**PR workflow:** Runs on every pull request to `main`. Steps: install Supabase CLI, run `supabase db lint` on migration files, run unit tests for Edge Functions (Deno test runner), run integration tests against a local Supabase instance (via `supabase start` in the Actions runner). PRs cannot be merged if this workflow fails.

**Staging deploy:** Runs on every merge to `main`. Steps: `supabase db push` to `homestay-staging` → `supabase functions deploy` to staging → run smoke tests against the staging Supabase project URL → notify on success or failure (Resend email to the dev team).

**Production deploy:** Manual trigger only (`workflow_dispatch`). Steps: `supabase db push` to `homestay-prod` → `supabase functions deploy` to production → run smoke tests → notify team. Requires a GitHub environment protection rule with at least one required reviewer approval before the deploy step executes.

### Frontend Deployment
The React SPA is deployed from the Lovable project to Lovable's built-in hosting for dev and staging iterations. For production, the built React app is deployed to Netlify (or Vercel) via GitHub Actions on merge to `main`, with the production Supabase project URL and anon key injected as build-time environment variables. Security headers are configured via the platform's `_headers` file (Netlify) or `vercel.json`.

### Scaling
At the target scale (hundreds of users), Supabase's Pro tier PostgreSQL instance and Edge Function capacity are more than sufficient. Edge Functions scale automatically on Supabase's infrastructure. If traffic grows, the PostgreSQL instance can be scaled up via the Supabase dashboard without architectural changes. Supabase Storage handles frontend asset and photo delivery via CDN independently.

## Technology Stack

| Component | Technology | Version | Rationale |
|---|---|---|---|
| Frontend framework | React | 18.3 | Lovable's native scaffold target; mature ecosystem |
| Frontend scaffolding | Lovable | Current (2025) | Constraint; component-by-component AI scaffolding with Plan mode for RLS-sensitive changes |
| Frontend hosting | Lovable built-in / Netlify | N/A | Lovable for iteration; Netlify for production with custom domain and security headers |
| Backend platform | Supabase | Pro tier | Replaces entire AWS stack; provides PostgreSQL, auth, API, Edge Functions, Storage, Vault |
| Database | PostgreSQL | 15 (managed by Supabase) | ACID transactions, serializable isolation for booking lock, native Supabase integration |
| Auth | Supabase GoTrue | v2 (Supabase-managed) | Native Supabase auth; JWT issuance, refresh token management, email verification |
| Auto-generated API | Supabase PostgREST | v12 (Supabase-managed) | REST over PostgreSQL tables; gated by RLS policies |
| Edge Functions runtime | Deno | 1.x (Supabase-managed) | Supabase's native Edge Function runtime; TypeScript support, V8 isolates |
| Edge Function language | TypeScript | 5.x | Type safety across functions and shared utilities |
| Validation library | Zod | 3.23 | Runtime schema validation with TypeScript inference; used in Edge Functions |
| Object storage | Supabase Storage | N/A (Supabase-managed) | Photo storage, CCPA exports; integrated signed URL and lifecycle policies |
| Secrets management | Supabase Vault | N/A (Supabase-managed) | Encrypted secrets storage; replaces SSM Parameter Store |
| Scheduled jobs | pg_cron | 1.4 (Supabase extension) | Cron-based scheduling within PostgreSQL; triggers Edge Functions on schedule |
| Payments | Stripe Connect | API version 2024-06-20 | Constraint; handles PCI scope, escrow, and host payouts |
| Transactional email | Resend | v2 API | Simple API, generous free tier, better DX than SES at early stage |
| IaC / Deployment | Supabase CLI | 1.x | Schema migrations, RLS policies, Edge Function deployment; replaces AWS SAM |
| CI/CD | GitHub Actions | N/A | Migration linting, Edge Function tests, environment deploy pipeline |
| Logging | Supabase Edge Function Logs + Supabase Dashboard | N/A | Built-in structured logging for Edge Functions; replaces CloudWatch |
| Alerting | Resend email to operator + Supabase Log Drains (optional) | N/A | Operator alerts for payout failures and critical errors via Resend |

## Key Architectural Decisions

**ADR-1: Supabase as the full backend platform, replacing AWS**
Decided to use Supabase (PostgreSQL, GoTrue, PostgREST, Edge Functions, Storage, Vault) as the complete backend, replacing Lambda, API Gateway, RDS Proxy, SES, CloudWatch, and SSM Parameter Store. Alternatives: retain the AWS stack (higher infrastructure overhead, disproportiona-3te to scale), use Firebase (no relational database, poor fit for booking constraints), use a custom Node.js server (contradicts serverless constraint). Supabase provides all required capabilities with a dramatically smaller operational surface for an early-stage product, and aligns with Lovable's native integration.

**ADR-2: RLS policies as the primary data isolation mechanism**
Decided to use Supabase Row-Level Security policies to enforce data access control at the database layer for all PostgREST operations. Alternatives: application-layer role checks only (weaker — a bug in the application layer exposes data), no RLS (all data accessible to any authenticated user — unacceptable). RLS enforces isolation even if Edge Function or frontend logic is incorrect, providing a defense-in-depth guarantee. The tradeoff is that RLS policy bugs silently return empty results rather than errors, which is mitigated by Lovable's Plan mode review before any RLS-sensitive code generation.

**ADR-3: PostgreSQL GiST exclusion constraint for atomic booking lock**
Decided to implement double-booking prevention using a GiST exclusion constraint with the `&&` (overlap) operator. Alternatives: serializable transactions via RPC (unnecessary complexity), unique partial indexes (cannot accurately model date range overlaps). Supabase natively supports the `btree_gist` extension, making this the most robust, database-level defense against race conditions.

**ADR-4: Edge Functions for all transactional and privileged operations**
Decided to route all operations requiring multi-step transactions, Stripe API calls, or service-role database access through Edge Functions, while using PostgREST directly (via the Supabase JS client) for simple reads covered by RLS. Alternatives: all operations through Edge Functions (unnecessary overhead for simple reads), all operations through PostgREST (no server-side transaction boundary, Stripe calls exposed to client). This hybrid approach uses the right tool for each operation.

**ADR-5: Lovable with Plan mode for RLS-sensitive scaffolding**
Decided to use Lovable in Plan mode before executing any prompt that generates database queries or data access patterns. This is a workflow constraint, not a code constraint. Alternatives: execute all Lovable prompts without review (high risk of silent RLS denials breaking data access in ways that are hard to debug), avoid Lovable for data-access components (defeats the purpose of the tooling). Plan mode review adds a small amount of friction in exchange for catching RLS mismatches before they become production bugs.

**ADR-6: Resend for transactional email, replacing SES**
Decided to use Resend for all transactional email. Alternatives: AWS SES (no longer available — AWS dependency removed per constraint), SendGrid (higher cost, more complex setup). Resend offers a simple REST API, a generous free tier, and easy domain verification — appropriate for the scale and operational simplicity goals of this project.

**ADR-7: CCPA deletion as PII scrub, not row deletion**
Retained from the prior architecture. Booking rows are retained with PII scrubbed from the profile record rather than cascading-deleting booking rows. Alternatives: hard delete all user data (breaks 3-year financial retention requirement). PII scrub satisfies the CCPA deletion right while preserving the financial records the platform is legally required to retain.

**ADR-8: Phased Lovable Prompt Pack as the downstream output format**
The downstream deliverable from this ADD is a Phased Lovable Prompt Pack — a sequenced set of Lovable prompts organized by feature dependency. Phase 1: auth and profile. Phase 2: listing browse and search. Phase 3: host listing management. Phase 4: booking flow and payments. Phase 5: admin and CCPA flows. Each prompt targets a single component or screen and is preceded by a Plan mode step for any RLS-sensitive operation.

## Error Handling & Failure Modes

### GoTrue Auth
- **Invalid credentials:** GoTrue returns a generic "invalid login credentials" error. The frontend displays this without distinguishing between "user not found" and "wrong password" to prevent user enumeration.
- **Expired JWT:** Supabase JS client automatically attempts token refresh. If refresh fails (revoked refresh token, user deleted), the client clears the session and the user is redirected to login.
- **Brute force:** GoTrue has built-in rate limiting on auth endpoints. Additionally, the `login_attempts` table tracks consecutive failures — after 10 failures within 15 minutes for the same email, the `auth` Edge Function (if needed to supplement GoTrue) soft-locks the account for 15 minutes.

### `booking-create` Edge Function — Availability Lock
- **Overlap detected by serializable transaction:** The `attempt_booking` Postgres function raises an application exception. The Edge Function maps this to a "these dates are no longer available — please select different dates" user-facing error. No booking row is written.
- **Stripe PaymentIntent creation fails:** The Edge Function calls the compensating Postgres function to cancel the `pending` booking row. Guest receives a payment failure message. No confirmed booking exists.
- **Stripe succeeds but database confirm update fails (partial state):** The `stripe-webhook` Edge Function receives `payment_intent.succeeded`. If no `confirmed` booking row exists for that PaymentIntent ID, it creates one in `confirmed` status as the recovery path. A log entry flags this anomaly for operator review.
- **Manual approval timeout:** The `booking-expire` Edge Function (runs every 15 minutes) detects `pending_approval` bookings older than 24 hours, cancels the Stripe PaymentIntent, and moves the booking to `declined`. Guest receives an automated email.

### `stripe-webhook` Edge Function
- **Invalid webhook signature:** Rejected immediately. A log entry is written. A success response is returned to Stripe (to avoid Stripe treating a non-Stripe request as a delivery failure and retrying).
- **Duplicate event:** The `stripe_webhook_events` idempotency table catches this. Returns success without reprocessing.
- **Processing failure (database error):** Returns a non-2xx response to Stripe. Stripe retries for up to 3 days. Persistent failures surface in the Supabase Edge Function logs and trigger an operator alert via Resend.

### `payout-scheduler` Edge Function
- **Stripe Transfer failure:** Marks `payout_status = failed` on the booking. Logs the Stripe error. Sends an operator alert via Resend. Does not auto-retry — operator uses the `admin` Edge Function's payout retry endpoint. The next scheduled run skips bookings already in `payout_status = failed`.
- **pg_cron invocation failure:** The failure is logged in `cron.job_run_details`. The next scheduled run (one hour later) picks up all pending payouts. Idempotency ensures no double-payouts.

### `ccpa-scrubber` Edge Function
- **Scrub failure mid-batch:** Each user scrub is committed individually. A failure on user N does not roll back users before it. The failed user's `deletion_status` remains `pending` and is retried on the next nightly run.
- **Storage deletion failure (avatar photo):** Logged, does not block database PII scrubbing. The orphaned Storage object is eventually cleaned by the Storage lifecycle policy.

### RLS Policy Misconfiguration
- **Silent data denial:** A missing or incorrect RLS policy causes PostgREST to return an empty result set rather than an error. This is the primary RLS failure mode. Mitigation: Lovable Plan mode review before executing any data-access prompt; integration tests in CI that assert specific rows are returned for specific authenticated users.
- **Overly permissive policy:** A policy that grants too broad access exposes other users' data. Mitigation: RLS policies are reviewed in PR workflow via `supabase db lint`; integration tests assert that user A cannot read user B's bookings.

### Supabase Platform Availability
- **Supabase service degradation:** Edge Functions and PostgREST are unavailable. The frontend shows a "service temporarily unavailable" state. No data is corrupted — in-flight database transactions are rolled back by PostgreSQL on connection loss. Supabase's managed platform targets 99.9% uptime on the Pro tier, consistent with the under-1-hour unplanned downtime target.
- **Database connection limits:** Supabase's connection pooler (PgBouncer, built into the platform) manages connections from Edge Functions. No equivalent to RDS Proxy connection exhaustion is expected at the target scale.

### Search
- **No results:** Empty result set returned with a 200 response. Frontend renders a "no listings found" state.
- **Invalid date inputs:** Rejected at Zod validation layer in the Edge Function (if going through an Edge Function) or by PostgREST type constraints (if using the auto-generated API directly).

### Pre-Launch Verification
- **Stripe:** Test using Stripe test mode. Verify webhook delivery using the Stripe Dashboard webhook event simulator. Verify idempotency by replaying events. Test the PaymentIntent capture → database failure → webhook recovery path in staging.
- **Resend:** Test with verified recipient email in dev/staging. Verify domain in production.
- **RLS policies:** Run integration tests in CI that authenticate as specific users and assert exact row visibility. Verify that unauthenticated users can only see active listing data.
- **Atomic lock:** Integration test that fires two concurrent `booking-create` Edge Function calls for the same listing/dates and asserts exactly one succeeds.
- **CCPA flow:** Execute an end-to-end deletion request in staging and verify PII fields are scrubbed and booking rows are retained.

## Risks and Mitigations

**Risk 1: RLS policy misconfiguration causes silent data leaks or access denials**
Component: Supabase RLS policies, PostgREST API layer.
Impact: A too-permissive policy exposes other users' booking or profile data; a too-restrictive policy silently breaks a feature (e.g., hosts cannot see their own bookings). Both are high-severity and difficult to debug after launch.
Mitigation: Lovable Plan mode review before every data-access prompt. `supabase db lint` in the PR CI pipeline. Integration tests in CI that assert specific row visibility per authenticated user role. RLS policies are the first thing reviewed in any debugging session for a "data not showing" issue.

**Risk 2: Serializable transaction contention under concurrent bookings**
Component: `booking-create` Edge Function, `attempt_booking` Postgres function.
Impact: High concurrency on the same listing causes serialization failures (deadlocks or serialization errors), resulting in booking errors for users. At hundreds-of-users scale this is unlikely but possible for a popular listing.
Mitigation: The `attempt_booking` function retries the serializable transaction up to 3 times with exponential backoff before returning a conflict error. The Edge Function maps repeated conflicts to a "dates unavailable" message rather than a generic error. Load tested in staging before launch.

**Risk 3: Stripe PaymentIntent captured but booking confirmation update fails**
Component: `booking-create` Edge Function, `stripe-webhook` Edge Function.
Impact: Guest is charged but has no confirmed booking in the system. High-severity customer experience failure.
Mitigation: The `stripe-webhook` recovery path creates the booking row on receipt of `payment_intent.succeeded` if no confirmed row exists. A log entry flags the anomaly. This path is explicitly integration-tested in staging by simulating a database failure after Stripe capture.

**Risk 4: Lovable-generated code bypasses Edge Functions and writes directly to tables**
Component: Lovable scaffolding, booking and payment flows.
Impact: A Lovable prompt generates a client-side insert into the `bookings` table directly via PostgREST, bypassing the transactional Edge Function and the atomic lock. This allows double bookings.
Mitigation: The RLS policy on `bookings` explicitly denies direct INSERT from any non-service-role client. Any direct client insert fails at the database level regardless of what Lovable generates. The Phased Lovable Prompt Pack explicitly instructs that booking creation prompts target the Edge Function invocation path, not a table insert.

**Risk 5: pg_cron scheduled job reliability for payout and expiry flows**
Component: `payout-scheduler`, `booking-expire`, pg_cron.
Impact: A pg_cron job silently stops running (Supabase extension issue, misconfigured job) and payouts are delayed indefinitely or expired bookings are never cancelled, keeping their availability locks held.
Mitigation: Each scheduled Edge Function logs its run start, row count processed, and completion status. A Resend alert is sent to the operator if a scheduled function processes zero rows when rows are expected (e.g., payout scheduler finds no pending payouts after 3 consecutive hours when confirmed bookings exist with past check-in dates). The `cron.job_run_details` table is checked weekly as part of operational monitoring.

Implementation Plan

Environment Setup

What you'll need

Before any code gets written, there are a handful of accounts to create and settings to configure. Getting these right upfront means your coding agent has everything it needs and won't get blocked mid-build.

Accounts to create

• Supabase — supabase.com — Your entire backend: database, authentication, file storage, server-side functions, and secrets management. You'll create three separate projects here (dev, staging, and production).
• Stripe — stripe.com — Handles all money movement: charging guests, holding funds in escrow, paying out hosts, and processing refunds. You'll need to enable Stripe Connect for the host payout feature.
• Resend — resend.com — Sends transactional emails to guests and hosts (booking confirmations, cancellation notices, payout alerts, and CCPA export links).
• GitHub — github.com — Hosts your code and runs the automated pipelines that deploy changes to staging and production.
• Netlify — netlify.com — Hosts the production frontend with the security settings your platform requires. (Lovable handles dev and staging frontend hosting directly.)
• Lovable — lovable.dev — Where you'll build the frontend, screen by screen. Connected to your GitHub repo so changes stay in sync.

Before you start

Supabase:

• Create three Supabase projects: homestay-dev, homestay-staging, and homestay-prod. Keep each project's URL and API keys handy — you'll need them for each environment.
• In each project, go to the database extensions panel and enable btree_gist and pg_cron. These are needed for the double-booking lock and the scheduled background jobs.
• In each project, enable point-in-time recovery (PITR) on homestay-prod. Daily backups are sufficient for dev and staging.
• In each project, go to Authentication → Settings and set the Site URL to the correct frontend URL for that environment (Lovable's URL for dev/staging, your custom domain for production).
• In each project, enable email confirmation for new user signups under Authentication → Email.
• In each project, create two Storage buckets: one named photos (public read) and one named ccpa-exports (private). Set a lifecycle rule on the photos bucket so objects under the pending/ path prefix expire after 24 hours.
• In each project's Vault, create secrets for: your Stripe secret key, your Stripe webhook signing secret, your Resend API key, and the service fee percentage (value: 12). Do this for each environment separately using that environment's Stripe and Resend credentials.

Stripe:

• Enable Stripe Connect in your Stripe dashboard under Settings → Connect. Choose the "Standard" account type for host onboarding.
• In test mode, create a webhook endpoint pointing to your homestay-staging Edge Function URL for stripe-webhook. Add these events: payment_intent.succeeded, payment_intent.payment_failed, transfer.failed, account.updated. Copy the signing secret into your staging Supabase Vault.
• Repeat the webhook setup in live mode pointing to your homestay-prod Edge Function URL, and copy the live signing secret into your production Vault.

Resend:

• Add and verify the domain you'll send emails from. This involves adding a few DNS records your domain registrar — Resend walks you through it step by step.
• Create an API key and store it in each Supabase project's Vault.

GitHub:

• Create a repository named homestay.
• Under Settings → Secrets and Variables → Actions, add secrets for each environment: the Supabase project URL, the Supabase service-role key, and the Supabase access token (used by the CLI to deploy). Name them with environment prefixes, e.g. SUPABASE_DEV_URL, SUPABASE_STAGING_URL, SUPABASE_PROD_URL, and so on.
• Under Settings → Environments, create a production environment and add at least one required reviewer — this person must approve before any production deploy runs.

Netlify:

• Connect your GitHub repo to a new Netlify site.
• Set the build command to whatever Lovable's exported React app uses, and the publish directory to dist.
• Add a _headers file to the project root (your coding agent will create this) — Netlify uses it to set the security response headers.

Lovable:

• Connect your Lovable project to the homestay GitHub repo so every Lovable change commits to a branch.
• In Lovable's Supabase integration settings, connect to your homestay-dev project for local iteration.

Environment variables

Your coding agent will need these available during implementation. For Edge Functions, these are retrieved from Supabase Vault at runtime — not stored as environment variables in the function code. For the CI/CD pipeline and local CLI use, these go in GitHub Actions secrets or your local environment.

Supabase (per environment — repeat for dev, staging, prod):

• SUPABASE_URL — Supabase dashboard → Project Settings → API → Project URL
• SUPABASE_ANON_KEY — Supabase dashboard → Project Settings → API → Project API Keys → anon public
• SUPABASE_SERVICE_ROLE_KEY — Supabase dashboard → Project Settings → API → Project API Keys → service_role (keep this secret — never expose it in frontend code)
• SUPABASE_ACCESS_TOKEN — Supabase dashboard → Account → Access Tokens (used by the CLI in GitHub Actions to deploy)
• SUPABASE_JWT_SECRET — Supabase dashboard → Project Settings → API → JWT Settings → JWT Secret

Stripe (stored in Supabase Vault, named exactly as listed):

• STRIPE_SECRET_KEY — Stripe dashboard → Developers → API Keys → Secret key
• STRIPE_WEBHOOK_SIGNING_SECRET — Stripe dashboard → Developers → Webhooks → your endpoint → Signing secret
• STRIPE_CONNECT_CLIENT_ID — Stripe dashboard → Settings → Connect → Client ID

Resend (stored in Supabase Vault):

• RESEND_API_KEY — Resend dashboard → API Keys → your key

Application config (stored in Supabase Vault):

• SERVICE_FEE_PERCENTAGE — Set to 12 (the guest-side service fee percentage, as a plain number string)
• OPERATOR_ALERT_EMAIL — The email address that receives payout failure and critical error alerts

Frontend build-time (GitHub Actions → Netlify for production):

• VITE_SUPABASE_URL — Same value as SUPABASE_URL for the production project
• VITE_SUPABASE_ANON_KEY — Same value as SUPABASE_ANON_KEY for the production project
• VITE_STRIPE_PUBLISHABLE_KEY — Stripe dashboard → Developers → API Keys → Publishable key

---

Phases

Phase 1: Database Schema, RLS Policies, and Auth Foundation

This phase lays the complete data foundation for the entire platform. Every table, index, constraint, RLS policy, and database trigger is defined here in migration files — all subsequent phases consume these contracts without changing them. Auth is wired up through Supabase GoTrue, and the profile auto-creation trigger is established so every new user immediately has a profiles row.

Contracts

Migration files (executed in order)

supabase/migrations/001_extensions.sql Enables btree_gist and pg_cron. No exported interface — prerequisite for subsequent migrations.

supabase/migrations/002_enums.sql Defines all PostgreSQL enum types used across tables:

• listing_status_enum: values draft, active, inactive
• booking_status_enum: values pending, pending_approval, confirmed, declined, cancelled_by_host, cancelled_by_guest, payment_failed
• payout_status_enum: values pending, completed, failed
• availability_rule_type_enum: values blocked_range, min_stay, checkin_days
• cancellation_policy_enum: values flexible, moderate, strict
• host_onboarding_status_enum: values not_started, pending_verification, verified
• deletion_status_enum: values none, pending, completed
• ccpa_request_type_enum: values deletion, export
• user_role_enum: values guest, host, admin, support

supabase/migrations/003_tables.sql Creates all tables. Column definitions:

profiles:

• id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE
• full_name text
• email text
• phone text
• avatar_url text
• roles user_role_enum[] NOT NULL DEFAULT '{guest}'
• stripe_customer_id text
• stripe_connect_account_id text
• host_onboarding_status host_onboarding_status_enum NOT NULL DEFAULT 'not_started'
• deletion_status deletion_status_enum NOT NULL DEFAULT 'none'
• deletion_requested_at timestamptz
• do_not_sell boolean NOT NULL DEFAULT false
• created_at timestamptz NOT NULL DEFAULT now()
• updated_at timestamptz NOT NULL DEFAULT now()

listings:

• id uuid PRIMARY KEY DEFAULT gen_random_uuid()
• host_user_id uuid NOT NULL REFERENCES profiles(id)
• title text NOT NULL
• description text
• location text NOT NULL
• latitude numeric(9,6)
• longitude numeric(9,6)
• max_guests integer NOT NULL
• base_price_cents integer NOT NULL
• cancellation_policy cancellation_policy_enum NOT NULL DEFAULT 'moderate'
• instant_book boolean NOT NULL DEFAULT false
• house_rules text
• status listing_status_enum NOT NULL DEFAULT 'draft'
• created_at timestamptz NOT NULL DEFAULT now()
• updated_at timestamptz NOT NULL DEFAULT now()

listing_amenities:

• id uuid PRIMARY KEY DEFAULT gen_random_uuid()
• listing_id uuid NOT NULL REFERENCES listings(id) ON DELETE CASCADE
• amenity text NOT NULL

listing_photos:

• id uuid PRIMARY KEY DEFAULT gen_random_uuid()
• listing_id uuid NOT NULL REFERENCES listings(id) ON DELETE CASCADE
• storage_path text NOT NULL
• display_order integer NOT NULL DEFAULT 0
• created_at timestamptz NOT NULL DEFAULT now()

availability_rules:

• id uuid PRIMARY KEY DEFAULT gen_random_uuid()
• listing_id uuid NOT NULL REFERENCES listings(id) ON DELETE CASCADE
• rule_type availability_rule_type_enum NOT NULL
• blocked_start date
• blocked_end date
• min_stay_nights integer
• checkin_days_bitmask integer — bitmask where bit 0 = Sunday, bit 6 = Saturday
• created_at timestamptz NOT NULL DEFAULT now()

pricing_overrides:

• id uuid PRIMARY KEY DEFAULT gen_random_uuid()
• listing_id uuid NOT NULL REFERENCES listings(id) ON DELETE CASCADE
• override_type text NOT NULL — values: seasonal, weekend
• season_start date
• season_end date
• price_cents integer NOT NULL
• created_at timestamptz NOT NULL DEFAULT now()

bookings:

• id uuid PRIMARY KEY DEFAULT gen_random_uuid()
• listing_id uuid NOT NULL REFERENCES listings(id)
• guest_user_id uuid NOT NULL REFERENCES profiles(id)
• check_in_date date NOT NULL
• check_out_date date NOT NULL
• date_range tsrange GENERATED ALWAYS AS (tsrange(check_in_date::timestamp, check_out_date::timestamp)) STORED
• num_guests integer NOT NULL
• total_price_cents integer NOT NULL
• service_fee_cents integer NOT NULL
• status booking_status_enum NOT NULL DEFAULT 'pending'
• payout_status payout_status_enum NOT NULL DEFAULT 'pending'
• stripe_payment_intent_id text
• stripe_transfer_id text
• cancellation_reason text
• created_at timestamptz NOT NULL DEFAULT now()
• updated_at timestamptz NOT NULL DEFAULT now()

stripe_webhook_events:

• id uuid PRIMARY KEY DEFAULT gen_random_uuid()
• stripe_event_id text NOT NULL UNIQUE
• event_type text NOT NULL
• processed_at timestamptz NOT NULL DEFAULT now()

ccpa_requests:

• id uuid PRIMARY KEY DEFAULT gen_random_uuid()
• user_id uuid NOT NULL REFERENCES profiles(id)
• request_type ccpa_request_type_enum NOT NULL
• requested_at timestamptz NOT NULL DEFAULT now()
• completed_at timestamptz

supabase/migrations/004_constraints_and_indexes.sql Adds the GiST exclusion constraint and all indexes:

-- Atomic availability lock
ALTER TABLE bookings ADD CONSTRAINT no_overlapping_bookings
  EXCLUDE USING gist (
    listing_id WITH =,
    date_range WITH &&
  )
  WHERE (status IN ('pending', 'pending_approval', 'confirmed'));

-- Indexes
CREATE INDEX idx_bookings_listing_dates ON bookings(listing_id, check_in_date, check_out_date);
CREATE INDEX idx_bookings_guest ON bookings(guest_user_id);
CREATE INDEX idx_bookings_payout ON bookings(payout_status) WHERE payout_status = 'pending';
CREATE INDEX idx_listings_search ON listings(location, max_guests, base_price_cents);
CREATE INDEX idx_availability_rules_listing ON availability_rules(listing_id);
CREATE INDEX idx_pricing_overrides_listing ON pricing_overrides(listing_id);
CREATE UNIQUE INDEX idx_stripe_events_idempotency ON stripe_webhook_events(stripe_event_id);
CREATE INDEX idx_profiles_deletion ON profiles(deletion_status) WHERE deletion_status = 'pending';

supabase/migrations/005_triggers.sql Two triggers:

• handle_new_user: fires AFTER INSERT ON auth.users, inserts a row into profiles with id = NEW.id, email = NEW.email, roles = '{guest}'. Function name: create_profile_for_user().
• handle_listing_soft_delete: fires BEFORE DELETE ON listings, sets status = 'inactive' and returns NULL (cancels the delete). Function name: soft_delete_listing().
• handle_updated_at: fires BEFORE UPDATE on profiles, listings, and bookings, sets updated_at = now(). Function name: set_updated_at().

supabase/migrations/006_rls.sql Enables RLS and creates all policies. Exact policy definitions:

profiles:

• ENABLE ROW LEVEL SECURITY
• SELECT: auth.uid() = id (role: authenticated)
• UPDATE: auth.uid() = id (role: authenticated)
• No INSERT policy for authenticated users — insert handled by trigger via service role
• No DELETE policy for any non-service role

listings:

• ENABLE ROW LEVEL SECURITY
• SELECT (public): status = 'active' (role: anon, authenticated)
• SELECT (own): auth.uid() = host_user_id (role: authenticated)
• INSERT: auth.uid() = host_user_id (role: authenticated)
• UPDATE: auth.uid() = host_user_id (role: authenticated)
• DELETE: auth.uid() = host_user_id (role: authenticated) — soft-deleted by trigger

listing_photos:

• ENABLE ROW LEVEL SECURITY
• SELECT (public): listing must have status = 'active' (subquery join to listings) (role: anon, authenticated)
• SELECT (own host): listing host_user_id = auth.uid() (role: authenticated)
• INSERT, UPDATE, DELETE: listing host_user_id = auth.uid() (role: authenticated)

listing_amenities:

• Same policy structure as listing_photos

availability_rules:

• ENABLE ROW LEVEL SECURITY
• SELECT: listing host_user_id = auth.uid() (role: authenticated)
• INSERT, UPDATE, DELETE: listing host_user_id = auth.uid() (role: authenticated)

pricing_overrides:

• Same policy structure as availability_rules

bookings:

• ENABLE ROW LEVEL SECURITY
• SELECT (guest): auth.uid() = guest_user_id (role: authenticated)
• SELECT (host): listing host_user_id = auth.uid() via subquery (role: authenticated)
• No INSERT, UPDATE, or DELETE for any authenticated or anon role — all mutations via service role in Edge Functions only

stripe_webhook_events:

• ENABLE ROW LEVEL SECURITY
• No policies for authenticated or anon — service role only

ccpa_requests:

• ENABLE ROW LEVEL SECURITY
• SELECT, INSERT: auth.uid() = user_id (role: authenticated)
• No UPDATE or DELETE for any non-service role

supabase/migrations/007_postgres_functions.sql Server-side PostgreSQL functions called by Edge Functions:

cancel_pending_booking(p_booking_id uuid) RETURNS void

• Updates bookings set status = 'declined' where id = p_booking_id and status IN ('pending', 'pending_approval')
• Called as a compensating action when Stripe fails during booking creation

confirm_booking(p_booking_id uuid, p_stripe_payment_intent_id text) RETURNS void

• Updates bookings set status = 'confirmed', stripe_payment_intent_id = p_stripe_payment_intent_id, updated_at = now() where id = p_booking_id

get_pricing_for_range(p_listing_id uuid, p_check_in date, p_check_out date) RETURNS TABLE(night_date date, price_cents integer)

• For each calendar night in the range (check_in inclusive, check_out exclusive), looks up price in priority order: seasonal override (date falls within season_start/season_end), weekend override (Friday or Saturday), base rate from listings.base_price_cents
• Returns one row per night

scrub_user_pii(p_user_id uuid) RETURNS void

• Updates profiles set full_name = '[deleted]', email = '[deleted]', phone = NULL, avatar_url = NULL, deletion_status = 'completed', updated_at = now() where id = p_user_id
• Does not delete the row

purge_old_webhook_events() RETURNS integer

• Deletes rows from stripe_webhook_events where processed_at < now() - interval '90 days'
• Returns count of deleted rows

supabase/migrations/008_cron_jobs.sql Registers pg_cron jobs (the Edge Function URLs use each project's function base URL, injected at migration time via a SQL variable or handled at deploy time by the CI script):

• payout-scheduler: every hour — SELECT cron.schedule('payout-scheduler', '0 * * * *', ...)
• booking-expire: every 15 minutes — SELECT cron.schedule('booking-expire', '*/15 * * * *', ...)
• ccpa-scrubber: nightly 02:00 UTC — SELECT cron.schedule('ccpa-scrubber', '0 2 * * *', ...)

Each job calls the corresponding Edge Function URL via net.http_post (using the pg_net extension) with the service-role key in the Authorization header.

Shared Edge Function module

supabase/functions/_shared/supabase-clients.ts

export function getServiceClient(): SupabaseClient
// Returns a Supabase client initialized with SUPABASE_URL and
// SUPABASE_SERVICE_ROLE_KEY (read from Deno.env). Used by all
// Edge Functions for privileged database access.

export function getUserClient(jwt: string): SupabaseClient
// Returns a Supabase client initialized with the caller's JWT,
// scoped to authenticated-user access (RLS applies).

supabase/functions/_shared/auth.ts

export async function requireAuth(req: Request): Promise<{ userId: string; roles: string[] }>
// Extracts the Bearer token from the Authorization header.
// Validates the JWT against SUPABASE_JWT_SECRET.
// Fetches the caller's profile row (roles array) via service client.
// Throws AuthError if no token, invalid token, or user not found.
// Returns { userId, roles }.

export class AuthError extends Error {
  constructor(message: string)
  // Signals authentication/authorization failure.
  // Edge Functions catch this and return a 401 response.
}

supabase/functions/_shared/vault.ts

export async function getSecret(name: string): Promise<string>
// Retrieves a secret from Supabase Vault by name using the service client.
// Throws VaultError if the secret is not found or retrieval fails.

export class VaultError extends Error {}

supabase/functions/_shared/resend.ts

export interface EmailPayload {
  to: string
  subject: string
  html: string
}

export async function sendEmail(payload: EmailPayload): Promise<void>
// Retrieves RESEND_API_KEY from Vault.
// Sends the email via Resend API.
// Logs and swallows errors — email failure does not throw.
// Sends from the verified domain address stored as RESEND_FROM_ADDRESS in Vault.

supabase/functions/_shared/errors.ts

export function errorResponse(message: string, code: string, status: number): Response
// Returns a Response with JSON body { error: { message, code } }
// and the given HTTP status.
// Never includes stack traces, Postgres error codes, or Stripe error details.

export function successResponse(data: unknown, status = 200): Response
// Returns a Response with JSON body { data } and the given HTTP status.

supabase/functions/_shared/validation.ts

export function validateBody<T>(schema: ZodSchema<T>, body: unknown): T
// Parses body against schema. Throws ValidationError with field-level
// messages on failure.

export class ValidationError extends Error {
  fields: Record<string, string>
}

Deliverables:

• All 8 migration files present in supabase/migrations/ and applying cleanly against a fresh Supabase project via supabase db push
• GiST exclusion constraint confirmed active — a direct SQL attempt to insert two overlapping confirmed bookings for the same listing is rejected
• Profile row auto-created on user registration (confirmed by inserting a test auth user and asserting the profiles row exists)
• All RLS policies verified: unauthenticated client can read active listings and no other table; authenticated user A cannot read user B's bookings or profile
• All shared Edge Function modules present and TypeScript-valid (no compile errors)
• supabase db lint passes with no warnings

---

Phase 2: Auth Flows and Profile Management (Frontend + Edge Function)

This phase delivers the guest-facing authentication screens (sign up, log in, log out, email verification) and the shared profile page where both guests and hosts manage their personal details. The Supabase JS client handles token refresh transparently. Because these screens read and write the profiles table, all Lovable prompts in this phase go through Plan mode before execution.

Contracts

Frontend modules

src/lib/supabase.ts

export const supabase: SupabaseClient
// Initialized with VITE_SUPABASE_URL and VITE_SUPABASE_ANON_KEY.
// Singleton — imported by all hooks and pages.

src/hooks/useAuth.ts

export interface AuthUser {
  id: string
  email: string
  roles: string[]          // from profiles.roles
  hostOnboardingStatus: 'not_started' | 'pending_verification' | 'verified'
  deletionStatus: 'none' | 'pending' | 'completed'
}

export function useAuth(): {
  user: AuthUser | null
  loading: boolean
  signUp: (email: string, password: string, fullName: string) => Promise<void>
  // Calls supabase.auth.signUp. Throws on error.
  signIn: (email: string, password: string) => Promise<void>
  // Calls supabase.auth.signInWithPassword. Throws on error.
  signOut: () => Promise<void>
  // Calls supabase.auth.signOut.
  refreshUser: () => Promise<void>
  // Re-fetches the profiles row and updates state.
}
// Subscribes to supabase.auth.onAuthStateChange.
// On session change, fetches the profiles row via PostgREST
// (SELECT id, roles, host_onboarding_status, deletion_status)
// and populates AuthUser.

src/hooks/useProfile.ts

export interface Profile {
  id: string
  fullName: string | null
  email: string | null
  phone: string | null
  avatarUrl: string | null
  roles: string[]
  hostOnboardingStatus: string
  deletionStatus: string
  doNotSell: boolean
}

export function useProfile(): {
  profile: Profile | null
  loading: boolean
  error: string | null
  updateProfile: (fields: Partial<Pick<Profile, 'fullName' | 'phone' | 'doNotSell'>>) => Promise<void>
  // Issues a PostgREST PATCH on profiles where id = auth.uid().
  // RLS enforces ownership. Throws on error.
}

src/pages/SignUpPage.tsx Form: email, password, full name. On submit calls useAuth().signUp. On success, shows "check your email" confirmation state. Maps GoTrue errors to friendly messages. No user enumeration — "invalid login credentials" is the generic error for both sign-up and sign-in failures.

src/pages/SignInPage.tsx Form: email, password. On submit calls useAuth().signIn. On success, redirects to /. Maps GoTrue error to generic "invalid email or password" message.

src/pages/ProfilePage.tsx Displays and edits fullName, phone, doNotSell. Uses useProfile. Shows current role badges (guest/host). Avatar upload is wired in Phase 3 (photo upload shared component). Shows a "Request account deletion" button that calls the ccpa-request Edge Function (implemented in Phase 7).

src/components/AuthGuard.tsx

export function AuthGuard({ children }: { children: ReactNode }): JSX.Element
// Reads from useAuth(). If loading, renders a loading spinner.
// If user is null, redirects to /sign-in.
// Otherwise renders children.

src/components/HostGuard.tsx

export function HostGuard({ children }: { children: ReactNode }): JSX.Element
// Reads from useAuth(). If user.roles does not include 'host', redirects to /.
// Otherwise renders children.

src/components/AdminGuard.tsx

export function AdminGuard({ children }: { children: ReactNode }): JSX.Element
// Reads from useAuth(). If user.roles does not include 'admin' or 'support',
// redirects to /.
// Otherwise renders children.

src/App.tsx Defines all client-side routes:

• / → ListingsSearchPage (Phase 3)
• /sign-up → SignUpPage
• /sign-in → SignInPage
• /profile → AuthGuard → ProfilePage
• /listings/:id → ListingDetailPage (Phase 3)
• /bookings → AuthGuard → GuestBookingsPage (Phase 4)
• /host/* → AuthGuard → HostGuard → host sub-routes (Phase 5)
• /admin/* → AuthGuard → AdminGuard → admin sub-routes (Phase 7)

Deliverables:

• User can sign up, receive a verification email, verify, and sign in
• profiles row is present after sign-up with roles = '{guest}'
• Authenticated user can update fullName and phone on the profile page; changes persist and are visible on reload
• Unauthenticated user visiting /profile is redirected to /sign-in
• AuthGuard, HostGuard, and AdminGuard redirect correctly for each role scenario

---

Phase 3: Listing Browse, Search, and Detail (Frontend)

This phase delivers the guest-facing listing discovery experience: a search/filter page and a listing detail page. All data reads go through PostgREST via the Supabase JS client — no Edge Function is needed for reads because RLS policies on listings and listing_photos permit public SELECT on active listings. Lovable prompts for the data-fetching hooks run through Plan mode to confirm the queries align with the listings RLS policies.

Contracts

Frontend modules

src/hooks/useListings.ts

export interface ListingSearchParams {
  location?: string           // partial match on listings.location
  checkIn?: string            // ISO date string
  checkOut?: string           // ISO date string
  numGuests?: number
  minPriceCents?: number
  maxPriceCents?: number
}

export interface ListingSummary {
  id: string
  title: string
  location: string
  maxGuests: number
  basePriceCents: number
  coverPhotoUrl: string | null   // signed URL for first listing_photos row by display_order
  instantBook: boolean
  cancellationPolicy: string
}

export function useListings(params: ListingSearchParams): {
  listings: ListingSummary[]
  loading: boolean
  error: string | null
}
// Queries PostgREST: SELECT from listings WHERE status = 'active'
// AND (location ilike '%{params.location}%' if provided)
// AND (max_guests >= params.numGuests if provided)
// AND (base_price_cents >= params.minPriceCents if provided)
// AND (base_price_cents <= params.maxPriceCents if provided)
// Joins to listing_photos to get the lowest display_order photo per listing.
// Generates a signed read URL for each cover photo via supabase.storage.
// Date availability filtering is not performed client-side —
// the booking flow enforces availability. Search returns all active listings
// matching the non-date filters; the booking step rejects unavailable dates.

src/hooks/useListingDetail.ts

export interface ListingDetail {
  id: string
  title: string
  description: string | null
  location: string
  latitude: number | null
  longitude: number | null
  maxGuests: number
  basePriceCents: number
  cancellationPolicy: string
  instantBook: boolean
  houseRules: string | null
  status: string
  hostUserId: string
  photos: Array<{ id: string; storageUrl: string; displayOrder: number }>
  amenities: Array<{ id: string; amenity: string }>
}

export function useListingDetail(listingId: string): {
  listing: ListingDetail | null
  loading: boolean
  error: string | null
}
// Queries PostgREST for the listing row, joined with listing_photos
// (ordered by display_order) and listing_amenities.
// Generates signed read URLs for all photos.
// Returns null if listing not found or status != 'active'.

src/pages/ListingsSearchPage.tsx Search bar (location text input), date range picker (check-in / check-out), guest count selector, price range sliders. Results rendered as a grid of ListingCard components. Empty state shows "No listings found." Driven by useListings.

src/pages/ListingDetailPage.tsx Full listing detail: photo carousel, title, location, description, amenities list, house rules, per-night price, cancellation policy label. "Book" button opens the booking panel (wired in Phase 4). Uses useListingDetail.

src/components/ListingCard.tsx

interface ListingCardProps {
  listing: ListingSummary
  onClick: () => void
}
// Renders cover photo, title, location, base price per night, instant book badge.

src/components/PhotoCarousel.tsx

interface PhotoCarouselProps {
  photos: Array<{ storageUrl: string; displayOrder: number }>
}
// Renders photos in display_order order with prev/next navigation.

Deliverables:

• Unauthenticated user can visit / and see all active listings
• Search filters (location, guest count, price range) narrow the result set correctly
• Clicking a listing navigates to /listings/:id and shows full detail including all photos and amenities
• No data from non-active listings is visible to any client
• Signed photo URLs resolve and render correctly

---

Phase 4: Booking Flow and Guest Payments (Frontend + Edge Functions)

This phase delivers the complete guest booking experience and the Stripe payment integration. Three Edge Functions are implemented: booking-create (the most critical), booking-cancel (guest-initiated path), and host-onboarding (needed so hosts can be verified before the host management phase). The stripe-webhook Edge Function is also implemented here because it is the recovery path for booking-create. Stripe.js renders card collection in the browser; no card data touches any Edge Function or database table.

Contracts

Edge Functions

supabase/functions/booking-create/index.ts

Zod request schema:

const BookingCreateSchema = z.object({
  listingId: z.string().uuid(),
  checkIn: z.string().regex(/^\d{4}-\d{2}-\d{2}$/),
  checkOut: z.string().regex(/^\d{4}-\d{2}-\d{2}$/),
  numGuests: z.number().int().min(1),
  stripePaymentMethodId: z.string(),
})

Additional validation (after schema parse, before database):

• checkOut must be after checkIn
• Stay length must not exceed 365 nights
• numGuests must not exceed listings.max_guests

Processing sequence:

1. requireAuth(req) → { userId, roles }
2. Validate request body with BookingCreateSchema
3. Retrieve SERVICE_FEE_PERCENTAGE from Vault
4. Call get_pricing_for_range(listingId, checkIn, checkOut) → array of { night_date, price_cents }; sum to subtotalCents; calculate serviceFeeCents = floor(subtotalCents * percentage / 100); totalCents = subtotalCents + serviceFeeCents
5. Execute Direct Insert: Validate availability rules (blocked dates, min stay, check-in day bitmask). If valid, attempt a direct INSERT into the bookings table using the Supabase service-role client.
   • On PostgreSQL exclusion constraint violation (error code 23P01): return errorResponse("These dates are not available", "DATES_UNAVAILABLE", 409)
6. Retrieve STRIPE_SECRET_KEY from Vault
7. Create Stripe PaymentIntent:
   • Amount: totalCents
   • Application fee: serviceFeeCents
   • Transfer destination: host's stripe_connect_account_id (fetched from profiles)
   • If listings.instant_book = true: create and immediately confirm with stripePaymentMethodId; capture mode = automatic
   • If listings.instant_book = false: create with stripePaymentMethodId; capture mode = manual (status = requires_capture); do not confirm yet
   • On Stripe failure: call cancel_pending_booking(bookingId); return errorResponse("Payment could not be processed", "PAYMENT_FAILED", 402)
8. If instant_book = true: call confirm_booking(bookingId, paymentIntentId); send confirmation email via sendEmail
9. If instant_book = false: update booking status = 'pending_approval', store stripe_payment_intent_id; send "awaiting host approval" email to guest; send "new booking request" email to host
10. Return successResponse({ bookingId, status }) with status 201

Rate limiting: before step 1, check a count of booking-create attempts by this user in the last 60 seconds using a booking_rate_limits table (or an in-memory counter backed by the database). If count ≥ 10, return errorResponse("Too many booking attempts", "RATE_LIMITED", 429).

supabase/functions/stripe-webhook/index.ts

Processing sequence:

1. Read raw request body as text (required for signature verification)
2. Retrieve STRIPE_WEBHOOK_SIGNING_SECRET from Vault
3. Verify Stripe webhook signature — on failure, log the attempt and return successResponse({}) (200, to avoid Stripe retry on non-Stripe requests)
4. Parse event JSON
5. Check stripe_webhook_events for existing stripe_event_id — if found, return successResponse({}) immediately
6. Insert stripe_event_id, event_type into stripe_webhook_events
7. Dispatch by event.type:
   • payment_intent.succeeded: if no confirmed booking exists for this paymentIntentId, call confirm_booking(bookingId, paymentIntentId); send confirmation email; log anomaly
   • payment_intent.payment_failed: update booking status = 'payment_failed'; send failure email to guest
   • transfer.failed: update booking payout_status = 'failed'; send operator alert email
   • account.updated: update profiles.host_onboarding_status = 'verified' where stripe_connect_account_id = event.account
8. On database error during dispatch: return a non-2xx response (Stripe retries)
9. Return successResponse({}) on success

supabase/functions/booking-cancel/index.ts

Zod request schema:

const BookingCancelSchema = z.object({
  bookingId: z.string().uuid(),
  initiatedBy: z.enum(['guest', 'host']),
})

Processing sequence:

1. requireAuth(req) → { userId, roles }
2. Validate request body
3. Fetch booking row via service client; assert it exists and status = 'confirmed'
4. If initiatedBy = 'host': assert userId matches listings.host_user_id for the booking
5. If initiatedBy = 'guest': assert userId = booking.guest_user_id
6. If initiatedBy = 'host': refund amount = booking.total_price_cents (full refund)
7. If initiatedBy = 'guest': calculate refund based on listings.cancellation_policy:
   • flexible: full refund if ≥ 1 day before check-in, else 50%
   • moderate: full refund if ≥ 5 days before check-in, else 50%
   • strict: 50% refund if ≥ 7 days before check-in, else 0%
8. Call Stripe Refund API with calculated amount and stripe_payment_intent_id
9. On Stripe failure: return errorResponse("Refund could not be processed", "REFUND_FAILED", 500) — do not update booking status
10. Update booking status to cancelled_by_host or cancelled_by_guest; record cancellation_reason
11. Send cancellation email to guest (and host if guest-initiated)
12. Return successResponse({ bookingId, refundAmountCents })

Same error handling contract as Phase 1's Vault access and same Resend email pattern as booking-create.

supabase/functions/booking-approve/index.ts

Zod request schema:

const BookingApproveSchema = z.object({
  bookingId: z.string().uuid(),
  action: z.enum(['approve', 'decline']),
})

Processing sequence:

1. requireAuth(req) → { userId, roles }
2. Validate request body
3. Fetch booking row; assert status = 'pending_approval'; assert userId matches listings.host_user_id
4. If action = 'approve': capture the Stripe PaymentIntent; call confirm_booking(bookingId, paymentIntentId); send confirmation email to guest
5. If action = 'decline': cancel the Stripe PaymentIntent; call cancel_pending_booking(bookingId); send decline email to guest
6. Return successResponse({ bookingId, status })

Same error handling contract as booking-create.

supabase/functions/host-onboarding/index.ts

Two sub-routes distinguished by query parameter action:

action = 'create-link':

1. requireAuth(req) → { userId }
2. Retrieve STRIPE_SECRET_KEY from Vault
3. Create a Stripe Connect account (if stripe_connect_account_id not already on profile) and a hosted onboarding link with return URL {FRONTEND_URL}/host/onboarding-complete and refresh URL {FRONTEND_URL}/host/onboarding
4. Store stripe_connect_account_id on profile if newly created; set host_onboarding_status = 'pending_verification'; add 'host' to profiles.roles array
5. Return successResponse({ onboardingUrl })

action = 'complete' (called from the return URL redirect handler):

1. requireAuth(req)
2. Fetch profile; if host_onboarding_status = 'verified', return success immediately (idempotent)
3. Return successResponse({ status: 'pending_verification' }) — actual verification update comes via stripe-webhook account.updated event

Additional migration

supabase/migrations/009_booking_rate_limits.sql Creates booking_rate_limit_checks(user_id uuid, attempted_at timestamptz) table with an index on (user_id, attempted_at). RLS: no authenticated access — service role only. The Edge Function inserts a row on each attempt and counts rows in the last 60 seconds.

Frontend modules

src/hooks/useBooking.ts

export interface BookingRequest {
  listingId: string
  checkIn: string          // ISO date
  checkOut: string         // ISO date
  numGuests: number
  stripePaymentMethodId: string
}

export interface BookingResult {
  bookingId: string
  status: 'confirmed' | 'pending_approval'
}

export function useBooking(): {
  createBooking: (req: BookingRequest) => Promise<BookingResult>
  // Invokes the booking-create Edge Function.
  // Throws with a user-readable message on error codes:
  //   DATES_UNAVAILABLE, BOOKING_CONFLICT, PAYMENT_FAILED, RATE_LIMITED
  cancelBooking: (bookingId: string, initiatedBy: 'guest' | 'host') => Promise<{ refundAmountCents: number }>
  // Invokes booking-cancel Edge Function.
  loading: boolean
  error: string | null
}

src/hooks/useGuestBookings.ts

export interface GuestBooking {
  id: string
  listingId: string
  listingTitle: string
  checkIn: string
  checkOut: string
  numGuests: number
  totalPriceCents: number
  serviceFeeCents: number
  status: string
  payoutStatus: string
  createdAt: string
}

export function useGuestBookings(): {
  bookings: GuestBooking[]
  loading: boolean
  error: string | null
}
// Queries PostgREST: SELECT bookings joined with listings (for title)
// WHERE guest_user_id = auth.uid(), ordered by created_at DESC.
// RLS enforces guest can only see their own bookings.

src/components/BookingPanel.tsx

interface BookingPanelProps {
  listing: ListingDetail
  checkIn: string
  checkOut: string
  numGuests: number
}
// Renders price breakdown (per night × nights + service fee = total).
// Renders Stripe card element (via Stripe.js Elements).
// On submit: calls stripe.createPaymentMethod(), then useBooking().createBooking().
// Shows appropriate state for instant-book vs. pending approval.
// Maps error codes to user-facing messages.

src/pages/GuestBookingsPage.tsx Lists all bookings from useGuestBookings. For each confirmed booking, shows a "Cancel" button that calls useBooking().cancelBooking. Shows refund estimate before confirmation. Cancellation of non-confirmed bookings is not available from this page.

src/lib/stripe.ts

export const stripePromise: Promise<Stripe | null>
// Initialized with VITE_STRIPE_PUBLISHABLE_KEY.
// Singleton — imported by BookingPanel and payment components.

Deliverables:

• Guest can complete a booking for an instant-book listing end-to-end: select dates, enter card details via Stripe Elements, confirm payment, see booking in their bookings list with status = confirmed
• Guest can initiate a booking for a manual-approval listing and see status = pending_approval
• Host can approve or decline a pending booking via booking-approve
• Guest can cancel a confirmed booking and receive the correct refund amount based on the listing's cancellation policy
• stripe-webhook correctly handles payment_intent.succeeded and account.updated events; idempotency check prevents duplicate processing
• A host completing Stripe Connect onboarding is redirected correctly and their profile roles array includes host
• Two concurrent booking-create calls for the same listing and dates result in exactly one confirmed booking and one conflict error

---

Phase 5: Host Listing Management (Frontend + Edge Functions)

This phase delivers the host-side dashboard: creating and editing listings, managing availability rules and pricing overrides, uploading photos, and toggling listing status. All host-facing writes go through PostgREST (gated by RLS) except photo confirmation, which goes through the listing-photo-confirm Edge Function. Hosts must have host_onboarding_status = verified before they can publish a listing — this is enforced in the ListingEditorPage UI and in the listing status update path.

Contracts

Edge Function

supabase/functions/listing-photo-confirm/index.ts

Zod request schema:

const PhotoConfirmSchema = z.object({
  listingId: z.string().uuid(),
  storagePath: z.string().min(1),   // path within the photos bucket, e.g. "pending/{listingId}/{uuid}"
  displayOrder: z.number().int().min(0),
})

Processing sequence:

1. requireAuth(req) → { userId }
2. Validate request body
3. Assert caller is the host of listingId (fetch listing via service client; compare host_user_id)
4. Check that the Storage object exists at storagePath using the service client — on failure return errorResponse("Photo not found in storage", "PHOTO_NOT_FOUND", 404)
5. Move object from pending/{listingId}/{uuid} path to photos/{listingId}/{uuid} (copy then delete source, or use the Storage move API if available)
6. Insert row into listing_photos: { listing_id, storage_path: newPath, display_order }
7. Return successResponse({ photoId, storagePath: newPath })

Same error handling contract as Phase 4's Edge Functions for Vault and auth failures.

Frontend modules

src/hooks/useHostListings.ts

export interface HostListingSummary {
  id: string
  title: string
  location: string
  status: string
  basePriceCents: number
  instantBook: boolean
  createdAt: string
}

export function useHostListings(): {
  listings: HostListingSummary[]
  loading: boolean
  error: string | null
  createListing: (fields: CreateListingFields) => Promise<{ id: string }>
  // POSTs to PostgREST listings table. RLS ensures host_user_id = auth.uid().
  updateListing: (id: string, fields: Partial<CreateListingFields>) => Promise<void>
  // PATCHes the listings row. RLS enforces ownership.
  setListingStatus: (id: string, status: 'active' | 'inactive') => Promise<void>
  // PATCHes listings.status. Enforces host_onboarding_status = 'verified'
  // client-side before allowing status = 'active'.
}

export interface CreateListingFields {
  title: string
  description?: string
  location: string
  latitude?: number
  longitude?: number
  maxGuests: number
  basePriceCents: number
  cancellationPolicy: string
  instantBook: boolean
  houseRules?: string
}

src/hooks/useAvailabilityRules.ts

export interface AvailabilityRule {
  id: string
  listingId: string
  ruleType: 'blocked_range' | 'min_stay' | 'checkin_days'
  blockedStart?: string
  blockedEnd?: string
  minStayNights?: number
  checkinDaysBitmask?: number
}

export function useAvailabilityRules(listingId: string): {
  rules: AvailabilityRule[]
  loading: boolean
  addRule: (rule: Omit<AvailabilityRule, 'id' | 'listingId'>) => Promise<void>
  deleteRule: (ruleId: string) => Promise<void>
  // All via PostgREST. RLS enforces host ownership through listing_id join.
}

src/hooks/usePricingOverrides.ts

export interface PricingOverride {
  id: string
  listingId: string
  overrideType: 'seasonal' | 'weekend'
  seasonStart?: string
  seasonEnd?: string
  priceCents: number
}

export function usePricingOverrides(listingId: string): {
  overrides: PricingOverride[]
  loading: boolean
  addOverride: (override: Omit<PricingOverride, 'id' | 'listingId'>) => Promise<void>
  deleteOverride: (overrideId: string) => Promise<void>
}

src/hooks/useListingPhotos.ts

export function useListingPhotos(listingId: string): {
  photos: Array<{ id: string; storageUrl: string; displayOrder: number }>
  loading: boolean
  uploadPhoto: (file: File, displayOrder: number) => Promise<void>
  // 1. Generates a signed upload URL via supabase.storage.createSignedUploadUrl
  //    targeting path `pending/{listingId}/{crypto.randomUUID()}`
  // 2. Uploads file directly to Storage using the signed URL
  // 3. Calls listing-photo-confirm Edge Function with storagePath and displayOrder
  // 4. Refreshes photos list on success
  deletePhoto: (photoId: string, storagePath: string) => Promise<void>
  // Deletes the listing_photos row via PostgREST (RLS enforces host ownership).
  // Deletes the Storage object via supabase.storage.remove.
  reorderPhoto: (photoId: string, newDisplayOrder: number) => Promise<void>
  // PATCHes listing_photos.display_order via PostgREST.
}

src/hooks/useAmenities.ts

export function useAmenities(listingId: string): {
  amenities: Array<{ id: string; amenity: string }>
  loading: boolean
  addAmenity: (amenity: string) => Promise<void>
  removeAmenity: (amenityId: string) => Promise<void>
}

src/hooks/useHostBookings.ts

export interface HostBooking {
  id: string
  guestUserId: string
  guestName: string | null
  checkIn: string
  checkOut: string
  numGuests: number
  totalPriceCents: number
  status: string
  payoutStatus: string
  createdAt: string
}

export function useHostBookings(listingId: string): {
  bookings: HostBooking[]
  loading: boolean
  error: string | null
  approveBooking: (bookingId: string) => Promise<void>
  // Invokes booking-approve Edge Function with action = 'approve'
  declineBooking: (bookingId: string) => Promise<void>
  // Invokes booking-approve Edge Function with action = 'decline'
  cancelBooking: (bookingId: string) => Promise<void>
  // Invokes booking-cancel Edge Function with initiatedBy = 'host'
}
// Queries PostgREST: SELECT from bookings joined with profiles (for guest name)
// WHERE listing.host_user_id = auth.uid(), for a given listingId.
// RLS host SELECT policy enforces this.

src/pages/host/HostDashboardPage.tsx Lists all host listings from useHostListings. Quick stats per listing (upcoming bookings count). "Create new listing" button navigates to /host/listings/new.

src/pages/host/ListingEditorPage.tsx Tabbed editor with sections: Basic Info, Photos, Amenities, Availability Rules, Pricing Overrides, Settings (instant book toggle, cancellation policy, listing status). Uses all host hooks above. Warns the host if host_onboarding_status != 'verified' and disables the "Set to Active" action. On first save, creates the listing with status = 'draft'.

src/pages/host/HostOnboardingPage.tsx Shows current onboarding status. If not_started or pending_verification, renders a "Connect with Stripe" button that calls host-onboarding Edge Function with action = 'create-link' and redirects to the returned onboardingUrl. If verified, shows a success state.

src/pages/host/HostBookingsPage.tsx Lists bookings for all host listings. Approve/decline actions for pending_approval bookings. Cancel action for confirmed bookings. Uses useHostBookings.

Deliverables:

• Host can create a listing, add photos (uploaded to Storage, confirmed via Edge Function), add amenities, set availability rules, set pricing overrides, and publish it (set to active)
• Photos render in the correct display_order on the listing detail page
• Host cannot set a listing to active before completing Stripe Connect onboarding (host_onboarding_status = verified)
• Host can view, approve, decline, and cancel bookings via HostBookingsPage
• Soft-delete trigger fires correctly — a host deleting a listing via PostgREST results in status = 'inactive', not a missing row
• Availability rule evaluation in the booking-create Edge Function correctly rejects bookings that violate a blocked_range, min_stay, or checkin_days rule for the listing before attempting the database insert

---

Phase 6: Scheduled Background Jobs (Edge Functions)

This phase implements the three scheduled Edge Functions that run autonomously: payout-scheduler, booking-expire, and ccpa-scrubber. These functions are triggered by the pg_cron jobs registered in Phase 1's migration. All three are idempotent — re-running them produces no additional side effects on records already processed.

Contracts

Edge Functions

supabase/functions/payout-scheduler/index.ts

Processing sequence:

1. Verify request originates from pg_cron (check for a shared secret in the Authorization header, stored in Vault as CRON_SECRET; reject other callers with 401)
2. Log run start
3. Query via service client: SELECT bookings WHERE status = 'confirmed' AND check_in_date < NOW() AND payout_status = 'pending'
4. For each booking: a. Retrieve host's stripe_connect_account_id from profiles b. Retrieve STRIPE_SECRET_KEY from Vault (cached for the run, not re-fetched per booking) c. Create a Stripe Transfer: amount = total_price_cents - service_fee_cents, destination = stripe_connect_account_id d. On success: UPDATE bookings SET payout_status = 'completed', stripe_transfer_id = transferId e. On Stripe failure: UPDATE bookings SET payout_status = 'failed'; send operator alert via sendEmail; log Stripe error (not returned to caller)
5. Log run completion with count of processed, completed, and failed records
6. Return successResponse({ processed, completed, failed })

If zero bookings are found when confirmed bookings with past check-in dates exist (detected by a separate query for status = 'confirmed' AND check_in_date < NOW()' count > 0 but pending payout count = 0 after 3 consecutive hours), sends operator alert. This sentinel logic is best-effort — a failed run is logged in cron.job_run_details.

supabase/functions/booking-expire/index.ts

Processing sequence:

1. Verify pg_cron caller via CRON_SECRET (same contract as payout-scheduler)
2. Query: SELECT bookings WHERE status = 'pending_approval' AND created_at < NOW() - interval '24 hours'
3. For each booking: a. Cancel the Stripe PaymentIntent (stripe_payment_intent_id) b. Call cancel_pending_booking(bookingId) c. Send "booking request expired" email to guest via sendEmail d. On Stripe failure: log error; still call cancel_pending_booking (do not leave booking in pending_approval — Stripe cancellation can be retried by operator)
4. Return successResponse({ expired: count })

Same error handling contract as payout-scheduler for Vault and Stripe access.

supabase/functions/ccpa-scrubber/index.ts

Processing sequence:

1. Verify pg_cron caller via CRON_SECRET
2. Handle export requests first: a. Query ccpa_requests WHERE request_type = 'export' AND completed_at IS NULL b. For each: assemble user data (profile row, booking rows excluding payment details) via service client; write JSON to ccpa-exports bucket at path exports/{userId}/{requestId}.json; generate a 1-hour signed URL; send via sendEmail to the user's current email; update ccpa_requests.completed_at = now()
3. Handle deletion requests in batches of 50: a. Query profiles WHERE deletion_status = 'pending' AND deletion_requested_at < NOW() - interval '30 days' LIMIT 50 b. For each user:
   • Call scrub_user_pii(userId) — committed individually
   • Attempt to delete avatar Storage object if avatar_url is set; log failure but do not abort
   • Update ccpa_requests for this user: completed_at = now()
   • On failure: log error; continue to next user (no rollback of others) c. Sleep 100ms between batches
4. Call purge_old_webhook_events() to clean up stripe_webhook_events rows older than 90 days
5. Return successResponse({ exportedCount, scrubbedCount, purgedWebhookEvents })

Same error handling contract as payout-scheduler for Vault and auth.

Additional migration

supabase/migrations/010_cron_secret.sql Adds CRON_SECRET to Supabase Vault setup instructions (documented in the migration comment — the actual secret value is set manually in each project's Vault, not stored in the migration file). No schema change required.

Deliverables:

• payout-scheduler correctly transfers funds for all confirmed bookings with past check-in dates and payout_status = pending; marks payout_status = completed on success and failed on Stripe error; sends operator alert on transfer failure
• booking-expire cancels all pending_approval bookings older than 24 hours, cancels their PaymentIntents, and emails the guests
• ccpa-scrubber scrubs PII for users with deletion_status = pending older than 30 days, retaining booking rows with nulled user references; generates and emails export files for export requests
• All three functions are idempotent — running them twice produces no duplicate Stripe calls or double-scrubs
• pg_cron jobs invoke each function on the correct schedule (verified by checking cron.job_run_details after manual schedule trigger in staging)

---

Phase 7: Admin, Support, and CCPA Request Flows (Frontend + Edge Functions)

This phase delivers the operator-facing admin interface, the CCPA deletion and export request endpoints, and the internal tools for support agents. The admin Edge Function gates all privileged operations behind a role check. Support agents can read but not write. A guest-facing "Request account deletion" flow and "Request data export" flow are added to the profile page.

Contracts

Edge Functions

supabase/functions/admin/index.ts

Sub-routes distinguished by action query parameter. All routes:

1. requireAuth(req) → { userId, roles }
2. Assert roles includes admin or support; if neither, return errorResponse("Forbidden", "FORBIDDEN", 403)
3. For write operations (payout retry, flag listing, user management): additionally assert roles includes admin; support role returns errorResponse("Forbidden", "FORBIDDEN", 403) for write actions

Actions:

action = 'list-users' (read):

• Query profiles via service client with optional email or deletion_status filter
• Return successResponse({ users: Profile[] })

action = 'list-listings' (read):

• Query all listings (any status) with optional host_user_id or status filter
• Return successResponse({ listings: ListingSummary[] })

action = 'list-bookings' (read):

• Query all bookings with optional listing_id, guest_user_id, or status filter
• Return successResponse({ bookings: Booking[] })

action = 'retry-payout' (write, admin only): Zod schema: { bookingId: z.string().uuid() }

• Fetch booking; assert payout_status = 'failed'
• Retrieve STRIPE_SECRET_KEY from Vault
• Create Stripe Transfer (same logic as payout-scheduler step 4c)
• On success: UPDATE payout_status = 'completed', stripe_transfer_id
• On failure: return errorResponse("Payout retry failed", "PAYOUT_RETRY_FAILED", 500)
• Return successResponse({ bookingId, stripeTransferId })

action = 'flag-listing' (write, admin only): Zod schema: { listingId: z.string().uuid(), active: z.boolean() }

• UPDATE listings.status = active ? 'active' : 'inactive'
• Return successResponse({ listingId, status })

action = 'list-hosts-by-verification' (read):

• Query profiles WHERE 'host' = ANY(roles) with optional host_onboarding_status filter
• Return successResponse({ hosts: Profile[] })

Same error handling contract as Phase 4's Edge Functions for Vault and auth failures.

supabase/functions/ccpa-request/index.ts

Zod request schema:

const CcpaRequestSchema = z.object({
  requestType: z.enum(['deletion', 'export']),
})

Processing sequence:

1. requireAuth(req) → { userId }
2. Validate request body
3. If requestType = 'deletion':
   • Check no existing ccpa_requests row with request_type = 'deletion' AND completed_at IS NULL for this user — if found, return errorResponse("A deletion request is already pending", "REQUEST_ALREADY_PENDING", 409)
   • INSERT into ccpa_requests: { user_id, request_type: 'deletion', requested_at: now() }
   • UPDATE profiles.deletion_status = 'pending', deletion_requested_at = now()
   • Send confirmation email to user via sendEmail
4. If requestType = 'export':
   • INSERT into ccpa_requests: { user_id, request_type: 'export', requested_at: now() }
   • Send "export request received" email; actual export is processed by ccpa-scrubber on next nightly run
5. Return successResponse({ requestType, requestedAt })

Frontend modules

src/hooks/useAdmin.ts

export interface AdminUser {
  id: string
  email: string | null
  fullName: string | null
  roles: string[]
  hostOnboardingStatus: string
  deletionStatus: string
  createdAt: string
}

export interface AdminListing {
  id: string
  title: string
  hostUserId: string
  status: string
  location: string
  createdAt: string
}

export interface AdminBooking {
  id: string
  listingId: string
  guestUserId: string
  checkIn: string
  checkOut: string
  status: string
  payoutStatus: string
  totalPriceCents: number
  createdAt: string
}

export function useAdmin(): {
  fetchUsers: (filters?: { email?: string; deletionStatus?: string }) => Promise<AdminUser[]>
  fetchListings: (filters?: { hostUserId?: string; status?: string }) => Promise<AdminListing[]>
  fetchBookings: (filters?: { listingId?: string; guestUserId?: string; status?: string }) => Promise<AdminBooking[]>
  retryPayout: (bookingId: string) => Promise<{ stripeTransferId: string }>
  flagListing: (listingId: string, active: boolean) => Promise<void>
  // All call the admin Edge Function with appropriate action parameter.
  loading: boolean
  error: string | null
}

src/hooks/useCcpaRequest.ts

export function useCcpaRequest(): {
  requestDeletion: () => Promise<void>
  // Calls ccpa-request Edge Function with requestType = 'deletion'
  requestExport: () => Promise<void>
  // Calls ccpa-request Edge Function with requestType = 'export'
  loading: boolean
  error: string | null
}

src/pages/admin/AdminDashboardPage.tsx Top-level admin layout with navigation tabs: Users, Listings, Bookings, Hosts.

src/pages/admin/AdminUsersPage.tsx Searchable user list from useAdmin().fetchUsers. Displays roles, deletion status. Read-only for support agents.

src/pages/admin/AdminListingsPage.tsx Filterable listings table. "Activate" / "Deactivate" buttons call useAdmin().flagListing. Disabled for support agents (role check client-side; also enforced server-side).

src/pages/admin/AdminBookingsPage.tsx Filterable bookings table. "Retry Payout" button for payout_status = failed bookings calls useAdmin().retryPayout. Disabled for support agents.

src/pages/admin/AdminHostsPage.tsx Lists hosts by host_onboarding_status. Read-only.

Updates to src/pages/ProfilePage.tsx (from Phase 2): Adds "Request account deletion" and "Request my data export" buttons that use useCcpaRequest. Deletion button shows a confirmation dialog and is disabled if a deletion request is already pending (deletion_status = 'pending').

Deliverables:

• Admin user can view all users, listings, bookings, and hosts via the admin dashboard
• Support agent can view all data but all write actions return a FORBIDDEN error both client-side (button disabled) and server-side (role assertion in Edge Function)
• Admin can retry a failed payout and see payout_status update to completed on success
• Admin can activate or deactivate any listing regardless of host ownership
• Guest can submit a CCPA deletion request from their profile page; deletion_status updates to pending; attempting a second deletion request returns a conflict response
• Guest can submit a CCPA export request; the nightly ccpa-scrubber run processes it and emails a signed URL
• End-to-end CCPA deletion flow verified in staging: PII fields scrubbed, booking rows retained with null user PII, deletion_status = completed

---

Phase 8: CI/CD Pipeline, Security Headers, and Deployment

This phase wires up the three GitHub Actions workflows, configures Netlify security headers, and verifies the complete three-environment deployment pipeline. No new product features are added. This phase makes the system production-deployable and verifiably secure at the transport layer.

Contracts

GitHub Actions workflow files

.github/workflows/pr.yml Triggers on: pull request to main

Steps:

1. Checkout repo
2. Install Supabase CLI
3. Run supabase db lint against all migration files — fails the workflow on any lint error
4. Run Deno unit tests for all Edge Functions (deno test supabase/functions/)
5. Start a local Supabase instance (supabase start)
6. Apply migrations to the local instance (supabase db push --local)
7. Run integration tests (see test contracts below)
8. Stop local Supabase instance

.github/workflows/staging-deploy.yml Triggers on: push to main

Steps:

1. Checkout repo
2. Install Supabase CLI
3. supabase db push targeting homestay-staging using SUPABASE_STAGING_URL and SUPABASE_ACCESS_TOKEN
4. supabase functions deploy --project-ref {stagingRef} for all Edge Functions
5. Run smoke tests against the staging URL (see smoke test contracts below)
6. On success or failure: send notification email to operator via Resend (using RESEND_API_KEY GitHub secret)

.github/workflows/production-deploy.yml Triggers on: workflow_dispatch (manual trigger only) Environment: production (requires GitHub environment approval before deploy step)

Steps:

1. Checkout repo
2. Install Supabase CLI
3. supabase db push targeting homestay-prod
4. supabase functions deploy --project-ref {prodRef} for all Edge Functions
5. Trigger Netlify deploy hook (POST to Netlify deploy hook URL stored as NETLIFY_DEPLOY_HOOK GitHub secret) — Netlify builds and deploys the frontend with production env vars
6. Run smoke tests against the production URL
7. Send deployment notification email

Integration tests

supabase/tests/rls.test.ts

• Assert unauthenticated client can SELECT active listings and listing photos; cannot SELECT bookings, profiles, or any other table
• Assert user A (authenticated) can SELECT their own profile; cannot SELECT user B's profile
• Assert user A cannot SELECT user B's bookings
• Assert host can SELECT their own listings (including drafts); cannot SELECT another host's draft listings
• Assert direct INSERT into bookings as authenticated user is rejected (no RLS policy permits it)
• Assert direct DELETE on listings as authenticated user results in status = 'inactive' (soft delete trigger), not a missing row

supabase/tests/booking-lock.test.ts

• Fire two concurrent booking-create function invocations for the same listing and overlapping dates
• Assert exactly one returns a 201 with status = 'confirmed' or 'pending_approval'
• Assert the other returns a 409 with code DATES_UNAVAILABLE or BOOKING_CONFLICT
• Assert there is exactly one booking row in the database for those dates and listing with a non-declined status

supabase/tests/ccpa.test.ts

• Create a test user with a booking
• Submit a deletion request via ccpa-request
• Manually invoke ccpa-scrubber (bypassing the 30-day wait in test mode by directly calling the scrub function with a service-role client)
• Assert profiles row has full_name = '[deleted]', email = '[deleted]', phone = null
• Assert the booking row still exists with the original guest_user_id reference intact

Smoke tests

supabase/tests/smoke.ts

• GET request to the PostgREST listings endpoint — assert 200 and a valid JSON array
• GET request to each deployed Edge Function URL with no auth — assert 401 (not 404 or 500), confirming the function is deployed and running
• POST to stripe-webhook with a malformed signature — assert 200 response (signature failure returns 200 to Stripe, not 4xx)

Security headers

netlify.toml (or public/_headers for Netlify) Applied to all responses from the production Netlify site:

Strict-Transport-Security: max-age=63072000; includeSubDomains; preload
X-Content-Type-Options: nosniff
X-Frame-Options: DENY
Referrer-Policy: strict-origin-when-cross-origin
Content-Security-Policy: default-src 'self'; script-src 'self' https://js.stripe.com; frame-src https://js.stripe.com; connect-src 'self' {SUPABASE_URL} https://api.stripe.com; img-src 'self' data: {SUPABASE_STORAGE_URL}; style-src 'self' 'unsafe-inline'

The {SUPABASE_URL} and {SUPABASE_STORAGE_URL} placeholders are replaced with the production Supabase project URL during the Netlify build step.

Deliverables:

• PR workflow blocks merge on any migration lint error, Edge Function unit test failure, or integration test failure
• Staging deploy runs automatically on merge to main and smoke tests pass against the staging Supabase project
• Production deploy requires manual trigger and reviewer approval; smoke tests pass against production
• Netlify production site returns all required security headers (verified with a browser developer tools network inspection or curl -I)
• RLS integration tests pass: correct row visibility per user role, direct booking INSERT denied for authenticated users
• Concurrent booking lock test passes: exactly one booking created for overlapping dates

Prompt Pack

This Prompt Pack provides a complete 8-phase step-by-step instruction sequence for Lovable to scaffold Homestay, a production short-term rental marketplace. Each prompt targets an isolated product phase—covering database migrations, RLS security policies, authentication, listing management, Stripe booking flows, Edge Functions, and CI/CD pipelines.

Homestay runs on a modern serverless tech stack comprising React 18, Supabase (PostgreSQL 15, GoTrue Auth, Edge Functions, Storage, and Vault), Stripe Connect for payment routing, and Resend for transactional emails.

---

Prompt 1

This prompt sets up the entire database foundation for Homestay — every table, every rule about who can access what data, and the background processes that keep the system consistent. When this prompt is complete, you will have a fully structured database but no visible app yet. You do not need to understand the instructions below.

---

Paste everything below into your AI coding tool:

You are building Homestay, a two-sided short-term rental marketplace. This is the first prompt. Nothing has been built yet. You will create the complete database schema, all Row-Level Security policies, all database triggers, all indexes, the GiST exclusion constraint for double-booking prevention, and the shared utility modules that all Edge Functions will depend on.

Use Lovable's Plan mode to review every migration and RLS policy before executing. This is critical — RLS policy mistakes silently deny data access instead of showing errors, which is extremely difficult to debug later.

---

Step 1: Database Migrations

Create the following migration files in supabase/migrations/. Apply them in the numbered order listed. Each file must apply cleanly with no errors before proceeding to the next.

supabase/migrations/001_extensions.sql

Enable the btree_gist and pg_cron and pg_net extensions. These must exist before any other migration runs. Use CREATE EXTENSION IF NOT EXISTS for each.

supabase/migrations/002_enums.sql

Define all PostgreSQL enum types. Use CREATE TYPE ... AS ENUM for each. The types and their allowed values:

• listing_status_enum: draft, active, inactive
• booking_status_enum: pending, pending_approval, confirmed, declined, cancelled_by_host, cancelled_by_guest, payment_failed
• payout_status_enum: pending, completed, failed
• availability_rule_type_enum: blocked_range, min_stay, checkin_days
• cancellation_policy_enum: flexible, moderate, strict
• host_onboarding_status_enum: not_started, pending_verification, verified
• deletion_status_enum: none, pending, completed
• ccpa_request_type_enum: deletion, export
• user_role_enum: guest, host, admin, support

supabase/migrations/003_tables.sql

Create the following tables with these exact column definitions:

profiles

• id uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE
• full_name text
• email text
• phone text
• avatar_url text
• roles user_role_enum[] NOT NULL DEFAULT '{guest}'
• stripe_customer_id text
• stripe_connect_account_id text
• host_onboarding_status host_onboarding_status_enum NOT NULL DEFAULT 'not_started'
• deletion_status deletion_status_enum NOT NULL DEFAULT 'none'
• deletion_requested_at timestamptz
• do_not_sell boolean NOT NULL DEFAULT false
• created_at timestamptz NOT NULL DEFAULT now()
• updated_at timestamptz NOT NULL DEFAULT now()

listings

• id uuid PRIMARY KEY DEFAULT gen_random_uuid()
• host_user_id uuid NOT NULL REFERENCES profiles(id)
• title text NOT NULL
• description text
• location text NOT NULL
• latitude numeric(9,6)
• longitude numeric(9,6)
• max_guests integer NOT NULL
• base_price_cents integer NOT NULL
• cancellation_policy cancellation_policy_enum NOT NULL DEFAULT 'moderate'
• instant_book boolean NOT NULL DEFAULT false
• house_rules text
• status listing_status_enum NOT NULL DEFAULT 'draft'
• created_at timestamptz NOT NULL DEFAULT now()
• updated_at timestamptz NOT NULL DEFAULT now()

listing_amenities

• id uuid PRIMARY KEY DEFAULT gen_random_uuid()
• listing_id uuid NOT NULL REFERENCES listings(id) ON DELETE CASCADE
• amenity text NOT NULL

listing_photos

• id uuid PRIMARY KEY DEFAULT gen_random_uuid()
• listing_id uuid NOT NULL REFERENCES listings(id) ON DELETE CASCADE
• storage_path text NOT NULL
• display_order integer NOT NULL DEFAULT 0
• created_at timestamptz NOT NULL DEFAULT now()

availability_rules

• id uuid PRIMARY KEY DEFAULT gen_random_uuid()
• listing_id uuid NOT NULL REFERENCES listings(id) ON DELETE CASCADE
• rule_type availability_rule_type_enum NOT NULL
• blocked_start date
• blocked_end date
• min_stay_nights integer
• checkin_days_bitmask integer
• created_at timestamptz NOT NULL DEFAULT now()

The checkin_days_bitmask column encodes allowed check-in days as a bitmask: bit 0 = Sunday, bit 1 = Monday, through bit 6 = Saturday.

pricing_overrides

• id uuid PRIMARY KEY DEFAULT gen_random_uuid()
• listing_id uuid NOT NULL REFERENCES listings(id) ON DELETE CASCADE
• override_type text NOT NULL
• season_start date
• season_end date
• price_cents integer NOT NULL
• created_at timestamptz NOT NULL DEFAULT now()

The override_type column accepts values seasonal and weekend.

bookings

• id uuid PRIMARY KEY DEFAULT gen_random_uuid()
• listing_id uuid NOT NULL REFERENCES listings(id)
• guest_user_id uuid NOT NULL REFERENCES profiles(id)
• check_in_date date NOT NULL
• check_out_date date NOT NULL
• date_range tsrange GENERATED ALWAYS AS (tsrange(check_in_date::timestamp, check_out_date::timestamp)) STORED
• num_guests integer NOT NULL
• total_price_cents integer NOT NULL
• service_fee_cents integer NOT NULL
• status booking_status_enum NOT NULL DEFAULT 'pending'
• payout_status payout_status_enum NOT NULL DEFAULT 'pending'
• stripe_payment_intent_id text
• stripe_transfer_id text
• cancellation_reason text
• created_at timestamptz NOT NULL DEFAULT now()
• updated_at timestamptz NOT NULL DEFAULT now()

stripe_webhook_events

• id uuid PRIMARY KEY DEFAULT gen_random_uuid()
• stripe_event_id text NOT NULL UNIQUE
• event_type text NOT NULL
• processed_at timestamptz NOT NULL DEFAULT now()

ccpa_requests

• id uuid PRIMARY KEY DEFAULT gen_random_uuid()
• user_id uuid NOT NULL REFERENCES profiles(id)
• request_type ccpa_request_type_enum NOT NULL
• requested_at timestamptz NOT NULL DEFAULT now()
• completed_at timestamptz

booking_rate_limit_checks

• id uuid PRIMARY KEY DEFAULT gen_random_uuid()
• user_id uuid NOT NULL
• attempted_at timestamptz NOT NULL DEFAULT now()

supabase/migrations/004_constraints_and_indexes.sql

Add the GiST exclusion constraint and all indexes. Use these exact definitions:

ALTER TABLE bookings ADD CONSTRAINT no_overlapping_bookings
  EXCLUDE USING gist (
    listing_id WITH =,
    date_range WITH &&
  )
  WHERE (status IN ('pending', 'pending_approval', 'confirmed'));

CREATE INDEX idx_bookings_listing_dates ON bookings(listing_id, check_in_date, check_out_date);
CREATE INDEX idx_bookings_guest ON bookings(guest_user_id);
CREATE INDEX idx_bookings_payout ON bookings(payout_status) WHERE payout_status = 'pending';
CREATE INDEX idx_listings_search ON listings(location, max_guests, base_price_cents);
CREATE INDEX idx_availability_rules_listing ON availability_rules(listing_id);
CREATE INDEX idx_pricing_overrides_listing ON pricing_overrides(listing_id);
CREATE UNIQUE INDEX idx_stripe_events_idempotency ON stripe_webhook_events(stripe_event_id);
CREATE INDEX idx_profiles_deletion ON profiles(deletion_status) WHERE deletion_status = 'pending';
CREATE INDEX idx_booking_rate_limits ON booking_rate_limit_checks(user_id, attempted_at);

supabase/migrations/005_triggers.sql

Create three trigger functions and their associated triggers:

create_profile_for_user() — fires AFTER INSERT ON auth.users for each row. Inserts a row into profiles with id = NEW.id, email = NEW.email, roles = '{guest}'. All other columns take their defaults.

soft_delete_listing() — fires BEFORE DELETE ON listings for each row. Sets status = 'inactive' on the row and returns NULL to cancel the actual deletion.

set_updated_at() — fires BEFORE UPDATE on profiles, listings, and bookings for each row. Sets updated_at = now() and returns NEW.

supabase/migrations/006_rls.sql

Enable Row-Level Security and create all policies. Run this migration through Plan mode in Lovable before executing — review every policy carefully.

Enable RLS on every table: profiles, listings, listing_amenities, listing_photos, availability_rules, pricing_overrides, bookings, stripe_webhook_events, ccpa_requests, booking_rate_limit_checks.

profiles policies:

• SELECT for role authenticated: auth.uid() = id
• UPDATE for role authenticated: auth.uid() = id
• No INSERT for authenticated — inserts are handled by the trigger via service role
• No DELETE for any non-service role

listings policies:

• SELECT for roles anon and authenticated: status = 'active'
• SELECT (own) for role authenticated: auth.uid() = host_user_id
• INSERT for role authenticated: auth.uid() = host_user_id
• UPDATE for role authenticated: auth.uid() = host_user_id
• DELETE for role authenticated: auth.uid() = host_user_id — the soft-delete trigger intercepts this and sets status to inactive rather than deleting the row

listing_photos policies:

• SELECT (public) for roles anon and authenticated: the associated listing_id must reference a listing with status = 'active' (use a subquery)
• SELECT (own host) for role authenticated: the associated listing_id must reference a listing where host_user_id = auth.uid()
• INSERT, UPDATE, DELETE for role authenticated: the associated listing_id must reference a listing where host_user_id = auth.uid()

listing_amenities policies: Same structure as listing_photos.

availability_rules policies:

• SELECT for role authenticated: listing host_user_id = auth.uid() via subquery
• INSERT, UPDATE, DELETE for role authenticated: listing host_user_id = auth.uid() via subquery

pricing_overrides policies: Same structure as availability_rules.

bookings policies:

• SELECT (guest) for role authenticated: auth.uid() = guest_user_id
• SELECT (host) for role authenticated: the associated listing_id references a listing where host_user_id = auth.uid() (use a subquery)
• No INSERT, UPDATE, or DELETE for any authenticated or anon role — all mutations go through Edge Functions using the service role

stripe_webhook_events policies: No policies for authenticated or anon. Service role only.

ccpa_requests policies:

• SELECT for role authenticated: auth.uid() = user_id
• INSERT for role authenticated: auth.uid() = user_id
• No UPDATE or DELETE for any non-service role

booking_rate_limit_checks policies: No policies for authenticated or anon. Service role only.

supabase/migrations/007_postgres_functions.sql

Create the following server-side PostgreSQL functions. These are called by Edge Functions using the service-role client. All functions require SECURITY DEFINER so they run with the function owner's privileges regardless of caller:

cancel_pending_booking(p_booking_id uuid) RETURNS void Updates the bookings table: set status = 'declined' where id = p_booking_id and status is either pending or pending_approval. This is a compensating action called when Stripe fails during booking creation.

confirm_booking(p_booking_id uuid, p_stripe_payment_intent_id text) RETURNS void Updates bookings: set status = 'confirmed', stripe_payment_intent_id = p_stripe_payment_intent_id, updated_at = now() where id = p_booking_id.

get_pricing_for_range(p_listing_id uuid, p_check_in date, p_check_out date) RETURNS TABLE(night_date date, price_cents integer) For each calendar night in the range (check-in inclusive, check-out exclusive — one row per night), determine the applicable price in this priority order:

1. If a row exists in pricing_overrides for this listing_id with override_type = 'seasonal' where the night date falls within season_start and season_end (inclusive), use that price_cents
2. Else if override_type = 'weekend' exists for this listing and the night date is a Friday or Saturday, use that price_cents
3. Else use base_price_cents from the listings table for this listing_id

Return one row per night with the resolved price.

scrub_user_pii(p_user_id uuid) RETURNS void Updates profiles: set full_name = '[deleted]', email = '[deleted]', phone = NULL, avatar_url = NULL, deletion_status = 'completed', updated_at = now() where id = p_user_id. Does not delete the row.

purge_old_webhook_events() RETURNS integer Deletes rows from stripe_webhook_events where processed_at is older than 90 days. Returns the count of deleted rows as an integer.

supabase/migrations/008_cron_jobs.sql

Register pg_cron jobs using cron.schedule. Each job invokes the corresponding Edge Function via net.http_post from the pg_net extension, with a CRON_SECRET bearer token in the Authorization header. The Edge Function URLs follow the pattern {SUPABASE_URL}/functions/v1/{function-name}.

Because the exact project URL is environment-specific, implement this migration so the URL is constructed from a Postgres configuration parameter (e.g. current_setting('app.supabase_url')) that is set at deploy time by the CI script before the migration runs. Document this requirement clearly in the migration file comment.

Register three jobs:

• payout-scheduler: schedule 0 * * * * (every hour)
• booking-expire: schedule */15 * * * * (every 15 minutes)
• ccpa-scrubber: schedule 0 2 * * * (nightly at 02:00 UTC)

---

Step 2: Shared Edge Function Modules

Create the following files in supabase/functions/_shared/. These modules are imported by all Edge Functions. Use TypeScript throughout.

supabase/functions/_shared/supabase-clients.ts

Export two functions:

export function getServiceClient(): SupabaseClient

Returns a Supabase client initialized with SUPABASE_URL and SUPABASE_SERVICE_ROLE_KEY read from Deno.env. This client bypasses RLS and is used for all privileged database operations inside Edge Functions. Must be callable multiple times safely — either create a singleton or create a new instance per call (the tool may choose).

export function getUserClient(jwt: string): SupabaseClient

Returns a Supabase client initialized with the caller's JWT, using the anon key for the base client but overriding the Authorization header with the provided JWT. RLS applies to all queries made with this client.

supabase/functions/_shared/auth.ts

Export:

export async function requireAuth(req: Request): Promise<{ userId: string; roles: string[] }>

Extracts the Bearer token from the Authorization header of req. Validates the JWT. Fetches the caller's profiles row (specifically the roles column) via the service client. Throws AuthError if: no token is present, the token is invalid or expired, or no matching profile row exists. Returns { userId, roles } on success.

export class AuthError extends Error {
  constructor(message: string)
}

Edge Functions catch AuthError and return an unauthorized response. The AuthError class must be exported so Edge Functions can use instanceof checks.

supabase/functions/_shared/vault.ts

Export:

export async function getSecret(name: string): Promise<string>

Retrieves a secret from Supabase Vault by the given name using the service client. Throws VaultError if the secret is not found or retrieval fails.

export class VaultError extends Error {}

supabase/functions/_shared/resend.ts

Export:

export interface EmailPayload {
  to: string
  subject: string
  html: string
}

export async function sendEmail(payload: EmailPayload): Promise<void>

Retrieves RESEND_API_KEY and RESEND_FROM_ADDRESS from Vault. Sends the email via the Resend API using the retrieved key and from address. On any error (Vault retrieval failure, API call failure, or non-success response), logs the error details and returns without throwing — email failure must never cause an Edge Function to fail its primary operation.

supabase/functions/_shared/errors.ts

Export:

export function errorResponse(message: string, code: string, status: number): Response

Returns a Response with Content-Type: application/json, the given HTTP status, and a JSON body shaped as { error: { message, code } }. Must never include stack traces, Postgres error codes, Stripe error objects, or any internal detail.

export function successResponse(data: unknown, status?: number): Response

Returns a Response with Content-Type: application/json, the given status (defaulting to 200), and a JSON body shaped as { data }.

Both functions must include CORS headers allowing requests from the production frontend domain. The allowed origin value must be read from Deno.env (set it as FRONTEND_URL in Vault, already provisioned in setup).

supabase/functions/_shared/validation.ts

Export:

export function validateBody<T>(schema: ZodSchema<T>, body: unknown): T

Parses body against the provided Zod schema. On success, returns the typed, validated value. On failure, throws ValidationError with a fields property mapping field names to human-readable error messages.

export class ValidationError extends Error {
  fields: Record<string, string>
}

Edge Functions catch ValidationError and return a response with the field-level messages in the error body using errorResponse.

---

Step 3: Apply Migrations

Apply all migrations to the homestay-dev Supabase project via Lovable's Supabase integration. Confirm each migration file is present in supabase/migrations/ and that the migrations apply in order without errors.

Do not build any frontend pages or components in this prompt.

---

Check before continuing (do this yourself, do not paste it):

• Open your Supabase homestay-dev dashboard → Table Editor and confirm all tables exist: profiles, listings, listing_amenities, listing_photos, availability_rules, pricing_overrides, bookings, stripe_webhook_events, ccpa_requests, booking_rate_limit_checks.
• In the Supabase dashboard → Authentication, create a test user manually. Then go to Table Editor → profiles and confirm a row was automatically created for that user with roles = {guest}.
• In the Supabase SQL Editor, run this statement and confirm it produces an error (the constraint is working): INSERT INTO bookings (listing_id, guest_user_id, check_in_date, check_out_date, num_guests, total_price_cents, service_fee_cents, status) VALUES (gen_random_uuid(), gen_random_uuid(), '2025-01-01', '2025-01-05', 2, 10000, 1200, 'confirmed'), (gen_random_uuid(), gen_random_uuid(), '2025-01-03', '2025-01-07', 2, 10000, 1200, 'confirmed'); — it should fail only if you use the same listing_id for both rows. If something is wrong: tell Lovable which migration produced an error and paste the error message.

---

Prompt 2

Run this after Prompt 1 is complete.

This prompt builds the sign-up, sign-in, sign-out, and profile pages. When this prompt is complete, you will be able to create an account, verify your email, sign in, and edit your profile — and you will be redirected to sign-in if you try to visit a protected page without being logged in. You do not need to understand the instructions below.

---

Paste everything below into your AI coding tool:

You are building Homestay, a two-sided short-term rental marketplace. The database schema, RLS policies, triggers, and shared Edge Function modules from Prompt 1 are already in place. This prompt builds the authentication flows and profile management screens.

Use Plan mode in Lovable before generating any component that reads from or writes to the profiles table — confirm the data access pattern aligns with the profiles RLS policy before executing.

Do not build any listing, booking, host, or admin screens in this prompt.

---

Step 1: Supabase Client Singleton

src/lib/supabase.ts

Create and export a singleton Supabase client:

export const supabase: SupabaseClient

Initialize it with import.meta.env.VITE_SUPABASE_URL and import.meta.env.VITE_SUPABASE_ANON_KEY. This singleton is imported by all hooks and pages throughout the app. It must not be re-initialized on re-renders.

---

Step 2: Auth Hook

src/hooks/useAuth.ts

Export the following interface and hook:

export interface AuthUser {
  id: string
  email: string
  roles: string[]
  hostOnboardingStatus: 'not_started' | 'pending_verification' | 'verified'
  deletionStatus: 'none' | 'pending' | 'completed'
}

export function useAuth(): {
  user: AuthUser | null
  loading: boolean
  signUp: (email: string, password: string, fullName: string) => Promise<void>
  signIn: (email: string, password: string) => Promise<void>
  signOut: () => Promise<void>
  refreshUser: () => Promise<void>
}

Behaviour:

• Subscribe to the Supabase auth state change event on mount; unsubscribe on unmount.
• When a session is established, fetch the authenticated user's profiles row (columns: id, roles, host_onboarding_status, deletion_status) via PostgREST and populate AuthUser. The email value comes from the Supabase session, not the profiles row.
• signUp calls Supabase auth sign-up with the provided email and password. It also passes fullName as user metadata so it is available during the profile setup step. Throws on any error.
• signIn calls Supabase auth sign-in with email and password. Throws on any error.
• signOut calls Supabase auth sign-out and clears user to null.
• refreshUser re-fetches the profiles row and updates state. Called after profile edits or onboarding status changes.
• While the initial session check is in progress, loading is true.

---

Step 3: Profile Hook

src/hooks/useProfile.ts

Export:

export interface Profile {
  id: string
  fullName: string | null
  email: string | null
  phone: string | null
  avatarUrl: string | null
  roles: string[]
  hostOnboardingStatus: string
  deletionStatus: string
  doNotSell: boolean
}

export function useProfile(): {
  profile: Profile | null
  loading: boolean
  error: string | null
  updateProfile: (fields: Partial<Pick<Profile, 'fullName' | 'phone' | 'doNotSell'>>) => Promise<void>
}

Behaviour:

• Fetches the full profiles row for the currently authenticated user via PostgREST on mount. RLS ensures users can only read their own row.
• updateProfile issues a PostgREST PATCH on the profiles table scoped to the authenticated user's id. Only the fields full_name, phone, and do_not_sell may be updated by this hook. Refreshes the local profile state on success.
• If the user is not authenticated, profile is null.

---

Step 4: Guard Components

src/components/AuthGuard.tsx

export function AuthGuard({ children }: { children: ReactNode }): JSX.Element

Reads from useAuth(). While loading is true, renders a full-page loading spinner. If user is null, redirects to /sign-in using the router. Otherwise renders children.

src/components/HostGuard.tsx

export function HostGuard({ children }: { children: ReactNode }): JSX.Element

Reads from useAuth(). If user.roles does not include 'host', redirects to /. Otherwise renders children. Assumes it is always rendered inside AuthGuard so user is guaranteed non-null.

src/components/AdminGuard.tsx

export function AdminGuard({ children }: { children: ReactNode }): JSX.Element

Reads from useAuth(). If user.roles does not include 'admin' or 'support', redirects to /. Otherwise renders children. Assumes it is always rendered inside AuthGuard.

---

Step 5: Pages

src/pages/SignUpPage.tsx

A sign-up form with fields: full name, email address, and password. On submit, calls useAuth().signUp. On success, displays a confirmation message telling the user to check their email to verify their account — do not redirect immediately. If the Supabase auth error is any variant of an invalid credentials or existing user error, display the message "Something went wrong. Please check your details and try again." — never distinguish between "email already taken" and other errors, to prevent user enumeration.

src/pages/SignInPage.tsx

A sign-in form with fields: email address and password. On submit, calls useAuth().signIn. On success, redirects to /. On any error, display the message "Invalid email or password." regardless of the specific error type.

src/pages/ProfilePage.tsx

Displays the authenticated user's profile using useProfile. Shows editable fields: full name, phone number, and a "Do not sell my data" toggle. Shows non-editable role badges for each role in user.roles. Shows the current hostOnboardingStatus as a status label.

Includes two action buttons at the bottom of the page:

• "Request account deletion" — this button calls the ccpa-request Edge Function (built in Prompt 7). For now, render the button but it does not need to do anything yet. Disable the button if profile.deletionStatus === 'pending'. Show the label "Deletion requested" instead when disabled.
• "Request my data export" — same placeholder treatment; wire it up in Prompt 7.

Save changes via useProfile().updateProfile on a "Save" button click. Show a success message on save.

---

Step 6: App Router

src/App.tsx

Define the full application route structure using React Router. Set up all routes now — pages that are not built yet render a placeholder component with the text "Coming soon":

• / → ListingsSearchPage (placeholder for now)
• /sign-up → SignUpPage
• /sign-in → SignInPage
• /profile → AuthGuard wrapping ProfilePage
• /listings/:id → ListingDetailPage (placeholder)
• /bookings → AuthGuard wrapping GuestBookingsPage (placeholder)
• /host → AuthGuard wrapping HostGuard wrapping HostDashboardPage (placeholder)
• /host/listings/new → AuthGuard wrapping HostGuard wrapping ListingEditorPage (placeholder)
• /host/listings/:id → AuthGuard wrapping HostGuard wrapping ListingEditorPage (placeholder)
• /host/onboarding → AuthGuard wrapping HostGuard wrapping HostOnboardingPage (placeholder)
• /host/onboarding-complete → AuthGuard wrapping HostGuard wrapping HostOnboardingPage (placeholder)
• /host/bookings → AuthGuard wrapping HostGuard wrapping HostBookingsPage (placeholder)
• /admin → AuthGuard wrapping AdminGuard wrapping AdminDashboardPage (placeholder)
• /admin/users → AuthGuard wrapping AdminGuard wrapping AdminUsersPage (placeholder)
• /admin/listings → AuthGuard wrapping AdminGuard wrapping AdminListingsPage (placeholder)
• /admin/bookings → AuthGuard wrapping AdminGuard wrapping AdminBookingsPage (placeholder)
• /admin/hosts → AuthGuard wrapping AdminGuard wrapping AdminHostsPage (placeholder)

Include a top-level navigation bar visible on all pages with links to /, a sign-in link (if not authenticated), and links to /profile and /bookings (if authenticated). The nav bar shows a sign-out button that calls useAuth().signOut.

---

Check before continuing (do this yourself, do not paste it):

• Visit the Lovable preview URL. You should see the navigation bar and a "Coming soon" placeholder at /.
• Click sign up, create a new account with a real email address you can access, and confirm you receive a verification email. After verifying, sign in — you should be redirected to / and the nav bar should show your email or a profile link.
• Navigate directly to /profile while signed out — you should be redirected to /sign-in. Sign in and return to /profile — you should see the profile form with your name and email.
• Edit your full name and save — reload the page and confirm the new name persists. If something is wrong: describe what you see and ask Lovable to fix the specific step that failed.

---

Prompt 3

Run this after Prompt 2 is complete.

This prompt builds the guest-facing listing discovery experience — a search page and a listing detail page. When this prompt is complete, any visitor (logged in or not) can browse listings, filter by location, guests, and price, and view full listing details including all photos. You do not need to understand the instructions below.

---

Paste everything below into your AI coding tool:

You are building Homestay, a two-sided short-term rental marketplace. The database schema, RLS policies, auth flows, and profile page from Prompts 1 and 2 are already in place. This prompt builds the guest-facing listing search and listing detail pages. All data reads go through PostgREST via the Supabase JS client — no Edge Functions are needed for these screens.

Use Plan mode before generating any component that queries the listings or listing_photos tables. Confirm the queries align with the RLS policies: unauthenticated users can read active listings and their photos; no other tables are accessible without authentication.

Do not build any booking, host management, or admin screens in this prompt.

---

Step 1: Listing Data Hooks

src/hooks/useListings.ts

Export:

export interface ListingSearchParams {
  location?: string
  checkIn?: string       // ISO date string, e.g. "2025-08-01"
  checkOut?: string      // ISO date string
  numGuests?: number
  minPriceCents?: number
  maxPriceCents?: number
}

export interface ListingSummary {
  id: string
  title: string
  location: string
  maxGuests: number
  basePriceCents: number
  coverPhotoUrl: string | null
  instantBook: boolean
  cancellationPolicy: string
}

export function useListings(params: ListingSearchParams): {
  listings: ListingSummary[]
  loading: boolean
  error: string | null
}

Behaviour:

• Queries PostgREST for rows in listings where status = 'active'.
• Applies additional filters when provided: location uses a case-insensitive partial match against listings.location; numGuests filters to listings where max_guests >= numGuests; minPriceCents and maxPriceCents filter on base_price_cents.
• checkIn and checkOut are collected by the search form but are not used to filter listings here — date availability is enforced at booking time. Pass them through so they can be forwarded to the booking flow when a guest clicks a listing.
• For each listing, fetches the listing_photos row with the lowest display_order for that listing. Generates a signed read URL for that photo using the Supabase storage client. If no photo exists, coverPhotoUrl is null.
• Re-runs the query when params changes.

src/hooks/useListingDetail.ts

Export:

export interface ListingDetail {
  id: string
  title: string
  description: string | null
  location: string
  latitude: number | null
  longitude: number | null
  maxGuests: number
  basePriceCents: number
  cancellationPolicy: string
  instantBook: boolean
  houseRules: string | null
  status: string
  hostUserId: string
  photos: Array<{ id: string; storageUrl: string; displayOrder: number }>
  amenities: Array<{ id: string; amenity: string }>
}

export function useListingDetail(listingId: string): {
  listing: ListingDetail | null
  loading: boolean
  error: string | null
}

Behaviour:

• Fetches the listings row for the given listingId via PostgREST.
• Also fetches all listing_photos rows for this listing ordered by display_order ascending, and all listing_amenities rows.
• Generates signed read URLs for all photos via the Supabase storage client.
• If the listing does not exist or status != 'active', returns listing: null.
• Sets error to a human-readable string on any fetch failure.

---

Step 2: Components

src/components/ListingCard.tsx

interface ListingCardProps {
  listing: ListingSummary
  onClick: () => void
}
export function ListingCard({ listing, onClick }: ListingCardProps): JSX.Element

Renders: the cover photo (or a placeholder image if coverPhotoUrl is null), the listing title, the location, the base price formatted as a dollar amount per night, and an "Instant Book" badge if listing.instantBook is true. The entire card is clickable and calls onClick.

src/components/PhotoCarousel.tsx

interface PhotoCarouselProps {
  photos: Array<{ storageUrl: string; displayOrder: number }>
}
export function PhotoCarousel({ photos }: PhotoCarouselProps): JSX.Element

Renders photos sorted by displayOrder with previous and next navigation controls. If photos is empty, renders a placeholder. Shows the current photo index and total count.

---

Step 3: Pages

src/pages/ListingsSearchPage.tsx

Replace the placeholder at / with this page. It contains:

• A search bar with: a location text input, a date range picker (check-in and check-out date inputs), a guest count stepper (minimum 1), and a price range control with minimum and maximum inputs.
• A "Search" button that applies the current filter values to useListings.
• A grid of ListingCard components driven by the listings returned from useListings.
• When loading is true, show a loading state in the grid area.
• When listings is empty and loading is false, display: "No listings found. Try adjusting your filters."
• Clicking a ListingCard navigates to /listings/:id, passing the selected checkIn, checkOut, and numGuests values as URL query parameters so the booking panel (built in Prompt 4) can pre-populate them.

src/pages/ListingDetailPage.tsx

Replace the placeholder at /listings/:id with this page. It contains:

• A PhotoCarousel with all listing photos.
• The listing title, location, description, maximum guests, and base price per night.
• A list of amenities.
• House rules text (if present).
• The cancellation policy label: "Flexible", "Moderate", or "Strict".
• A "Book" button / booking panel area — for now, render the button but it does not need to do anything yet. It will be fully wired up in Prompt 4. Pre-populate the date and guest inputs from the URL query parameters (checkIn, checkOut, numGuests) if present.
• If loading is true, show a page-level loading state.
• If listing is null after loading completes, show: "This listing is not available."

---

Check before continuing (do this yourself, do not paste it):

• Visit the Lovable preview URL at /. The search page should display. If no listings exist yet in the database, the grid should show "No listings found."
• Using the Supabase dashboard SQL editor, insert a test listing with status = 'active' directly into the listings table. Reload the search page — the listing card should appear.
• Click the listing card and confirm you are taken to the detail page at /listings/:id. The page should show the listing title, location, and the message "No listings found" area replaced by listing detail. If something is wrong: tell Lovable exactly which element is missing or incorrect.

---

Prompt 4

Run this after Prompt 3 is complete.

This prompt builds the guest booking flow including Stripe payment collection, the booking history page, and host approval controls. It also creates four Edge Functions: the booking creation logic, the Stripe webhook handler, the booking cancellation handler, and the host approval/decline handler, plus the host Stripe Connect onboarding flow. When this prompt is complete, a guest can book a listing end-to-end, and a host can approve or decline manual-approval bookings. You do not need to understand the instructions below.

---

Paste everything below into your AI coding tool:

You are building Homestay, a two-sided short-term rental marketplace. The database schema, RLS policies, auth flows, profile page, listing search, and listing detail page from Prompts 1–3 are already in place. This prompt builds the booking flow, Stripe payment integration, and host Connect onboarding.

Use Plan mode before generating any component that reads from or writes to the bookings table. Confirm the queries align with the RLS policies: guests can only read their own bookings; all booking mutations go through Edge Functions using the service role.

Do not build the host listing editor, availability rules, pricing overrides, scheduled jobs, or admin screens in this prompt.

---

Step 1: Edge Functions

All Edge Functions in this prompt must:

• Import from ../_shared/supabase-clients.ts, ../_shared/auth.ts, ../_shared/vault.ts, ../_shared/resend.ts, ../_shared/errors.ts, and ../_shared/validation.ts as appropriate.
• Set CORS headers on all responses, allowing requests from the production frontend domain (read FRONTEND_URL from Vault or Deno.env).
• Return errorResponse for all error cases, successResponse for all success cases.
• Never return internal error details, stack traces, Postgres error codes, or Stripe error objects to the caller.

supabase/functions/booking-create/index.ts

Define the request schema using Zod:

• listingId: UUID string
• checkIn: string matching the pattern for an ISO date (four digits, hyphen, two digits, hyphen, two digits)
• checkOut: string matching the same ISO date pattern
• numGuests: integer, minimum 1
• stripePaymentMethodId: non-empty string

Processing sequence — implement in this exact order:

1. Rate limit check: using the service client, count rows in booking_rate_limit_checks for this user where attempted_at > now() - interval '60 seconds'. If count ≥ 10, return errorResponse("Too many booking attempts. Please wait a moment and try again.", "RATE_LIMITED", 429). Otherwise, insert a new row recording this attempt.

2. Call requireAuth(req) to get { userId, roles }. Return an unauthorized response if it throws AuthError.

3. Validate the request body with the Zod schema. Return a validation error response if it throws ValidationError.

4. Additional validation (after schema parse):

   • checkOut must be strictly after checkIn — if not, return errorResponse("Check-out date must be after check-in date.", "INVALID_DATES", 400)
   • The number of nights must not exceed 365 — if it does, return errorResponse("Maximum stay is 365 nights.", "STAY_TOO_LONG", 400)

5. Fetch the listings row for listingId via service client. If not found or status != 'active', return errorResponse("Listing not found.", "LISTING_NOT_FOUND", 404).

6. Validate numGuests <= listings.max_guests — if not, return errorResponse("Guest count exceeds the listing's maximum.", "GUEST_COUNT_EXCEEDED", 400).

7. Validate availability rules for the listing. Using the service client, fetch all availability_rules rows for this listing. Check in this order:

   • For each rule of type blocked_range: if any night in the requested range falls within blocked_start to blocked_end (inclusive), return errorResponse("Some of the selected dates are blocked by the host.", "DATES_BLOCKED", 409).
   • For the rule of type min_stay (if any): if the number of requested nights is less than min_stay_nights, return errorResponse("This listing requires a minimum stay of N nights.", "MIN_STAY_REQUIRED", 400) (substitute N with the actual value).
   • For the rule of type checkin_days (if any): if the check-in day of the week is not permitted by checkin_days_bitmask, return errorResponse("Check-in is not permitted on the selected day.", "CHECKIN_DAY_NOT_PERMITTED", 400).

8. Retrieve SERVICE_FEE_PERCENTAGE from Vault. Call get_pricing_for_range(listingId, checkIn, checkOut) via the service client. Sum all returned price_cents values to get subtotalCents. Calculate serviceFeeCents = Math.floor(subtotalCents * percentage / 100). totalCents = subtotalCents + serviceFeeCents.

9. Insert a booking row via the service client with: listing_id, guest_user_id = userId, check_in_date, check_out_date, num_guests, total_price_cents = totalCents, service_fee_cents = serviceFeeCents, status = 'pending'. If the insert fails due to the GiST exclusion constraint (Postgres error code 23P01), return errorResponse("These dates are not available. Please select different dates.", "DATES_UNAVAILABLE", 409). Capture the new bookingId from the insert result.

10. Retrieve STRIPE_SECRET_KEY from Vault. Fetch the host's stripe_connect_account_id from the profiles table for listings.host_user_id.

11. Create a Stripe PaymentIntent:

    • Amount: totalCents
    • Application fee amount: serviceFeeCents
    • Transfer destination: host's stripe_connect_account_id
    • Payment method: stripePaymentMethodId
    • If listings.instant_book = true: confirm the PaymentIntent immediately with automatic capture
    • If listings.instant_book = false: create with manual capture (status will be requires_capture); do not confirm yet
    • On any Stripe error: call cancel_pending_booking(bookingId) via service client, then return errorResponse("Payment could not be processed. Please check your card details.", "PAYMENT_FAILED", 402)

12. If instant_book = true:

    • Call confirm_booking(bookingId, paymentIntentId) via service client
    • Send a booking confirmation email to the guest via sendEmail with subject "Your booking is confirmed" and relevant booking details in the body
    • Return successResponse({ bookingId, status: 'confirmed' }, 201)

13. If instant_book = false:

    • Update the booking row: status = 'pending_approval', stripe_payment_intent_id = paymentIntentId
    • Send an "awaiting host approval" email to the guest
    • Send a "new booking request" email to the host (fetch host email from their profile row)
    • Return successResponse({ bookingId, status: 'pending_approval' }, 201)

supabase/functions/stripe-webhook/index.ts

Processing sequence:

1. Read the raw request body as text (required for webhook signature verification — do not parse as JSON first).

2. Retrieve STRIPE_WEBHOOK_SIGNING_SECRET from Vault.

3. Verify the Stripe webhook signature using the raw body text and the signing secret. On verification failure: log the failure details server-side, then return successResponse({}) with status 200. This returns 200 (not an error status) because a non-200 response would cause Stripe to retry the event, and the request may not be from Stripe.

4. Parse the event from the raw body text as JSON.

5. Check the stripe_webhook_events table via service client for an existing row with stripe_event_id = event.id. If found, return successResponse({}) immediately (idempotency).

6. Insert a row into stripe_webhook_events: { stripe_event_id: event.id, event_type: event.type }.

7. Dispatch on event.type:

   • payment_intent.succeeded: Extract the paymentIntentId from the event. Query bookings for a row where stripe_payment_intent_id = paymentIntentId and status = 'confirmed'. If no such row exists, call confirm_booking(bookingId, paymentIntentId) for the matching pending booking row, send a confirmation email, and log an anomaly entry server-side (the Edge Function log is sufficient).

   • payment_intent.payment_failed: Find the matching booking by stripe_payment_intent_id. Update status = 'payment_failed'. Send a payment failure notification email to the guest.

   • transfer.failed: Find the matching booking by stripe_transfer_id. Update payout_status = 'failed'. Send an operator alert email via sendEmail to OPERATOR_ALERT_EMAIL (retrieved from Vault).

   • account.updated: Extract the connected account ID from the event. Update profiles where stripe_connect_account_id = event.account: set host_onboarding_status = 'verified'.

8. On any database error during dispatch: return a non-200 response so Stripe retries.

9. On success: return successResponse({}).

supabase/functions/booking-cancel/index.ts

Define the request schema using Zod:

• bookingId: UUID string
• initiatedBy: one of the values 'guest' or 'host'

Processing sequence:

1. requireAuth(req) → { userId, roles }. Return unauthorized on AuthError.
2. Validate request body. Return validation error on ValidationError.
3. Fetch the booking row via service client. If not found or status != 'confirmed', return errorResponse("Booking not found or cannot be cancelled.", "BOOKING_NOT_CANCELLABLE", 404).
4. Fetch the listing row to get host_user_id and cancellation_policy.
5. If initiatedBy = 'host': assert userId === listing.host_user_id. If not, return errorResponse("Not authorised to cancel this booking.", "FORBIDDEN", 403).
6. If initiatedBy = 'guest': assert userId === booking.guest_user_id. If not, return errorResponse("Not authorised to cancel this booking.", "FORBIDDEN", 403).
7. Calculate refund amount:
   • If initiatedBy = 'host': refundAmountCents = booking.total_price_cents (full refund, unconditional)
   • If initiatedBy = 'guest':
     • Days until check-in = difference between check_in_date and today's date
     • Policy flexible: full refund if days until check-in ≥ 1, else 50% of total_price_cents
     • Policy moderate: full refund if days until check-in ≥ 5, else 50% of total_price_cents
     • Policy strict: 50% of total_price_cents if days until check-in ≥ 7, else 0
8. Retrieve STRIPE_SECRET_KEY from Vault. Call the Stripe Refund API with refundAmountCents and the booking's stripe_payment_intent_id. On Stripe failure: return errorResponse("Refund could not be processed. Please contact support.", "REFUND_FAILED", 500) — do not update the booking status.
9. Update booking: status = 'cancelled_by_host' or 'cancelled_by_guest'; cancellation_reason = initiatedBy.
10. Send cancellation email to the guest. If initiatedBy = 'guest', also send a notification email to the host.
11. Return successResponse({ bookingId, refundAmountCents }).

supabase/functions/booking-approve/index.ts

Define the request schema using Zod:

• bookingId: UUID string
• action: one of the values 'approve' or 'decline'

Processing sequence:

1. requireAuth(req) → { userId, roles }. Return unauthorized on AuthError.
2. Validate request body.
3. Fetch the booking row via service client. Assert status = 'pending_approval'. If not, return errorResponse("Booking is not awaiting approval.", "INVALID_STATUS", 409).
4. Fetch the listing. Assert userId === listing.host_user_id. If not, return errorResponse("Not authorised.", "FORBIDDEN", 403).
5. Retrieve STRIPE_SECRET_KEY from Vault.
6. If action = 'approve':
   • Capture the Stripe PaymentIntent identified by booking.stripe_payment_intent_id
   • On Stripe failure: return errorResponse("Payment capture failed.", "PAYMENT_CAPTURE_FAILED", 500)
   • Call confirm_booking(bookingId, paymentIntentId) via service client
   • Send booking confirmation email to the guest
7. If action = 'decline':
   • Cancel the Stripe PaymentIntent
   • Call cancel_pending_booking(bookingId) via service client
   • Send a booking decline email to the guest
8. Return successResponse({ bookingId, status: action === 'approve' ? 'confirmed' : 'declined' }).

supabase/functions/host-onboarding/index.ts

Handles two sub-routes via the action query parameter.

action = 'create-link':

1. requireAuth(req) → { userId }. Return unauthorized on AuthError.
2. Retrieve STRIPE_SECRET_KEY and FRONTEND_URL from Vault.
3. Fetch the caller's profile row via service client.
4. If stripe_connect_account_id is null or empty: create a new Stripe Connect account. Store the resulting account ID on the profile: update profiles.stripe_connect_account_id.
5. Create a Stripe Connect hosted onboarding link for the account, with:
   • Return URL: {FRONTEND_URL}/host/onboarding-complete
   • Refresh URL: {FRONTEND_URL}/host/onboarding
6. Update profile: host_onboarding_status = 'pending_verification'. Add 'host' to the roles array if not already present (use a Postgres array append operation).
7. Return successResponse({ onboardingUrl }).

action = 'complete':

1. requireAuth(req) → { userId }. Return unauthorized on AuthError.
2. Fetch profile. If host_onboarding_status = 'verified', return successResponse({ status: 'verified' }) immediately.
3. Return successResponse({ status: 'pending_verification' }). The actual status update to verified arrives via the stripe-webhook account.updated event.

---

Step 2: Stripe Frontend Library

src/lib/stripe.ts

Export:

export const stripePromise: Promise<Stripe | null>

Initialize using loadStripe from the Stripe.js library with import.meta.env.VITE_STRIPE_PUBLISHABLE_KEY. This must be a module-level singleton — do not call loadStripe inside a component render function.

---

Step 3: Booking Hooks

src/hooks/useBooking.ts

Export:

export interface BookingRequest {
  listingId: string
  checkIn: string
  checkOut: string
  numGuests: number
  stripePaymentMethodId: string
}

export interface BookingResult {
  bookingId: string
  status: 'confirmed' | 'pending_approval'
}

export function useBooking(): {
  createBooking: (req: BookingRequest) => Promise<BookingResult>
  cancelBooking: (bookingId: string, initiatedBy: 'guest' | 'host') => Promise<{ refundAmountCents: number }>
  loading: boolean
  error: string | null
}

Behaviour:

• createBooking invokes the booking-create Edge Function with the request payload. The call must include the authenticated user's JWT. On success, returns BookingResult. On error, throws with a user-readable message mapped from these error codes: DATES_UNAVAILABLE → "These dates are not available. Please select different dates.", PAYMENT_FAILED → "Payment could not be processed. Please check your card details.", RATE_LIMITED → "You're making too many booking attempts. Please wait a moment.", MIN_STAY_REQUIRED and CHECKIN_DAY_NOT_PERMITTED → use the message from the error response directly.
• cancelBooking invokes the booking-cancel Edge Function. On success, returns the refund amount. On error, throws with a user-readable message.
• loading reflects whether either function is in progress.
• error holds the last error message, or null.

src/hooks/useGuestBookings.ts

Export:

export interface GuestBooking {
  id: string
  listingId: string
  listingTitle: string
  checkIn: string
  checkOut: string
  numGuests: number
  totalPriceCents: number
  serviceFeeCents: number
  status: string
  payoutStatus: string
  createdAt: string
}

export function useGuestBookings(): {
  bookings: GuestBooking[]
  loading: boolean
  error: string | null
}

Behaviour:

• Queries PostgREST for rows in bookings joined with listings (to get the listing title) where guest_user_id = auth.uid(), ordered by created_at descending. RLS ensures guests can only see their own bookings.

---

Step 4: Booking UI Components and Pages

src/components/BookingPanel.tsx

interface BookingPanelProps {
  listing: ListingDetail
  checkIn: string
  checkOut: string
  numGuests: number
  onCheckInChange: (date: string) => void
  onCheckOutChange: (date: string) => void
  onNumGuestsChange: (count: number) => void
}
export function BookingPanel(props: BookingPanelProps): JSX.Element

Renders:

• Date inputs for check-in and check-out (pre-populated from props, editable).
• A guest count stepper bounded by 1 and listing.maxGuests.
• A price breakdown section: calculated per-night cost, number of nights, service fee (displayed as a percentage), and total. Recalculates when dates or guest count change. Use the listing.basePriceCents for the per-night estimate — the exact amount is calculated server-side; display this as an estimate.
• A Stripe CardElement wrapped in a Stripe Elements provider using stripePromise. The card element must be rendered inside Stripe's hosted iframe — never collect raw card data outside it.
• A "Confirm Booking" button. On click:
  1. Call stripe.createPaymentMethod({ type: 'card', card: cardElement }) to get a paymentMethodId
  2. Call useBooking().createBooking(...) with the paymentMethodId
  3. On success with status = 'confirmed': show "Booking confirmed! Check your email." message
  4. On success with status = 'pending_approval': show "Booking request sent! The host will review your request within 24 hours."
  5. On error: display the error message returned from useBooking
• While useBooking().loading is true, disable the button and show a loading indicator.

Wire the BookingPanel into ListingDetailPage: render it below the listing description, pre-populated with the checkIn, checkOut, and numGuests values from the URL query parameters.

src/pages/GuestBookingsPage.tsx

Replace the placeholder at /bookings. Shows the authenticated guest's booking history from useGuestBookings. For each booking:

• Display: listing title, check-in and check-out dates, number of guests, total price (formatted as dollars), status label, and payout status.
• If status = 'confirmed', show a "Cancel Booking" button. On click, show a confirmation dialog that displays the refund amount estimate based on the listing's cancellation policy (use the policy from the booking's listing). On confirm, call useBooking().cancelBooking(bookingId, 'guest'). Show the refund amount in a success message after cancellation.
• If status = 'pending_approval', show a "Pending host approval" label. No cancel action from this page.

src/pages/host/HostOnboardingPage.tsx

Replace the placeholder at /host/onboarding and /host/onboarding-complete. Shows the current hostOnboardingStatus from useAuth():

• If not_started or pending_verification: display an explanation of why Stripe Connect onboarding is required, and a "Connect with Stripe" button that calls the host-onboarding Edge Function with action = 'create-link' and then redirects the browser to the returned onboardingUrl.
• If verified: display a success state — "Your account is verified. You can now publish listings."
• On the /host/onboarding-complete route, call the Edge Function with action = 'complete' on mount and call useAuth().refreshUser() to pick up any status changes, then display the appropriate state.

---

Step 5: Stripe Webhook Setup Note

After deploying the stripe-webhook Edge Function, the function's URL is available in the Supabase dashboard under Edge Functions. You will need to register this URL as a Stripe webhook endpoint. Complete the following steps now in your Stripe test-mode dashboard:

1. Go to Stripe → Developers → Webhooks → Add endpoint
2. Set the endpoint URL to the stripe-webhook Edge Function URL from your Supabase homestay-dev project
3. Select these events: payment_intent.succeeded, payment_intent.payment_failed, transfer.failed, account.updated
4. Copy the signing secret and update the STRIPE_WEBHOOK_SIGNING_SECRET value in your homestay-dev Supabase Vault

---

Check before continuing (do this yourself, do not paste it):

• Using a Stripe test card number, complete a booking on an instant-book listing end-to-end. Confirm the booking appears in the guest bookings page at /bookings with status = confirmed.
• Cancel the confirmed booking from the guest bookings page. Confirm the status changes to cancelled_by_guest and a refund amount is shown.
• In Stripe's test dashboard, use the webhook event simulator to send a payment_intent.succeeded event and confirm the stripe-webhook function receives it (check Supabase Edge Function logs — no errors should appear).
• Begin the host Stripe Connect onboarding flow at /host/onboarding and confirm you are redirected to Stripe's hosted onboarding page. If something is wrong: paste the error from the Supabase Edge Function logs into Lovable and ask it to fix the specific step.

---

Prompt 5

Run this after Prompt 4 is complete.

This prompt builds the complete host-side experience: a dashboard for managing listings, a full listing editor with photos, amenities, availability rules, and pricing overrides, and a host bookings management page. When this prompt is complete, a verified host can create and publish a listing, manage all its settings, and respond to booking requests. You do not need to understand the instructions below.

---

Paste everything below into your AI coding tool:

You are building Homestay, a two-sided short-term rental marketplace. Prompts 1–4 are complete: the database, auth, listing browse, booking flow, and host onboarding are all in place. This prompt builds the host-side listing management dashboard.

Use Plan mode before generating any component that reads from or writes to listings, listing_photos, listing_amenities, availability_rules, or pricing_overrides. Confirm each query aligns with the RLS policies for those tables — hosts can only access records linked to listings they own.

Do not build scheduled background jobs, admin screens, or CCPA flows in this prompt.

---

Step 1: Edge Function

supabase/functions/listing-photo-confirm/index.ts

Define the request schema using Zod:

• listingId: UUID string
• storagePath: non-empty string (the path within the photos bucket, e.g. pending/{listingId}/{uuid})
• displayOrder: integer, minimum 0

Processing sequence:

1. requireAuth(req) → { userId }. Return unauthorized on AuthError.
2. Validate request body. Return validation error on ValidationError.
3. Fetch the listings row for listingId via service client. Assert listing.host_user_id === userId. If not, return errorResponse("Not authorised.", "FORBIDDEN", 403).
4. Verify the Storage object exists at storagePath in the photos bucket using the service client. If it does not exist, return errorResponse("Photo not found in storage. Please try uploading again.", "PHOTO_NOT_FOUND", 404).
5. Construct the destination path by replacing the pending/ prefix in storagePath with photos/ (e.g. pending/{listingId}/{uuid} → photos/{listingId}/{uuid}). Move the object by copying it to the new path and then deleting the source.
6. Insert a row into listing_photos: { listing_id: listingId, storage_path: destinationPath, display_order: displayOrder }. Capture the new photoId.
7. Return successResponse({ photoId, storagePath: destinationPath }).

---

Step 2: Host Data Hooks

src/hooks/useHostListings.ts

Export:

export interface HostListingSummary {
  id: string
  title: string
  location: string
  status: string
  basePriceCents: number
  instantBook: boolean
  createdAt: string
}

export interface CreateListingFields {
  title: string
  description?: string
  location: string
  latitude?: number
  longitude?: number
  maxGuests: number
  basePriceCents: number
  cancellationPolicy: string
  instantBook: boolean
  houseRules?: string
}

export function useHostListings(): {
  listings: HostListingSummary[]
  loading: boolean
  error: string | null
  createListing: (fields: CreateListingFields) => Promise<{ id: string }>
  updateListing: (id: string, fields: Partial<CreateListingFields>) => Promise<void>
  setListingStatus: (id: string, status: 'active' | 'inactive') => Promise<void>
}

Behaviour:

• Fetches all listings for the authenticated user via PostgREST where host_user_id = auth.uid(). The RLS SELECT (own) policy on listings makes this work — it returns drafts, active, and inactive listings.
• createListing inserts a new listing row via PostgREST with status = 'draft'. RLS enforces that host_user_id is set to auth.uid().
• updateListing patches the listing row via PostgREST. RLS enforces host ownership.
• setListingStatus patches status on the listing. Before calling PostgREST for status = 'active', check the authenticated user's hostOnboardingStatus from useAuth(). If it is not 'verified', throw with message "You must complete Stripe Connect onboarding before publishing a listing." and do not make the API call.

src/hooks/useAvailabilityRules.ts

Export:

export interface AvailabilityRule {
  id: string
  listingId: string
  ruleType: 'blocked_range' | 'min_stay' | 'checkin_days'
  blockedStart?: string
  blockedEnd?: string
  minStayNights?: number
  checkinDaysBitmask?: number
}

export function useAvailabilityRules(listingId: string): {
  rules: AvailabilityRule[]
  loading: boolean
  addRule: (rule: Omit<AvailabilityRule, 'id' | 'listingId'>) => Promise<void>
  deleteRule: (ruleId: string) => Promise<void>
}

Behaviour: All operations via PostgREST. RLS enforces host ownership through the listing_id → listings.host_user_id join. Refreshes the rules list after add or delete.

src/hooks/usePricingOverrides.ts

Export:

export interface PricingOverride {
  id: string
  listingId: string
  overrideType: 'seasonal' | 'weekend'
  seasonStart?: string
  seasonEnd?: string
  priceCents: number
}

export function usePricingOverrides(listingId: string): {
  overrides: PricingOverride[]
  loading: boolean
  addOverride: (override: Omit<PricingOverride, 'id' | 'listingId'>) => Promise<void>
  deleteOverride: (overrideId: string) => Promise<void>
}

Behaviour: All operations via PostgREST. Same RLS ownership model as availability rules.

src/hooks/useListingPhotos.ts

Export:

export function useListingPhotos(listingId: string): {
  photos: Array<{ id: string; storageUrl: string; displayOrder: number }>
  loading: boolean
  uploadPhoto: (file: File, displayOrder: number) => Promise<void>
  deletePhoto: (photoId: string, storagePath: string) => Promise<void>
  reorderPhoto: (photoId: string, newDisplayOrder: number) => Promise<void>
}

Behaviour:

• Fetches listing_photos rows for the listing via PostgREST, ordered by display_order. Generates signed read URLs for each photo.
• uploadPhoto: (1) calls supabase.storage.createSignedUploadUrl targeting the path pending/{listingId}/{crypto.randomUUID()} in the photos bucket; (2) uploads the file directly to Storage using the signed upload URL; (3) calls the listing-photo-confirm Edge Function with the storage path and display order; (4) refreshes the photos list on success.
• deletePhoto: deletes the listing_photos row via PostgREST (RLS enforces host ownership), then deletes the Storage object at storagePath.
• reorderPhoto: patches display_order on the listing_photos row via PostgREST.

src/hooks/useAmenities.ts

Export:

export function useAmenities(listingId: string): {
  amenities: Array<{ id: string; amenity: string }>
  loading: boolean
  addAmenity: (amenity: string) => Promise<void>
  removeAmenity: (amenityId: string) => Promise<void>
}

Behaviour: All operations via PostgREST. RLS enforces host ownership.

src/hooks/useHostBookings.ts

Export:

export interface HostBooking {
  id: string
  guestUserId: string
  guestName: string | null
  checkIn: string
  checkOut: string
  numGuests: number
  totalPriceCents: number
  status: string
  payoutStatus: string
  createdAt: string
}

export function useHostBookings(listingId: string): {
  bookings: HostBooking[]
  loading: boolean
  error: string | null
  approveBooking: (bookingId: string) => Promise<void>
  declineBooking: (bookingId: string) => Promise<void>
  cancelBooking: (bookingId: string) => Promise<void>
}

Behaviour:

• Fetches bookings from PostgREST for the given listingId, joined with profiles to get full_name as guestName. The RLS host SELECT policy on bookings permits this — it allows hosts to see bookings for their own listings.
• approveBooking: invokes the booking-approve Edge Function with { bookingId, action: 'approve' }. Refreshes the list on success.
• declineBooking: invokes the booking-approve Edge Function with { bookingId, action: 'decline' }. Refreshes the list on success.
• cancelBooking: invokes the booking-cancel Edge Function with { bookingId, initiatedBy: 'host' }. Refreshes the list on success.

---

Step 3: Host Pages

src/pages/host/HostDashboardPage.tsx

Replace the placeholder at /host. Displays a list of the host's listings using useHostListings. For each listing, show: title, location, status badge, base price per night. Link each listing to /host/listings/:id. Include a prominent "Create New Listing" button that navigates to /host/listings/new. Show a count of upcoming bookings per listing as a summary stat (query from useHostBookings for each listing, counting bookings with status = 'confirmed' and check_in_date in the future).

src/pages/host/ListingEditorPage.tsx

Replace the placeholders at /host/listings/new and /host/listings/:id. When the route is /host/listings/new, the page creates a new listing on first save. When the route includes an existing ID, the page loads and edits that listing.

Structure the page as a tabbed editor with these tabs:

• Basic Info: Fields for title (required), description, location (required), latitude, longitude, maximum guests (required, integer), base price per night in dollars (required, stored as cents), house rules. Save button at the bottom.

• Photos: Grid of current photos with drag-to-reorder (update displayOrder via reorderPhoto on drop). Each photo has a delete button. An upload button opens a file picker that calls useListingPhotos().uploadPhoto. Show upload progress. Photos display in displayOrder order.

• Amenities: List of existing amenities with a remove button for each. An input to add a new amenity by text. Uses useAmenities.

• Availability Rules: Shows current rules. Form to add a new rule with a type selector:

  • blocked_range: date range picker for start and end
  • min_stay: integer input for minimum nights
  • checkin_days: a set of day-of-week checkboxes (Sunday through Saturday) that encode into the checkinDaysBitmask value. Bit 0 = Sunday, bit 1 = Monday, through bit 6 = Saturday. Delete button for each existing rule.

• Pricing Overrides: Shows current overrides. Form to add a new override:

  • Type seasonal: date range for season start and end, price per night in dollars
  • Type weekend: price per night in dollars (no dates required) Delete button for each existing override.

• Settings: Instant book toggle. Cancellation policy selector (Flexible / Moderate / Strict). Listing status control:

  • If status = 'draft': "Publish Listing" button that calls setListingStatus('active'). If hostOnboardingStatus != 'verified', show a warning: "Complete Stripe Connect onboarding to publish this listing" and disable the button.
  • If status = 'active': "Deactivate Listing" button that calls setListingStatus('inactive').
  • If status = 'inactive': "Reactivate Listing" button that calls setListingStatus('active').

src/pages/host/HostBookingsPage.tsx

Replace the placeholder at /host/bookings. Shows all bookings across all of the host's listings. For each booking, display: listing title (link to the listing), guest name, check-in and check-out dates, number of guests, total price, status, and payout status. Action buttons:

• If status = 'pending_approval': "Approve" and "Decline" buttons.
• If status = 'confirmed': "Cancel Booking" button with a confirmation dialog.

Use useHostBookings — call it for each of the host's listings from useHostListings and combine the results.

---

Check before continuing (do this yourself, do not paste it):

• Sign in as a verified host (complete the Stripe Connect onboarding flow from Prompt 4 first). Navigate to /host and confirm the dashboard loads with a "Create New Listing" button.
• Create a listing, fill in all required fields, and save. Navigate to the Photos tab and upload at least one image — confirm it appears in the grid after upload.
• Navigate to the Settings tab and click "Publish Listing". Confirm the status changes to active. Then visit the public search page at / — the listing should appear in search results.
• Try publishing a listing while logged in as a user whose host_onboarding_status is not verified. The button should be disabled with the warning message shown. If something is wrong: describe what is missing or broken and ask Lovable to fix that specific tab or action.

---

Prompt 6

Run this after Prompt 5 is complete.

This prompt creates the three scheduled background functions that run automatically: one that pays out hosts after guests check in, one that cancels booking requests that hosts do not respond to within 24 hours, and one that handles user data deletion and export requests overnight. When this prompt is complete, these processes run on their own schedules without any manual action. You do not need to understand the instructions below.

---

Paste everything below into your AI coding tool:

You are building Homestay, a two-sided short-term rental marketplace. Prompts 1–5 are complete: the full database, auth, listing management, and booking flows are in place. This prompt creates three scheduled Edge Functions that are triggered automatically by pg_cron. These functions have no frontend component.

Do not build any admin UI, CCPA request endpoints, or CI/CD configuration in this prompt.

---

Step 1: Scheduled Edge Functions

All three functions share the same caller-verification contract: before performing any work, each function must verify that the request was made by pg_cron by checking the Authorization header for a Bearer token matching the CRON_SECRET value retrieved from Vault. If the token does not match, return errorResponse("Unauthorized", "UNAUTHORIZED", 401) immediately.

All three functions must be idempotent: running them multiple times produces no additional side effects on records already processed (no double-payouts, no double-scrubs, no duplicate Stripe calls).

supabase/functions/payout-scheduler/index.ts

Processing sequence:

1. Verify caller via CRON_SECRET as described above.
2. Log run start to the Edge Function console.
3. Retrieve STRIPE_SECRET_KEY from Vault once for the entire run — do not retrieve it per booking.
4. Query via service client: all bookings rows where status = 'confirmed' AND check_in_date < NOW() AND payout_status = 'pending'.
5. For each booking in the result: a. Fetch the host's stripe_connect_account_id from profiles where id = listings.host_user_id for this booking's listing_id. b. Calculate the transfer amount: booking.total_price_cents - booking.service_fee_cents. c. Create a Stripe Transfer: amount = transfer amount, destination = stripe_connect_account_id. The transfer represents the host's payout after the service fee has been deducted. d. On Stripe success: update bookings: payout_status = 'completed', stripe_transfer_id = {transferId}. e. On Stripe failure: update bookings: payout_status = 'failed'. Retrieve OPERATOR_ALERT_EMAIL from Vault and send an alert email via sendEmail with the booking ID and the Stripe error message (log the raw Stripe error server-side; only include the booking ID in the email body). Log the Stripe error to the Edge Function console.
6. Log run completion with counts: total processed, completed successfully, failed.
7. Return successResponse({ processed: totalCount, completed: completedCount, failed: failedCount }).

supabase/functions/booking-expire/index.ts

Processing sequence:

1. Verify caller via CRON_SECRET.
2. Retrieve STRIPE_SECRET_KEY from Vault.
3. Query via service client: all bookings rows where status = 'pending_approval' AND created_at < NOW() - interval '24 hours'.
4. For each booking: a. Cancel the Stripe PaymentIntent identified by booking.stripe_payment_intent_id. b. On Stripe failure: log the error to the console. Still proceed to step c — do not leave the booking in pending_approval even if Stripe cancellation fails. Log for operator follow-up. c. Call cancel_pending_booking(bookingId) via service client. d. Fetch the guest's email from profiles where id = booking.guest_user_id. Send a "Your booking request has expired — the host did not respond within 24 hours" email via sendEmail.
5. Return successResponse({ expired: count }).

supabase/functions/ccpa-scrubber/index.ts

Processing sequence:

1. Verify caller via CRON_SECRET.
2. Process export requests first: a. Query ccpa_requests via service client where request_type = 'export' AND completed_at IS NULL. b. For each request:
   • Fetch the user's profile row and all their booking rows (excluding stripe_payment_intent_id and stripe_transfer_id) via service client.
   • Assemble a JSON object containing the profile data and booking data.
   • Write the JSON as a file to the ccpa-exports Storage bucket at path exports/{userId}/{requestId}.json using the service client.
   • Generate a signed URL for this file with a 1-hour expiry using the service client.
   • Send the signed URL to the user's current profile email via sendEmail with subject "Your Homestay data export is ready".
   • Update the ccpa_requests row: completed_at = now().
3. Process deletion requests in batches of 50: a. Query profiles via service client where deletion_status = 'pending' AND deletion_requested_at < NOW() - interval '30 days' LIMIT 50. b. For each user in the batch:
   • Call scrub_user_pii(userId) via service client. This is committed individually — a failure on one user must not prevent others from being processed. Wrap this in a try/catch; on failure, log the error and continue.
   • If the profile row has a non-null avatar_url, attempt to delete the Storage object at that path. If deletion fails, log the error but do not abort.
   • Update the matching ccpa_requests row for this user (if any): completed_at = now(). c. After processing each batch, wait 100ms before querying the next batch (if more than 50 users are pending, loop with this sleep).
4. Purge old webhook events: Call purge_old_webhook_events() via the service client. Log the count of deleted rows.
5. Return successResponse({ exportedCount, scrubbedCount, purgedWebhookEvents }).

---

Step 2: Verify pg_cron Registration

The pg_cron jobs were registered in the 008_cron_jobs.sql migration from Prompt 1. Verify they are active:

1. In the Supabase dashboard for homestay-dev, go to Database → Extensions and confirm pg_cron is enabled.
2. Run this SQL in the Supabase SQL editor: SELECT jobname, schedule, active FROM cron.job; — confirm three jobs appear: payout-scheduler, booking-expire, and ccpa-scrubber, all with active = true.
3. If the jobs are not registered, re-run the 008_cron_jobs.sql migration manually via the SQL editor, substituting the correct Edge Function URLs for your homestay-dev project and the CRON_SECRET value from Vault.

---

Check before continuing (do this yourself, do not paste it):

• In the Supabase SQL editor, manually call SELECT net.http_post(url := '{your-homestay-dev-edge-function-url}/payout-scheduler', headers := '{"Authorization": "Bearer {your-CRON_SECRET}"}', body := '{}') substituting real values. Then check the Supabase Edge Function logs — the function should log a run start and completion with zero records processed (since no payouts are due yet in your dev environment). If something is wrong: paste the error from the Edge Function logs into Lovable and ask it to fix the specific function.
• Create a test booking with a past check-in date and payout_status = 'pending' directly in the database, then invoke payout-scheduler manually again. Confirm payout_status changes to completed in the bookings table.
• Confirm booking-expire and ccpa-scrubber respond with a success body when invoked with the correct CRON_SECRET (zero records processed is acceptable for a fresh dev environment).

---

Prompt 7

Run this after Prompt 6 is complete.

This prompt adds the operator admin dashboard and the CCPA request flows for guests. When this prompt is complete, admin users can view all users, listings, and bookings; retry failed payouts; activate or deactivate any listing; and support agents can view everything but cannot make changes. Guests can request deletion or export of their data from the profile page. You do not need to understand the instructions below.

---

Paste everything below into your AI coding tool:

You are building Homestay, a two-sided short-term rental marketplace. Prompts 1–6 are complete. This prompt adds the admin and support operator interface and the CCPA data request endpoints for guests.

Use Plan mode before generating any component that reads from the admin Edge Function — confirm the queries use the service-role client, not PostgREST with the user's JWT.

Do not build CI/CD pipelines, security headers configuration, or deployment workflows in this prompt.

---

Step 1: Edge Functions

supabase/functions/admin/index.ts

All admin Edge Function routes share this authorization pattern:

1. requireAuth(req) → { userId, roles }. Return unauthorized on AuthError.
2. If roles does not include 'admin' or 'support': return errorResponse("Forbidden", "FORBIDDEN", 403).
3. For any write action (those explicitly marked below): if roles does not include 'admin', return errorResponse("Forbidden", "FORBIDDEN", 403). The support role is read-only.

The action is determined by the action query parameter on the request URL.

action = 'list-users' (read):

• Accept optional query parameters email and deletionStatus for filtering.
• Query profiles via service client. Apply filters if provided: email as a case-insensitive partial match on profiles.email; deletionStatus as an exact match on deletion_status.
• Return successResponse({ users }).

action = 'list-listings' (read):

• Accept optional query parameters hostUserId and status for filtering.
• Query all listings rows via service client (any status). Apply filters if provided.
• Return successResponse({ listings }).

action = 'list-bookings' (read):

• Accept optional query parameters listingId, guestUserId, and status for filtering.
• Query all bookings rows via service client. Apply filters if provided.
• Return successResponse({ bookings }).

action = 'retry-payout' (write, admin only):

• Validate request body with Zod schema: { bookingId: uuid string }.
• Fetch booking via service client. Assert payout_status = 'failed'. If not, return errorResponse("Payout is not in failed status.", "INVALID_STATUS", 409).
• Retrieve STRIPE_SECRET_KEY from Vault.
• Fetch host's stripe_connect_account_id from profiles.
• Create a Stripe Transfer: amount = booking.total_price_cents - booking.service_fee_cents, destination = stripe_connect_account_id.
• On success: update booking payout_status = 'completed', stripe_transfer_id. Return successResponse({ bookingId, stripeTransferId }).
• On failure: return errorResponse("Payout retry failed. Check Stripe for details.", "PAYOUT_RETRY_FAILED", 500).

action = 'flag-listing' (write, admin only):

• Validate request body with Zod schema: { listingId: uuid string, active: boolean }.
• Update listings.status to 'active' if active = true, or 'inactive' if active = false. Use the service client — bypasses the soft-delete trigger since this is an UPDATE, not a DELETE.
• Return successResponse({ listingId, status }).

action = 'list-hosts-by-verification' (read):

• Accept optional query parameter hostOnboardingStatus for filtering.
• Query profiles via service client where 'host' = ANY(roles). Apply filter if provided.
• Return successResponse({ hosts }).

supabase/functions/ccpa-request/index.ts

Define the request schema using Zod:

• requestType: one of 'deletion' or 'export'

Processing sequence:

1. requireAuth(req) → { userId }. Return unauthorized on AuthError.
2. Validate request body. Return validation error on ValidationError.
3. If requestType = 'deletion':
   • Query ccpa_requests via service client for an existing row where user_id = userId AND request_type = 'deletion' AND completed_at IS NULL. If found: return errorResponse("A deletion request is already pending for your account.", "REQUEST_ALREADY_PENDING", 409).
   • Insert into ccpa_requests: { user_id: userId, request_type: 'deletion', requested_at: now() }.
   • Update profiles via service client: deletion_status = 'pending', deletion_requested_at = now().
   • Send confirmation email to the user: "Your account deletion request has been received. Your data will be removed within 30 days."
4. If requestType = 'export':
   • Insert into ccpa_requests: { user_id: userId, request_type: 'export', requested_at: now() }.
   • Send confirmation email: "Your data export request has been received. You will receive a download link within 24 hours."
5. Return successResponse({ requestType, requestedAt: now().toISOString() }).

---

Step 2: Frontend Hooks

src/hooks/useAdmin.ts

Export:

export interface AdminUser {
  id: string
  email: string | null
  fullName: string | null
  roles: string[]
  hostOnboardingStatus: string
  deletionStatus: string
  createdAt: string
}

export interface AdminListing {
  id: string
  title: string
  hostUserId: string
  status: string
  location: string
  createdAt: string
}

export interface AdminBooking {
  id: string
  listingId: string
  guestUserId: string
  checkIn: string
  checkOut: string
  status: string
  payoutStatus: string
  totalPriceCents: number
  createdAt: string
}

export function useAdmin(): {
  fetchUsers: (filters?: { email?: string; deletionStatus?: string }) => Promise<AdminUser[]>
  fetchListings: (filters?: { hostUserId?: string; status?: string }) => Promise<AdminListing[]>
  fetchBookings: (filters?: { listingId?: string; guestUserId?: string; status?: string }) => Promise<AdminBooking[]>
  retryPayout: (bookingId: string) => Promise<{ stripeTransferId: string }>
  flagListing: (listingId: string, active: boolean) => Promise<void>
  loading: boolean
  error: string | null
}

Behaviour: Each function invokes the admin Edge Function with the appropriate action query parameter and passes the filter values as query parameters or request body as needed. Includes the authenticated user's JWT in the request. On any error response with code FORBIDDEN, throw with message "You do not have permission to perform this action."

src/hooks/useCcpaRequest.ts

Export:

export function useCcpaRequest(): {
  requestDeletion: () => Promise<void>
  requestExport: () => Promise<void>
  loading: boolean
  error: string | null
}

Behaviour:

• requestDeletion: invokes the ccpa-request Edge Function with requestType = 'deletion'. On REQUEST_ALREADY_PENDING error, sets error to "A deletion request is already pending for your account." without throwing.
• requestExport: invokes the ccpa-request Edge Function with requestType = 'export'.
• On success, sets error to null.

---

Step 3: Admin Pages

src/pages/admin/AdminDashboardPage.tsx

Replace the placeholder at /admin. Renders a top-level layout with navigation tabs for: Users, Listings, Bookings, Hosts. Each tab links to the corresponding admin sub-page. Show the current user's role as a label (Admin or Support).

src/pages/admin/AdminUsersPage.tsx

Replace the placeholder at /admin/users. Shows a filterable table of users from useAdmin().fetchUsers. Columns: email, full name, roles, onboarding status, deletion status, created date. Includes a filter input for email and a dropdown for deletion status. The table is read-only — no action buttons.

src/pages/admin/AdminListingsPage.tsx

Replace the placeholder at /admin/listings. Shows a filterable table of all listings from useAdmin().fetchListings. Columns: title, host ID, location, status, created date. Includes a status filter dropdown. For each listing, show an "Activate" or "Deactivate" button based on current status. These buttons call useAdmin().flagListing. Buttons are disabled (greyed out with a tooltip "Read-only access") for users whose role is support.

src/pages/admin/AdminBookingsPage.tsx

Replace the placeholder at /admin/bookings. Shows a filterable table of all bookings from useAdmin().fetchBookings. Columns: booking ID, listing ID, guest ID, check-in, check-out, status, payout status, total price. Includes filters for status and payout status. For bookings with payoutStatus = 'failed', show a "Retry Payout" button that calls useAdmin().retryPayout. The button is disabled for support role users.

src/pages/admin/AdminHostsPage.tsx

Replace the placeholder at /admin/hosts. Shows a filterable list of host profiles from useAdmin().fetchUsers filtered to include only users with 'host' in their roles, with an additional filter for hostOnboardingStatus. Read-only.

---

Step 4: Profile Page Updates

Update src/pages/ProfilePage.tsx to wire up the CCPA buttons that were placeholders in Prompt 2:

• "Request account deletion" button: on click, show a confirmation dialog explaining that personal data will be deleted within 30 days and that booking history is retained for legal purposes. On confirm, call useCcpaRequest().requestDeletion. On success, show a confirmation message: "Your deletion request has been received." Disable the button and show "Deletion requested" if profile.deletionStatus === 'pending'.

• "Request my data export" button: on click, call useCcpaRequest().requestExport. On success, show: "Your data export request has been received. You will receive an email with a download link within 24 hours."

Both buttons show a loading state while useCcpaRequest().loading is true.

---

Check before continuing (do this yourself, do not paste it):

• Sign in as a user with the admin role (set this directly in the Supabase dashboard by updating the roles column on the profile row). Navigate to /admin/users — you should see the users table populated.
• Navigate to /admin/listings and deactivate a listing using the Deactivate button. Confirm the listing no longer appears in the public search results at /.
• Sign in as a user with the support role. Navigate to /admin/bookings — the "Retry Payout" button should be visible but disabled. Attempting to call the action directly should return a Forbidden error (check Edge Function logs). If something is wrong: describe the specific button or page that is incorrect and ask Lovable to fix it.
• From the profile page as a regular guest, click "Request account deletion" and confirm the dialog. Verify deletion_status changes to pending in the Supabase dashboard under the profiles table.

---

Prompt 8

Run this after Prompt 7 is complete.

This prompt sets up the automated deployment pipelines, security headers, and test suite. When this prompt is complete, every code change is automatically tested before it can be merged, staging deploys happen automatically, production deploys require manual approval, and the production site returns the security headers required for PCI and general web security. You do not need to understand the instructions below.

---

Paste everything below into your AI coding tool:

You are building Homestay, a two-sided short-term rental marketplace. Prompts 1–7 are complete: the full product is built. This prompt adds the GitHub Actions CI/CD pipelines, integration tests, smoke tests, and Netlify security headers configuration. No new product features are added in this prompt.

Do not modify any existing Edge Function, page, hook, or migration file. Only create new files for workflows, tests, and deployment configuration.

---

Step 1: Integration Tests

supabase/tests/rls.test.ts

Write integration tests using the Deno test runner. These tests run against a local Supabase instance (started by the CI workflow). Each test must authenticate as a specific user using the Supabase JS client and assert the exact data visible from that perspective.

Tests to implement:

• Unauthenticated access to listings: An unauthenticated client (anon key only) queries the listings table. Assert it receives only rows with status = 'active'. Assert it receives zero rows from bookings. Assert it receives zero rows from profiles.

• Profile isolation: User A (authenticated) queries the profiles table. Assert it receives exactly their own profile row and no other rows.

• Booking isolation: User A queries the bookings table. Assert it receives only bookings where guest_user_id matches User A's ID. Assert it receives zero rows from User B's bookings.

• Host listing access: Host A queries the listings table. Assert it receives their own listings of any status (including drafts). Assert it does not receive Host B's draft listings.

• Direct booking insert blocked: Authenticated User A attempts to insert a row directly into the bookings table via PostgREST. Assert the operation fails — no row should be inserted.

• Listing soft delete: Host A issues a DELETE on one of their own listing rows via PostgREST. Assert the row still exists in the database. Assert the row's status is 'inactive'.

supabase/tests/booking-lock.test.ts

Write a concurrency test using the Deno test runner:

• Create a test listing with instant_book = true and status = 'active' via the service-role client.
• Fire two concurrent booking-create Edge Function invocations for the same listing and overlapping dates (use Promise.all to make them truly concurrent).
• Assert that exactly one response has a success status (status code in the 2xx range) with status in the response body equal to 'confirmed' or 'pending_approval'.
• Assert that the other response has an error status with error code DATES_UNAVAILABLE or BOOKING_CONFLICT.
• Query the bookings table directly via service client. Assert exactly one row exists for that listing and date range with a status that is not 'declined'.

supabase/tests/ccpa.test.ts

Write an end-to-end CCPA test using the Deno test runner:

• Create a test user via the Supabase auth admin API (service role) with a known email.
• Insert a booking row for that user via service client.
• Call the ccpa-request Edge Function as that user with requestType = 'deletion'. Assert a success response.
• Directly invoke scrub_user_pii(userId) via the service client (bypassing the 30-day wait that applies in production).
• Query profiles for that user. Assert full_name = '[deleted]', email = '[deleted]', phone IS NULL, avatar_url IS NULL, deletion_status = 'completed'.
• Query bookings for that user's guest_user_id. Assert the booking row still exists (the row must be retained even after PII scrubbing).

supabase/tests/smoke.ts

Write smoke tests to run against a deployed environment (staging or production URL passed as an environment variable SMOKE_TEST_URL):

• A GET request to the PostgREST listings endpoint at {SMOKE_TEST_URL}/rest/v1/listings. Assert a successful response with a JSON array body.
• A GET request to each deployed Edge Function URL (booking-create, stripe-webhook, booking-cancel, booking-approve, host-onboarding, listing-photo-confirm, payout-scheduler, booking-expire, ccpa-scrubber, admin, ccpa-request) with no Authorization header. Assert the response is an unauthorized error response — not a server error and not a "function not found" response.
• A POST request to {SMOKE_TEST_URL}/functions/v1/stripe-webhook with a deliberately malformed body and a fake signature header. Assert the response status is 200 (the webhook handler returns 200 even on signature failure to prevent Stripe retry loops).

---

Step 2: GitHub Actions Workflows

.github/workflows/pr.yml

Trigger: on pull request opened, synchronized, or reopened targeting the main branch.

Steps:

1. Check out the repository.
2. Install the Supabase CLI.
3. Run supabase db lint against all files in supabase/migrations/. Fail the workflow if any lint errors are reported.
4. Run Deno unit tests: deno test supabase/functions/ — this runs any *.test.ts files found within the Edge Function source directories.
5. Start a local Supabase instance using supabase start.
6. Apply all migrations to the local instance using supabase db push --local.
7. Run integration tests: execute supabase/tests/rls.test.ts, supabase/tests/booking-lock.test.ts, and supabase/tests/ccpa.test.ts using the Deno test runner, with the local Supabase URL and service-role key injected as environment variables.
8. Stop the local Supabase instance using supabase stop.

The workflow must fail on any step error. PRs cannot be merged if this workflow fails.

.github/workflows/staging-deploy.yml

Trigger: on push to the main branch.

Steps:

1. Check out the repository.
2. Install the Supabase CLI.
3. Set the Supabase project to the staging project reference using SUPABASE_ACCESS_TOKEN and SUPABASE_STAGING_PROJECT_REF from GitHub secrets.
4. Run supabase db push targeting the staging project.
5. Run supabase functions deploy for all Edge Functions in supabase/functions/, targeting the staging project.
6. Run smoke tests: execute supabase/tests/smoke.ts with SMOKE_TEST_URL set to SUPABASE_STAGING_URL from GitHub secrets.
7. Send a deployment notification email via Resend regardless of success or failure. The email goes to OPERATOR_ALERT_EMAIL (stored as a GitHub secret) with subject "Homestay staging deploy: succeeded" or "Homestay staging deploy: failed" and the workflow run URL in the body. Use the Resend API directly from the workflow using RESEND_API_KEY from GitHub secrets.

.github/workflows/production-deploy.yml

Trigger: manual (workflow_dispatch only). Environment: production — this environment requires reviewer approval in GitHub before the deploy steps execute.

Steps:

1. Check out the repository.
2. Install the Supabase CLI.
3. Set the Supabase project to the production project reference.
4. Run supabase db push targeting the production project using SUPABASE_PROD_PROJECT_REF and SUPABASE_ACCESS_TOKEN.
5. Run supabase functions deploy for all Edge Functions, targeting the production project.
6. Trigger the Netlify deploy hook by sending a POST request to the URL stored in NETLIFY_DEPLOY_HOOK GitHub secret. This causes Netlify to build and deploy the frontend with production environment variables.
7. Wait 60 seconds for the Netlify build to complete (use a fixed sleep — no webhook confirmation needed at this stage).
8. Run smoke tests: execute supabase/tests/smoke.ts with SMOKE_TEST_URL set to SUPABASE_PROD_URL.
9. Send a deployment notification email on success or failure (same pattern as the staging workflow).

After setting up this workflow, add the NETLIFY_DEPLOY_HOOK secret to GitHub Actions. Find this value in your Netlify site → Site Settings → Build & Deploy → Build hooks → Create a hook named "GitHub Actions Production Deploy" and copy the URL.

---

Step 3: Security Headers

netlify.toml

Create this file in the project root. It configures security response headers for all responses served by the Netlify production site.

The headers to apply to all paths (/*):

• Strict-Transport-Security: max-age=63072000; includeSubDomains; preload
• X-Content-Type-Options: nosniff
• X-Frame-Options: DENY
• Referrer-Policy: strict-origin-when-cross-origin
• Content-Security-Policy: a policy that allows scripts from 'self' and https://js.stripe.com; allows frames from https://js.stripe.com; allows connections to 'self', the Supabase project URL, and https://api.stripe.com; allows images from 'self', data:, and the Supabase Storage URL; allows styles from 'self' and 'unsafe-inline'; blocks everything else by default

The Supabase project URL and Supabase Storage URL must be injected as Netlify build environment variables during the Netlify deploy step. Configure the netlify.toml to reference these as environment variables using Netlify's [build.environment] section, and reference them in the Content-Security-Policy header value. Name the variables VITE_SUPABASE_URL (already configured in Netlify from the setup steps) — the storage URL follows the pattern {VITE_SUPABASE_URL}/storage/v1.

Also configure the Netlify build command and publish directory in netlify.toml:

• Build command: npm run build
• Publish directory: dist

---

Step 4: Post-Setup Instructions

After this prompt is complete, perform these final setup steps manually:

1. In GitHub → repository Settings → Secrets and Variables → Actions, add NETLIFY_DEPLOY_HOOK with the URL from your Netlify site build hook (created during the production deploy workflow setup above).

2. In your Stripe live-mode dashboard → Developers → Webhooks, create an endpoint pointing to the stripe-webhook Edge Function URL from your homestay-prod Supabase project. Add the same four events as the test-mode webhook. Copy the live signing secret into the STRIPE_WEBHOOK_SIGNING_SECRET Vault entry in homestay-prod.

3. Trigger the production deploy workflow manually from GitHub → Actions → production-deploy → Run workflow. Approve the deployment when the reviewer approval prompt appears.

---

Check before continuing (do this yourself, do not paste it):

• Open a pull request against main in your GitHub repository (any small change). Confirm the PR workflow runs and all steps pass — the checks should appear on the PR page with green checkmarks.
• Merge the PR to main. Confirm the staging deploy workflow runs automatically, deploys to staging, and sends a notification email to your operator alert address.
• After the staging deploy succeeds, manually trigger the production deploy workflow from GitHub Actions. Approve the reviewer prompt. Confirm the workflow completes and the Netlify site is updated.
• Visit the production Netlify site URL. Open your browser's developer tools → Network tab, click any request, and look at the response headers. Confirm you can see Strict-Transport-Security, X-Content-Type-Options, X-Frame-Options, and Content-Security-Policy headers present on the response. If something is wrong: check the Netlify deploy log for errors and tell Lovable which header is missing or which workflow step failed.

Before You Build

The Before You Build guide details the prerequisite service accounts, database extension configurations, Supabase Vault secrets, and GitHub Actions repository secrets required prior to executing Prompt 1 of the Homestay Lovable Prompt Pack generated by Zalcro.

---

How to Use This Prompt Pack

This Prompt Pack is a sequence of ready-to-paste instructions for Lovable, your AI coding tool. Each prompt builds one piece of Homestay. You paste them one at a time, in order, and confirm each piece is working before moving to the next. You do not need to understand the technical details inside each prompt — they are written for Lovable, not for you.

Lovable works best when you:

• Use Plan mode before executing any prompt that touches the database or data access. Lovable will show you what it intends to do — review it before clicking Execute. This is especially important for prompts that involve Row-Level Security (RLS) policies, because a misconfigured policy silently hides data rather than showing an error.
• Paste the full prompt text, including every line. Lovable processes the whole prompt at once.
• Wait for Lovable to finish completely before checking the result or moving to the next prompt.
• If something looks wrong after a prompt, describe the problem to Lovable in the chat and ask it to fix it — do not move to the next prompt until it is resolved.
• Connect a GitHub repository before starting so every change is saved and you can roll back if needed.

---

What You're Building

Homestay is a short-term rental marketplace where hosts list homes or rooms and guests search, book, and pay for overnight stays. Hosts receive payouts automatically after guests check in, and the platform handles all payment routing, refunds, and cancellations. Guests and hosts share a single account with role-based access, and the platform includes an admin dashboard for operators and support staff.

---

This Pack Contains 8 Prompts

Prompt	What it delivers
Prompt 1	Complete database schema, all RLS policies, triggers, indexes, and shared Edge Function utilities
Prompt 2	Sign-up, sign-in, sign-out, email verification, and the profile page
Prompt 3	Guest-facing listing search page and listing detail page
Prompt 4	Guest booking flow, Stripe payment collection, booking history, and host approval/decline
Prompt 5	Host dashboard, listing editor, photo uploads, availability rules, and pricing overrides
Prompt 6	Scheduled background jobs: automatic payouts, booking expiry, and CCPA data scrubbing
Prompt 7	Admin and support dashboard, and guest-facing CCPA deletion and data export requests
Prompt 8	GitHub Actions CI/CD pipelines, Netlify security headers, integration tests, and smoke tests

---

Before You Run Prompt 1: Complete These Steps

Work through every item below before opening Lovable. Prompt 1 will fail or behave incorrectly if any of these are missing.

1. Create Accounts

You need active accounts on all of these services before you start:

• Supabase — supabase.com
• Stripe — stripe.com
• Resend — resend.com
• GitHub — github.com
• Netlify — netlify.com

2. Set Up Your Supabase Projects

Create three separate Supabase projects: homestay-dev, homestay-staging, and homestay-prod. You will use homestay-dev for all Lovable development. Repeat the following steps in each project:

In each project's database settings:

• Go to Database → Extensions and enable btree_gist
• Go to Database → Extensions and enable pg_cron
• Go to Database → Extensions and enable pg_net (required for pg_cron to call Edge Functions)

In each project's authentication settings:

• Go to Authentication → URL Configuration and set the Site URL to the correct frontend URL for that environment (Lovable's preview URL for dev, your custom domain for production)
• Go to Authentication → Email and enable "Confirm email" so new users must verify before signing in

In each project's storage settings:

• Create a bucket named photos with public read access enabled
• Create a bucket named ccpa-exports with public read access disabled (private)
• On the photos bucket, set a lifecycle rule so objects whose path starts with pending/ expire after 24 hours

In each project's Vault (Database → Vault → Secrets): Create the following secrets. Use the exact names listed — your Edge Functions will look for them by these exact names:

• STRIPE_SECRET_KEY — your Stripe secret key for that environment (test key for dev/staging, live key for prod)
• STRIPE_WEBHOOK_SIGNING_SECRET — from the Stripe webhook endpoint you will create in step 4 below
• STRIPE_CONNECT_CLIENT_ID — from Stripe → Settings → Connect → Client ID
• RESEND_API_KEY — from Resend → API Keys
• RESEND_FROM_ADDRESS — the verified email address you will send from (e.g. noreply@yourdomain.com)
• SERVICE_FEE_PERCENTAGE — enter the value 12 (no percent sign)
• OPERATOR_ALERT_EMAIL — the email address that receives payout failure alerts
• FRONTEND_URL — the full URL of the frontend for that environment (e.g. your Lovable preview URL for dev)
• CRON_SECRET — generate a long random string (e.g. 40+ random characters); this protects your scheduled jobs

On homestay-prod only:

• Go to Database → Backups and enable Point-in-Time Recovery (PITR)

3. Connect Lovable to Supabase

In your Lovable project settings, connect Lovable to your homestay-dev Supabase project. Lovable uses this connection to apply database migrations and interact with your database during development.

4. Set Up Stripe

• In your Stripe dashboard, go to Settings → Connect and enable Connect. Choose "Standard" accounts for host onboarding.
• In test mode, go to Developers → Webhooks and create a new endpoint. You will need the URL of your stripe-webhook Edge Function — this URL is not available until Prompt 4 creates the function. Skip the webhook endpoint step for now and complete it as part of Prompt 4's verification steps.
• In live mode, repeat the webhook setup after Prompt 4 is deployed to production.

5. Set Up Resend

• In the Resend dashboard, go to Domains and add the domain you will send emails from. Follow Resend's instructions to add the required DNS records at your domain registrar. Wait for the domain to verify (usually a few minutes to an hour).
• Go to API Keys and create a new API key. Store it in each Supabase project's Vault as RESEND_API_KEY.

6. Set Up GitHub

• Create a new repository named homestay.
• Connect your Lovable project to this repository (Lovable → Settings → GitHub).
• In the GitHub repository, go to Settings → Secrets and Variables → Actions. Add the following secrets:

Secret name	Where to find the value
SUPABASE_DEV_URL	Supabase homestay-dev → Project Settings → API → Project URL
SUPABASE_STAGING_URL	Supabase homestay-staging → Project Settings → API → Project URL
SUPABASE_PROD_URL	Supabase homestay-prod → Project Settings → API → Project URL
SUPABASE_DEV_SERVICE_ROLE_KEY	Supabase homestay-dev → Project Settings → API → service_role key
SUPABASE_STAGING_SERVICE_ROLE_KEY	Supabase homestay-staging → Project Settings → API → service_role key
SUPABASE_PROD_SERVICE_ROLE_KEY	Supabase homestay-prod → Project Settings → API → service_role key
SUPABASE_ACCESS_TOKEN	Supabase dashboard → Account (top right) → Access Tokens
SUPABASE_STAGING_PROJECT_REF	Supabase homestay-staging → Project Settings → General → Reference ID
SUPABASE_PROD_PROJECT_REF	Supabase homestay-prod → Project Settings → General → Reference ID
RESEND_API_KEY	Resend dashboard → API Keys
NETLIFY_DEPLOY_HOOK	Set up in Prompt 8 — skip for now

• In the GitHub repository, go to Settings → Environments and create an environment named production. Add at least one required reviewer — this person must approve before any production deployment runs.

7. Set Up Netlify

• Go to your Netlify dashboard and create a new site connected to your homestay GitHub repository.
• Set the build command to npm run build and the publish directory to dist.
• In Netlify → Site settings → Environment variables, add:

Variable name	Where to find the value
VITE_SUPABASE_URL	Supabase homestay-prod → Project Settings → API → Project URL
VITE_SUPABASE_ANON_KEY	Supabase homestay-prod → Project Settings → API → anon public key
VITE_STRIPE_PUBLISHABLE_KEY	Stripe dashboard → Developers → API Keys → Publishable key (live mode)

---

Environment Variables Reference

These are all the secrets and configuration values used across the project. Edge Function secrets are stored in Supabase Vault (not in code or environment variables). Frontend values are stored as build-time environment variables.

Supabase Vault secrets (set in each project's Vault with these exact names):

Secret name	Source
STRIPE_SECRET_KEY	Stripe → Developers → API Keys → Secret key
STRIPE_WEBHOOK_SIGNING_SECRET	Stripe → Developers → Webhooks → your endpoint → Signing secret
STRIPE_CONNECT_CLIENT_ID	Stripe → Settings → Connect → Client ID
RESEND_API_KEY	Resend → API Keys
RESEND_FROM_ADDRESS	The verified sender address you configured in Resend
SERVICE_FEE_PERCENTAGE	Set to 12
OPERATOR_ALERT_EMAIL	The email address for operator alerts
FRONTEND_URL	The full base URL of the frontend for that environment
CRON_SECRET	A long random string you generate; must be identical across all three projects

Frontend build-time variables (set in Netlify for production; Lovable's Supabase integration handles dev):

Variable name	Source
VITE_SUPABASE_URL	Supabase homestay-prod → Project Settings → API → Project URL
VITE_SUPABASE_ANON_KEY	Supabase homestay-prod → Project Settings → API → anon public key
VITE_STRIPE_PUBLISHABLE_KEY	Stripe → Developers → API Keys → Publishable key

GitHub Actions secrets (set in the repository's Secrets panel):

All values listed in step 6 above, plus NETLIFY_DEPLOY_HOOK added during Prompt 8.

---