File size: 5,397 Bytes
a51a15b |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 |
/**
____ _
| _ \ (_)
| |_) | __ _ ___ ___ _ _ _ _ __ ___ _ __
| _ < / _` / __|/ _ \ | | | | '_ ` _ \| '_ \
| |_) | (_| \__ \ __/ | |_| | | | | | | |_) |
|____/ \__,_|___/\___| |\__,_|_| |_| |_| .__/
_/ | | |
|__/ |_|
Basejump is a starter kit for building SaaS products on top of Supabase.
Learn more at https://usebasejump.com
*/
/**
* -------------------------------------------------------
* Section - Basejump schema setup and utility functions
* -------------------------------------------------------
*/
-- revoke execution by default from public
ALTER DEFAULT PRIVILEGES REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA PUBLIC REVOKE EXECUTE ON FUNCTIONS FROM anon, authenticated;
-- Create basejump schema
CREATE SCHEMA IF NOT EXISTS basejump;
GRANT USAGE ON SCHEMA basejump to authenticated;
GRANT USAGE ON SCHEMA basejump to service_role;
/**
* -------------------------------------------------------
* Section - Enums
* -------------------------------------------------------
*/
/**
* Invitation types are either email or link. Email invitations are sent to
* a single user and can only be claimed once. Link invitations can be used multiple times
* Both expire after 24 hours
*/
DO
$$
BEGIN
-- check it account_role already exists on basejump schema
IF NOT EXISTS(SELECT 1
FROM pg_type t
JOIN pg_namespace n ON n.oid = t.typnamespace
WHERE t.typname = 'invitation_type'
AND n.nspname = 'basejump') THEN
CREATE TYPE basejump.invitation_type AS ENUM ('one_time', '24_hour');
end if;
end;
$$;
/**
* -------------------------------------------------------
* Section - Basejump settings
* -------------------------------------------------------
*/
CREATE TABLE IF NOT EXISTS basejump.config
(
enable_team_accounts boolean default true,
enable_personal_account_billing boolean default true,
enable_team_account_billing boolean default true,
billing_provider text default 'stripe'
);
-- create config row
INSERT INTO basejump.config (enable_team_accounts, enable_personal_account_billing, enable_team_account_billing)
VALUES (true, true, true);
-- enable select on the config table
GRANT SELECT ON basejump.config TO authenticated, service_role;
-- enable RLS on config
ALTER TABLE basejump.config
ENABLE ROW LEVEL SECURITY;
create policy "Basejump settings can be read by authenticated users" on basejump.config
for select
to authenticated
using (
true
);
/**
* -------------------------------------------------------
* Section - Basejump utility functions
* -------------------------------------------------------
*/
/**
basejump.get_config()
Get the full config object to check basejump settings
This is not accessible from the outside, so can only be used inside postgres functions
*/
CREATE OR REPLACE FUNCTION basejump.get_config()
RETURNS json AS
$$
DECLARE
result RECORD;
BEGIN
SELECT * from basejump.config limit 1 into result;
return row_to_json(result);
END;
$$ LANGUAGE plpgsql;
grant execute on function basejump.get_config() to authenticated, service_role;
/**
basejump.is_set("field_name")
Check a specific boolean config value
*/
CREATE OR REPLACE FUNCTION basejump.is_set(field_name text)
RETURNS boolean AS
$$
DECLARE
result BOOLEAN;
BEGIN
execute format('select %I from basejump.config limit 1', field_name) into result;
return result;
END;
$$ LANGUAGE plpgsql;
grant execute on function basejump.is_set(text) to authenticated;
/**
* Automatic handling for maintaining created_at and updated_at timestamps
* on tables
*/
CREATE OR REPLACE FUNCTION basejump.trigger_set_timestamps()
RETURNS TRIGGER AS
$$
BEGIN
if TG_OP = 'INSERT' then
NEW.created_at = now();
NEW.updated_at = now();
else
NEW.updated_at = now();
NEW.created_at = OLD.created_at;
end if;
RETURN NEW;
END
$$ LANGUAGE plpgsql;
/**
* Automatic handling for maintaining created_by and updated_by timestamps
* on tables
*/
CREATE OR REPLACE FUNCTION basejump.trigger_set_user_tracking()
RETURNS TRIGGER AS
$$
BEGIN
if TG_OP = 'INSERT' then
NEW.created_by = auth.uid();
NEW.updated_by = auth.uid();
else
NEW.updated_by = auth.uid();
NEW.created_by = OLD.created_by;
end if;
RETURN NEW;
END
$$ LANGUAGE plpgsql;
/**
basejump.generate_token(length)
Generates a secure token - used internally for invitation tokens
but could be used elsewhere. Check out the invitations table for more info on
how it's used
*/
CREATE OR REPLACE FUNCTION basejump.generate_token(length int)
RETURNS text AS
$$
select regexp_replace(replace(
replace(replace(replace(encode(gen_random_bytes(length)::bytea, 'base64'), '/', ''), '+',
''), '\', ''),
'=',
''), E'[\\n\\r]+', '', 'g');
$$ LANGUAGE sql;
grant execute on function basejump.generate_token(int) to authenticated; |