Multi-tenant SaaS

CREATE TYPE member_role AS ENUM ('owner', 'admin', 'member', 'viewer');

CREATE TYPE subscription_status AS ENUM ('trialing', 'active', 'past_due', 'canceled');

CREATE TABLE users (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    email text NOT NULL UNIQUE,
    display_name text,
    created_at timestamptz DEFAULT now() NOT NULL
);

CREATE TABLE organizations (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    slug text NOT NULL UNIQUE,
    name text NOT NULL,
    created_at timestamptz DEFAULT now() NOT NULL
);

CREATE TABLE memberships (
    organization_id uuid NOT NULL,
    user_id uuid NOT NULL,
    role member_role DEFAULT 'member' NOT NULL,
    joined_at timestamptz DEFAULT now() NOT NULL,
    PRIMARY KEY (organization_id, user_id)
);

CREATE TABLE plans (
    id serial PRIMARY KEY,
    code text NOT NULL UNIQUE,
    name text NOT NULL,
    price_cents integer NOT NULL,
    seat_limit integer,
    CHECK (price_cents >= 0)
);

CREATE TABLE subscriptions (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    organization_id uuid NOT NULL,
    plan_id integer NOT NULL,
    status subscription_status DEFAULT 'trialing' NOT NULL,
    current_period_end timestamptz NOT NULL
);
CREATE INDEX subscriptions_org_idx ON subscriptions (organization_id);

CREATE TABLE projects (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    organization_id uuid NOT NULL,
    name text NOT NULL,
    created_by uuid,
    created_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX projects_org_idx ON projects (organization_id);

ALTER TABLE memberships ADD FOREIGN KEY (organization_id) REFERENCES organizations (id) ON DELETE CASCADE;

ALTER TABLE memberships ADD FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE;

ALTER TABLE subscriptions ADD FOREIGN KEY (organization_id) REFERENCES organizations (id) ON DELETE CASCADE;

ALTER TABLE subscriptions ADD FOREIGN KEY (plan_id) REFERENCES plans (id);

ALTER TABLE projects ADD FOREIGN KEY (organization_id) REFERENCES organizations (id) ON DELETE CASCADE;

ALTER TABLE projects ADD FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL;