129 lines
4.8 KiB
PL/PgSQL
129 lines
4.8 KiB
PL/PgSQL
-- 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;
|