linumiq_net-docker/dev/supabase/migrations/0001_init.sql

-- v1 initial schema for linumiq tunnel platform
-- Phase 2 / Wave A backend

BEGIN;

CREATE EXTENSION IF NOT EXISTS citext;

-- ---------------------------------------------------------------------------
-- Tables
-- ---------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS public.tunnels (
  user_id      uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  subdomain    citext UNIQUE NOT NULL
                 CHECK (subdomain ~ '^[a-z0-9-]{3,32}$'
                        AND subdomain NOT IN ('app','api','www','admin','auth','mail','static')),
  token        text UNIQUE NOT NULL,
  is_active    boolean NOT NULL DEFAULT true,
  bytes_used   bigint NOT NULL DEFAULT 0,
  quota_bytes  bigint NOT NULL DEFAULT 1099511627776,
  created_at   timestamptz NOT NULL DEFAULT now(),
  last_seen_at timestamptz
);

CREATE TABLE IF NOT EXISTS public.subscriptions (
  user_id    uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  plan       text NOT NULL DEFAULT 'free',
  status     text NOT NULL DEFAULT 'active',
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS public.usage_samples (
  id          bigserial PRIMARY KEY,
  subdomain   citext NOT NULL,
  ts          timestamptz NOT NULL DEFAULT now(),
  bytes_delta bigint NOT NULL
);
CREATE INDEX IF NOT EXISTS usage_samples_subdomain_ts_idx
  ON public.usage_samples (subdomain, ts DESC);

CREATE TABLE IF NOT EXISTS public.users_profile (
  user_id    uuid PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email      text,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- ---------------------------------------------------------------------------
-- Trigger: auto-create users_profile row on auth.users insert
-- ---------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  INSERT INTO public.users_profile (user_id, email)
  VALUES (NEW.id, NEW.email)
  ON CONFLICT (user_id) DO NOTHING;
  RETURN NEW;
END;
$$;

REVOKE ALL ON FUNCTION public.handle_new_user() FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.handle_new_user() TO supabase_auth_admin;

DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- ---------------------------------------------------------------------------
-- Row-level security
-- ---------------------------------------------------------------------------

ALTER TABLE public.tunnels       ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.subscriptions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.usage_samples ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.users_profile ENABLE ROW LEVEL SECURITY;

-- tunnels: owner can SELECT and UPDATE. No INSERT/DELETE for users.
DROP POLICY IF EXISTS tunnels_select_own ON public.tunnels;
CREATE POLICY tunnels_select_own ON public.tunnels
  FOR SELECT TO authenticated
  USING (auth.uid() = user_id);

DROP POLICY IF EXISTS tunnels_update_own ON public.tunnels;
CREATE POLICY tunnels_update_own ON public.tunnels
  FOR UPDATE TO authenticated
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- subscriptions: owner can SELECT.
DROP POLICY IF EXISTS subscriptions_select_own ON public.subscriptions;
CREATE POLICY subscriptions_select_own ON public.subscriptions
  FOR SELECT TO authenticated
  USING (auth.uid() = user_id);

-- usage_samples: no anon/authenticated access (service_role bypasses RLS).
-- (No policies created → all access denied for non-service roles.)

-- users_profile: owner can SELECT and UPDATE.
DROP POLICY IF EXISTS users_profile_select_own ON public.users_profile;
CREATE POLICY users_profile_select_own ON public.users_profile
  FOR SELECT TO authenticated
  USING (auth.uid() = user_id);

DROP POLICY IF EXISTS users_profile_update_own ON public.users_profile;
CREATE POLICY users_profile_update_own ON public.users_profile
  FOR UPDATE TO authenticated
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

-- ---------------------------------------------------------------------------
-- Grants: let PostgREST roles see the tables; RLS still gates access.
-- ---------------------------------------------------------------------------

GRANT USAGE ON SCHEMA public TO anon, authenticated, service_role;

GRANT SELECT, UPDATE ON public.tunnels       TO authenticated;
GRANT SELECT         ON public.subscriptions TO authenticated;
GRANT SELECT, UPDATE ON public.users_profile TO authenticated;

GRANT ALL ON public.tunnels, public.subscriptions, public.usage_samples, public.users_profile TO service_role;
GRANT USAGE, SELECT ON SEQUENCE public.usage_samples_id_seq TO service_role;

COMMIT;