Schema: basejump

Auto-generated by npm run gendocs. Do not edit by hand — changes will be overwritten. To update, run npm run gendocs against a database with the latest migrations applied.

Counts: 6 tables · 0 views · 0 materialized views · 13 functions · 12 policies

---

Tables (6)

account_user

RLS enabled.

Column	Type	Null	Default	Comment
user_id	uuid	no
account_id	uuid	no
account_role	basejump.account_role	no
created_at	timestamp with time zone	yes	now()

Primary key: PRIMARY KEY (user_id, account_id)

Foreign keys:

• account_user_account_id_fkey: FOREIGN KEY (account_id) REFERENCES basejump.accounts(id) ON DELETE CASCADE
• account_user_user_id_fkey: FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE

Policies:

• Account users can be deleted except primary account owner — DELETE, PERMISSIVE, roles: authenticated
  • USING: ((basejump.has_role_on_account(account_id) = true) AND (user_id <> ( SELECT accounts.primary_owner_user_id FROM basejump.accounts WHERE (account_user.account_id = accounts.id))))
• users can view their own account_users — SELECT, PERMISSIVE, roles: authenticated
  • USING: (user_id = auth.uid())
• users can view their teammates — SELECT, PERMISSIVE, roles: authenticated
  • USING: (basejump.has_role_on_account(account_id) = true)

accounts

RLS enabled.

Column	Type	Null	Default	Comment
id	uuid	no	uuid_generate_v4()
primary_owner_user_id	uuid	no	auth.uid()
name	text	yes
slug	text	yes
personal_account	boolean	no	false
updated_at	timestamp with time zone	yes
created_at	timestamp with time zone	yes
created_by	uuid	yes
updated_by	uuid	yes
private_metadata	jsonb	yes	'{}'::jsonb
public_metadata	jsonb	yes	'{}'::jsonb
organization_number	character varying(30)	yes
website_url	character varying(255)	yes
nace_category_id	integer	yes
business_description	text	yes
category_id	integer	yes
emission_factor_id	integer	yes
street	text	yes
city	text	yes
state	text	yes
postal_code	character varying(10)	yes
country_code	character varying(2)	yes
email_invoices	text	yes
name_synonyms	text[]	yes

Primary key: PRIMARY KEY (id)

Unique:

• accounts_organization_number_key: UNIQUE (organization_number)
• accounts_slug_key: UNIQUE (slug)

Foreign keys:

• accounts_category_id_fkey: FOREIGN KEY (category_id) REFERENCES category(id) ON DELETE SET NULL
• accounts_country_code_fkey: FOREIGN KEY (country_code) REFERENCES country(code) ON DELETE SET NULL
• accounts_created_by_fkey: FOREIGN KEY (created_by) REFERENCES auth.users(id)
• accounts_emission_factor_id_fkey: FOREIGN KEY (emission_factor_id) REFERENCES emission_factor(id) ON DELETE SET NULL
• accounts_nace_category_id_fkey: FOREIGN KEY (nace_category_id) REFERENCES nace_category(id) ON DELETE SET NULL
• accounts_primary_owner_user_id_fkey: FOREIGN KEY (primary_owner_user_id) REFERENCES auth.users(id) ON DELETE CASCADE
• accounts_updated_by_fkey: FOREIGN KEY (updated_by) REFERENCES auth.users(id)

Checks:

• basejump_accounts_slug_null_if_personal_account_true: CHECK (personal_account = true AND slug IS NULL OR personal_account = false AND slug IS NOT NULL)

Policies:

• Accounts are viewable by any user — SELECT, PERMISSIVE, roles: authenticated
  • USING: true
• Accounts are viewable by members — SELECT, PERMISSIVE, roles: authenticated
  • USING: (basejump.has_role_on_account(id) = true)
• Accounts are viewable by primary owner — SELECT, PERMISSIVE, roles: authenticated
  • USING: (primary_owner_user_id = auth.uid())
• Accounts can be edited by owners — UPDATE, PERMISSIVE, roles: authenticated
  • USING: (basejump.has_role_on_account(id, 'owner'::basejump.account_role) = true)
• Team accounts can be created by any user — INSERT, PERMISSIVE, roles: authenticated
  • WITH CHECK: ((basejump.is_set('enable_team_accounts'::text) = true) AND (personal_account = false))

billing_customers

RLS enabled.

Column	Type	Null	Default	Comment
account_id	uuid	no
id	text	no
email	text	yes
active	boolean	yes
provider	text	yes

Primary key: PRIMARY KEY (id)

Foreign keys:

• billing_customers_account_id_fkey: FOREIGN KEY (account_id) REFERENCES basejump.accounts(id) ON DELETE CASCADE

Policies:

• Can only view own billing customer data. — SELECT, PERMISSIVE, roles: public
  • USING: (basejump.has_role_on_account(account_id) = true)

billing_subscriptions

RLS enabled.

Column	Type	Null	Default	Comment
id	text	no
account_id	uuid	no
billing_customer_id	text	no
status	basejump.subscription_status	yes
metadata	jsonb	yes
price_id	text	yes
plan_name	text	yes
quantity	integer	yes
cancel_at_period_end	boolean	yes
created	timestamp with time zone	no	timezone('utc'::text, now())
current_period_start	timestamp with time zone	no	timezone('utc'::text, now())
current_period_end	timestamp with time zone	no	timezone('utc'::text, now())
ended_at	timestamp with time zone	yes	timezone('utc'::text, now())
cancel_at	timestamp with time zone	yes	timezone('utc'::text, now())
canceled_at	timestamp with time zone	yes	timezone('utc'::text, now())
trial_start	timestamp with time zone	yes	timezone('utc'::text, now())
trial_end	timestamp with time zone	yes	timezone('utc'::text, now())
provider	text	yes

Primary key: PRIMARY KEY (id)

Foreign keys:

• billing_subscriptions_account_id_fkey: FOREIGN KEY (account_id) REFERENCES basejump.accounts(id) ON DELETE CASCADE
• billing_subscriptions_billing_customer_id_fkey: FOREIGN KEY (billing_customer_id) REFERENCES basejump.billing_customers(id) ON DELETE CASCADE

Policies:

• Can only view own billing subscription data. — SELECT, PERMISSIVE, roles: public
  • USING: (basejump.has_role_on_account(account_id) = true)

config

RLS enabled.

Column	Type	Null	Default	Comment
enable_team_accounts	boolean	yes	true
enable_personal_account_billing	boolean	yes	true
enable_team_account_billing	boolean	yes	true
billing_provider	text	yes	'stripe'::text

Policies:

• Basejump settings can be read by authenticated users — SELECT, PERMISSIVE, roles: authenticated
  • USING: true

invitations

RLS enabled.

Column	Type	Null	Default	Comment
id	uuid	no	uuid_generate_v4()
account_role	basejump.account_role	no
account_id	uuid	no
token	text	no	basejump.generate_token(30)
invited_by_user_id	uuid	no
account_name	text	yes
updated_at	timestamp with time zone	yes
created_at	timestamp with time zone	yes
invitation_type	basejump.invitation_type	no
email	text	yes

Primary key: PRIMARY KEY (id)

Unique:

• invitations_token_key: UNIQUE (token)

Foreign keys:

• invitations_account_id_fkey: FOREIGN KEY (account_id) REFERENCES basejump.accounts(id) ON DELETE CASCADE
• invitations_invited_by_user_id_fkey: FOREIGN KEY (invited_by_user_id) REFERENCES auth.users(id) ON DELETE CASCADE

Policies:

• Invitations can be created by account owners or parent accounts — ALL, PERMISSIVE, roles: authenticated
  • USING: ((basejump.is_set('enable_team_accounts'::text) = true) AND (EXISTS ( SELECT 1 FROM basejump.accounts WHERE ((accounts.id = invitations.account_id) AND (accounts.personal_account = false)))) AND ((basejump.has_role_on_account(account_id, 'owner'::basejump.account_role) = true) OR (EXISTS ( SELECT 1 FROM get_parent_accounts(get_account_slug(invitations.account_id)) parent_account(id, parent_account_id, name, slug) WHERE basejump.has_role_on_account(parent_account.id, 'owner'::basejump.account_role))) OR (EXISTS ( SELECT 1 FROM account_account aa WHERE ((aa.account_id = invitations.account_id) AND (aa.relation = 'customer-to'::account_relation_type) AND basejump.has_role_on_account(aa.other_account_id, 'owner'::basejump.account_role))))))
  • WITH CHECK: ((basejump.is_set('enable_team_accounts'::text) = true) AND (EXISTS ( SELECT 1 FROM basejump.accounts WHERE ((accounts.id = invitations.account_id) AND (accounts.personal_account = false)))) AND ((basejump.has_role_on_account(account_id, 'owner'::basejump.account_role) = true) OR (EXISTS ( SELECT 1 FROM get_parent_accounts(get_account_slug(invitations.account_id)) parent_account(id, parent_account_id, name, slug) WHERE basejump.has_role_on_account(parent_account.id, 'owner'::basejump.account_role))) OR (EXISTS ( SELECT 1 FROM account_account aa WHERE ((aa.account_id = invitations.account_id) AND (aa.relation = 'customer-to'::account_relation_type) AND basejump.has_role_on_account(aa.other_account_id, 'owner'::basejump.account_role))))))

Functions (13)

add_current_user_to_new_account()

• Returns: trigger
• Language: plpgsql
• Kind: function
• SECURITY DEFINER

find_account(p_account_name text)

• Returns: basejump.accounts
• Language: plpgsql
• Kind: function
• SECURITY DEFINER

generate_token(length integer)

• Returns: text
• Language: sql
• Kind: function

get_accounts_with_role(passed_in_role basejump.account_role DEFAULT NULL::basejump.account_role)

• Returns: SETOF uuid
• Language: sql
• Kind: function
• SECURITY DEFINER

get_config()

• Returns: json
• Language: plpgsql
• Kind: function

has_role_on_account(account_id uuid, account_role basejump.account_role DEFAULT NULL::basejump.account_role)

• Returns: boolean
• Language: sql
• Kind: function
• SECURITY DEFINER

is_set(field_name text)

• Returns: boolean
• Language: plpgsql
• Kind: function

protect_account_fields()

• Returns: trigger
• Language: plpgsql
• Kind: function

run_new_user_setup()

• Returns: trigger
• Language: plpgsql
• Kind: function
• SECURITY DEFINER

slugify_account_slug()

• Returns: trigger
• Language: plpgsql
• Kind: function

trigger_set_invitation_details()

• Returns: trigger
• Language: plpgsql
• Kind: function

trigger_set_timestamps()

• Returns: trigger
• Language: plpgsql
• Kind: function

trigger_set_user_tracking()

• Returns: trigger
• Language: plpgsql
• Kind: function