Tidyroom PRD, brand kit & board

CREATE TABLE plans (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), code text NOT NULL UNIQUE, name text NOT NULL, price_cents integer NOT NULL CHECK (price_cents >= 0), currency text NOT NULL, trial_days integer NOT NULL DEFAULT 14 CHECK (trial_days >= 0), max_clients integer, max_storage_bytes bigint, is_active boolean NOT NULL DEFAULT true, created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE workspaces (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), owner_user_id uuid NOT NULL REFERENCES auth.users(id), name text NOT NULL, slug text NOT NULL UNIQUE, logo_path text, primary_color text NOT NULL DEFAULT '#1F2937' CHECK (primary_color ~ '^#[0-9A-Fa-f]{6}$'), accent_color text NOT NULL DEFAULT '#2563EB' CHECK (accent_color ~ '^#[0-9A-Fa-f]{6}$'), currency text NOT NULL, next_invoice_number integer NOT NULL DEFAULT 1, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE subscriptions (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), workspace_id uuid NOT NULL UNIQUE REFERENCES workspaces(id) ON DELETE CASCADE, plan_id uuid NOT NULL REFERENCES plans(id), status text NOT NULL CHECK (status IN ('trialing','active','past_due','canceled','expired')), trial_ends_at timestamptz, current_period_end timestamptz, provider text NOT NULL, provider_customer_ref text, provider_subscription_ref text, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE payout_accounts (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), workspace_id uuid NOT NULL UNIQUE REFERENCES workspaces(id) ON DELETE CASCADE, provider text NOT NULL, provider_account_ref text, status text NOT NULL DEFAULT 'not_connected' CHECK (status IN ('not_connected','pending','active','restricted')), created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE clients (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE, name text NOT NULL, email text NOT NULL, company text, status text NOT NULL DEFAULT 'invited' CHECK (status IN ('invited','active','archived')), created_at timestamptz NOT NULL DEFAULT now(), UNIQUE (workspace_id, email));
CREATE TABLE client_invites (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE, client_id uuid NOT NULL REFERENCES clients(id) ON DELETE CASCADE, token_hash text NOT NULL UNIQUE, expires_at timestamptz NOT NULL, accepted_at timestamptz, revoked_at timestamptz, created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE portal_members (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), client_id uuid NOT NULL REFERENCES clients(id) ON DELETE CASCADE, user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE, created_at timestamptz NOT NULL DEFAULT now(), UNIQUE (client_id, user_id));
CREATE TABLE projects (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE, client_id uuid NOT NULL REFERENCES clients(id) ON DELETE CASCADE, name text NOT NULL, status text NOT NULL DEFAULT 'not_started' CHECK (status IN ('not_started','in_progress','waiting_on_client','in_review','completed','on_hold')), stage text, status_updated_at timestamptz NOT NULL DEFAULT now(), created_at timestamptz NOT NULL DEFAULT now(), archived_at timestamptz);
CREATE TABLE files (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE, client_id uuid NOT NULL REFERENCES clients(id) ON DELETE CASCADE, project_id uuid REFERENCES projects(id) ON DELETE SET NULL, uploaded_by_user_id uuid NOT NULL REFERENCES auth.users(id), uploaded_by_role text NOT NULL CHECK (uploaded_by_role IN ('freelancer','client')), storage_path text NOT NULL UNIQUE, file_name text NOT NULL, mime_type text, size_bytes bigint CHECK (size_bytes >= 0), upload_status text NOT NULL DEFAULT 'pending' CHECK (upload_status IN ('pending','ready')), created_at timestamptz NOT NULL DEFAULT now(), ready_at timestamptz);
CREATE TABLE file_receipts (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), file_id uuid NOT NULL REFERENCES files(id) ON DELETE CASCADE, client_id uuid NOT NULL REFERENCES clients(id) ON DELETE CASCADE, user_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE, first_opened_at timestamptz NOT NULL DEFAULT now(), UNIQUE (file_id, user_id));
CREATE TABLE message_threads (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE, client_id uuid NOT NULL REFERENCES clients(id) ON DELETE CASCADE, project_id uuid REFERENCES projects(id) ON DELETE SET NULL, subject text, created_at timestamptz NOT NULL DEFAULT now(), last_message_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE messages (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), thread_id uuid NOT NULL REFERENCES message_threads(id) ON DELETE CASCADE, workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE, client_id uuid NOT NULL REFERENCES clients(id) ON DELETE CASCADE, sender_user_id uuid NOT NULL REFERENCES auth.users(id), sender_role text NOT NULL CHECK (sender_role IN ('freelancer','client')), body text NOT NULL, needs_reply boolean NOT NULL DEFAULT true, client_request_id uuid, created_at timestamptz NOT NULL DEFAULT now(), UNIQUE (thread_id, client_request_id));
CREATE TABLE invoices (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE, client_id uuid NOT NULL REFERENCES clients(id) ON DELETE CASCADE, project_id uuid REFERENCES projects(id) ON DELETE SET NULL, number integer NOT NULL, status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','sent','viewed','paid','void')), currency text NOT NULL, subtotal_cents bigint NOT NULL CHECK (subtotal_cents >= 0), tax_cents bigint NOT NULL DEFAULT 0 CHECK (tax_cents >= 0), total_cents bigint NOT NULL CHECK (total_cents > 0), due_date date, note text, sent_at timestamptz, first_viewed_at timestamptz, last_reminder_at timestamptz, paid_at timestamptz, voided_at timestamptz, created_at timestamptz NOT NULL DEFAULT now(), UNIQUE (workspace_id, number), CHECK (total_cents = subtotal_cents + tax_cents));
CREATE TABLE invoice_line_items (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), invoice_id uuid NOT NULL REFERENCES invoices(id) ON DELETE CASCADE, position integer NOT NULL, description text NOT NULL, quantity numeric(10,2) NOT NULL CHECK (quantity > 0), unit_amount_cents bigint NOT NULL CHECK (unit_amount_cents >= 0), UNIQUE (invoice_id, position));
CREATE TABLE invoice_events (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), invoice_id uuid NOT NULL REFERENCES invoices(id) ON DELETE CASCADE, workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE, kind text NOT NULL CHECK (kind IN ('created','sent','viewed','reminder_sent','payment_started','payment_succeeded','payment_failed','voided')), actor_role text NOT NULL CHECK (actor_role IN ('freelancer','client','system')), actor_user_id uuid, meta jsonb NOT NULL DEFAULT '{}'::jsonb, created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE payment_attempts (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), invoice_id uuid NOT NULL REFERENCES invoices(id) ON DELETE CASCADE, workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE, provider text NOT NULL, provider_checkout_ref text, idempotency_key text NOT NULL UNIQUE, amount_cents bigint NOT NULL CHECK (amount_cents > 0), currency text NOT NULL, status text NOT NULL DEFAULT 'created' CHECK (status IN ('created','pending','succeeded','failed','expired')), failure_reason text, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE provider_events (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), provider text NOT NULL, provider_event_id text NOT NULL, event_type text NOT NULL, payload jsonb NOT NULL, received_at timestamptz NOT NULL DEFAULT now(), processed_at timestamptz, processing_error text, UNIQUE (provider, provider_event_id));
CREATE TABLE notification_outbox (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), workspace_id uuid NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE, recipient_email text NOT NULL, kind text NOT NULL CHECK (kind IN ('client_invite','new_message','invoice_sent','invoice_reminder','invoice_paid')), payload jsonb NOT NULL DEFAULT '{}'::jsonb, dedupe_key text NOT NULL UNIQUE, status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','sent','failed')), attempts integer NOT NULL DEFAULT 0, send_after timestamptz NOT NULL DEFAULT now(), sent_at timestamptz, last_error text, created_at timestamptz NOT NULL DEFAULT now());