CREATE OR REPLACE FUNCTION update_updated_on_column() RETURNS TRIGGER AS $$ BEGIN IF row(NEW.*) IS DISTINCT FROM row(OLD.*) THEN NEW.updated_on = NOW() AT TIME ZONE 'UTC'; RETURN NEW; ELSE RETURN OLD; END IF; END; $$ language 'plpgsql'; CREATE TYPE domain_level AS ENUM ( 'SYSTEM', 'USER' ); CREATE TYPE domain_service AS ENUM ( 'LINKS', 'SHORT', 'LIST' ); CREATE TYPE domain_status AS ENUM ( 'PENDING', 'APPROVED', 'ERROR' ); CREATE TYPE invoice_status AS ENUM ( 'PENDING', 'PAID', 'REFUNDED', 'PARTIAL_REFUND' ); CREATE TYPE org_link_visibility AS ENUM ( 'PUBLIC', 'PRIVATE', 'RESTRICTED' ); CREATE TYPE org_link_type AS ENUM ( 'LINK', 'NOTE' ); CREATE TYPE org_link_perm AS ENUM ( 'READ', 'WRITE', 'ADMIN_WRITE' ); CREATE TYPE org_type AS ENUM ( 'USER', 'NORMAL' ); CREATE TYPE subscription_type AS ENUM ( 'PERSONAL', 'BUSINESS' ); CREATE TYPE qr_code_type AS ENUM ( 'LIST', 'SHORT' ); CREATE TYPE totals_meta_type AS ENUM ( 'REFERRER', 'COUNTRY', 'CITY', 'DEVICE' ); CREATE TABLE users ( id SERIAL PRIMARY KEY, full_name VARCHAR ( 150 ) NOT NULL, password VARCHAR ( 1024 ) NOT NULL, email VARCHAR ( 255 ) UNIQUE NOT NULL, picture VARCHAR(1024) DEFAULT '', settings JSONB DEFAULT '{}', is_verified BOOLEAN DEFAULT FALSE, is_superuser BOOLEAN DEFAULT FALSE, is_staff BOOLEAN DEFAULT FALSE, is_locked BOOLEAN DEFAULT FALSE, lock_reason VARCHAR (255) DEFAULT '', created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, last_login TIMESTAMPTZ DEFAULT NULL ); CREATE INDEX users_id_idx ON users (id); CREATE TABLE organizations ( id SERIAL PRIMARY KEY, owner_id INT REFERENCES users (id) ON DELETE CASCADE NOT NULL, org_type org_type DEFAULT 'USER', name VARCHAR ( 150 ) NOT NULL, slug VARCHAR ( 150 ) UNIQUE NOT NULL, image VARCHAR(1024) DEFAULT '', settings JSONB DEFAULT '{}', timezone VARCHAR(20) NOT NULL DEFAULT 'UTC', is_active BOOLEAN DEFAULT TRUE, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX organizations_id_idx ON organizations (id); CREATE INDEX organizations_owner_id_idx ON organizations (owner_id); CREATE INDEX organizations_slug_idx ON organizations (slug); CREATE TRIGGER update_organizations_modtime BEFORE UPDATE ON organizations FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column(); CREATE TABLE sessions ( token TEXT PRIMARY KEY, data BYTEA NOT NULL, expiry TIMESTAMPTZ NOT NULL ); CREATE INDEX sessions_expiry_idx ON sessions (expiry); CREATE TABLE confirmations ( id SERIAL PRIMARY KEY, "type" INT NOT NULL, -- email, password reset confirmation_target VARCHAR DEFAULT NULL, user_id INT REFERENCES users (id) ON DELETE CASCADE NOT NULL, key VARCHAR NOT NULL UNIQUE, confirm_time TIMESTAMPTZ DEFAULT NULL, expire_time TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP + interval '24 hours', created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE email_notifications ( id SERIAL PRIMARY KEY, email VARCHAR ( 255 ) UNIQUE NOT NULL, count INT NOT NULL DEFAULT 0, notifications JSON NOT NULL, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX email_notifications_id_idx on email_notifications (id); CREATE TRIGGER update_email_notifications_modtime BEFORE UPDATE ON email_notifications FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column(); CREATE TABLE base_urls ( id SERIAL PRIMARY KEY, title VARCHAR ( 150 ) DEFAULT '', url TEXT UNIQUE NOT NULL, public_ready BOOLEAN DEFAULT FALSE, hash VARCHAR(128) UNIQUE NOT NULL, data JSONB DEFAULT '{}', counter INT DEFAULT 0, parse_attempts INT DEFAULT 0, last_parse_attempt TIMESTAMPTZ, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX base_urls_id_idx ON base_urls (id); CREATE TRIGGER base_urls_modtime BEFORE UPDATE ON base_urls FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column(); CREATE TABLE org_links ( id SERIAL PRIMARY KEY, title VARCHAR ( 150 ) NOT NULL, url TEXT NOT NULL, description TEXT DEFAULT '', "type" org_link_type default 'LINK', hash VARCHAR(128) UNIQUE NOT NULL, base_url_id INT REFERENCES base_urls (id) ON DELETE CASCADE, org_id INT REFERENCES organizations (id) ON DELETE CASCADE NOT NULL, user_id INT REFERENCES users (id) ON DELETE SET NULL, visibility org_link_visibility DEFAULT 'PUBLIC', unread BOOLEAN DEFAULT FALSE NOT NULL, starred BOOLEAN DEFAULT FALSE NOT NULL, archive_url TEXT DEFAULT '', created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP CONSTRAINT unique_base_url_org UNIQUE (base_url_id, org_id) ); CREATE INDEX org_links_id_idx ON org_links (id); CREATE INDEX org_links_base_url_id_idx ON org_links (base_url_id); CREATE INDEX org_links_org_id_idx ON org_links (org_id); CREATE INDEX org_links_user_id_idx ON org_links (user_id); CREATE INDEX org_links_search_idx ON org_links USING GIN (to_tsvector('simple', title || ' ' || description || ' ' || url)); CREATE TRIGGER update_org_links_modtime BEFORE UPDATE ON org_links FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column(); CREATE TABLE organization_users ( id SERIAL PRIMARY KEY, org_id INT REFERENCES organizations (id) ON DELETE CASCADE NOT NULL, user_id INT REFERENCES users (id) ON DELETE CASCADE NOT NULL, permission org_link_perm default 'READ', is_active BOOLEAN DEFAULT TRUE, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT unique_user_org UNIQUE (org_id, user_id) ); CREATE INDEX organization_users_id_idx ON organization_users (id); CREATE INDEX organization_users_org_id_idx ON organization_users (org_id); CREATE INDEX organization_users_user_id_idx ON organization_users (user_id); CREATE TRIGGER update_organization_users_modtime BEFORE UPDATE ON organization_users FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column(); CREATE TABLE domains ( id SERIAL PRIMARY KEY, name VARCHAR (255) NOT NULL, lookup_name VARCHAR (500) NOT NULL, -- padded in case... org_id INT REFERENCES organizations (id) ON DELETE CASCADE, level domain_level NOT NULL, service domain_service NOT NULL, status domain_status DEFAULT 'PENDING' NOT NULL, is_active BOOLEAN DEFAULT TRUE, last_action TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT unique_lookup_name_active UNIQUE (lookup_name, is_active) ); CREATE INDEX domains_id_idx ON domains (id); CREATE INDEX domains_org_id_idx ON domains (org_id); CREATE TRIGGER update_domains_modtime BEFORE UPDATE ON domains FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column(); CREATE TABLE oauth2_clients ( id serial PRIMARY KEY, owner_id integer NOT NULL REFERENCES users (id) ON DELETE CASCADE NOT NULL, name character varying(256) NOT NULL, description character varying, key character varying NOT NULL UNIQUE, secret_hash character varying(128) NOT NULL, secret_partial character varying(8) NOT NULL, redirect_url character varying, client_url character varying, revoked boolean DEFAULT false NOT NULL, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX oauth2_clients_id_idx ON oauth2_clients (id); CREATE INDEX oauth2_clients_owner_id_idx ON oauth2_clients (owner_id); CREATE TRIGGER update_oauth2_clients_modtime BEFORE UPDATE ON oauth2_clients FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column(); CREATE TABLE oauth2_grants ( id serial PRIMARY KEY, issued TIMESTAMPTZ NOT NULL, expires TIMESTAMPTZ NOT NULL, comment character varying, token_hash character varying(128) NOT NULL, refresh_token_hash character varying(128) NOT NULL, grants character varying default '', user_id integer NOT NULL REFERENCES users (id) ON DELETE CASCADE, client_id integer REFERENCES oauth2_clients (id) ON DELETE CASCADE ); CREATE INDEX oauth2_grants_id_idx ON oauth2_grants (id); CREATE INDEX oauth2_grants_user_id_idx ON oauth2_grants (user_id); CREATE INDEX oauth2_grants_client_id_idx ON oauth2_grants (client_id); CREATE TABLE oauth2_authorizations ( id serial PRIMARY KEY, code character varying(128) NOT NULL, payload character varying NOT NULL, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX oauth2_authorizations_id_idx ON oauth2_authorizations (id); CREATE INDEX oauth2_authorizations_code_idx ON oauth2_authorizations (code); CREATE TABLE tags ( id SERIAL PRIMARY KEY, name VARCHAR ( 50 ) NOT NULL, slug VARCHAR ( 50 ) UNIQUE NOT NULL, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX tags_id_idx ON tags (id); CREATE INDEX tags_id_slug ON tags (slug); CREATE TABLE tag_links ( id SERIAL PRIMARY KEY, org_link_id INT REFERENCES org_links (id) ON DELETE CASCADE NOT NULL, tag_id INT REFERENCES tags (id) ON DELETE CASCADE NOT NULL, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT unique_tag_link UNIQUE (org_link_id, tag_id) ); CREATE INDEX tag_links_id_idx ON tag_links (id); CREATE INDEX tag_links_org_link_id_idx ON tag_links (org_link_id); CREATE INDEX tag_links_tag_id_idx ON tag_links (tag_id); CREATE TABLE slack_connections ( id SERIAL PRIMARY KEY, token TEXT NOT NULL, team_id TEXT UNIQUE NOT NULL, org_id INT REFERENCES organizations (id) ON DELETE CASCADE NOT NULL UNIQUE, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX slack_connections_id_idx ON slack_connections (id); CREATE INDEX slack_connections_org_id_idx ON slack_connections (org_id); CREATE TABLE slack_users ( id SERIAL PRIMARY KEY, slack_conn_id INT REFERENCES slack_connections (id) ON DELETE CASCADE NOT NULL, user_id INT REFERENCES users (id) ON DELETE CASCADE NOT NULL, slack_user TEXT NOT NULL, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT unique_slack_conn_user UNIQUE (slack_conn_id, user_id) ); CREATE INDEX slack_users_id_idx ON slack_users (id); CREATE INDEX slack_users_slack_conn_id_idx ON slack_users (slack_conn_id); CREATE TABLE mattermost_connections ( id SERIAL PRIMARY KEY, team_id TEXT UNIQUE NOT NULL, org_id INT REFERENCES organizations (id) ON DELETE CASCADE NOT NULL UNIQUE, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX mattermost_connections_id_idx ON mattermost_connections (id); CREATE INDEX mattermost_connections_org_id_idx ON mattermost_connections (org_id); CREATE TABLE mattermost_users ( id SERIAL PRIMARY KEY, mattermost_conn_id INT REFERENCES mattermost_connections (id) ON DELETE CASCADE NOT NULL, user_id INT REFERENCES users (id) ON DELETE CASCADE NOT NULL, mattermost_user TEXT NOT NULL, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT unique_mattermost_conn_user UNIQUE (mattermost_conn_id, user_id) ); CREATE INDEX mattermost_users_id_idx ON mattermost_users (id); CREATE INDEX mattermost_users_mattermost_conn_id_idx ON mattermost_users (mattermost_conn_id); CREATE TABLE link_shorts ( id SERIAL PRIMARY KEY, title VARCHAR ( 150 ) DEFAULT '', url TEXT NOT NULL, short_code TEXT DEFAULT '', domain_id INT REFERENCES domains (id) ON DELETE RESTRICT NOT NULL, org_id INT REFERENCES organizations (id) ON DELETE CASCADE NOT NULL, user_id INT REFERENCES users (id) ON DELETE SET NULL, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT unique_short_code_domain UNIQUE (short_code, domain_id) ); CREATE INDEX link_shorts_id_idx ON link_shorts (id); CREATE INDEX link_shorts_domain_id_idx ON link_shorts (domain_id); CREATE INDEX link_shorts_org_id_idx ON link_shorts (org_id); CREATE INDEX link_shorts_user_id_idx ON link_shorts (user_id); CREATE TRIGGER update_link_shorts_modtime BEFORE UPDATE ON link_shorts FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column(); CREATE TABLE tag_link_shorts ( id SERIAL PRIMARY KEY, link_short_id INT REFERENCES link_shorts (id) ON DELETE CASCADE NOT NULL, tag_id INT REFERENCES tags (id) ON DELETE CASCADE NOT NULL, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT unique_tag_link_short UNIQUE (link_short_id, tag_id) ); CREATE INDEX tag_link_shorts_id_idx ON tag_link_shorts (id); CREATE INDEX tag_link_shorts_link_short_id_idx ON tag_link_shorts (link_short_id); CREATE INDEX tag_link_shorts_tag_id_idx ON tag_link_shorts (tag_id); CREATE TABLE listings ( id SERIAL PRIMARY KEY, title VARCHAR ( 150 ) NOT NULL, slug VARCHAR ( 150 ) NOT NULL, image VARCHAR(1024) DEFAULT '', metadata JSONB DEFAULT '{}', domain_id INT REFERENCES domains (id) ON DELETE RESTRICT NOT NULL, org_id INT REFERENCES organizations (id) ON DELETE CASCADE NOT NULL, user_id INT REFERENCES users (id) ON DELETE SET NULL, is_default BOOLEAN DEFAULT FALSE, is_active BOOLEAN DEFAULT TRUE, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT unique_slug_domain UNIQUE (slug, domain_id) ); CREATE INDEX listings_id_idx ON listings (id); CREATE INDEX listings_domain_id_idx ON listings (domain_id); CREATE INDEX listings_org_id_idx ON listings (org_id); CREATE INDEX listings_domain_user_id_idx ON listings (user_id); CREATE TRIGGER update_listings_modtime BEFORE UPDATE ON link_shorts FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column(); CREATE TABLE tag_listings ( id SERIAL PRIMARY KEY, listing_id INT REFERENCES listings (id) ON DELETE CASCADE NOT NULL, tag_id INT REFERENCES tags (id) ON DELETE CASCADE NOT NULL, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT unique_tag_listing UNIQUE (listing_id, tag_id) ); CREATE INDEX tag_listings_id_idx ON tag_listings (id); CREATE INDEX tag_listings_listing_id_idx ON tag_listings (listing_id); CREATE INDEX tag_listings_tag_id_idx ON tag_listings (tag_id); CREATE TABLE listing_links ( id SERIAL PRIMARY KEY, title VARCHAR ( 150 ) NOT NULL, listing_id INT REFERENCES listings (id) ON DELETE CASCADE NOT NULL, link_order INT DEFAULT 0, url TEXT NOT NULL, description TEXT DEFAULT '', user_id INT REFERENCES users (id) ON DELETE SET NULL, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX listing_links_id_idx ON listing_links (id); CREATE INDEX listing_links_listing_id_idx ON listing_links (listing_id); CREATE INDEX listing_links_user_id_idx ON listing_links (user_id); CREATE TRIGGER update_listing_links_modtime BEFORE UPDATE ON listing_links FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column(); CREATE TABLE qr_codes ( id SERIAL PRIMARY KEY, title VARCHAR (150) NOT NULL, hash_id VARCHAR(10) UNIQUE NOT NULL, url TEXT NOT NULL, org_id INT REFERENCES organizations (id) ON DELETE CASCADE NOT NULL, code_type qr_code_type NOT NULL, image_path TEXT DEFAULT '', user_id INT REFERENCES users (id) ON DELETE CASCADE NOT NULL, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX qr_codes_id_idx ON qr_codes (id); CREATE INDEX qr_codes_hash_id_idx ON qr_codes (hash_id); CREATE INDEX qr_codes_org_id_idx ON qr_codes (org_id); CREATE INDEX qr_codes_user_id_idx ON qr_codes (user_id); CREATE TRIGGER update_qr_codes_modtime BEFORE UPDATE ON qr_codes FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column(); CREATE TABLE qr_codes_shorts ( id SERIAL PRIMARY KEY, qr_code_id INT REFERENCES qr_codes (id) ON DELETE CASCADE NOT NULL, short_id INT REFERENCES link_shorts (id) ON DELETE CASCADE NOT NULL, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX qr_codes_shorts_id_idx ON qr_codes_shorts (id); CREATE INDEX qr_codes_shorts_code_id_idx ON qr_codes_shorts (qr_code_id); CREATE INDEX qr_codes_shorts_short_id_idx ON qr_codes_shorts (short_id); CREATE TRIGGER update_qr_codes_shorts_modtime BEFORE UPDATE ON qr_codes_shorts FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column(); CREATE TABLE qr_codes_lists ( id SERIAL PRIMARY KEY, qr_code_id INT REFERENCES qr_codes (id) ON DELETE CASCADE NOT NULL, list_id INT REFERENCES listings (id) ON DELETE CASCADE NOT NULL, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX qr_codes_lists_id_idx ON qr_codes_lists (id); CREATE INDEX qr_codes_lists_code_id_idx ON qr_codes_lists (qr_code_id); CREATE INDEX qr_codes_lists_list_id_idx ON qr_codes_lists (list_id); CREATE TABLE daily_totals ( id SERIAL PRIMARY KEY, clicks INT DEFAULT 0, unique_clicks INT DEFAULT 0, "date" TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, link_short_id INT REFERENCES link_shorts (id) ON DELETE CASCADE DEFAULT NULL, list_id INT REFERENCES listings (id) ON DELETE CASCADE DEFAULT NULL, listing_link_id INT REFERENCES listing_links (id) ON DELETE CASCADE DEFAULT NULL, qr_id INT REFERENCES qr_codes (id) ON DELETE CASCADE DEFAULT NULL, -- Check constraint to ensure only one of the three columns is NOT NULL CONSTRAINT only_one_not_null_constraint CHECK ( (listing_link_id IS NOT NULL AND link_short_id IS NULL AND list_id IS NULL AND qr_id IS NULL) OR (listing_link_id IS NULL AND link_short_id IS NOT NULL AND list_id IS NULL AND qr_id IS NULL) OR (listing_link_id IS NULL AND link_short_id IS NULL AND list_id IS NOT NULL AND qr_id IS NULL) OR (listing_link_id IS NULL AND link_short_id IS NULL AND list_id IS NULL AND qr_id IS NOT NULL) ) ); CREATE INDEX daily_totals_id_idx ON daily_totals (id); CREATE INDEX daily_totals_link_short_id_idx ON daily_totals (link_short_id); CREATE INDEX daily_totals_list_id_idx ON daily_totals (list_id); CREATE INDEX daily_totals_listing_link_id_idx ON daily_totals (listing_link_id); CREATE INDEX daily_totals_qr_id_idx ON daily_totals (qr_id); CREATE TABLE totals_meta ( id SERIAL PRIMARY KEY, daily_total_id INT REFERENCES daily_totals (id) ON DELETE CASCADE NOT NULL, meta_type totals_meta_type DEFAULT 'REFERRER', meta_value VARCHAR(1024) DEFAULT '', clicks INT DEFAULT 0, unique_clicks INT DEFAULT 0, CONSTRAINT unique_daily_total_meta UNIQUE (daily_total_id, meta_type, meta_value) ); CREATE INDEX totals_meta_id_idx ON totals_meta (id); CREATE INDEX totals_meta_daily_total_id_idx ON totals_meta (daily_total_id); CREATE TABLE subscription_plans ( id SERIAL PRIMARY KEY, name VARCHAR(50) NOT NULL, plan_id VARCHAR(150) NOT NULL, stripe_price_id VARCHAR(150) NOT NULL, price INT NOT NULL CHECK (price > 0), "type" subscription_type NOT NULL DEFAULT 'PERSONAL', is_active BOOLEAN DEFAULT true, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX subscription_plans_id_idx ON subscription_plans (id); CREATE TRIGGER update_subscription_plans_modtime BEFORE UPDATE ON subscription_plans FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column(); CREATE TABLE subscriptions ( id SERIAL PRIMARY KEY, user_id INT REFERENCES users (id) ON DELETE CASCADE NOT NULL, org_id INT REFERENCES organizations (id) ON DELETE CASCADE NOT NULL, stripe_id VARCHAR(150) UNIQUE NOT NULL, subscription_plan_id INT REFERENCES subscription_plans (id) ON DELETE RESTRICT DEFAULT NULL, start_date TIMESTAMPTZ DEFAULT NULL, end_date TIMESTAMPTZ DEFAULT NULL, cancel_end BOOLEAN DEFAULT false, is_active BOOLEAN DEFAULT true, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX subscriptions_id_idx ON subscriptions (id); CREATE INDEX subscriptions_user_id_idx ON subscriptions (user_id); CREATE INDEX subscriptions_org_id_idx ON subscriptions (org_id); CREATE TRIGGER update_subscriptions_modtime BEFORE UPDATE ON subscriptions FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column(); CREATE TABLE invoices ( id SERIAL PRIMARY KEY, status invoice_status DEFAULT 'PENDING', user_id INT REFERENCES users (id) ON DELETE CASCADE NOT NULL, org_id INT REFERENCES organizations (id) ON DELETE CASCADE NOT NULL, stripe_id VARCHAR(150), subscription_id INT REFERENCES subscriptions (id) ON DELETE RESTRICT DEFAULT NULL, currency VARCHAR(10) DEFAULT 'USD', amount INT NOT NULL, amount_paid INT DEFAULT 0, amount_net INT DEFAULT 0, amount_refunded INT DEFAULT 0, payment_fee INT DEFAULT 0, hosted_invoice_url VARCHAR(1024), created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX invoices_id_idx ON invoices (id); CREATE INDEX invoices_user_id_idx ON invoices (user_id); CREATE INDEX invoices_org_id_idx ON invoices (org_id); CREATE TRIGGER update_invoices_modtime BEFORE UPDATE ON invoices FOR EACH ROW EXECUTE PROCEDURE update_updated_on_column(); CREATE TABLE followers ( id SERIAL PRIMARY KEY, user_id INT REFERENCES users (id) ON DELETE CASCADE NOT NULL, org_id INT REFERENCES organizations (id) ON DELETE CASCADE NOT NULL, created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT unique_followers UNIQUE (user_id, org_id) ); CREATE INDEX followers_id_idx ON followers (id); CREATE INDEX followers_user_id_idx ON followers (user_id); CREATE INDEX followers_org_id_idx ON followers (org_id); CREATE TABLE audit_log ( id serial PRIMARY KEY, user_id INTEGER REFERENCES users (id) ON DELETE CASCADE, ip_address character varying(50) NOT NULL, event_type character varying(256) NOT NULL, details character varying(512), metadata JSONB DEFAULT '{}', created_on TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE INDEX audit_log_id_idx ON audit_log (id); CREATE INDEX audit_log_created_on_idx ON audit_log (created_on);