5631 lines
197 KiB
PL/PgSQL
5631 lines
197 KiB
PL/PgSQL
set check_function_bodies = off;
|
|
|
|
CREATE OR REPLACE FUNCTION kit.add_current_user_to_new_account()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path TO ''
|
|
AS $function$begin
|
|
if new.primary_owner_user_id = auth.uid() then
|
|
insert into medreport.accounts_memberships(
|
|
account_id,
|
|
user_id,
|
|
account_role)
|
|
values(
|
|
new.id,
|
|
auth.uid(),
|
|
medreport.get_upper_system_role());
|
|
|
|
end if;
|
|
|
|
return NEW;
|
|
|
|
end;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION kit.check_team_account()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$begin
|
|
if(
|
|
select
|
|
is_personal_account
|
|
from
|
|
medreport.accounts
|
|
where
|
|
id = new.account_id) then
|
|
raise exception 'Account must be an team account';
|
|
|
|
end if;
|
|
|
|
return NEW;
|
|
|
|
end;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION kit.cleanup_expired_nonces(p_older_than_days integer DEFAULT 1, p_include_used boolean DEFAULT true, p_include_revoked boolean DEFAULT true)
|
|
RETURNS integer
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path TO ''
|
|
AS $function$DECLARE
|
|
v_count INTEGER;
|
|
BEGIN
|
|
-- Count and delete expired or used nonces based on parameters
|
|
WITH deleted AS (
|
|
DELETE FROM medreport.nonces
|
|
WHERE
|
|
(
|
|
-- Expired and unused tokens
|
|
(expires_at < NOW() AND used_at IS NULL)
|
|
|
|
-- Used tokens older than specified days (if enabled)
|
|
OR (p_include_used = TRUE AND used_at < NOW() - (p_older_than_days * interval '1 day'))
|
|
|
|
-- Revoked tokens older than specified days (if enabled)
|
|
OR (p_include_revoked = TRUE AND revoked = TRUE AND created_at < NOW() - (p_older_than_days * interval '1 day'))
|
|
)
|
|
RETURNING 1
|
|
)
|
|
SELECT COUNT(*) INTO v_count FROM deleted;
|
|
|
|
RETURN v_count;
|
|
END;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION kit.handle_update_user_email()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path TO ''
|
|
AS $function$begin
|
|
update
|
|
medreport.accounts
|
|
set
|
|
email = new.email
|
|
where
|
|
primary_owner_user_id = new.id
|
|
and is_personal_account = true;
|
|
|
|
return new;
|
|
|
|
end;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION kit.prevent_account_owner_membership_delete()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$begin
|
|
if exists(
|
|
select
|
|
1
|
|
from
|
|
medreport.accounts
|
|
where
|
|
id = old.account_id
|
|
and primary_owner_user_id = old.user_id) then
|
|
raise exception 'The primary account owner cannot be removed from the account membership list';
|
|
|
|
end if;
|
|
|
|
return old;
|
|
|
|
end;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION kit.set_slug_from_account_name()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path TO ''
|
|
AS $function$declare
|
|
sql_string varchar;
|
|
tmp_slug varchar;
|
|
increment integer;
|
|
tmp_row record;
|
|
tmp_row_count integer;
|
|
begin
|
|
tmp_row_count = 1;
|
|
|
|
increment = 0;
|
|
|
|
while tmp_row_count > 0 loop
|
|
if increment > 0 then
|
|
tmp_slug = kit.slugify(new.name || ' ' || increment::varchar);
|
|
|
|
else
|
|
tmp_slug = kit.slugify(new.name);
|
|
|
|
end if;
|
|
|
|
sql_string = format('select count(1) cnt from medreport.accounts where slug = ''' || tmp_slug ||
|
|
'''; ');
|
|
|
|
for tmp_row in execute (sql_string)
|
|
loop
|
|
raise notice 'tmp_row %', tmp_row;
|
|
|
|
tmp_row_count = tmp_row.cnt;
|
|
|
|
end loop;
|
|
|
|
increment = increment +1;
|
|
|
|
end loop;
|
|
|
|
new.slug := tmp_slug;
|
|
|
|
return NEW;
|
|
|
|
end$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION kit.setup_new_user()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path TO ''
|
|
AS $function$declare
|
|
user_name text;
|
|
picture_url text;
|
|
begin
|
|
if new.raw_user_meta_data ->> 'name' is not null then
|
|
user_name := new.raw_user_meta_data ->> 'name';
|
|
|
|
end if;
|
|
|
|
if user_name is null and new.email is not null then
|
|
user_name := split_part(new.email, '@', 1);
|
|
|
|
end if;
|
|
|
|
if user_name is null then
|
|
user_name := '';
|
|
|
|
end if;
|
|
|
|
if new.raw_user_meta_data ->> 'avatar_url' is not null then
|
|
picture_url := new.raw_user_meta_data ->> 'avatar_url';
|
|
else
|
|
picture_url := null;
|
|
end if;
|
|
|
|
insert into medreport.accounts(
|
|
id,
|
|
primary_owner_user_id,
|
|
name,
|
|
is_personal_account,
|
|
picture_url,
|
|
email)
|
|
values (
|
|
new.id,
|
|
new.id,
|
|
user_name,
|
|
true,
|
|
picture_url,
|
|
new.email);
|
|
|
|
return new;
|
|
|
|
end;$function$
|
|
;
|
|
|
|
|
|
create schema if not exists "medreport";
|
|
|
|
create type "medreport"."analysis_order_status" as enum ('QUEUED', 'ON_HOLD', 'PROCESSING', 'COMPLETED', 'REJECTED', 'CANCELLED');
|
|
|
|
create type "medreport"."app_permissions" as enum ('roles.manage', 'billing.manage', 'settings.manage', 'members.manage', 'invites.manage');
|
|
|
|
create type "medreport"."billing_provider" as enum ('stripe', 'lemon-squeezy', 'paddle');
|
|
|
|
create type "medreport"."notification_channel" as enum ('in_app', 'email');
|
|
|
|
create type "medreport"."notification_type" as enum ('info', 'warning', 'error');
|
|
|
|
create type "medreport"."payment_status" as enum ('pending', 'succeeded', 'failed');
|
|
|
|
create type "medreport"."subscription_item_type" as enum ('flat', 'per_seat', 'metered');
|
|
|
|
create type "medreport"."subscription_status" as enum ('active', 'trialing', 'past_due', 'canceled', 'unpaid', 'incomplete', 'incomplete_expired', 'paused');
|
|
|
|
create sequence "medreport"."billing_customers_id_seq";
|
|
|
|
create sequence "medreport"."invitations_id_seq";
|
|
|
|
create table "medreport"."account_params" (
|
|
"recorded_at" timestamp with time zone not null default now(),
|
|
"id" uuid not null default gen_random_uuid(),
|
|
"account_id" uuid not null default auth.uid(),
|
|
"weight" integer,
|
|
"height" integer
|
|
);
|
|
|
|
|
|
alter table "medreport"."account_params" enable row level security;
|
|
|
|
create table "medreport"."accounts" (
|
|
"id" uuid not null default uuid_generate_v4(),
|
|
"primary_owner_user_id" uuid not null default auth.uid(),
|
|
"name" character varying(255) not null,
|
|
"slug" text,
|
|
"email" character varying(320),
|
|
"is_personal_account" boolean not null default false,
|
|
"updated_at" timestamp with time zone,
|
|
"created_at" timestamp with time zone,
|
|
"created_by" uuid,
|
|
"updated_by" uuid,
|
|
"picture_url" character varying(1000),
|
|
"public_data" jsonb not null default '{}'::jsonb,
|
|
"last_name" text,
|
|
"personal_code" text,
|
|
"city" text,
|
|
"has_consent_personal_data" boolean,
|
|
"phone" text
|
|
);
|
|
|
|
|
|
alter table "medreport"."accounts" enable row level security;
|
|
|
|
create table "medreport"."accounts_memberships" (
|
|
"user_id" uuid not null,
|
|
"account_id" uuid not null,
|
|
"account_role" character varying(50) not null,
|
|
"created_at" timestamp with time zone not null default CURRENT_TIMESTAMP,
|
|
"updated_at" timestamp with time zone not null default CURRENT_TIMESTAMP,
|
|
"created_by" uuid,
|
|
"updated_by" uuid
|
|
);
|
|
|
|
|
|
alter table "medreport"."accounts_memberships" enable row level security;
|
|
|
|
create table "medreport"."analyses" (
|
|
"id" bigint generated by default as identity not null,
|
|
"analysis_id_oid" text not null,
|
|
"analysis_id_original" text not null,
|
|
"tehik_short_loinc" text,
|
|
"tehik_loinc_name" text,
|
|
"analysis_name_lab" text,
|
|
"order" smallint not null,
|
|
"created_at" timestamp with time zone not null default now(),
|
|
"updated_at" timestamp with time zone default now(),
|
|
"parent_analysis_element_id" bigint not null
|
|
);
|
|
|
|
|
|
alter table "medreport"."analyses" enable row level security;
|
|
|
|
create table "medreport"."analysis_elements" (
|
|
"id" bigint generated by default as identity not null,
|
|
"analysis_id_oid" text not null,
|
|
"analysis_id_original" text not null,
|
|
"tehik_short_loinc" text not null,
|
|
"tehik_loinc_name" text not null,
|
|
"analysis_name_lab" text,
|
|
"order" smallint not null,
|
|
"created_at" timestamp with time zone not null default now(),
|
|
"updated_at" timestamp with time zone default now(),
|
|
"parent_analysis_group_id" bigint not null,
|
|
"material_groups" jsonb[]
|
|
);
|
|
|
|
|
|
alter table "medreport"."analysis_elements" enable row level security;
|
|
|
|
create table "medreport"."analysis_groups" (
|
|
"id" bigint generated by default as identity not null,
|
|
"original_id" text not null,
|
|
"name" text not null,
|
|
"order" smallint not null,
|
|
"created_at" timestamp with time zone not null default now(),
|
|
"updated_at" timestamp with time zone default now()
|
|
);
|
|
|
|
|
|
alter table "medreport"."analysis_groups" enable row level security;
|
|
|
|
create table "medreport"."analysis_orders" (
|
|
"analysis_element_ids" bigint[],
|
|
"analysis_ids" bigint[],
|
|
"user_id" uuid not null,
|
|
"status" medreport.analysis_order_status not null,
|
|
"created_at" timestamp with time zone not null default now(),
|
|
"id" bigint generated by default as identity not null
|
|
);
|
|
|
|
|
|
alter table "medreport"."analysis_orders" enable row level security;
|
|
|
|
create table "medreport"."analysis_response_elements" (
|
|
"id" bigint generated by default as identity not null,
|
|
"analysis_response_id" bigint not null,
|
|
"analysis_element_original_id" text not null,
|
|
"unit" text,
|
|
"response_value" jsonb not null,
|
|
"response_time" timestamp with time zone not null,
|
|
"norm_upper" double precision,
|
|
"norm_upper_included" boolean,
|
|
"norm_lower" double precision,
|
|
"norm_lower_included" boolean,
|
|
"norm_status" smallint,
|
|
"original_response_element" jsonb not null,
|
|
"created_at" timestamp with time zone not null default now(),
|
|
"updated_at" timestamp with time zone default now()
|
|
);
|
|
|
|
|
|
alter table "medreport"."analysis_response_elements" enable row level security;
|
|
|
|
create table "medreport"."analysis_responses" (
|
|
"id" bigint generated by default as identity not null,
|
|
"analysis_order_id" bigint not null,
|
|
"order_number" text not null,
|
|
"order_status" medreport.analysis_order_status not null,
|
|
"user_id" uuid not null,
|
|
"created_at" timestamp with time zone not null default now(),
|
|
"updated_at" timestamp with time zone default now()
|
|
);
|
|
|
|
|
|
alter table "medreport"."analysis_responses" enable row level security;
|
|
|
|
create table "medreport"."billing_customers" (
|
|
"account_id" uuid not null,
|
|
"id" integer not null default nextval('medreport.billing_customers_id_seq'::regclass),
|
|
"email" text,
|
|
"provider" medreport.billing_provider not null,
|
|
"customer_id" text not null
|
|
);
|
|
|
|
|
|
alter table "medreport"."billing_customers" enable row level security;
|
|
|
|
create table "medreport"."codes" (
|
|
"id" bigint generated by default as identity not null,
|
|
"hk_code" text not null,
|
|
"hk_code_multiplier" bigint not null,
|
|
"coefficient" double precision not null,
|
|
"price" double precision not null,
|
|
"analysis_group_id" bigint,
|
|
"analysis_element_id" bigint,
|
|
"analysis_id" bigint,
|
|
"updated_at" timestamp with time zone default now(),
|
|
"created_at" timestamp with time zone not null default now()
|
|
);
|
|
|
|
|
|
alter table "medreport"."codes" enable row level security;
|
|
|
|
create table "medreport"."config" (
|
|
"enable_team_accounts" boolean not null default true,
|
|
"enable_account_billing" boolean not null default true,
|
|
"enable_team_account_billing" boolean not null default true,
|
|
"billing_provider" medreport.billing_provider not null default 'stripe'::medreport.billing_provider
|
|
);
|
|
|
|
|
|
alter table "medreport"."config" enable row level security;
|
|
|
|
create table "medreport"."connected_online_providers" (
|
|
"id" bigint not null,
|
|
"name" text not null,
|
|
"email" text,
|
|
"phone_number" text,
|
|
"can_select_worker" boolean not null,
|
|
"personal_code_required" boolean not null,
|
|
"created_at" timestamp with time zone not null default now(),
|
|
"updated_at" timestamp without time zone default now()
|
|
);
|
|
|
|
|
|
alter table "medreport"."connected_online_providers" enable row level security;
|
|
|
|
create table "medreport"."connected_online_reservation" (
|
|
"id" bigint generated by default as identity not null,
|
|
"user_id" uuid not null,
|
|
"booking_code" text not null,
|
|
"service_id" bigint not null,
|
|
"clinic_id" bigint not null,
|
|
"service_user_id" bigint,
|
|
"sync_user_id" bigint not null,
|
|
"requires_payment" boolean not null,
|
|
"comments" text,
|
|
"start_time" timestamp with time zone not null,
|
|
"lang" text not null,
|
|
"discount_code" text,
|
|
"created_at" timestamp with time zone not null default now(),
|
|
"updated_at" timestamp with time zone default now()
|
|
);
|
|
|
|
|
|
alter table "medreport"."connected_online_reservation" enable row level security;
|
|
|
|
create table "medreport"."connected_online_services" (
|
|
"id" bigint not null,
|
|
"clinic_id" bigint not null,
|
|
"sync_id" bigint not null,
|
|
"name" text not null,
|
|
"description" text,
|
|
"price" double precision not null,
|
|
"requires_payment" boolean not null,
|
|
"duration" bigint not null,
|
|
"neto_duration" bigint,
|
|
"display" text,
|
|
"price_periods" text,
|
|
"online_hide_duration" bigint,
|
|
"online_hide_price" bigint,
|
|
"code" text not null,
|
|
"has_free_codes" boolean not null,
|
|
"created_at" timestamp with time zone not null default now(),
|
|
"updated_at" timestamp with time zone default now()
|
|
);
|
|
|
|
|
|
alter table "medreport"."connected_online_services" enable row level security;
|
|
|
|
create table "medreport"."invitations" (
|
|
"id" integer not null default nextval('medreport.invitations_id_seq'::regclass),
|
|
"email" character varying(255) not null,
|
|
"account_id" uuid not null,
|
|
"invited_by" uuid not null,
|
|
"role" character varying(50) not null,
|
|
"invite_token" character varying(255) not null,
|
|
"created_at" timestamp with time zone not null default CURRENT_TIMESTAMP,
|
|
"updated_at" timestamp with time zone not null default CURRENT_TIMESTAMP,
|
|
"expires_at" timestamp with time zone not null default (CURRENT_TIMESTAMP + '7 days'::interval)
|
|
);
|
|
|
|
|
|
alter table "medreport"."invitations" enable row level security;
|
|
|
|
create table "medreport"."medreport_product_groups" (
|
|
"id" bigint generated by default as identity not null,
|
|
"name" text not null,
|
|
"created_at" timestamp with time zone not null default now(),
|
|
"updated_at" timestamp with time zone
|
|
);
|
|
|
|
|
|
alter table "medreport"."medreport_product_groups" enable row level security;
|
|
|
|
create table "medreport"."medreport_products" (
|
|
"id" bigint generated by default as identity not null,
|
|
"name" text not null,
|
|
"product_group_id" bigint,
|
|
"created_at" timestamp with time zone not null default now(),
|
|
"updated_at" timestamp with time zone default now()
|
|
);
|
|
|
|
|
|
alter table "medreport"."medreport_products" enable row level security;
|
|
|
|
create table "medreport"."medreport_products_analyses_relations" (
|
|
"product_id" bigint not null,
|
|
"analysis_element_id" bigint,
|
|
"analysis_id" bigint
|
|
);
|
|
|
|
|
|
alter table "medreport"."medreport_products_analyses_relations" enable row level security;
|
|
|
|
create table "medreport"."medreport_products_external_services_relations" (
|
|
"product_id" bigint not null,
|
|
"connected_online_service_id" bigint not null
|
|
);
|
|
|
|
|
|
alter table "medreport"."medreport_products_external_services_relations" enable row level security;
|
|
|
|
create table "medreport"."nonces" (
|
|
"id" uuid not null default gen_random_uuid(),
|
|
"client_token" text not null,
|
|
"nonce" text not null,
|
|
"user_id" uuid,
|
|
"purpose" text not null,
|
|
"expires_at" timestamp with time zone not null,
|
|
"created_at" timestamp with time zone not null default now(),
|
|
"used_at" timestamp with time zone,
|
|
"revoked" boolean not null default false,
|
|
"revoked_reason" text,
|
|
"verification_attempts" integer not null default 0,
|
|
"last_verification_at" timestamp with time zone,
|
|
"last_verification_ip" inet,
|
|
"last_verification_user_agent" text,
|
|
"metadata" jsonb default '{}'::jsonb,
|
|
"scopes" text[] default '{}'::text[]
|
|
);
|
|
|
|
|
|
alter table "medreport"."nonces" enable row level security;
|
|
|
|
create table "medreport"."notifications" (
|
|
"id" bigint generated always as identity not null,
|
|
"account_id" uuid not null,
|
|
"type" medreport.notification_type not null default 'info'::medreport.notification_type,
|
|
"body" character varying(5000) not null,
|
|
"link" character varying(255),
|
|
"channel" medreport.notification_channel not null default 'in_app'::medreport.notification_channel,
|
|
"dismissed" boolean not null default false,
|
|
"expires_at" timestamp with time zone default (now() + '1 mon'::interval),
|
|
"created_at" timestamp with time zone not null default now()
|
|
);
|
|
|
|
|
|
alter table "medreport"."notifications" enable row level security;
|
|
|
|
create table "medreport"."order_items" (
|
|
"id" text not null,
|
|
"order_id" text not null,
|
|
"product_id" text not null,
|
|
"variant_id" text not null,
|
|
"price_amount" numeric,
|
|
"quantity" integer not null default 1,
|
|
"created_at" timestamp with time zone not null default CURRENT_TIMESTAMP,
|
|
"updated_at" timestamp with time zone not null default CURRENT_TIMESTAMP
|
|
);
|
|
|
|
|
|
alter table "medreport"."order_items" enable row level security;
|
|
|
|
create table "medreport"."orders" (
|
|
"id" text not null,
|
|
"account_id" uuid not null,
|
|
"billing_customer_id" integer not null,
|
|
"status" medreport.payment_status not null,
|
|
"billing_provider" medreport.billing_provider not null,
|
|
"total_amount" numeric not null,
|
|
"currency" character varying(3) not null,
|
|
"created_at" timestamp with time zone not null default CURRENT_TIMESTAMP,
|
|
"updated_at" timestamp with time zone not null default CURRENT_TIMESTAMP
|
|
);
|
|
|
|
|
|
alter table "medreport"."orders" enable row level security;
|
|
|
|
create table "medreport"."role_permissions" (
|
|
"id" bigint generated by default as identity not null,
|
|
"role" character varying(50) not null,
|
|
"permission" medreport.app_permissions not null
|
|
);
|
|
|
|
|
|
alter table "medreport"."role_permissions" enable row level security;
|
|
|
|
create table "medreport"."roles" (
|
|
"name" character varying(50) not null,
|
|
"hierarchy_level" integer not null
|
|
);
|
|
|
|
|
|
alter table "medreport"."roles" enable row level security;
|
|
|
|
create table "medreport"."subscription_items" (
|
|
"id" character varying(255) not null,
|
|
"subscription_id" text not null,
|
|
"product_id" character varying(255) not null,
|
|
"variant_id" character varying(255) not null,
|
|
"type" medreport.subscription_item_type not null,
|
|
"price_amount" numeric,
|
|
"quantity" integer not null default 1,
|
|
"interval" character varying(255) not null,
|
|
"interval_count" integer not null,
|
|
"created_at" timestamp with time zone not null default CURRENT_TIMESTAMP,
|
|
"updated_at" timestamp with time zone not null default CURRENT_TIMESTAMP
|
|
);
|
|
|
|
|
|
alter table "medreport"."subscription_items" enable row level security;
|
|
|
|
create table "medreport"."subscriptions" (
|
|
"id" text not null,
|
|
"account_id" uuid not null,
|
|
"billing_customer_id" integer not null,
|
|
"status" medreport.subscription_status not null,
|
|
"active" boolean not null,
|
|
"billing_provider" medreport.billing_provider not null,
|
|
"cancel_at_period_end" boolean not null,
|
|
"currency" character varying(3) not null,
|
|
"created_at" timestamp with time zone not null default CURRENT_TIMESTAMP,
|
|
"updated_at" timestamp with time zone not null default CURRENT_TIMESTAMP,
|
|
"period_starts_at" timestamp with time zone not null,
|
|
"period_ends_at" timestamp with time zone not null,
|
|
"trial_starts_at" timestamp with time zone,
|
|
"trial_ends_at" timestamp with time zone
|
|
);
|
|
|
|
/* START OF DROPPING OLD PUBLIC FIELDS */
|
|
|
|
drop trigger if exists "add_current_user_to_new_account" on "public"."accounts";
|
|
|
|
drop trigger if exists "protect_account_fields" on "public"."accounts";
|
|
|
|
drop trigger if exists "set_slug_from_account_name" on "public"."accounts";
|
|
|
|
drop trigger if exists "update_slug_from_account_name" on "public"."accounts";
|
|
|
|
drop trigger if exists "prevent_account_owner_membership_delete_check" on "public"."accounts_memberships";
|
|
|
|
drop trigger if exists "prevent_memberships_update_check" on "public"."accounts_memberships";
|
|
|
|
drop trigger if exists "analysis_change_record_timestamps" on "public"."analyses";
|
|
|
|
drop trigger if exists "analysis_elements_change_record_timestamps" on "public"."analysis_elements";
|
|
|
|
drop trigger if exists "analysis_groups_change_record_timestamps" on "public"."analysis_groups";
|
|
|
|
drop trigger if exists "codes_change_record_timestamps" on "public"."codes";
|
|
|
|
drop trigger if exists "connected_online_providers_change_record_timestamps" on "public"."connected_online_providers";
|
|
|
|
drop trigger if exists "connected_online_services_change_record_timestamps" on "public"."connected_online_services";
|
|
|
|
drop trigger if exists "only_team_accounts_check" on "public"."invitations";
|
|
|
|
drop trigger if exists "check_not_already_tied_to_connected_online" on "public"."medreport_products_analyses_relations";
|
|
|
|
drop trigger if exists "check_not_already_tied_to_analysis" on "public"."medreport_products_external_services_relations";
|
|
|
|
drop trigger if exists "update_notification_dismissed_status" on "public"."notifications";
|
|
|
|
drop policy "users can insert their params" on "public"."account_params";
|
|
|
|
drop policy "users can read their params" on "public"."account_params";
|
|
|
|
drop policy "accounts_read" on "public"."accounts";
|
|
|
|
drop policy "accounts_self_update" on "public"."accounts";
|
|
|
|
drop policy "create_org_account" on "public"."accounts";
|
|
|
|
drop policy "restrict_mfa_accounts" on "public"."accounts";
|
|
|
|
drop policy "super_admins_access_accounts" on "public"."accounts";
|
|
|
|
drop policy "accounts_memberships_delete" on "public"."accounts_memberships";
|
|
|
|
drop policy "accounts_memberships_read" on "public"."accounts_memberships";
|
|
|
|
drop policy "restrict_mfa_accounts_memberships" on "public"."accounts_memberships";
|
|
|
|
drop policy "super_admins_access_accounts_memberships" on "public"."accounts_memberships";
|
|
|
|
drop policy "analysis_all" on "public"."analyses";
|
|
|
|
drop policy "analysis_select" on "public"."analyses";
|
|
|
|
drop policy "analysis_elements_all" on "public"."analysis_elements";
|
|
|
|
drop policy "analysis_elements_select" on "public"."analysis_elements";
|
|
|
|
drop policy "analysis_groups_all" on "public"."analysis_groups";
|
|
|
|
drop policy "analysis_groups_select" on "public"."analysis_groups";
|
|
|
|
drop policy "analysis_all" on "public"."analysis_orders";
|
|
|
|
drop policy "service_role_all" on "public"."analysis_orders";
|
|
|
|
drop policy "select_own" on "public"."analysis_response_elements";
|
|
|
|
drop policy "service_role_all" on "public"."analysis_response_elements";
|
|
|
|
drop policy "select_own" on "public"."analysis_responses";
|
|
|
|
drop policy "service_role_all" on "public"."analysis_responses";
|
|
|
|
drop policy "billing_customers_read_self" on "public"."billing_customers";
|
|
|
|
drop policy "codes_all" on "public"."codes";
|
|
|
|
drop policy "public config can be read by authenticated users" on "public"."config";
|
|
|
|
drop policy "authenticated_select" on "public"."connected_online_providers";
|
|
|
|
drop policy "service_role_all" on "public"."connected_online_providers";
|
|
|
|
drop policy "own_all" on "public"."connected_online_reservation";
|
|
|
|
drop policy "service_role_all" on "public"."connected_online_reservation";
|
|
|
|
drop policy "authenticated_select" on "public"."connected_online_services";
|
|
|
|
drop policy "service_role_all" on "public"."connected_online_services";
|
|
|
|
drop policy "invitations_create_self" on "public"."invitations";
|
|
|
|
drop policy "invitations_delete" on "public"."invitations";
|
|
|
|
drop policy "invitations_read_self" on "public"."invitations";
|
|
|
|
drop policy "invitations_update" on "public"."invitations";
|
|
|
|
drop policy "restrict_mfa_invitations" on "public"."invitations";
|
|
|
|
drop policy "super_admins_access_invitations" on "public"."invitations";
|
|
|
|
drop policy "read_all" on "public"."medreport_product_groups";
|
|
|
|
drop policy "Enable read access for all users" on "public"."medreport_products_analyses_relations";
|
|
|
|
drop policy "Users can read their own nonces" on "public"."nonces";
|
|
|
|
drop policy "notifications_read_self" on "public"."notifications";
|
|
|
|
drop policy "notifications_update_self" on "public"."notifications";
|
|
|
|
drop policy "restrict_mfa_notifications" on "public"."notifications";
|
|
|
|
drop policy "order_items_read_self" on "public"."order_items";
|
|
|
|
drop policy "restrict_mfa_order_items" on "public"."order_items";
|
|
|
|
drop policy "super_admins_access_order_items" on "public"."order_items";
|
|
|
|
drop policy "orders_read_self" on "public"."orders";
|
|
|
|
drop policy "restrict_mfa_orders" on "public"."orders";
|
|
|
|
drop policy "super_admins_access_orders" on "public"."orders";
|
|
|
|
drop policy "restrict_mfa_role_permissions" on "public"."role_permissions";
|
|
|
|
drop policy "role_permissions_read" on "public"."role_permissions";
|
|
|
|
drop policy "super_admins_access_role_permissions" on "public"."role_permissions";
|
|
|
|
drop policy "roles_read" on "public"."roles";
|
|
|
|
drop policy "restrict_mfa_subscription_items" on "public"."subscription_items";
|
|
|
|
drop policy "subscription_items_read_self" on "public"."subscription_items";
|
|
|
|
drop policy "super_admins_access_subscription_items" on "public"."subscription_items";
|
|
|
|
drop policy "restrict_mfa_subscriptions" on "public"."subscriptions";
|
|
|
|
drop policy "subscriptions_read_self" on "public"."subscriptions";
|
|
|
|
drop policy "super_admins_access_subscriptions" on "public"."subscriptions";
|
|
|
|
alter table "public"."accounts" drop constraint "accounts_created_by_fkey";
|
|
|
|
alter table "public"."accounts" drop constraint "accounts_email_key";
|
|
|
|
alter table "public"."accounts" drop constraint "accounts_primary_owner_user_id_fkey";
|
|
|
|
alter table "public"."accounts" drop constraint "accounts_slug_key";
|
|
|
|
alter table "public"."accounts" drop constraint "accounts_slug_null_if_personal_account_true";
|
|
|
|
alter table "public"."accounts" drop constraint "accounts_updated_by_fkey";
|
|
|
|
alter table "public"."accounts_memberships" drop constraint "accounts_memberships_account_id_fkey";
|
|
|
|
alter table "public"."accounts_memberships" drop constraint "accounts_memberships_account_role_fkey";
|
|
|
|
alter table "public"."accounts_memberships" drop constraint "accounts_memberships_created_by_fkey";
|
|
|
|
alter table "public"."accounts_memberships" drop constraint "accounts_memberships_updated_by_fkey";
|
|
|
|
alter table "public"."accounts_memberships" drop constraint "accounts_memberships_user_id_fkey";
|
|
|
|
alter table "public"."analyses" drop constraint "analyses_analysis_id_original_key";
|
|
|
|
alter table "public"."analyses" drop constraint "analyses_parent_analysis_element_id_fkey";
|
|
|
|
alter table "public"."analysis_elements" drop constraint "analysis_elements_analysis_id_original_key";
|
|
|
|
alter table "public"."analysis_elements" drop constraint "analysis_elements_parent_analysis_group_id_fkey";
|
|
|
|
alter table "public"."analysis_groups" drop constraint "analysis_group_original_id_key";
|
|
|
|
alter table "public"."analysis_orders" drop constraint "analysis_orders_user_id_fkey";
|
|
|
|
alter table "public"."analysis_response_elements" drop constraint "analysis_response_element_analysis_response_id_fkey";
|
|
|
|
alter table "public"."analysis_responses" drop constraint "analysis_response_user_id_fkey";
|
|
|
|
alter table "public"."analysis_responses" drop constraint "analysis_responses_analysis_order_id_fkey";
|
|
|
|
alter table "public"."analysis_responses" drop constraint "analysis_responses_order_number_key";
|
|
|
|
alter table "public"."billing_customers" drop constraint "billing_customers_account_id_customer_id_provider_key";
|
|
|
|
alter table "public"."billing_customers" drop constraint "billing_customers_account_id_fkey";
|
|
|
|
alter table "public"."codes" drop constraint "codes_analysis_element_id_fkey";
|
|
|
|
alter table "public"."codes" drop constraint "codes_analysis_group_id_fkey";
|
|
|
|
alter table "public"."codes" drop constraint "codes_analysis_id_fkey";
|
|
|
|
alter table "public"."medreport_products_external_services_relations" drop constraint "medreport_products_connected_online_services_id_fkey";
|
|
|
|
alter table "public"."medreport_products_external_services_relations" drop constraint "medreport_products_connected_online_services_id_key";
|
|
|
|
alter table "public"."medreport_products_external_services_relations" drop constraint "medreport_products_connected_online_services_product_id_fkey";
|
|
|
|
alter table "public"."connected_online_services" drop constraint "connected_online_services_clinic_id_fkey";
|
|
|
|
alter table "public"."connected_online_services" drop constraint "connected_online_services_id_key";
|
|
|
|
alter table "public"."connected_online_providers" drop constraint "connected_online_providers_id_key";
|
|
|
|
alter table "public"."connected_online_reservation" drop constraint "connected_online_reservation_booking_code_key";
|
|
|
|
alter table "public"."connected_online_reservation" drop constraint "connected_online_reservation_user_id_fkey";
|
|
|
|
alter table "public"."invitations" drop constraint "invitations_account_id_fkey";
|
|
|
|
alter table "public"."invitations" drop constraint "invitations_email_account_id_key";
|
|
|
|
alter table "public"."invitations" drop constraint "invitations_invite_token_key";
|
|
|
|
alter table "public"."invitations" drop constraint "invitations_invited_by_fkey";
|
|
|
|
alter table "public"."invitations" drop constraint "invitations_role_fkey";
|
|
|
|
alter table "public"."medreport_product_groups" drop constraint "medreport_product_groups_name_key";
|
|
|
|
alter table "public"."medreport_products" drop constraint "medreport_products_name_key";
|
|
|
|
alter table "public"."medreport_products" drop constraint "medreport_products_product_groups_id_fkey";
|
|
|
|
alter table "public"."medreport_products_analyses_relations" drop constraint "medreport_products_analyses_analysis_element_id_fkey";
|
|
|
|
alter table "public"."medreport_products_analyses_relations" drop constraint "medreport_products_analyses_analysis_element_id_key";
|
|
|
|
alter table "public"."medreport_products_analyses_relations" drop constraint "medreport_products_analyses_analysis_id_fkey";
|
|
|
|
alter table "public"."medreport_products_analyses_relations" drop constraint "medreport_products_analyses_analysis_id_key";
|
|
|
|
alter table "public"."medreport_products_analyses_relations" drop constraint "medreport_products_analyses_product_id_fkey";
|
|
|
|
alter table "public"."medreport_products_analyses_relations" drop constraint "product_can_be_tied_to_only_one_analysis_item";
|
|
|
|
alter table "public"."medreport_products_analyses_relations" drop constraint "product_can_be_tied_to_only_one_external_item";
|
|
|
|
alter table "public"."nonces" drop constraint "nonces_user_id_fkey";
|
|
|
|
alter table "public"."notifications" drop constraint "notifications_account_id_fkey";
|
|
|
|
alter table "public"."order_items" drop constraint "order_items_order_id_fkey";
|
|
|
|
alter table "public"."order_items" drop constraint "order_items_order_id_product_id_variant_id_key";
|
|
|
|
alter table "public"."orders" drop constraint "orders_account_id_fkey";
|
|
|
|
alter table "public"."orders" drop constraint "orders_billing_customer_id_fkey";
|
|
|
|
alter table "public"."role_permissions" drop constraint "role_permissions_role_fkey";
|
|
|
|
alter table "public"."role_permissions" drop constraint "role_permissions_role_permission_key";
|
|
|
|
alter table "public"."roles" drop constraint "roles_hierarchy_level_check";
|
|
|
|
alter table "public"."roles" drop constraint "roles_hierarchy_level_key";
|
|
|
|
alter table "public"."subscription_items" drop constraint "subscription_items_interval_count_check";
|
|
|
|
alter table "public"."subscription_items" drop constraint "subscription_items_subscription_id_fkey";
|
|
|
|
alter table "public"."subscription_items" drop constraint "subscription_items_subscription_id_product_id_variant_id_key";
|
|
|
|
alter table "public"."subscriptions" drop constraint "subscriptions_account_id_fkey";
|
|
|
|
alter table "public"."subscriptions" drop constraint "subscriptions_billing_customer_id_fkey";
|
|
|
|
alter table "public"."account_params" drop constraint "account_params_pkey";
|
|
|
|
alter table "public"."accounts" drop constraint "accounts_pkey";
|
|
|
|
alter table "public"."accounts_memberships" drop constraint "accounts_memberships_pkey";
|
|
|
|
alter table "public"."analyses" drop constraint "analysis_pkey";
|
|
|
|
alter table "public"."analysis_elements" drop constraint "analysis_elements_pkey";
|
|
|
|
alter table "public"."analysis_groups" drop constraint "analysis_group_pkey";
|
|
|
|
alter table "public"."analysis_orders" drop constraint "analysis_orders_pkey";
|
|
|
|
alter table "public"."analysis_response_elements" drop constraint "analysis_response_element_pkey";
|
|
|
|
alter table "public"."analysis_responses" drop constraint "analysis_response_pkey";
|
|
|
|
alter table "public"."billing_customers" drop constraint "billing_customers_pkey";
|
|
|
|
alter table "public"."codes" drop constraint "codes_pkey";
|
|
|
|
alter table "public"."connected_online_providers" drop constraint "connected_online_providers_pkey";
|
|
|
|
alter table "public"."connected_online_reservation" drop constraint "connected_online_reservation_pkey";
|
|
|
|
alter table "public"."connected_online_services" drop constraint "connected_online_services_pkey";
|
|
|
|
alter table "public"."invitations" drop constraint "invitations_pkey";
|
|
|
|
alter table "public"."medreport_product_groups" drop constraint "medreport_product_groups_pkey";
|
|
|
|
alter table "public"."medreport_products" drop constraint "medreport_products_pkey";
|
|
|
|
alter table "public"."medreport_products_analyses_relations" drop constraint "medreport_products_analyses_pkey";
|
|
|
|
alter table "public"."medreport_products_external_services_relations" drop constraint "medreport_products_connected_online_services_pkey";
|
|
|
|
alter table "public"."nonces" drop constraint "nonces_pkey";
|
|
|
|
alter table "public"."notifications" drop constraint "notifications_pkey";
|
|
|
|
alter table "public"."order_items" drop constraint "order_items_pkey";
|
|
|
|
alter table "public"."orders" drop constraint "orders_pkey";
|
|
|
|
alter table "public"."role_permissions" drop constraint "role_permissions_pkey";
|
|
|
|
alter table "public"."roles" drop constraint "roles_pkey";
|
|
|
|
alter table "public"."subscription_items" drop constraint "subscription_items_pkey";
|
|
|
|
alter table "public"."subscriptions" drop constraint "subscriptions_pkey";
|
|
|
|
drop index if exists "public"."account_params_pkey";
|
|
|
|
drop index if exists "public"."accounts_email_key";
|
|
|
|
drop index if exists "public"."accounts_memberships_pkey";
|
|
|
|
drop index if exists "public"."accounts_pkey";
|
|
|
|
drop index if exists "public"."accounts_slug_key";
|
|
|
|
drop index if exists "public"."analyses_analysis_id_original_key";
|
|
|
|
drop index if exists "public"."analysis_elements_analysis_id_original_key";
|
|
|
|
drop index if exists "public"."analysis_elements_pkey";
|
|
|
|
drop index if exists "public"."analysis_group_original_id_key";
|
|
|
|
drop index if exists "public"."analysis_group_pkey";
|
|
|
|
drop index if exists "public"."analysis_orders_pkey";
|
|
|
|
drop index if exists "public"."analysis_pkey";
|
|
|
|
drop index if exists "public"."analysis_response_element_pkey";
|
|
|
|
drop index if exists "public"."analysis_response_pkey";
|
|
|
|
drop index if exists "public"."analysis_responses_order_number_key";
|
|
|
|
drop index if exists "public"."billing_customers_account_id_customer_id_provider_key";
|
|
|
|
drop index if exists "public"."billing_customers_pkey";
|
|
|
|
drop index if exists "public"."codes_pkey";
|
|
|
|
drop index if exists "public"."connected_online_providers_id_key";
|
|
|
|
drop index if exists "public"."connected_online_providers_pkey";
|
|
|
|
drop index if exists "public"."connected_online_reservation_booking_code_key";
|
|
|
|
drop index if exists "public"."connected_online_reservation_pkey";
|
|
|
|
drop index if exists "public"."connected_online_services_id_key";
|
|
|
|
drop index if exists "public"."connected_online_services_pkey";
|
|
|
|
drop index if exists "public"."idx_nonces_status";
|
|
|
|
drop index if exists "public"."idx_notifications_account_dismissed";
|
|
|
|
drop index if exists "public"."invitations_email_account_id_key";
|
|
|
|
drop index if exists "public"."invitations_invite_token_key";
|
|
|
|
drop index if exists "public"."invitations_pkey";
|
|
|
|
drop index if exists "public"."ix_accounts_is_personal_account";
|
|
|
|
drop index if exists "public"."ix_accounts_memberships_account_id";
|
|
|
|
drop index if exists "public"."ix_accounts_memberships_account_role";
|
|
|
|
drop index if exists "public"."ix_accounts_memberships_user_id";
|
|
|
|
drop index if exists "public"."ix_accounts_primary_owner_user_id";
|
|
|
|
drop index if exists "public"."ix_billing_customers_account_id";
|
|
|
|
drop index if exists "public"."ix_invitations_account_id";
|
|
|
|
drop index if exists "public"."ix_order_items_order_id";
|
|
|
|
drop index if exists "public"."ix_orders_account_id";
|
|
|
|
drop index if exists "public"."ix_role_permissions_role";
|
|
|
|
drop index if exists "public"."ix_subscription_items_subscription_id";
|
|
|
|
drop index if exists "public"."ix_subscriptions_account_id";
|
|
|
|
drop index if exists "public"."medreport_product_groups_name_key";
|
|
|
|
drop index if exists "public"."medreport_product_groups_pkey";
|
|
|
|
drop index if exists "public"."medreport_products_analyses_analysis_element_id_key";
|
|
|
|
drop index if exists "public"."medreport_products_analyses_analysis_id_key";
|
|
|
|
drop index if exists "public"."medreport_products_analyses_pkey";
|
|
|
|
drop index if exists "public"."medreport_products_connected_online_services_id_key";
|
|
|
|
drop index if exists "public"."medreport_products_connected_online_services_pkey";
|
|
|
|
drop index if exists "public"."medreport_products_name_key";
|
|
|
|
drop index if exists "public"."medreport_products_pkey";
|
|
|
|
drop index if exists "public"."nonces_pkey";
|
|
|
|
drop index if exists "public"."notifications_pkey";
|
|
|
|
drop index if exists "public"."order_items_order_id_product_id_variant_id_key";
|
|
|
|
drop index if exists "public"."order_items_pkey";
|
|
|
|
drop index if exists "public"."orders_pkey";
|
|
|
|
drop index if exists "public"."role_permissions_pkey";
|
|
|
|
drop index if exists "public"."role_permissions_role_permission_key";
|
|
|
|
drop index if exists "public"."roles_hierarchy_level_key";
|
|
|
|
drop index if exists "public"."roles_pkey";
|
|
|
|
drop index if exists "public"."subscription_items_pkey";
|
|
|
|
drop index if exists "public"."subscription_items_subscription_id_product_id_variant_id_key";
|
|
|
|
drop index if exists "public"."subscriptions_pkey";
|
|
|
|
drop index if exists "public"."unique_personal_account";
|
|
|
|
drop function if exists "public"."accept_invitation"(token text, user_id uuid);
|
|
|
|
drop function if exists "public"."add_invitations_to_account"(account_slug text, invitations invitation[]);
|
|
|
|
drop function if exists "public"."can_action_account_member"(target_team_account_id uuid, target_user_id uuid);
|
|
|
|
drop function if exists "public"."check_tied_to_analysis_item"();
|
|
|
|
drop function if exists "public"."check_tied_to_connected_online"();
|
|
|
|
drop function if exists "public"."create_invitation"(account_id uuid, email text, role character varying);
|
|
|
|
drop function if exists "public"."create_nonce"(p_user_id uuid, p_purpose text, p_expires_in_seconds integer, p_metadata jsonb, p_scopes text[], p_revoke_previous boolean);
|
|
|
|
drop function if exists "public"."create_team_account"(account_name text);
|
|
|
|
drop function if exists "public"."get_account_invitations"(account_slug text);
|
|
|
|
drop function if exists "public"."get_account_members"(account_slug text);
|
|
|
|
drop function if exists "public"."get_config"();
|
|
|
|
drop function if exists "public"."get_nonce_status"(p_id uuid);
|
|
|
|
drop function if exists "public"."get_upper_system_role"();
|
|
|
|
drop function if exists "public"."has_active_subscription"(target_account_id uuid);
|
|
|
|
drop function if exists "public"."has_more_elevated_role"(target_user_id uuid, target_account_id uuid, role_name character varying);
|
|
|
|
-- drop function if exists "public"."has_permission"(user_id uuid, account_id uuid, permission_name app_permissions);
|
|
|
|
drop function if exists "public"."has_personal_code"(account_id uuid);
|
|
|
|
-- drop function if exists "public"."has_role_on_account"(account_id uuid, account_role character varying);
|
|
|
|
drop function if exists "public"."has_same_role_hierarchy_level"(target_user_id uuid, target_account_id uuid, role_name character varying);
|
|
|
|
drop function if exists "public"."is_aal2"();
|
|
|
|
drop function if exists "public"."is_account_owner"(account_id uuid);
|
|
|
|
drop function if exists "public"."is_account_team_member"(target_account_id uuid);
|
|
|
|
drop function if exists "public"."is_mfa_compliant"();
|
|
|
|
drop function if exists "public"."is_set"(field_name text);
|
|
|
|
drop function if exists "public"."is_super_admin"();
|
|
|
|
drop function if exists "public"."is_team_member"(account_id uuid, user_id uuid);
|
|
|
|
drop function if exists "public"."revoke_nonce"(p_id uuid, p_reason text);
|
|
|
|
drop function if exists "public"."team_account_workspace"(account_slug text);
|
|
|
|
drop function if exists "public"."transfer_team_account_ownership"(target_account_id uuid, new_owner_id uuid);
|
|
|
|
drop function if exists "public"."trigger_set_timestamps"();
|
|
|
|
drop function if exists "public"."trigger_set_user_tracking"();
|
|
|
|
drop function if exists "public"."update_account"(p_name character varying, p_last_name text, p_personal_code text, p_phone text, p_city text, p_has_consent_personal_data boolean, p_uid uuid);
|
|
|
|
drop function if exists "public"."upsert_order"(target_account_id uuid, target_customer_id character varying, target_order_id text, status payment_status, billing_provider billing_provider, total_amount numeric, currency character varying, line_items jsonb);
|
|
|
|
drop function if exists "public"."upsert_subscription"(target_account_id uuid, target_customer_id character varying, target_subscription_id text, active boolean, status subscription_status, billing_provider billing_provider, cancel_at_period_end boolean, currency character varying, period_starts_at timestamp with time zone, period_ends_at timestamp with time zone, line_items jsonb, trial_starts_at timestamp with time zone, trial_ends_at timestamp with time zone);
|
|
|
|
alter table "medreport"."subscriptions" enable row level security;
|
|
|
|
alter sequence "medreport"."billing_customers_id_seq" owned by "medreport"."billing_customers"."id";
|
|
|
|
alter sequence "medreport"."invitations_id_seq" owned by "medreport"."invitations"."id";
|
|
|
|
CREATE UNIQUE INDEX account_params_pkey ON medreport.account_params USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX accounts_email_key ON medreport.accounts USING btree (email);
|
|
|
|
CREATE UNIQUE INDEX accounts_memberships_pkey ON medreport.accounts_memberships USING btree (user_id, account_id);
|
|
|
|
CREATE UNIQUE INDEX accounts_pkey ON medreport.accounts USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX accounts_slug_key ON medreport.accounts USING btree (slug);
|
|
|
|
CREATE UNIQUE INDEX analyses_analysis_id_original_key ON medreport.analyses USING btree (analysis_id_original);
|
|
|
|
CREATE UNIQUE INDEX analysis_elements_analysis_id_original_key ON medreport.analysis_elements USING btree (analysis_id_original);
|
|
|
|
CREATE UNIQUE INDEX analysis_elements_pkey ON medreport.analysis_elements USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX analysis_group_original_id_key ON medreport.analysis_groups USING btree (original_id);
|
|
|
|
CREATE UNIQUE INDEX analysis_group_pkey ON medreport.analysis_groups USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX analysis_orders_pkey ON medreport.analysis_orders USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX analysis_pkey ON medreport.analyses USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX analysis_response_element_pkey ON medreport.analysis_response_elements USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX analysis_response_pkey ON medreport.analysis_responses USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX analysis_responses_order_number_key ON medreport.analysis_responses USING btree (order_number);
|
|
|
|
CREATE UNIQUE INDEX billing_customers_account_id_customer_id_provider_key ON medreport.billing_customers USING btree (account_id, customer_id, provider);
|
|
|
|
CREATE UNIQUE INDEX billing_customers_pkey ON medreport.billing_customers USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX codes_pkey ON medreport.codes USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX connected_online_providers_id_key ON medreport.connected_online_providers USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX connected_online_providers_pkey ON medreport.connected_online_providers USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX connected_online_reservation_booking_code_key ON medreport.connected_online_reservation USING btree (booking_code);
|
|
|
|
CREATE UNIQUE INDEX connected_online_reservation_pkey ON medreport.connected_online_reservation USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX connected_online_services_id_key ON medreport.connected_online_services USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX connected_online_services_pkey ON medreport.connected_online_services USING btree (id);
|
|
|
|
CREATE INDEX idx_nonces_status ON medreport.nonces USING btree (client_token, user_id, purpose, expires_at) WHERE ((used_at IS NULL) AND (revoked = false));
|
|
|
|
CREATE INDEX idx_notifications_account_dismissed ON medreport.notifications USING btree (account_id, dismissed, expires_at);
|
|
|
|
CREATE UNIQUE INDEX invitations_email_account_id_key ON medreport.invitations USING btree (email, account_id);
|
|
|
|
CREATE UNIQUE INDEX invitations_invite_token_key ON medreport.invitations USING btree (invite_token);
|
|
|
|
CREATE UNIQUE INDEX invitations_pkey ON medreport.invitations USING btree (id);
|
|
|
|
CREATE INDEX ix_accounts_is_personal_account ON medreport.accounts USING btree (is_personal_account);
|
|
|
|
CREATE INDEX ix_accounts_memberships_account_id ON medreport.accounts_memberships USING btree (account_id);
|
|
|
|
CREATE INDEX ix_accounts_memberships_account_role ON medreport.accounts_memberships USING btree (account_role);
|
|
|
|
CREATE INDEX ix_accounts_memberships_user_id ON medreport.accounts_memberships USING btree (user_id);
|
|
|
|
CREATE INDEX ix_accounts_primary_owner_user_id ON medreport.accounts USING btree (primary_owner_user_id);
|
|
|
|
CREATE INDEX ix_billing_customers_account_id ON medreport.billing_customers USING btree (account_id);
|
|
|
|
CREATE INDEX ix_invitations_account_id ON medreport.invitations USING btree (account_id);
|
|
|
|
CREATE INDEX ix_order_items_order_id ON medreport.order_items USING btree (order_id);
|
|
|
|
CREATE INDEX ix_orders_account_id ON medreport.orders USING btree (account_id);
|
|
|
|
CREATE INDEX ix_role_permissions_role ON medreport.role_permissions USING btree (role);
|
|
|
|
CREATE INDEX ix_subscription_items_subscription_id ON medreport.subscription_items USING btree (subscription_id);
|
|
|
|
CREATE INDEX ix_subscriptions_account_id ON medreport.subscriptions USING btree (account_id);
|
|
|
|
CREATE UNIQUE INDEX medreport_product_groups_name_key ON medreport.medreport_product_groups USING btree (name);
|
|
|
|
CREATE UNIQUE INDEX medreport_product_groups_pkey ON medreport.medreport_product_groups USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX medreport_products_analyses_analysis_element_id_key ON medreport.medreport_products_analyses_relations USING btree (analysis_element_id);
|
|
|
|
CREATE UNIQUE INDEX medreport_products_analyses_analysis_id_key ON medreport.medreport_products_analyses_relations USING btree (analysis_id);
|
|
|
|
CREATE UNIQUE INDEX medreport_products_analyses_pkey ON medreport.medreport_products_analyses_relations USING btree (product_id);
|
|
|
|
CREATE UNIQUE INDEX medreport_products_connected_online_services_id_key ON medreport.medreport_products_external_services_relations USING btree (connected_online_service_id);
|
|
|
|
CREATE UNIQUE INDEX medreport_products_connected_online_services_pkey ON medreport.medreport_products_external_services_relations USING btree (connected_online_service_id);
|
|
|
|
CREATE UNIQUE INDEX medreport_products_name_key ON medreport.medreport_products USING btree (name);
|
|
|
|
CREATE UNIQUE INDEX medreport_products_pkey ON medreport.medreport_products USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX nonces_pkey ON medreport.nonces USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX notifications_pkey ON medreport.notifications USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX order_items_order_id_product_id_variant_id_key ON medreport.order_items USING btree (order_id, product_id, variant_id);
|
|
|
|
CREATE UNIQUE INDEX order_items_pkey ON medreport.order_items USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX orders_pkey ON medreport.orders USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX role_permissions_pkey ON medreport.role_permissions USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX role_permissions_role_permission_key ON medreport.role_permissions USING btree (role, permission);
|
|
|
|
CREATE UNIQUE INDEX roles_hierarchy_level_key ON medreport.roles USING btree (hierarchy_level);
|
|
|
|
CREATE UNIQUE INDEX roles_pkey ON medreport.roles USING btree (name);
|
|
|
|
CREATE UNIQUE INDEX subscription_items_pkey ON medreport.subscription_items USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX subscription_items_subscription_id_product_id_variant_id_key ON medreport.subscription_items USING btree (subscription_id, product_id, variant_id);
|
|
|
|
CREATE UNIQUE INDEX subscriptions_pkey ON medreport.subscriptions USING btree (id);
|
|
|
|
CREATE UNIQUE INDEX unique_personal_account ON medreport.accounts USING btree (primary_owner_user_id) WHERE (is_personal_account = true);
|
|
|
|
alter table "medreport"."account_params" add constraint "account_params_pkey" PRIMARY KEY using index "account_params_pkey";
|
|
|
|
alter table "medreport"."accounts" add constraint "accounts_pkey" PRIMARY KEY using index "accounts_pkey";
|
|
|
|
alter table "medreport"."accounts_memberships" add constraint "accounts_memberships_pkey" PRIMARY KEY using index "accounts_memberships_pkey";
|
|
|
|
alter table "medreport"."analyses" add constraint "analysis_pkey" PRIMARY KEY using index "analysis_pkey";
|
|
|
|
alter table "medreport"."analysis_elements" add constraint "analysis_elements_pkey" PRIMARY KEY using index "analysis_elements_pkey";
|
|
|
|
alter table "medreport"."analysis_groups" add constraint "analysis_group_pkey" PRIMARY KEY using index "analysis_group_pkey";
|
|
|
|
alter table "medreport"."analysis_orders" add constraint "analysis_orders_pkey" PRIMARY KEY using index "analysis_orders_pkey";
|
|
|
|
alter table "medreport"."analysis_response_elements" add constraint "analysis_response_element_pkey" PRIMARY KEY using index "analysis_response_element_pkey";
|
|
|
|
alter table "medreport"."analysis_responses" add constraint "analysis_response_pkey" PRIMARY KEY using index "analysis_response_pkey";
|
|
|
|
alter table "medreport"."billing_customers" add constraint "billing_customers_pkey" PRIMARY KEY using index "billing_customers_pkey";
|
|
|
|
alter table "medreport"."codes" add constraint "codes_pkey" PRIMARY KEY using index "codes_pkey";
|
|
|
|
alter table "medreport"."connected_online_providers" add constraint "connected_online_providers_pkey" PRIMARY KEY using index "connected_online_providers_pkey";
|
|
|
|
alter table "medreport"."connected_online_reservation" add constraint "connected_online_reservation_pkey" PRIMARY KEY using index "connected_online_reservation_pkey";
|
|
|
|
alter table "medreport"."connected_online_services" add constraint "connected_online_services_pkey" PRIMARY KEY using index "connected_online_services_pkey";
|
|
|
|
alter table "medreport"."invitations" add constraint "invitations_pkey" PRIMARY KEY using index "invitations_pkey";
|
|
|
|
alter table "medreport"."medreport_product_groups" add constraint "medreport_product_groups_pkey" PRIMARY KEY using index "medreport_product_groups_pkey";
|
|
|
|
alter table "medreport"."medreport_products" add constraint "medreport_products_pkey" PRIMARY KEY using index "medreport_products_pkey";
|
|
|
|
alter table "medreport"."medreport_products_analyses_relations" add constraint "medreport_products_analyses_pkey" PRIMARY KEY using index "medreport_products_analyses_pkey";
|
|
|
|
alter table "medreport"."medreport_products_external_services_relations" add constraint "medreport_products_connected_online_services_pkey" PRIMARY KEY using index "medreport_products_connected_online_services_pkey";
|
|
|
|
alter table "medreport"."nonces" add constraint "nonces_pkey" PRIMARY KEY using index "nonces_pkey";
|
|
|
|
alter table "medreport"."notifications" add constraint "notifications_pkey" PRIMARY KEY using index "notifications_pkey";
|
|
|
|
alter table "medreport"."order_items" add constraint "order_items_pkey" PRIMARY KEY using index "order_items_pkey";
|
|
|
|
alter table "medreport"."orders" add constraint "orders_pkey" PRIMARY KEY using index "orders_pkey";
|
|
|
|
alter table "medreport"."role_permissions" add constraint "role_permissions_pkey" PRIMARY KEY using index "role_permissions_pkey";
|
|
|
|
alter table "medreport"."roles" add constraint "roles_pkey" PRIMARY KEY using index "roles_pkey";
|
|
|
|
alter table "medreport"."subscription_items" add constraint "subscription_items_pkey" PRIMARY KEY using index "subscription_items_pkey";
|
|
|
|
alter table "medreport"."subscriptions" add constraint "subscriptions_pkey" PRIMARY KEY using index "subscriptions_pkey";
|
|
|
|
alter table "medreport"."accounts" add constraint "accounts_created_by_fkey" FOREIGN KEY (created_by) REFERENCES auth.users(id) not valid;
|
|
|
|
alter table "medreport"."accounts" validate constraint "accounts_created_by_fkey";
|
|
|
|
alter table "medreport"."accounts" add constraint "accounts_email_key" UNIQUE using index "accounts_email_key";
|
|
|
|
alter table "medreport"."accounts" add constraint "accounts_primary_owner_user_id_fkey" FOREIGN KEY (primary_owner_user_id) REFERENCES auth.users(id) ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."accounts" validate constraint "accounts_primary_owner_user_id_fkey";
|
|
|
|
alter table "medreport"."accounts" add constraint "accounts_slug_key" UNIQUE using index "accounts_slug_key";
|
|
|
|
alter table "medreport"."accounts" add constraint "accounts_slug_null_if_personal_account_true" CHECK ((((is_personal_account = true) AND (slug IS NULL)) OR ((is_personal_account = false) AND (slug IS NOT NULL)))) not valid;
|
|
|
|
alter table "medreport"."accounts" validate constraint "accounts_slug_null_if_personal_account_true";
|
|
|
|
alter table "medreport"."accounts" add constraint "accounts_updated_by_fkey" FOREIGN KEY (updated_by) REFERENCES auth.users(id) not valid;
|
|
|
|
alter table "medreport"."accounts" validate constraint "accounts_updated_by_fkey";
|
|
|
|
alter table "medreport"."accounts_memberships" add constraint "accounts_memberships_account_id_fkey" FOREIGN KEY (account_id) REFERENCES medreport.accounts(id) ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."accounts_memberships" validate constraint "accounts_memberships_account_id_fkey";
|
|
|
|
alter table "medreport"."accounts_memberships" add constraint "accounts_memberships_account_role_fkey" FOREIGN KEY (account_role) REFERENCES medreport.roles(name) not valid;
|
|
|
|
alter table "medreport"."accounts_memberships" validate constraint "accounts_memberships_account_role_fkey";
|
|
|
|
alter table "medreport"."accounts_memberships" add constraint "accounts_memberships_created_by_fkey" FOREIGN KEY (created_by) REFERENCES auth.users(id) not valid;
|
|
|
|
alter table "medreport"."accounts_memberships" validate constraint "accounts_memberships_created_by_fkey";
|
|
|
|
alter table "medreport"."accounts_memberships" add constraint "accounts_memberships_updated_by_fkey" FOREIGN KEY (updated_by) REFERENCES auth.users(id) not valid;
|
|
|
|
alter table "medreport"."accounts_memberships" validate constraint "accounts_memberships_updated_by_fkey";
|
|
|
|
alter table "medreport"."accounts_memberships" add constraint "accounts_memberships_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."accounts_memberships" validate constraint "accounts_memberships_user_id_fkey";
|
|
|
|
alter table "medreport"."analyses" add constraint "analyses_analysis_id_original_key" UNIQUE using index "analyses_analysis_id_original_key";
|
|
|
|
alter table "medreport"."analyses" add constraint "analyses_parent_analysis_element_id_fkey" FOREIGN KEY (parent_analysis_element_id) REFERENCES medreport.analysis_elements(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."analyses" validate constraint "analyses_parent_analysis_element_id_fkey";
|
|
|
|
alter table "medreport"."analysis_elements" add constraint "analysis_elements_analysis_id_original_key" UNIQUE using index "analysis_elements_analysis_id_original_key";
|
|
|
|
alter table "medreport"."analysis_elements" add constraint "analysis_elements_parent_analysis_group_id_fkey" FOREIGN KEY (parent_analysis_group_id) REFERENCES medreport.analysis_groups(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."analysis_elements" validate constraint "analysis_elements_parent_analysis_group_id_fkey";
|
|
|
|
alter table "medreport"."analysis_groups" add constraint "analysis_group_original_id_key" UNIQUE using index "analysis_group_original_id_key";
|
|
|
|
alter table "medreport"."analysis_orders" add constraint "analysis_orders_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth.users(id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID not valid;
|
|
|
|
alter table "medreport"."analysis_orders" validate constraint "analysis_orders_user_id_fkey";
|
|
|
|
alter table "medreport"."analysis_response_elements" add constraint "analysis_response_element_analysis_response_id_fkey" FOREIGN KEY (analysis_response_id) REFERENCES medreport.analysis_responses(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."analysis_response_elements" validate constraint "analysis_response_element_analysis_response_id_fkey";
|
|
|
|
alter table "medreport"."analysis_responses" add constraint "analysis_response_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth.users(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."analysis_responses" validate constraint "analysis_response_user_id_fkey";
|
|
|
|
alter table "medreport"."analysis_responses" add constraint "analysis_responses_analysis_order_id_fkey" FOREIGN KEY (analysis_order_id) REFERENCES medreport.analysis_orders(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."analysis_responses" validate constraint "analysis_responses_analysis_order_id_fkey";
|
|
|
|
alter table "medreport"."analysis_responses" add constraint "analysis_responses_order_number_key" UNIQUE using index "analysis_responses_order_number_key";
|
|
|
|
alter table "medreport"."billing_customers" add constraint "billing_customers_account_id_customer_id_provider_key" UNIQUE using index "billing_customers_account_id_customer_id_provider_key";
|
|
|
|
alter table "medreport"."billing_customers" add constraint "billing_customers_account_id_fkey" FOREIGN KEY (account_id) REFERENCES medreport.accounts(id) ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."billing_customers" validate constraint "billing_customers_account_id_fkey";
|
|
|
|
alter table "medreport"."codes" add constraint "codes_analysis_element_id_fkey" FOREIGN KEY (analysis_element_id) REFERENCES medreport.analysis_elements(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."codes" validate constraint "codes_analysis_element_id_fkey";
|
|
|
|
alter table "medreport"."codes" add constraint "codes_analysis_group_id_fkey" FOREIGN KEY (analysis_group_id) REFERENCES medreport.analysis_groups(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."codes" validate constraint "codes_analysis_group_id_fkey";
|
|
|
|
alter table "medreport"."codes" add constraint "codes_analysis_id_fkey" FOREIGN KEY (analysis_id) REFERENCES medreport.analyses(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."codes" validate constraint "codes_analysis_id_fkey";
|
|
|
|
alter table "medreport"."connected_online_providers" add constraint "connected_online_providers_id_key" UNIQUE using index "connected_online_providers_id_key";
|
|
|
|
alter table "medreport"."connected_online_reservation" add constraint "connected_online_reservation_booking_code_key" UNIQUE using index "connected_online_reservation_booking_code_key";
|
|
|
|
alter table "medreport"."connected_online_reservation" add constraint "connected_online_reservation_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth.users(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."connected_online_reservation" validate constraint "connected_online_reservation_user_id_fkey";
|
|
|
|
alter table "medreport"."connected_online_services" add constraint "connected_online_services_clinic_id_fkey" FOREIGN KEY (clinic_id) REFERENCES medreport.connected_online_providers(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."connected_online_services" validate constraint "connected_online_services_clinic_id_fkey";
|
|
|
|
alter table "medreport"."connected_online_services" add constraint "connected_online_services_id_key" UNIQUE using index "connected_online_services_id_key";
|
|
|
|
alter table "medreport"."invitations" add constraint "invitations_account_id_fkey" FOREIGN KEY (account_id) REFERENCES medreport.accounts(id) ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."invitations" validate constraint "invitations_account_id_fkey";
|
|
|
|
alter table "medreport"."invitations" add constraint "invitations_email_account_id_key" UNIQUE using index "invitations_email_account_id_key";
|
|
|
|
alter table "medreport"."invitations" add constraint "invitations_invite_token_key" UNIQUE using index "invitations_invite_token_key";
|
|
|
|
alter table "medreport"."invitations" add constraint "invitations_invited_by_fkey" FOREIGN KEY (invited_by) REFERENCES auth.users(id) ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."invitations" validate constraint "invitations_invited_by_fkey";
|
|
|
|
alter table "medreport"."invitations" add constraint "invitations_role_fkey" FOREIGN KEY (role) REFERENCES medreport.roles(name) not valid;
|
|
|
|
alter table "medreport"."invitations" validate constraint "invitations_role_fkey";
|
|
|
|
alter table "medreport"."medreport_product_groups" add constraint "medreport_product_groups_name_key" UNIQUE using index "medreport_product_groups_name_key";
|
|
|
|
alter table "medreport"."medreport_products" add constraint "medreport_products_name_key" UNIQUE using index "medreport_products_name_key";
|
|
|
|
alter table "medreport"."medreport_products" add constraint "medreport_products_product_groups_id_fkey" FOREIGN KEY (product_group_id) REFERENCES medreport.medreport_product_groups(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."medreport_products" validate constraint "medreport_products_product_groups_id_fkey";
|
|
|
|
alter table "medreport"."medreport_products_analyses_relations" add constraint "medreport_products_analyses_analysis_element_id_fkey" FOREIGN KEY (analysis_element_id) REFERENCES medreport.analysis_elements(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."medreport_products_analyses_relations" validate constraint "medreport_products_analyses_analysis_element_id_fkey";
|
|
|
|
alter table "medreport"."medreport_products_analyses_relations" add constraint "medreport_products_analyses_analysis_element_id_key" UNIQUE using index "medreport_products_analyses_analysis_element_id_key";
|
|
|
|
alter table "medreport"."medreport_products_analyses_relations" add constraint "medreport_products_analyses_analysis_id_fkey" FOREIGN KEY (analysis_id) REFERENCES medreport.analyses(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."medreport_products_analyses_relations" validate constraint "medreport_products_analyses_analysis_id_fkey";
|
|
|
|
alter table "medreport"."medreport_products_analyses_relations" add constraint "medreport_products_analyses_analysis_id_key" UNIQUE using index "medreport_products_analyses_analysis_id_key";
|
|
|
|
alter table "medreport"."medreport_products_analyses_relations" add constraint "medreport_products_analyses_product_id_fkey" FOREIGN KEY (product_id) REFERENCES medreport.medreport_products(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."medreport_products_analyses_relations" validate constraint "medreport_products_analyses_product_id_fkey";
|
|
|
|
alter table "medreport"."medreport_products_analyses_relations" add constraint "product_can_be_tied_to_only_one_analysis_item" CHECK (((analysis_id IS NULL) OR (analysis_element_id IS NULL))) not valid;
|
|
|
|
alter table "medreport"."medreport_products_analyses_relations" validate constraint "product_can_be_tied_to_only_one_analysis_item";
|
|
|
|
alter table "medreport"."medreport_products_analyses_relations" add constraint "product_can_be_tied_to_only_one_external_item" CHECK (((analysis_id IS NULL) OR (analysis_element_id IS NULL))) not valid;
|
|
|
|
alter table "medreport"."medreport_products_analyses_relations" validate constraint "product_can_be_tied_to_only_one_external_item";
|
|
|
|
alter table "medreport"."medreport_products_external_services_relations" add constraint "medreport_products_connected_online_services_id_fkey" FOREIGN KEY (connected_online_service_id) REFERENCES medreport.connected_online_services(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."medreport_products_external_services_relations" validate constraint "medreport_products_connected_online_services_id_fkey";
|
|
|
|
alter table "medreport"."medreport_products_external_services_relations" add constraint "medreport_products_connected_online_services_id_key" UNIQUE using index "medreport_products_connected_online_services_id_key";
|
|
|
|
alter table "medreport"."medreport_products_external_services_relations" add constraint "medreport_products_connected_online_services_product_id_fkey" FOREIGN KEY (product_id) REFERENCES medreport.medreport_products(id) ON UPDATE CASCADE ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."medreport_products_external_services_relations" validate constraint "medreport_products_connected_online_services_product_id_fkey";
|
|
|
|
alter table "medreport"."nonces" add constraint "nonces_user_id_fkey" FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."nonces" validate constraint "nonces_user_id_fkey";
|
|
|
|
alter table "medreport"."notifications" add constraint "notifications_account_id_fkey" FOREIGN KEY (account_id) REFERENCES medreport.accounts(id) ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."notifications" validate constraint "notifications_account_id_fkey";
|
|
|
|
alter table "medreport"."order_items" add constraint "order_items_order_id_fkey" FOREIGN KEY (order_id) REFERENCES medreport.orders(id) ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."order_items" validate constraint "order_items_order_id_fkey";
|
|
|
|
alter table "medreport"."order_items" add constraint "order_items_order_id_product_id_variant_id_key" UNIQUE using index "order_items_order_id_product_id_variant_id_key";
|
|
|
|
alter table "medreport"."orders" add constraint "orders_account_id_fkey" FOREIGN KEY (account_id) REFERENCES medreport.accounts(id) ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."orders" validate constraint "orders_account_id_fkey";
|
|
|
|
alter table "medreport"."orders" add constraint "orders_billing_customer_id_fkey" FOREIGN KEY (billing_customer_id) REFERENCES medreport.billing_customers(id) ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."orders" validate constraint "orders_billing_customer_id_fkey";
|
|
|
|
alter table "medreport"."role_permissions" add constraint "role_permissions_role_fkey" FOREIGN KEY (role) REFERENCES medreport.roles(name) not valid;
|
|
|
|
alter table "medreport"."role_permissions" validate constraint "role_permissions_role_fkey";
|
|
|
|
alter table "medreport"."role_permissions" add constraint "role_permissions_role_permission_key" UNIQUE using index "role_permissions_role_permission_key";
|
|
|
|
alter table "medreport"."roles" add constraint "roles_hierarchy_level_check" CHECK ((hierarchy_level > 0)) not valid;
|
|
|
|
alter table "medreport"."roles" validate constraint "roles_hierarchy_level_check";
|
|
|
|
alter table "medreport"."roles" add constraint "roles_hierarchy_level_key" UNIQUE using index "roles_hierarchy_level_key";
|
|
|
|
alter table "medreport"."subscription_items" add constraint "subscription_items_interval_count_check" CHECK ((interval_count > 0)) not valid;
|
|
|
|
alter table "medreport"."subscription_items" validate constraint "subscription_items_interval_count_check";
|
|
|
|
alter table "medreport"."subscription_items" add constraint "subscription_items_subscription_id_fkey" FOREIGN KEY (subscription_id) REFERENCES medreport.subscriptions(id) ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."subscription_items" validate constraint "subscription_items_subscription_id_fkey";
|
|
|
|
alter table "medreport"."subscription_items" add constraint "subscription_items_subscription_id_product_id_variant_id_key" UNIQUE using index "subscription_items_subscription_id_product_id_variant_id_key";
|
|
|
|
alter table "medreport"."subscriptions" add constraint "subscriptions_account_id_fkey" FOREIGN KEY (account_id) REFERENCES medreport.accounts(id) ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."subscriptions" validate constraint "subscriptions_account_id_fkey";
|
|
|
|
alter table "medreport"."subscriptions" add constraint "subscriptions_billing_customer_id_fkey" FOREIGN KEY (billing_customer_id) REFERENCES medreport.billing_customers(id) ON DELETE CASCADE not valid;
|
|
|
|
alter table "medreport"."subscriptions" validate constraint "subscriptions_billing_customer_id_fkey";
|
|
|
|
set check_function_bodies = off;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.accept_invitation(token text, user_id uuid)
|
|
RETURNS uuid
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$declare
|
|
target_account_id uuid;
|
|
target_role varchar(50);
|
|
begin
|
|
select
|
|
account_id,
|
|
role into target_account_id,
|
|
target_role
|
|
from
|
|
medreport.invitations
|
|
where
|
|
invite_token = token
|
|
and expires_at > now();
|
|
|
|
if not found then
|
|
raise exception 'Invalid or expired invitation token';
|
|
end if;
|
|
|
|
insert into medreport.accounts_memberships(
|
|
user_id,
|
|
account_id,
|
|
account_role)
|
|
values (
|
|
accept_invitation.user_id,
|
|
target_account_id,
|
|
target_role);
|
|
|
|
delete from medreport.invitations
|
|
where invite_token = token;
|
|
|
|
return target_account_id;
|
|
end;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.add_invitations_to_account(account_slug text, invitations invitation[])
|
|
RETURNS medreport.invitations[]
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$declare
|
|
new_invitation medreport.invitations;
|
|
all_invitations medreport.invitations[] := array[]::medreport.invitations[];
|
|
invite_token text;
|
|
email text;
|
|
role varchar(50);
|
|
begin
|
|
FOREACH email,
|
|
role in array invitations loop
|
|
invite_token := extensions.uuid_generate_v4();
|
|
|
|
insert into medreport.invitations(
|
|
email,
|
|
account_id,
|
|
invited_by,
|
|
role,
|
|
invite_token)
|
|
values (
|
|
email,
|
|
(
|
|
select
|
|
id
|
|
from
|
|
medreport.accounts
|
|
where
|
|
slug = account_slug), auth.uid(), role, invite_token)
|
|
returning
|
|
* into new_invitation;
|
|
|
|
all_invitations := array_append(all_invitations, new_invitation);
|
|
|
|
end loop;
|
|
|
|
return all_invitations;
|
|
|
|
end;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.can_action_account_member(target_team_account_id uuid, target_user_id uuid)
|
|
RETURNS boolean
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$declare
|
|
permission_granted boolean;
|
|
target_user_hierarchy_level int;
|
|
current_user_hierarchy_level int;
|
|
is_account_owner boolean;
|
|
target_user_role varchar(50);
|
|
begin
|
|
if target_user_id = auth.uid() then
|
|
raise exception 'You cannot update your own account membership with this function';
|
|
end if;
|
|
|
|
-- an account owner can action any member of the account
|
|
if medreport.is_account_owner(target_team_account_id) then
|
|
return true;
|
|
end if;
|
|
|
|
-- check the target user is the primary owner of the account
|
|
select
|
|
exists (
|
|
select
|
|
1
|
|
from
|
|
medreport.accounts
|
|
where
|
|
id = target_team_account_id
|
|
and primary_owner_user_id = target_user_id) into is_account_owner;
|
|
|
|
if is_account_owner then
|
|
raise exception 'The primary account owner cannot be actioned';
|
|
end if;
|
|
|
|
-- validate the auth user has the required permission on the account
|
|
-- to manage members of the account
|
|
select
|
|
medreport.has_permission(auth.uid(), target_team_account_id,
|
|
'members.manage'::medreport.app_permissions) into
|
|
permission_granted;
|
|
|
|
-- if the user does not have the required permission, raise an exception
|
|
if not permission_granted then
|
|
raise exception 'You do not have permission to action a member from this account';
|
|
end if;
|
|
|
|
-- get the role of the target user
|
|
select
|
|
am.account_role,
|
|
r.hierarchy_level
|
|
from
|
|
medreport.accounts_memberships as am
|
|
join
|
|
medreport.roles as r on am.account_role = r.name
|
|
where
|
|
am.account_id = target_team_account_id
|
|
and am.user_id = target_user_id
|
|
into target_user_role, target_user_hierarchy_level;
|
|
|
|
-- get the hierarchy level of the current user
|
|
select
|
|
r.hierarchy_level into current_user_hierarchy_level
|
|
from
|
|
medreport.roles as r
|
|
join
|
|
medreport.accounts_memberships as am on r.name = am.account_role
|
|
where
|
|
am.account_id = target_team_account_id
|
|
and am.user_id = auth.uid();
|
|
|
|
if target_user_role is null then
|
|
raise exception 'The target user does not have a role on the account';
|
|
end if;
|
|
|
|
if current_user_hierarchy_level is null then
|
|
raise exception 'The current user does not have a role on the account';
|
|
end if;
|
|
|
|
-- check the current user has a higher role than the target user
|
|
if current_user_hierarchy_level >= target_user_hierarchy_level then
|
|
raise exception 'You do not have permission to action a member from this account';
|
|
end if;
|
|
|
|
return true;
|
|
|
|
end;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.check_tied_to_analysis_item()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $function$BEGIN
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM medreport_products_analyses_relations
|
|
WHERE product_id = NEW.product_id
|
|
) THEN
|
|
RAISE EXCEPTION 'Value "%" already exists in medreport_products_analyses_relations', NEW.product_id;
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.check_tied_to_connected_online()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
AS $function$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM medreport_products_external_services_relations
|
|
WHERE product_id = NEW.product_id
|
|
) THEN
|
|
RAISE EXCEPTION 'Value "%" already exists in medreport_products_external_services_relations', NEW.product_id;
|
|
END IF;
|
|
|
|
RETURN NEW;
|
|
END;
|
|
$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.create_invitation(account_id uuid, email text, role character varying)
|
|
RETURNS medreport.invitations
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$declare
|
|
new_invitation medreport.invitations;
|
|
invite_token text;
|
|
begin
|
|
invite_token := extensions.uuid_generate_v4();
|
|
|
|
insert into medreport.invitations(
|
|
email,
|
|
account_id,
|
|
invited_by,
|
|
role,
|
|
invite_token)
|
|
values (
|
|
email,
|
|
account_id,
|
|
auth.uid(),
|
|
role,
|
|
invite_token)
|
|
returning
|
|
* into new_invitation;
|
|
|
|
return new_invitation;
|
|
|
|
end;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.create_nonce(p_user_id uuid DEFAULT NULL::uuid, p_purpose text DEFAULT NULL::text, p_expires_in_seconds integer DEFAULT 3600, p_metadata jsonb DEFAULT NULL::jsonb, p_scopes text[] DEFAULT NULL::text[], p_revoke_previous boolean DEFAULT true)
|
|
RETURNS jsonb
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path TO ''
|
|
AS $function$DECLARE
|
|
v_client_token TEXT;
|
|
v_nonce TEXT;
|
|
v_expires_at TIMESTAMPTZ;
|
|
v_id UUID;
|
|
v_plaintext_token TEXT;
|
|
v_revoked_count INTEGER;
|
|
BEGIN
|
|
-- Revoke previous tokens for the same user and purpose if requested
|
|
-- This only applies if a user ID is provided (not for anonymous tokens)
|
|
IF p_revoke_previous = TRUE AND p_user_id IS NOT NULL THEN
|
|
WITH revoked AS (
|
|
UPDATE medreport.nonces
|
|
SET
|
|
revoked = TRUE,
|
|
revoked_reason = 'Superseded by new token with same purpose'
|
|
WHERE
|
|
user_id = p_user_id
|
|
AND purpose = p_purpose
|
|
AND used_at IS NULL
|
|
AND revoked = FALSE
|
|
AND expires_at > NOW()
|
|
RETURNING 1
|
|
)
|
|
SELECT COUNT(*) INTO v_revoked_count FROM revoked;
|
|
END IF;
|
|
|
|
-- Generate a 6-digit token
|
|
v_plaintext_token := (100000 + floor(random() * 900000))::text;
|
|
v_client_token := extensions.crypt(v_plaintext_token, extensions.gen_salt('bf'));
|
|
|
|
-- Still generate a secure nonce for internal use
|
|
v_nonce := encode(extensions.gen_random_bytes(24), 'base64');
|
|
v_nonce := extensions.crypt(v_nonce, extensions.gen_salt('bf'));
|
|
|
|
-- Calculate expiration time
|
|
v_expires_at := NOW() + (p_expires_in_seconds * interval '1 second');
|
|
|
|
-- Insert the new nonce
|
|
INSERT INTO medreport.nonces (
|
|
client_token,
|
|
nonce,
|
|
user_id,
|
|
expires_at,
|
|
metadata,
|
|
purpose,
|
|
scopes
|
|
)
|
|
VALUES (
|
|
v_client_token,
|
|
v_nonce,
|
|
p_user_id,
|
|
v_expires_at,
|
|
COALESCE(p_metadata, '{}'::JSONB),
|
|
p_purpose,
|
|
COALESCE(p_scopes, '{}'::TEXT[])
|
|
)
|
|
RETURNING id INTO v_id;
|
|
|
|
-- Return the token information
|
|
-- Note: returning the plaintext token, not the hash
|
|
RETURN jsonb_build_object(
|
|
'id', v_id,
|
|
'token', v_plaintext_token,
|
|
'expires_at', v_expires_at,
|
|
'revoked_previous_count', COALESCE(v_revoked_count, 0)
|
|
);
|
|
END;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.create_team_account(account_name text)
|
|
RETURNS medreport.accounts
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$declare
|
|
new_account medreport.accounts;
|
|
begin
|
|
if (not medreport.is_set('enable_team_accounts')) then
|
|
raise exception 'Team accounts are not enabled';
|
|
end if;
|
|
|
|
insert into medreport.accounts(
|
|
name,
|
|
is_personal_account)
|
|
values (
|
|
account_name,
|
|
false)
|
|
returning
|
|
* into new_account;
|
|
|
|
return new_account;
|
|
|
|
end;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.get_account_invitations(account_slug text)
|
|
RETURNS TABLE(id integer, email character varying, account_id uuid, invited_by uuid, role character varying, created_at timestamp with time zone, updated_at timestamp with time zone, expires_at timestamp with time zone, inviter_name character varying, inviter_email character varying)
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$begin
|
|
return query
|
|
select
|
|
invitation.id,
|
|
invitation.email,
|
|
invitation.account_id,
|
|
invitation.invited_by,
|
|
invitation.role,
|
|
invitation.created_at,
|
|
invitation.updated_at,
|
|
invitation.expires_at,
|
|
account.name,
|
|
account.email
|
|
from
|
|
medreport.invitations as invitation
|
|
join medreport.accounts as account on invitation.account_id = account.id
|
|
where
|
|
account.slug = account_slug;
|
|
|
|
end;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.get_account_members(account_slug text)
|
|
RETURNS TABLE(id uuid, user_id uuid, account_id uuid, role character varying, role_hierarchy_level integer, primary_owner_user_id uuid, name character varying, email character varying, picture_url character varying, created_at timestamp with time zone, updated_at timestamp with time zone)
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$begin
|
|
return QUERY
|
|
select
|
|
acc.id,
|
|
am.user_id,
|
|
am.account_id,
|
|
am.account_role,
|
|
r.hierarchy_level,
|
|
a.primary_owner_user_id,
|
|
acc.name,
|
|
acc.email,
|
|
acc.picture_url,
|
|
am.created_at,
|
|
am.updated_at
|
|
from
|
|
medreport.accounts_memberships am
|
|
join medreport.accounts a on a.id = am.account_id
|
|
join medreport.accounts acc on acc.id = am.user_id
|
|
join medreport.roles r on r.name = am.account_role
|
|
where
|
|
a.slug = account_slug;
|
|
|
|
end;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.get_config()
|
|
RETURNS json
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$declare
|
|
result record;
|
|
begin
|
|
select
|
|
*
|
|
from
|
|
medreport.config
|
|
limit 1 into result;
|
|
|
|
return row_to_json(result);
|
|
|
|
end;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.get_nonce_status(p_id uuid)
|
|
RETURNS jsonb
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path TO ''
|
|
AS $function$DECLARE
|
|
v_nonce medreport.nonces;
|
|
BEGIN
|
|
SELECT * INTO v_nonce FROM medreport.nonces WHERE id = p_id;
|
|
|
|
IF v_nonce.id IS NULL THEN
|
|
RETURN jsonb_build_object('exists', false);
|
|
END IF;
|
|
|
|
RETURN jsonb_build_object(
|
|
'exists', true,
|
|
'purpose', v_nonce.purpose,
|
|
'user_id', v_nonce.user_id,
|
|
'created_at', v_nonce.created_at,
|
|
'expires_at', v_nonce.expires_at,
|
|
'used_at', v_nonce.used_at,
|
|
'revoked', v_nonce.revoked,
|
|
'revoked_reason', v_nonce.revoked_reason,
|
|
'verification_attempts', v_nonce.verification_attempts,
|
|
'last_verification_at', v_nonce.last_verification_at,
|
|
'last_verification_ip', v_nonce.last_verification_ip,
|
|
'is_valid', (v_nonce.used_at IS NULL AND NOT v_nonce.revoked AND v_nonce.expires_at > NOW())
|
|
);
|
|
END;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.get_upper_system_role()
|
|
RETURNS character varying
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$declare
|
|
role varchar(50);
|
|
begin
|
|
select name from medreport.roles
|
|
where hierarchy_level = 1 into role;
|
|
|
|
return role;
|
|
end;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.has_active_subscription(target_account_id uuid)
|
|
RETURNS boolean
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$begin
|
|
return exists (
|
|
select
|
|
1
|
|
from
|
|
medreport.subscriptions
|
|
where
|
|
account_id = target_account_id
|
|
and active = true);
|
|
|
|
end;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.has_more_elevated_role(target_user_id uuid, target_account_id uuid, role_name character varying)
|
|
RETURNS boolean
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$declare
|
|
declare is_primary_owner boolean;
|
|
user_role_hierarchy_level int;
|
|
target_role_hierarchy_level int;
|
|
begin
|
|
-- Check if the user is the primary owner of the account
|
|
select
|
|
exists (
|
|
select
|
|
1
|
|
from
|
|
medreport.accounts
|
|
where
|
|
id = target_account_id
|
|
and primary_owner_user_id = target_user_id) into is_primary_owner;
|
|
|
|
-- If the user is the primary owner, they have the highest role and can
|
|
-- perform any action
|
|
if is_primary_owner then
|
|
return true;
|
|
end if;
|
|
|
|
-- Get the hierarchy level of the user's role within the account
|
|
select
|
|
hierarchy_level into user_role_hierarchy_level
|
|
from
|
|
medreport.roles
|
|
where
|
|
name =(
|
|
select
|
|
account_role
|
|
from
|
|
medreport.accounts_memberships
|
|
where
|
|
account_id = target_account_id
|
|
and target_user_id = user_id);
|
|
|
|
if user_role_hierarchy_level is null then
|
|
return false;
|
|
end if;
|
|
|
|
-- Get the hierarchy level of the target role
|
|
select
|
|
hierarchy_level into target_role_hierarchy_level
|
|
from
|
|
medreport.roles
|
|
where
|
|
name = role_name;
|
|
|
|
-- If the target role does not exist, the user cannot perform the action
|
|
if target_role_hierarchy_level is null then
|
|
return false;
|
|
end if;
|
|
|
|
-- If the user's role is higher than the target role, they can perform
|
|
-- the action
|
|
return user_role_hierarchy_level < target_role_hierarchy_level;
|
|
|
|
end;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.has_permission(user_id uuid, account_id uuid, permission_name medreport.app_permissions)
|
|
RETURNS boolean
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$
|
|
begin
|
|
return exists(
|
|
select
|
|
1
|
|
from
|
|
public.accounts_memberships
|
|
join public.role_permissions on
|
|
accounts_memberships.account_role =
|
|
role_permissions.role
|
|
where
|
|
accounts_memberships.user_id = has_permission.user_id
|
|
and accounts_memberships.account_id = has_permission.account_id
|
|
and role_permissions.permission = has_permission.permission_name);
|
|
|
|
end;
|
|
|
|
$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.has_personal_code(account_id uuid)
|
|
RETURNS boolean
|
|
LANGUAGE plpgsql
|
|
AS $function$BEGIN
|
|
RETURN EXISTS (
|
|
SELECT 1
|
|
FROM public.accounts
|
|
WHERE id = account_id
|
|
AND personal_code IS NOT NULL
|
|
AND personal_code <> ''
|
|
);
|
|
END;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.has_role_on_account(account_id uuid, account_role character varying DEFAULT NULL::character varying)
|
|
RETURNS boolean
|
|
LANGUAGE sql
|
|
SECURITY DEFINER
|
|
SET search_path TO ''
|
|
AS $function$select
|
|
exists(
|
|
select
|
|
1
|
|
from
|
|
medreport.accounts_memberships membership
|
|
where
|
|
membership.user_id = (select auth.uid())
|
|
and membership.account_id = has_role_on_account.account_id
|
|
and((membership.account_role = has_role_on_account.account_role
|
|
or has_role_on_account.account_role is null)));$function$
|
|
;
|
|
|
|
grant
|
|
execute on function medreport.has_role_on_account (uuid, varchar) to authenticated;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.has_same_role_hierarchy_level(target_user_id uuid, target_account_id uuid, role_name character varying)
|
|
RETURNS boolean
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$declare
|
|
is_primary_owner boolean;
|
|
user_role_hierarchy_level int;
|
|
target_role_hierarchy_level int;
|
|
begin
|
|
-- Check if the user is the primary owner of the account
|
|
select
|
|
exists (
|
|
select
|
|
1
|
|
from
|
|
medreport.accounts
|
|
where
|
|
id = target_account_id
|
|
and primary_owner_user_id = target_user_id) into is_primary_owner;
|
|
|
|
-- If the user is the primary owner, they have the highest role and can perform any action
|
|
if is_primary_owner then
|
|
return true;
|
|
end if;
|
|
|
|
-- Get the hierarchy level of the user's role within the account
|
|
select
|
|
hierarchy_level into user_role_hierarchy_level
|
|
from
|
|
medreport.roles
|
|
where
|
|
name =(
|
|
select
|
|
account_role
|
|
from
|
|
medreport.accounts_memberships
|
|
where
|
|
account_id = target_account_id
|
|
and target_user_id = user_id);
|
|
|
|
-- If the user does not have a role in the account, they cannot perform the action
|
|
if user_role_hierarchy_level is null then
|
|
return false;
|
|
end if;
|
|
|
|
-- Get the hierarchy level of the target role
|
|
select
|
|
hierarchy_level into target_role_hierarchy_level
|
|
from
|
|
medreport.roles
|
|
where
|
|
name = role_name;
|
|
|
|
-- If the target role does not exist, the user cannot perform the action
|
|
if target_role_hierarchy_level is null then
|
|
return false;
|
|
end if;
|
|
|
|
-- check the user's role hierarchy level is the same as the target role
|
|
return user_role_hierarchy_level = target_role_hierarchy_level;
|
|
|
|
end;$function$
|
|
;
|
|
|
|
grant
|
|
execute on function medreport.has_same_role_hierarchy_level (uuid, uuid, varchar) to authenticated,
|
|
service_role;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.is_aal2()
|
|
RETURNS boolean
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$
|
|
declare
|
|
is_aal2 boolean;
|
|
begin
|
|
select auth.jwt() ->> 'aal' = 'aal2' into is_aal2;
|
|
|
|
return coalesce(is_aal2, false);
|
|
end
|
|
$function$
|
|
;
|
|
|
|
grant execute on function medreport.is_aal2() to authenticated;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.is_account_owner(account_id uuid)
|
|
RETURNS boolean
|
|
LANGUAGE sql
|
|
SET search_path TO ''
|
|
AS $function$select
|
|
exists(
|
|
select
|
|
1
|
|
from
|
|
medreport.accounts
|
|
where
|
|
id = is_account_owner.account_id
|
|
and primary_owner_user_id = auth.uid());$function$
|
|
;
|
|
|
|
grant
|
|
execute on function medreport.is_account_owner (uuid) to authenticated,
|
|
service_role;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.is_account_team_member(target_account_id uuid)
|
|
RETURNS boolean
|
|
LANGUAGE sql
|
|
SET search_path TO ''
|
|
AS $function$select exists(
|
|
select 1
|
|
from medreport.accounts_memberships as membership
|
|
where medreport.is_team_member (membership.account_id, target_account_id)
|
|
);$function$
|
|
;
|
|
|
|
grant
|
|
execute on function medreport.is_account_team_member (uuid) to authenticated,
|
|
service_role;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.is_mfa_compliant()
|
|
RETURNS boolean
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path TO ''
|
|
AS $function$begin
|
|
return array[(select auth.jwt()->>'aal')] <@ (
|
|
select
|
|
case
|
|
when count(id) > 0 then array['aal2']
|
|
else array['aal1', 'aal2']
|
|
end as aal
|
|
from auth.mfa_factors
|
|
where ((select auth.uid()) = auth.mfa_factors.user_id) and auth.mfa_factors.status = 'verified'
|
|
);
|
|
end$function$
|
|
;
|
|
|
|
grant execute on function medreport.is_mfa_compliant() to authenticated;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.is_set(field_name text)
|
|
RETURNS boolean
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$declare
|
|
result boolean;
|
|
begin
|
|
execute format('select %I from medreport.config limit 1', field_name) into result;
|
|
|
|
return result;
|
|
|
|
end;$function$
|
|
;
|
|
|
|
grant
|
|
execute on function medreport.is_set (text) to authenticated;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.is_super_admin()
|
|
RETURNS boolean
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$declare
|
|
is_super_admin boolean;
|
|
begin
|
|
if not medreport.is_aal2() then
|
|
return false;
|
|
end if;
|
|
|
|
select (auth.jwt() ->> 'app_metadata')::jsonb ->> 'role' = 'super-admin' into is_super_admin;
|
|
|
|
return coalesce(is_super_admin, false);
|
|
end$function$
|
|
;
|
|
|
|
grant execute on function medreport.is_super_admin() to authenticated;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.is_team_member(account_id uuid, user_id uuid)
|
|
RETURNS boolean
|
|
LANGUAGE sql
|
|
SECURITY DEFINER
|
|
SET search_path TO ''
|
|
AS $function$select
|
|
exists(
|
|
select
|
|
1
|
|
from
|
|
medreport.accounts_memberships membership
|
|
where
|
|
medreport.has_role_on_account(account_id)
|
|
and membership.user_id = is_team_member.user_id
|
|
and membership.account_id = is_team_member.account_id);$function$
|
|
;
|
|
|
|
grant
|
|
execute on function medreport.is_team_member (uuid, uuid) to authenticated,
|
|
service_role;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.revoke_nonce(p_id uuid, p_reason text DEFAULT NULL::text)
|
|
RETURNS boolean
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path TO ''
|
|
AS $function$DECLARE
|
|
v_affected_rows INTEGER;
|
|
BEGIN
|
|
UPDATE public.nonces
|
|
SET
|
|
revoked = TRUE,
|
|
revoked_reason = p_reason
|
|
WHERE
|
|
id = p_id
|
|
AND used_at IS NULL
|
|
AND NOT revoked
|
|
RETURNING 1 INTO v_affected_rows;
|
|
|
|
RETURN v_affected_rows > 0;
|
|
END;$function$
|
|
;
|
|
|
|
grant execute on function medreport.revoke_nonce to service_role;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.team_account_workspace(account_slug text)
|
|
RETURNS TABLE(id uuid, name character varying, picture_url character varying, slug text, role character varying, role_hierarchy_level integer, primary_owner_user_id uuid, subscription_status medreport.subscription_status, permissions medreport.app_permissions[])
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$begin
|
|
return QUERY
|
|
select
|
|
accounts.id,
|
|
accounts.name,
|
|
accounts.picture_url,
|
|
accounts.slug,
|
|
accounts_memberships.account_role,
|
|
roles.hierarchy_level,
|
|
accounts.primary_owner_user_id,
|
|
subscriptions.status,
|
|
array_agg(role_permissions.permission)
|
|
from
|
|
medreport.accounts
|
|
join medreport.accounts_memberships on accounts.id = accounts_memberships.account_id
|
|
left join medreport.subscriptions on accounts.id = subscriptions.account_id
|
|
join medreport.roles on accounts_memberships.account_role = roles.name
|
|
left join medreport.role_permissions on accounts_memberships.account_role = role_permissions.role
|
|
where
|
|
accounts.slug = account_slug
|
|
and medreport.accounts_memberships.user_id = (select auth.uid())
|
|
group by
|
|
accounts.id,
|
|
accounts_memberships.account_role,
|
|
subscriptions.status,
|
|
roles.hierarchy_level;
|
|
end;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.transfer_team_account_ownership(target_account_id uuid, new_owner_id uuid)
|
|
RETURNS void
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$begin
|
|
if current_user not in('service_role') then
|
|
raise exception 'You do not have permission to transfer account ownership';
|
|
end if;
|
|
|
|
-- verify the user is already a member of the account
|
|
if not exists(
|
|
select
|
|
1
|
|
from
|
|
medreport.accounts_memberships
|
|
where
|
|
target_account_id = account_id
|
|
and user_id = new_owner_id) then
|
|
raise exception 'The new owner must be a member of the account';
|
|
end if;
|
|
|
|
-- update the primary owner of the account
|
|
update
|
|
medreport.accounts
|
|
set
|
|
primary_owner_user_id = new_owner_id
|
|
where
|
|
id = target_account_id
|
|
and is_personal_account = false;
|
|
|
|
-- update membership assigning it the hierarchy role
|
|
update
|
|
medreport.accounts_memberships
|
|
set
|
|
account_role =(
|
|
medreport.get_upper_system_role())
|
|
where
|
|
target_account_id = account_id
|
|
and user_id = new_owner_id
|
|
and account_role <>(
|
|
medreport.get_upper_system_role());
|
|
|
|
end;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.trigger_set_timestamps()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$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$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.trigger_set_user_tracking()
|
|
RETURNS trigger
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$
|
|
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
|
|
$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.update_account(p_name character varying, p_last_name text, p_personal_code text, p_phone text, p_city text, p_has_consent_personal_data boolean, p_uid uuid)
|
|
RETURNS void
|
|
LANGUAGE plpgsql
|
|
AS $function$begin
|
|
update medreport.accounts
|
|
set name = coalesce(p_name, name),
|
|
last_name = coalesce(p_last_name, last_name),
|
|
personal_code = coalesce(p_personal_code, personal_code),
|
|
phone = coalesce(p_phone, phone),
|
|
city = coalesce(p_city, city),
|
|
has_consent_personal_data = coalesce(p_has_consent_personal_data,
|
|
has_consent_personal_data)
|
|
where id = p_uid;
|
|
end;$function$
|
|
;
|
|
|
|
grant
|
|
execute on function medreport.update_account(p_name character varying, p_last_name text, p_personal_code text, p_phone text, p_city text, p_has_consent_personal_data boolean, p_uid uuid) to authenticated,
|
|
service_role;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.upsert_order(target_account_id uuid, target_customer_id character varying, target_order_id text, status medreport.payment_status, billing_provider medreport.billing_provider, total_amount numeric, currency character varying, line_items jsonb)
|
|
RETURNS medreport.orders
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$declare
|
|
new_order medreport.orders;
|
|
new_billing_customer_id int;
|
|
begin
|
|
insert into medreport.billing_customers(
|
|
account_id,
|
|
provider,
|
|
customer_id)
|
|
values (
|
|
target_account_id,
|
|
billing_provider,
|
|
target_customer_id)
|
|
on conflict (
|
|
account_id,
|
|
provider,
|
|
customer_id)
|
|
do update set
|
|
provider = excluded.provider
|
|
returning
|
|
id into new_billing_customer_id;
|
|
|
|
insert into medreport.orders(
|
|
account_id,
|
|
billing_customer_id,
|
|
id,
|
|
status,
|
|
billing_provider,
|
|
total_amount,
|
|
currency)
|
|
values (
|
|
target_account_id,
|
|
new_billing_customer_id,
|
|
target_order_id,
|
|
status,
|
|
billing_provider,
|
|
total_amount,
|
|
currency)
|
|
on conflict (
|
|
id)
|
|
do update set
|
|
status = excluded.status,
|
|
total_amount = excluded.total_amount,
|
|
currency = excluded.currency
|
|
returning
|
|
* into new_order;
|
|
|
|
-- Upsert order items and delete ones that are not in the line_items array
|
|
with item_data as (
|
|
select
|
|
(line_item ->> 'id')::varchar as line_item_id,
|
|
(line_item ->> 'product_id')::varchar as prod_id,
|
|
(line_item ->> 'variant_id')::varchar as var_id,
|
|
(line_item ->> 'price_amount')::numeric as price_amt,
|
|
(line_item ->> 'quantity')::integer as qty
|
|
from
|
|
jsonb_array_elements(line_items) as line_item
|
|
),
|
|
line_item_ids as (
|
|
select line_item_id from item_data
|
|
),
|
|
deleted_items as (
|
|
delete from
|
|
medreport.order_items
|
|
where
|
|
medreport.order_items.order_id = new_order.id
|
|
and medreport.order_items.id not in (select line_item_id from line_item_ids)
|
|
returning *
|
|
)
|
|
insert into medreport.order_items(
|
|
id,
|
|
order_id,
|
|
product_id,
|
|
variant_id,
|
|
price_amount,
|
|
quantity)
|
|
select
|
|
line_item_id,
|
|
target_order_id,
|
|
prod_id,
|
|
var_id,
|
|
price_amt,
|
|
qty
|
|
from
|
|
item_data
|
|
on conflict (id)
|
|
do update set
|
|
price_amount = excluded.price_amount,
|
|
product_id = excluded.product_id,
|
|
variant_id = excluded.variant_id,
|
|
quantity = excluded.quantity;
|
|
|
|
return new_order;
|
|
|
|
end;$function$
|
|
;
|
|
|
|
grant
|
|
execute on function medreport.upsert_order (
|
|
uuid,
|
|
varchar,
|
|
text,
|
|
medreport.payment_status,
|
|
medreport.billing_provider,
|
|
numeric,
|
|
varchar,
|
|
jsonb
|
|
) to service_role;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.upsert_subscription(target_account_id uuid, target_customer_id character varying, target_subscription_id text, active boolean, status medreport.subscription_status, billing_provider medreport.billing_provider, cancel_at_period_end boolean, currency character varying, period_starts_at timestamp with time zone, period_ends_at timestamp with time zone, line_items jsonb, trial_starts_at timestamp with time zone DEFAULT NULL::timestamp with time zone, trial_ends_at timestamp with time zone DEFAULT NULL::timestamp with time zone)
|
|
RETURNS medreport.subscriptions
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$declare
|
|
new_subscription medreport.subscriptions;
|
|
new_billing_customer_id int;
|
|
begin
|
|
insert into medreport.billing_customers(
|
|
account_id,
|
|
provider,
|
|
customer_id)
|
|
values (
|
|
target_account_id,
|
|
billing_provider,
|
|
target_customer_id)
|
|
on conflict (
|
|
account_id,
|
|
provider,
|
|
customer_id)
|
|
do update set
|
|
provider = excluded.provider
|
|
returning
|
|
id into new_billing_customer_id;
|
|
|
|
insert into medreport.subscriptions(
|
|
account_id,
|
|
billing_customer_id,
|
|
id,
|
|
active,
|
|
status,
|
|
billing_provider,
|
|
cancel_at_period_end,
|
|
currency,
|
|
period_starts_at,
|
|
period_ends_at,
|
|
trial_starts_at,
|
|
trial_ends_at)
|
|
values (
|
|
target_account_id,
|
|
new_billing_customer_id,
|
|
target_subscription_id,
|
|
active,
|
|
status,
|
|
billing_provider,
|
|
cancel_at_period_end,
|
|
currency,
|
|
period_starts_at,
|
|
period_ends_at,
|
|
trial_starts_at,
|
|
trial_ends_at)
|
|
on conflict (
|
|
id)
|
|
do update set
|
|
active = excluded.active,
|
|
status = excluded.status,
|
|
cancel_at_period_end = excluded.cancel_at_period_end,
|
|
currency = excluded.currency,
|
|
period_starts_at = excluded.period_starts_at,
|
|
period_ends_at = excluded.period_ends_at,
|
|
trial_starts_at = excluded.trial_starts_at,
|
|
trial_ends_at = excluded.trial_ends_at
|
|
returning
|
|
* into new_subscription;
|
|
|
|
-- Upsert subscription items and delete ones that are not in the line_items array
|
|
with item_data as (
|
|
select
|
|
(line_item ->> 'id')::varchar as line_item_id,
|
|
(line_item ->> 'product_id')::varchar as prod_id,
|
|
(line_item ->> 'variant_id')::varchar as var_id,
|
|
(line_item ->> 'type')::public.subscription_item_type as type,
|
|
(line_item ->> 'price_amount')::numeric as price_amt,
|
|
(line_item ->> 'quantity')::integer as qty,
|
|
(line_item ->> 'interval')::varchar as intv,
|
|
(line_item ->> 'interval_count')::integer as intv_count
|
|
from
|
|
jsonb_array_elements(line_items) as line_item
|
|
),
|
|
line_item_ids as (
|
|
select line_item_id from item_data
|
|
),
|
|
deleted_items as (
|
|
delete from
|
|
medreport.subscription_items
|
|
where
|
|
medreport.subscription_items.subscription_id = new_subscription.id
|
|
and medreport.subscription_items.id not in (select line_item_id from line_item_ids)
|
|
returning *
|
|
)
|
|
insert into medreport.subscription_items(
|
|
id,
|
|
subscription_id,
|
|
product_id,
|
|
variant_id,
|
|
type,
|
|
price_amount,
|
|
quantity,
|
|
interval,
|
|
interval_count)
|
|
select
|
|
line_item_id,
|
|
target_subscription_id,
|
|
prod_id,
|
|
var_id,
|
|
type,
|
|
price_amt,
|
|
qty,
|
|
intv,
|
|
intv_count
|
|
from
|
|
item_data
|
|
on conflict (id)
|
|
do update set
|
|
product_id = excluded.product_id,
|
|
variant_id = excluded.variant_id,
|
|
price_amount = excluded.price_amount,
|
|
quantity = excluded.quantity,
|
|
interval = excluded.interval,
|
|
type = excluded.type,
|
|
interval_count = excluded.interval_count;
|
|
|
|
return new_subscription;
|
|
|
|
end;$function$
|
|
;
|
|
|
|
grant
|
|
execute on function medreport.upsert_subscription (
|
|
uuid,
|
|
varchar,
|
|
text,
|
|
bool,
|
|
medreport.subscription_status,
|
|
medreport.billing_provider,
|
|
bool,
|
|
varchar,
|
|
timestamptz,
|
|
timestamptz,
|
|
jsonb,
|
|
timestamptz,
|
|
timestamptz
|
|
) to service_role;
|
|
|
|
create or replace view "medreport"."user_account_workspace" as SELECT accounts.id,
|
|
accounts.name,
|
|
accounts.picture_url,
|
|
( SELECT subscriptions.status
|
|
FROM medreport.subscriptions
|
|
WHERE (subscriptions.account_id = accounts.id)
|
|
LIMIT 1) AS subscription_status
|
|
FROM medreport.accounts
|
|
WHERE ((accounts.primary_owner_user_id = ( SELECT auth.uid() AS uid)) AND (accounts.is_personal_account = true))
|
|
LIMIT 1;
|
|
|
|
grant
|
|
select
|
|
on medreport.user_account_workspace to authenticated,
|
|
service_role;
|
|
|
|
create or replace view "medreport"."user_accounts" as SELECT account.id,
|
|
account.name,
|
|
account.picture_url,
|
|
account.slug,
|
|
membership.account_role AS role
|
|
FROM (medreport.accounts account
|
|
JOIN medreport.accounts_memberships membership ON ((account.id = membership.account_id)))
|
|
WHERE ((membership.user_id = ( SELECT auth.uid() AS uid)) AND (account.is_personal_account = false) AND (account.id IN ( SELECT accounts_memberships.account_id
|
|
FROM medreport.accounts_memberships
|
|
WHERE (accounts_memberships.user_id = ( SELECT auth.uid() AS uid)))));
|
|
|
|
grant
|
|
select
|
|
on medreport.user_accounts to authenticated,
|
|
service_role;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.verify_nonce(p_token text, p_purpose text, p_user_id uuid DEFAULT NULL::uuid, p_required_scopes text[] DEFAULT NULL::text[], p_max_verification_attempts integer DEFAULT 5, p_ip inet DEFAULT NULL::inet, p_user_agent text DEFAULT NULL::text)
|
|
RETURNS jsonb
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path TO ''
|
|
AS $function$DECLARE
|
|
v_nonce RECORD;
|
|
v_matching_count INTEGER;
|
|
BEGIN
|
|
-- Count how many matching tokens exist before verification attempt
|
|
SELECT COUNT(*)
|
|
INTO v_matching_count
|
|
FROM medreport.nonces
|
|
WHERE purpose = p_purpose;
|
|
|
|
-- Update verification attempt counter and tracking info for all matching tokens
|
|
UPDATE medreport.nonces
|
|
SET verification_attempts = verification_attempts + 1,
|
|
last_verification_at = NOW(),
|
|
last_verification_ip = COALESCE(p_ip, last_verification_ip),
|
|
last_verification_user_agent = COALESCE(p_user_agent, last_verification_user_agent)
|
|
WHERE client_token = extensions.crypt(p_token, client_token)
|
|
AND purpose = p_purpose;
|
|
|
|
-- Find the nonce by token and purpose
|
|
-- Modified to handle user-specific tokens better
|
|
SELECT *
|
|
INTO v_nonce
|
|
FROM medreport.nonces
|
|
WHERE client_token = extensions.crypt(p_token, client_token)
|
|
AND purpose = p_purpose
|
|
-- Only apply user_id filter if the token was created for a specific user
|
|
AND (
|
|
-- Case 1: Anonymous token (user_id is NULL in DB)
|
|
(user_id IS NULL)
|
|
OR
|
|
-- Case 2: User-specific token (check if user_id matches)
|
|
(user_id = p_user_id)
|
|
)
|
|
AND used_at IS NULL
|
|
AND NOT revoked
|
|
AND expires_at > NOW();
|
|
|
|
-- Check if nonce exists
|
|
IF v_nonce.id IS NULL THEN
|
|
RETURN jsonb_build_object(
|
|
'valid', false,
|
|
'message', 'Invalid or expired token'
|
|
);
|
|
END IF;
|
|
|
|
-- Check if max verification attempts exceeded
|
|
IF p_max_verification_attempts > 0 AND v_nonce.verification_attempts > p_max_verification_attempts THEN
|
|
-- Automatically revoke the token
|
|
UPDATE medreport.nonces
|
|
SET revoked = TRUE,
|
|
revoked_reason = 'Maximum verification attempts exceeded'
|
|
WHERE id = v_nonce.id;
|
|
|
|
RETURN jsonb_build_object(
|
|
'valid', false,
|
|
'message', 'Token revoked due to too many verification attempts',
|
|
'max_attempts_exceeded', true
|
|
);
|
|
END IF;
|
|
|
|
-- Check scopes if required
|
|
IF p_required_scopes IS NOT NULL AND array_length(p_required_scopes, 1) > 0 THEN
|
|
-- Fix scope validation to properly check if token scopes contain all required scopes
|
|
-- Using array containment check: array1 @> array2 (array1 contains array2)
|
|
IF NOT (v_nonce.scopes @> p_required_scopes) THEN
|
|
RETURN jsonb_build_object(
|
|
'valid', false,
|
|
'message', 'Token does not have required permissions',
|
|
'token_scopes', v_nonce.scopes,
|
|
'required_scopes', p_required_scopes
|
|
);
|
|
END IF;
|
|
END IF;
|
|
|
|
-- Mark nonce as used
|
|
UPDATE medreport.nonces
|
|
SET used_at = NOW()
|
|
WHERE id = v_nonce.id;
|
|
|
|
-- Return success with metadata
|
|
RETURN jsonb_build_object(
|
|
'valid', true,
|
|
'user_id', v_nonce.user_id,
|
|
'metadata', v_nonce.metadata,
|
|
'scopes', v_nonce.scopes,
|
|
'purpose', v_nonce.purpose
|
|
);
|
|
END;$function$
|
|
;
|
|
|
|
grant
|
|
execute on function medreport.verify_nonce to authenticated,
|
|
service_role;
|
|
|
|
grant delete on table "medreport"."account_params" to "anon";
|
|
|
|
grant insert on table "medreport"."account_params" to "anon";
|
|
|
|
grant references on table "medreport"."account_params" to "anon";
|
|
|
|
grant select on table "medreport"."account_params" to "anon";
|
|
|
|
grant trigger on table "medreport"."account_params" to "anon";
|
|
|
|
grant truncate on table "medreport"."account_params" to "anon";
|
|
|
|
grant update on table "medreport"."account_params" to "anon";
|
|
|
|
grant delete on table "medreport"."account_params" to "authenticated";
|
|
|
|
grant insert on table "medreport"."account_params" to "authenticated";
|
|
|
|
grant references on table "medreport"."account_params" to "authenticated";
|
|
|
|
grant select on table "medreport"."account_params" to "authenticated";
|
|
|
|
grant trigger on table "medreport"."account_params" to "authenticated";
|
|
|
|
grant truncate on table "medreport"."account_params" to "authenticated";
|
|
|
|
grant update on table "medreport"."account_params" to "authenticated";
|
|
|
|
grant delete on table "medreport"."account_params" to "service_role";
|
|
|
|
grant insert on table "medreport"."account_params" to "service_role";
|
|
|
|
grant references on table "medreport"."account_params" to "service_role";
|
|
|
|
grant select on table "medreport"."account_params" to "service_role";
|
|
|
|
grant trigger on table "medreport"."account_params" to "service_role";
|
|
|
|
grant truncate on table "medreport"."account_params" to "service_role";
|
|
|
|
grant update on table "medreport"."account_params" to "service_role";
|
|
|
|
grant delete on table "medreport"."accounts" to "anon";
|
|
|
|
grant insert on table "medreport"."accounts" to "anon";
|
|
|
|
grant references on table "medreport"."accounts" to "anon";
|
|
|
|
grant select on table "medreport"."accounts" to "anon";
|
|
|
|
grant trigger on table "medreport"."accounts" to "anon";
|
|
|
|
grant truncate on table "medreport"."accounts" to "anon";
|
|
|
|
grant update on table "medreport"."accounts" to "anon";
|
|
|
|
grant delete on table "medreport"."accounts" to "authenticated";
|
|
|
|
grant insert on table "medreport"."accounts" to "authenticated";
|
|
|
|
grant select on table "medreport"."accounts" to "authenticated";
|
|
|
|
grant update on table "medreport"."accounts" to "authenticated";
|
|
|
|
grant delete on table "medreport"."accounts" to "service_role";
|
|
|
|
grant insert on table "medreport"."accounts" to "service_role";
|
|
|
|
grant select on table "medreport"."accounts" to "service_role";
|
|
|
|
grant update on table "medreport"."accounts" to "service_role";
|
|
|
|
grant delete on table "medreport"."accounts_memberships" to "anon";
|
|
|
|
grant insert on table "medreport"."accounts_memberships" to "anon";
|
|
|
|
grant references on table "medreport"."accounts_memberships" to "anon";
|
|
|
|
grant select on table "medreport"."accounts_memberships" to "anon";
|
|
|
|
grant trigger on table "medreport"."accounts_memberships" to "anon";
|
|
|
|
grant truncate on table "medreport"."accounts_memberships" to "anon";
|
|
|
|
grant update on table "medreport"."accounts_memberships" to "anon";
|
|
|
|
grant delete on table "medreport"."accounts_memberships" to "authenticated";
|
|
|
|
grant insert on table "medreport"."accounts_memberships" to "authenticated";
|
|
|
|
grant select on table "medreport"."accounts_memberships" to "authenticated";
|
|
|
|
grant update on table "medreport"."accounts_memberships" to "authenticated";
|
|
|
|
grant delete on table "medreport"."accounts_memberships" to "service_role";
|
|
|
|
grant insert on table "medreport"."accounts_memberships" to "service_role";
|
|
|
|
grant select on table "medreport"."accounts_memberships" to "service_role";
|
|
|
|
grant update on table "medreport"."accounts_memberships" to "service_role";
|
|
|
|
grant delete on table "medreport"."analyses" to "anon";
|
|
|
|
grant insert on table "medreport"."analyses" to "anon";
|
|
|
|
grant references on table "medreport"."analyses" to "anon";
|
|
|
|
grant select on table "medreport"."analyses" to "anon";
|
|
|
|
grant trigger on table "medreport"."analyses" to "anon";
|
|
|
|
grant truncate on table "medreport"."analyses" to "anon";
|
|
|
|
grant update on table "medreport"."analyses" to "anon";
|
|
|
|
grant delete on table "medreport"."analyses" to "authenticated";
|
|
|
|
grant insert on table "medreport"."analyses" to "authenticated";
|
|
|
|
grant references on table "medreport"."analyses" to "authenticated";
|
|
|
|
grant select on table "medreport"."analyses" to "authenticated";
|
|
|
|
grant trigger on table "medreport"."analyses" to "authenticated";
|
|
|
|
grant truncate on table "medreport"."analyses" to "authenticated";
|
|
|
|
grant update on table "medreport"."analyses" to "authenticated";
|
|
|
|
grant delete on table "medreport"."analyses" to "service_role";
|
|
|
|
grant insert on table "medreport"."analyses" to "service_role";
|
|
|
|
grant references on table "medreport"."analyses" to "service_role";
|
|
|
|
grant select on table "medreport"."analyses" to "service_role";
|
|
|
|
grant trigger on table "medreport"."analyses" to "service_role";
|
|
|
|
grant truncate on table "medreport"."analyses" to "service_role";
|
|
|
|
grant update on table "medreport"."analyses" to "service_role";
|
|
|
|
grant delete on table "medreport"."analysis_elements" to "anon";
|
|
|
|
grant insert on table "medreport"."analysis_elements" to "anon";
|
|
|
|
grant references on table "medreport"."analysis_elements" to "anon";
|
|
|
|
grant select on table "medreport"."analysis_elements" to "anon";
|
|
|
|
grant trigger on table "medreport"."analysis_elements" to "anon";
|
|
|
|
grant truncate on table "medreport"."analysis_elements" to "anon";
|
|
|
|
grant update on table "medreport"."analysis_elements" to "anon";
|
|
|
|
grant delete on table "medreport"."analysis_elements" to "authenticated";
|
|
|
|
grant insert on table "medreport"."analysis_elements" to "authenticated";
|
|
|
|
grant references on table "medreport"."analysis_elements" to "authenticated";
|
|
|
|
grant select on table "medreport"."analysis_elements" to "authenticated";
|
|
|
|
grant trigger on table "medreport"."analysis_elements" to "authenticated";
|
|
|
|
grant truncate on table "medreport"."analysis_elements" to "authenticated";
|
|
|
|
grant update on table "medreport"."analysis_elements" to "authenticated";
|
|
|
|
grant delete on table "medreport"."analysis_elements" to "service_role";
|
|
|
|
grant insert on table "medreport"."analysis_elements" to "service_role";
|
|
|
|
grant references on table "medreport"."analysis_elements" to "service_role";
|
|
|
|
grant select on table "medreport"."analysis_elements" to "service_role";
|
|
|
|
grant trigger on table "medreport"."analysis_elements" to "service_role";
|
|
|
|
grant truncate on table "medreport"."analysis_elements" to "service_role";
|
|
|
|
grant update on table "medreport"."analysis_elements" to "service_role";
|
|
|
|
grant delete on table "medreport"."analysis_groups" to "anon";
|
|
|
|
grant insert on table "medreport"."analysis_groups" to "anon";
|
|
|
|
grant references on table "medreport"."analysis_groups" to "anon";
|
|
|
|
grant select on table "medreport"."analysis_groups" to "anon";
|
|
|
|
grant trigger on table "medreport"."analysis_groups" to "anon";
|
|
|
|
grant truncate on table "medreport"."analysis_groups" to "anon";
|
|
|
|
grant update on table "medreport"."analysis_groups" to "anon";
|
|
|
|
grant delete on table "medreport"."analysis_groups" to "authenticated";
|
|
|
|
grant insert on table "medreport"."analysis_groups" to "authenticated";
|
|
|
|
grant references on table "medreport"."analysis_groups" to "authenticated";
|
|
|
|
grant select on table "medreport"."analysis_groups" to "authenticated";
|
|
|
|
grant trigger on table "medreport"."analysis_groups" to "authenticated";
|
|
|
|
grant truncate on table "medreport"."analysis_groups" to "authenticated";
|
|
|
|
grant update on table "medreport"."analysis_groups" to "authenticated";
|
|
|
|
grant delete on table "medreport"."analysis_groups" to "service_role";
|
|
|
|
grant insert on table "medreport"."analysis_groups" to "service_role";
|
|
|
|
grant references on table "medreport"."analysis_groups" to "service_role";
|
|
|
|
grant select on table "medreport"."analysis_groups" to "service_role";
|
|
|
|
grant trigger on table "medreport"."analysis_groups" to "service_role";
|
|
|
|
grant truncate on table "medreport"."analysis_groups" to "service_role";
|
|
|
|
grant update on table "medreport"."analysis_groups" to "service_role";
|
|
|
|
grant delete on table "medreport"."analysis_orders" to "anon";
|
|
|
|
grant insert on table "medreport"."analysis_orders" to "anon";
|
|
|
|
grant references on table "medreport"."analysis_orders" to "anon";
|
|
|
|
grant select on table "medreport"."analysis_orders" to "anon";
|
|
|
|
grant trigger on table "medreport"."analysis_orders" to "anon";
|
|
|
|
grant truncate on table "medreport"."analysis_orders" to "anon";
|
|
|
|
grant update on table "medreport"."analysis_orders" to "anon";
|
|
|
|
grant delete on table "medreport"."analysis_orders" to "authenticated";
|
|
|
|
grant insert on table "medreport"."analysis_orders" to "authenticated";
|
|
|
|
grant references on table "medreport"."analysis_orders" to "authenticated";
|
|
|
|
grant select on table "medreport"."analysis_orders" to "authenticated";
|
|
|
|
grant trigger on table "medreport"."analysis_orders" to "authenticated";
|
|
|
|
grant truncate on table "medreport"."analysis_orders" to "authenticated";
|
|
|
|
grant update on table "medreport"."analysis_orders" to "authenticated";
|
|
|
|
grant delete on table "medreport"."analysis_orders" to "service_role";
|
|
|
|
grant insert on table "medreport"."analysis_orders" to "service_role";
|
|
|
|
grant references on table "medreport"."analysis_orders" to "service_role";
|
|
|
|
grant select on table "medreport"."analysis_orders" to "service_role";
|
|
|
|
grant trigger on table "medreport"."analysis_orders" to "service_role";
|
|
|
|
grant truncate on table "medreport"."analysis_orders" to "service_role";
|
|
|
|
grant update on table "medreport"."analysis_orders" to "service_role";
|
|
|
|
grant delete on table "medreport"."analysis_response_elements" to "anon";
|
|
|
|
grant insert on table "medreport"."analysis_response_elements" to "anon";
|
|
|
|
grant references on table "medreport"."analysis_response_elements" to "anon";
|
|
|
|
grant select on table "medreport"."analysis_response_elements" to "anon";
|
|
|
|
grant trigger on table "medreport"."analysis_response_elements" to "anon";
|
|
|
|
grant truncate on table "medreport"."analysis_response_elements" to "anon";
|
|
|
|
grant update on table "medreport"."analysis_response_elements" to "anon";
|
|
|
|
grant delete on table "medreport"."analysis_response_elements" to "authenticated";
|
|
|
|
grant insert on table "medreport"."analysis_response_elements" to "authenticated";
|
|
|
|
grant references on table "medreport"."analysis_response_elements" to "authenticated";
|
|
|
|
grant select on table "medreport"."analysis_response_elements" to "authenticated";
|
|
|
|
grant trigger on table "medreport"."analysis_response_elements" to "authenticated";
|
|
|
|
grant truncate on table "medreport"."analysis_response_elements" to "authenticated";
|
|
|
|
grant update on table "medreport"."analysis_response_elements" to "authenticated";
|
|
|
|
grant delete on table "medreport"."analysis_response_elements" to "service_role";
|
|
|
|
grant insert on table "medreport"."analysis_response_elements" to "service_role";
|
|
|
|
grant references on table "medreport"."analysis_response_elements" to "service_role";
|
|
|
|
grant select on table "medreport"."analysis_response_elements" to "service_role";
|
|
|
|
grant trigger on table "medreport"."analysis_response_elements" to "service_role";
|
|
|
|
grant truncate on table "medreport"."analysis_response_elements" to "service_role";
|
|
|
|
grant update on table "medreport"."analysis_response_elements" to "service_role";
|
|
|
|
grant delete on table "medreport"."analysis_responses" to "anon";
|
|
|
|
grant insert on table "medreport"."analysis_responses" to "anon";
|
|
|
|
grant references on table "medreport"."analysis_responses" to "anon";
|
|
|
|
grant select on table "medreport"."analysis_responses" to "anon";
|
|
|
|
grant trigger on table "medreport"."analysis_responses" to "anon";
|
|
|
|
grant truncate on table "medreport"."analysis_responses" to "anon";
|
|
|
|
grant update on table "medreport"."analysis_responses" to "anon";
|
|
|
|
grant delete on table "medreport"."analysis_responses" to "authenticated";
|
|
|
|
grant insert on table "medreport"."analysis_responses" to "authenticated";
|
|
|
|
grant references on table "medreport"."analysis_responses" to "authenticated";
|
|
|
|
grant select on table "medreport"."analysis_responses" to "authenticated";
|
|
|
|
grant trigger on table "medreport"."analysis_responses" to "authenticated";
|
|
|
|
grant truncate on table "medreport"."analysis_responses" to "authenticated";
|
|
|
|
grant update on table "medreport"."analysis_responses" to "authenticated";
|
|
|
|
grant delete on table "medreport"."analysis_responses" to "service_role";
|
|
|
|
grant insert on table "medreport"."analysis_responses" to "service_role";
|
|
|
|
grant references on table "medreport"."analysis_responses" to "service_role";
|
|
|
|
grant select on table "medreport"."analysis_responses" to "service_role";
|
|
|
|
grant trigger on table "medreport"."analysis_responses" to "service_role";
|
|
|
|
grant truncate on table "medreport"."analysis_responses" to "service_role";
|
|
|
|
grant update on table "medreport"."analysis_responses" to "service_role";
|
|
|
|
grant delete on table "medreport"."billing_customers" to "anon";
|
|
|
|
grant insert on table "medreport"."billing_customers" to "anon";
|
|
|
|
grant references on table "medreport"."billing_customers" to "anon";
|
|
|
|
grant select on table "medreport"."billing_customers" to "anon";
|
|
|
|
grant trigger on table "medreport"."billing_customers" to "anon";
|
|
|
|
grant truncate on table "medreport"."billing_customers" to "anon";
|
|
|
|
grant update on table "medreport"."billing_customers" to "anon";
|
|
|
|
grant select on table "medreport"."billing_customers" to "authenticated";
|
|
|
|
grant delete on table "medreport"."billing_customers" to "service_role";
|
|
|
|
grant insert on table "medreport"."billing_customers" to "service_role";
|
|
|
|
grant select on table "medreport"."billing_customers" to "service_role";
|
|
|
|
grant update on table "medreport"."billing_customers" to "service_role";
|
|
|
|
grant delete on table "medreport"."codes" to "anon";
|
|
|
|
grant insert on table "medreport"."codes" to "anon";
|
|
|
|
grant references on table "medreport"."codes" to "anon";
|
|
|
|
grant select on table "medreport"."codes" to "anon";
|
|
|
|
grant trigger on table "medreport"."codes" to "anon";
|
|
|
|
grant truncate on table "medreport"."codes" to "anon";
|
|
|
|
grant update on table "medreport"."codes" to "anon";
|
|
|
|
grant delete on table "medreport"."codes" to "authenticated";
|
|
|
|
grant insert on table "medreport"."codes" to "authenticated";
|
|
|
|
grant references on table "medreport"."codes" to "authenticated";
|
|
|
|
grant select on table "medreport"."codes" to "authenticated";
|
|
|
|
grant trigger on table "medreport"."codes" to "authenticated";
|
|
|
|
grant truncate on table "medreport"."codes" to "authenticated";
|
|
|
|
grant update on table "medreport"."codes" to "authenticated";
|
|
|
|
grant delete on table "medreport"."codes" to "service_role";
|
|
|
|
grant insert on table "medreport"."codes" to "service_role";
|
|
|
|
grant references on table "medreport"."codes" to "service_role";
|
|
|
|
grant select on table "medreport"."codes" to "service_role";
|
|
|
|
grant trigger on table "medreport"."codes" to "service_role";
|
|
|
|
grant truncate on table "medreport"."codes" to "service_role";
|
|
|
|
grant update on table "medreport"."codes" to "service_role";
|
|
|
|
grant delete on table "medreport"."config" to "anon";
|
|
|
|
grant insert on table "medreport"."config" to "anon";
|
|
|
|
grant references on table "medreport"."config" to "anon";
|
|
|
|
grant select on table "medreport"."config" to "anon";
|
|
|
|
grant trigger on table "medreport"."config" to "anon";
|
|
|
|
grant truncate on table "medreport"."config" to "anon";
|
|
|
|
grant update on table "medreport"."config" to "anon";
|
|
|
|
grant select on table "medreport"."config" to "authenticated";
|
|
|
|
grant select on table "medreport"."config" to "service_role";
|
|
|
|
grant delete on table "medreport"."connected_online_providers" to "anon";
|
|
|
|
grant insert on table "medreport"."connected_online_providers" to "anon";
|
|
|
|
grant references on table "medreport"."connected_online_providers" to "anon";
|
|
|
|
grant select on table "medreport"."connected_online_providers" to "anon";
|
|
|
|
grant trigger on table "medreport"."connected_online_providers" to "anon";
|
|
|
|
grant truncate on table "medreport"."connected_online_providers" to "anon";
|
|
|
|
grant update on table "medreport"."connected_online_providers" to "anon";
|
|
|
|
grant delete on table "medreport"."connected_online_providers" to "authenticated";
|
|
|
|
grant insert on table "medreport"."connected_online_providers" to "authenticated";
|
|
|
|
grant references on table "medreport"."connected_online_providers" to "authenticated";
|
|
|
|
grant select on table "medreport"."connected_online_providers" to "authenticated";
|
|
|
|
grant trigger on table "medreport"."connected_online_providers" to "authenticated";
|
|
|
|
grant truncate on table "medreport"."connected_online_providers" to "authenticated";
|
|
|
|
grant update on table "medreport"."connected_online_providers" to "authenticated";
|
|
|
|
grant delete on table "medreport"."connected_online_providers" to "service_role";
|
|
|
|
grant insert on table "medreport"."connected_online_providers" to "service_role";
|
|
|
|
grant references on table "medreport"."connected_online_providers" to "service_role";
|
|
|
|
grant select on table "medreport"."connected_online_providers" to "service_role";
|
|
|
|
grant trigger on table "medreport"."connected_online_providers" to "service_role";
|
|
|
|
grant truncate on table "medreport"."connected_online_providers" to "service_role";
|
|
|
|
grant update on table "medreport"."connected_online_providers" to "service_role";
|
|
|
|
grant delete on table "medreport"."connected_online_reservation" to "anon";
|
|
|
|
grant insert on table "medreport"."connected_online_reservation" to "anon";
|
|
|
|
grant references on table "medreport"."connected_online_reservation" to "anon";
|
|
|
|
grant select on table "medreport"."connected_online_reservation" to "anon";
|
|
|
|
grant trigger on table "medreport"."connected_online_reservation" to "anon";
|
|
|
|
grant truncate on table "medreport"."connected_online_reservation" to "anon";
|
|
|
|
grant update on table "medreport"."connected_online_reservation" to "anon";
|
|
|
|
grant delete on table "medreport"."connected_online_reservation" to "authenticated";
|
|
|
|
grant insert on table "medreport"."connected_online_reservation" to "authenticated";
|
|
|
|
grant references on table "medreport"."connected_online_reservation" to "authenticated";
|
|
|
|
grant select on table "medreport"."connected_online_reservation" to "authenticated";
|
|
|
|
grant trigger on table "medreport"."connected_online_reservation" to "authenticated";
|
|
|
|
grant truncate on table "medreport"."connected_online_reservation" to "authenticated";
|
|
|
|
grant update on table "medreport"."connected_online_reservation" to "authenticated";
|
|
|
|
grant delete on table "medreport"."connected_online_reservation" to "service_role";
|
|
|
|
grant insert on table "medreport"."connected_online_reservation" to "service_role";
|
|
|
|
grant references on table "medreport"."connected_online_reservation" to "service_role";
|
|
|
|
grant select on table "medreport"."connected_online_reservation" to "service_role";
|
|
|
|
grant trigger on table "medreport"."connected_online_reservation" to "service_role";
|
|
|
|
grant truncate on table "medreport"."connected_online_reservation" to "service_role";
|
|
|
|
grant update on table "medreport"."connected_online_reservation" to "service_role";
|
|
|
|
grant delete on table "medreport"."connected_online_services" to "anon";
|
|
|
|
grant insert on table "medreport"."connected_online_services" to "anon";
|
|
|
|
grant references on table "medreport"."connected_online_services" to "anon";
|
|
|
|
grant select on table "medreport"."connected_online_services" to "anon";
|
|
|
|
grant trigger on table "medreport"."connected_online_services" to "anon";
|
|
|
|
grant truncate on table "medreport"."connected_online_services" to "anon";
|
|
|
|
grant update on table "medreport"."connected_online_services" to "anon";
|
|
|
|
grant delete on table "medreport"."connected_online_services" to "authenticated";
|
|
|
|
grant insert on table "medreport"."connected_online_services" to "authenticated";
|
|
|
|
grant references on table "medreport"."connected_online_services" to "authenticated";
|
|
|
|
grant select on table "medreport"."connected_online_services" to "authenticated";
|
|
|
|
grant trigger on table "medreport"."connected_online_services" to "authenticated";
|
|
|
|
grant truncate on table "medreport"."connected_online_services" to "authenticated";
|
|
|
|
grant update on table "medreport"."connected_online_services" to "authenticated";
|
|
|
|
grant delete on table "medreport"."connected_online_services" to "service_role";
|
|
|
|
grant insert on table "medreport"."connected_online_services" to "service_role";
|
|
|
|
grant references on table "medreport"."connected_online_services" to "service_role";
|
|
|
|
grant select on table "medreport"."connected_online_services" to "service_role";
|
|
|
|
grant trigger on table "medreport"."connected_online_services" to "service_role";
|
|
|
|
grant truncate on table "medreport"."connected_online_services" to "service_role";
|
|
|
|
grant update on table "medreport"."connected_online_services" to "service_role";
|
|
|
|
grant delete on table "medreport"."invitations" to "anon";
|
|
|
|
grant insert on table "medreport"."invitations" to "anon";
|
|
|
|
grant references on table "medreport"."invitations" to "anon";
|
|
|
|
grant select on table "medreport"."invitations" to "anon";
|
|
|
|
grant trigger on table "medreport"."invitations" to "anon";
|
|
|
|
grant truncate on table "medreport"."invitations" to "anon";
|
|
|
|
grant update on table "medreport"."invitations" to "anon";
|
|
|
|
grant delete on table "medreport"."invitations" to "authenticated";
|
|
|
|
grant insert on table "medreport"."invitations" to "authenticated";
|
|
|
|
grant select on table "medreport"."invitations" to "authenticated";
|
|
|
|
grant update on table "medreport"."invitations" to "authenticated";
|
|
|
|
grant delete on table "medreport"."invitations" to "service_role";
|
|
|
|
grant insert on table "medreport"."invitations" to "service_role";
|
|
|
|
grant select on table "medreport"."invitations" to "service_role";
|
|
|
|
grant update on table "medreport"."invitations" to "service_role";
|
|
|
|
grant delete on table "medreport"."medreport_product_groups" to "anon";
|
|
|
|
grant insert on table "medreport"."medreport_product_groups" to "anon";
|
|
|
|
grant references on table "medreport"."medreport_product_groups" to "anon";
|
|
|
|
grant select on table "medreport"."medreport_product_groups" to "anon";
|
|
|
|
grant trigger on table "medreport"."medreport_product_groups" to "anon";
|
|
|
|
grant truncate on table "medreport"."medreport_product_groups" to "anon";
|
|
|
|
grant update on table "medreport"."medreport_product_groups" to "anon";
|
|
|
|
grant delete on table "medreport"."medreport_product_groups" to "authenticated";
|
|
|
|
grant insert on table "medreport"."medreport_product_groups" to "authenticated";
|
|
|
|
grant references on table "medreport"."medreport_product_groups" to "authenticated";
|
|
|
|
grant select on table "medreport"."medreport_product_groups" to "authenticated";
|
|
|
|
grant trigger on table "medreport"."medreport_product_groups" to "authenticated";
|
|
|
|
grant truncate on table "medreport"."medreport_product_groups" to "authenticated";
|
|
|
|
grant update on table "medreport"."medreport_product_groups" to "authenticated";
|
|
|
|
grant delete on table "medreport"."medreport_product_groups" to "service_role";
|
|
|
|
grant insert on table "medreport"."medreport_product_groups" to "service_role";
|
|
|
|
grant references on table "medreport"."medreport_product_groups" to "service_role";
|
|
|
|
grant select on table "medreport"."medreport_product_groups" to "service_role";
|
|
|
|
grant trigger on table "medreport"."medreport_product_groups" to "service_role";
|
|
|
|
grant truncate on table "medreport"."medreport_product_groups" to "service_role";
|
|
|
|
grant update on table "medreport"."medreport_product_groups" to "service_role";
|
|
|
|
grant delete on table "medreport"."medreport_products" to "anon";
|
|
|
|
grant insert on table "medreport"."medreport_products" to "anon";
|
|
|
|
grant references on table "medreport"."medreport_products" to "anon";
|
|
|
|
grant select on table "medreport"."medreport_products" to "anon";
|
|
|
|
grant trigger on table "medreport"."medreport_products" to "anon";
|
|
|
|
grant truncate on table "medreport"."medreport_products" to "anon";
|
|
|
|
grant update on table "medreport"."medreport_products" to "anon";
|
|
|
|
grant delete on table "medreport"."medreport_products" to "authenticated";
|
|
|
|
grant insert on table "medreport"."medreport_products" to "authenticated";
|
|
|
|
grant references on table "medreport"."medreport_products" to "authenticated";
|
|
|
|
grant select on table "medreport"."medreport_products" to "authenticated";
|
|
|
|
grant trigger on table "medreport"."medreport_products" to "authenticated";
|
|
|
|
grant truncate on table "medreport"."medreport_products" to "authenticated";
|
|
|
|
grant update on table "medreport"."medreport_products" to "authenticated";
|
|
|
|
grant delete on table "medreport"."medreport_products" to "service_role";
|
|
|
|
grant insert on table "medreport"."medreport_products" to "service_role";
|
|
|
|
grant references on table "medreport"."medreport_products" to "service_role";
|
|
|
|
grant select on table "medreport"."medreport_products" to "service_role";
|
|
|
|
grant trigger on table "medreport"."medreport_products" to "service_role";
|
|
|
|
grant truncate on table "medreport"."medreport_products" to "service_role";
|
|
|
|
grant update on table "medreport"."medreport_products" to "service_role";
|
|
|
|
grant delete on table "medreport"."medreport_products_analyses_relations" to "anon";
|
|
|
|
grant insert on table "medreport"."medreport_products_analyses_relations" to "anon";
|
|
|
|
grant references on table "medreport"."medreport_products_analyses_relations" to "anon";
|
|
|
|
grant select on table "medreport"."medreport_products_analyses_relations" to "anon";
|
|
|
|
grant trigger on table "medreport"."medreport_products_analyses_relations" to "anon";
|
|
|
|
grant truncate on table "medreport"."medreport_products_analyses_relations" to "anon";
|
|
|
|
grant update on table "medreport"."medreport_products_analyses_relations" to "anon";
|
|
|
|
grant delete on table "medreport"."medreport_products_analyses_relations" to "authenticated";
|
|
|
|
grant insert on table "medreport"."medreport_products_analyses_relations" to "authenticated";
|
|
|
|
grant references on table "medreport"."medreport_products_analyses_relations" to "authenticated";
|
|
|
|
grant select on table "medreport"."medreport_products_analyses_relations" to "authenticated";
|
|
|
|
grant trigger on table "medreport"."medreport_products_analyses_relations" to "authenticated";
|
|
|
|
grant truncate on table "medreport"."medreport_products_analyses_relations" to "authenticated";
|
|
|
|
grant update on table "medreport"."medreport_products_analyses_relations" to "authenticated";
|
|
|
|
grant delete on table "medreport"."medreport_products_analyses_relations" to "service_role";
|
|
|
|
grant insert on table "medreport"."medreport_products_analyses_relations" to "service_role";
|
|
|
|
grant references on table "medreport"."medreport_products_analyses_relations" to "service_role";
|
|
|
|
grant select on table "medreport"."medreport_products_analyses_relations" to "service_role";
|
|
|
|
grant trigger on table "medreport"."medreport_products_analyses_relations" to "service_role";
|
|
|
|
grant truncate on table "medreport"."medreport_products_analyses_relations" to "service_role";
|
|
|
|
grant update on table "medreport"."medreport_products_analyses_relations" to "service_role";
|
|
|
|
grant delete on table "medreport"."medreport_products_external_services_relations" to "anon";
|
|
|
|
grant insert on table "medreport"."medreport_products_external_services_relations" to "anon";
|
|
|
|
grant references on table "medreport"."medreport_products_external_services_relations" to "anon";
|
|
|
|
grant select on table "medreport"."medreport_products_external_services_relations" to "anon";
|
|
|
|
grant trigger on table "medreport"."medreport_products_external_services_relations" to "anon";
|
|
|
|
grant truncate on table "medreport"."medreport_products_external_services_relations" to "anon";
|
|
|
|
grant update on table "medreport"."medreport_products_external_services_relations" to "anon";
|
|
|
|
grant delete on table "medreport"."medreport_products_external_services_relations" to "authenticated";
|
|
|
|
grant insert on table "medreport"."medreport_products_external_services_relations" to "authenticated";
|
|
|
|
grant references on table "medreport"."medreport_products_external_services_relations" to "authenticated";
|
|
|
|
grant select on table "medreport"."medreport_products_external_services_relations" to "authenticated";
|
|
|
|
grant trigger on table "medreport"."medreport_products_external_services_relations" to "authenticated";
|
|
|
|
grant truncate on table "medreport"."medreport_products_external_services_relations" to "authenticated";
|
|
|
|
grant update on table "medreport"."medreport_products_external_services_relations" to "authenticated";
|
|
|
|
grant delete on table "medreport"."medreport_products_external_services_relations" to "service_role";
|
|
|
|
grant insert on table "medreport"."medreport_products_external_services_relations" to "service_role";
|
|
|
|
grant references on table "medreport"."medreport_products_external_services_relations" to "service_role";
|
|
|
|
grant select on table "medreport"."medreport_products_external_services_relations" to "service_role";
|
|
|
|
grant trigger on table "medreport"."medreport_products_external_services_relations" to "service_role";
|
|
|
|
grant truncate on table "medreport"."medreport_products_external_services_relations" to "service_role";
|
|
|
|
grant update on table "medreport"."medreport_products_external_services_relations" to "service_role";
|
|
|
|
grant delete on table "medreport"."nonces" to "anon";
|
|
|
|
grant insert on table "medreport"."nonces" to "anon";
|
|
|
|
grant references on table "medreport"."nonces" to "anon";
|
|
|
|
grant select on table "medreport"."nonces" to "anon";
|
|
|
|
grant trigger on table "medreport"."nonces" to "anon";
|
|
|
|
grant truncate on table "medreport"."nonces" to "anon";
|
|
|
|
grant update on table "medreport"."nonces" to "anon";
|
|
|
|
grant delete on table "medreport"."nonces" to "authenticated";
|
|
|
|
grant insert on table "medreport"."nonces" to "authenticated";
|
|
|
|
grant references on table "medreport"."nonces" to "authenticated";
|
|
|
|
grant select on table "medreport"."nonces" to "authenticated";
|
|
|
|
grant trigger on table "medreport"."nonces" to "authenticated";
|
|
|
|
grant truncate on table "medreport"."nonces" to "authenticated";
|
|
|
|
grant update on table "medreport"."nonces" to "authenticated";
|
|
|
|
grant delete on table "medreport"."nonces" to "service_role";
|
|
|
|
grant insert on table "medreport"."nonces" to "service_role";
|
|
|
|
grant references on table "medreport"."nonces" to "service_role";
|
|
|
|
grant select on table "medreport"."nonces" to "service_role";
|
|
|
|
grant trigger on table "medreport"."nonces" to "service_role";
|
|
|
|
grant truncate on table "medreport"."nonces" to "service_role";
|
|
|
|
grant update on table "medreport"."nonces" to "service_role";
|
|
|
|
grant delete on table "medreport"."notifications" to "anon";
|
|
|
|
grant insert on table "medreport"."notifications" to "anon";
|
|
|
|
grant references on table "medreport"."notifications" to "anon";
|
|
|
|
grant select on table "medreport"."notifications" to "anon";
|
|
|
|
grant trigger on table "medreport"."notifications" to "anon";
|
|
|
|
grant truncate on table "medreport"."notifications" to "anon";
|
|
|
|
grant update on table "medreport"."notifications" to "anon";
|
|
|
|
grant select on table "medreport"."notifications" to "authenticated";
|
|
|
|
grant update on table "medreport"."notifications" to "authenticated";
|
|
|
|
grant insert on table "medreport"."notifications" to "service_role";
|
|
|
|
grant select on table "medreport"."notifications" to "service_role";
|
|
|
|
grant update on table "medreport"."notifications" to "service_role";
|
|
|
|
grant delete on table "medreport"."order_items" to "anon";
|
|
|
|
grant insert on table "medreport"."order_items" to "anon";
|
|
|
|
grant references on table "medreport"."order_items" to "anon";
|
|
|
|
grant select on table "medreport"."order_items" to "anon";
|
|
|
|
grant trigger on table "medreport"."order_items" to "anon";
|
|
|
|
grant truncate on table "medreport"."order_items" to "anon";
|
|
|
|
grant update on table "medreport"."order_items" to "anon";
|
|
|
|
grant select on table "medreport"."order_items" to "authenticated";
|
|
|
|
grant delete on table "medreport"."order_items" to "service_role";
|
|
|
|
grant insert on table "medreport"."order_items" to "service_role";
|
|
|
|
grant select on table "medreport"."order_items" to "service_role";
|
|
|
|
grant update on table "medreport"."order_items" to "service_role";
|
|
|
|
grant delete on table "medreport"."orders" to "anon";
|
|
|
|
grant insert on table "medreport"."orders" to "anon";
|
|
|
|
grant references on table "medreport"."orders" to "anon";
|
|
|
|
grant select on table "medreport"."orders" to "anon";
|
|
|
|
grant trigger on table "medreport"."orders" to "anon";
|
|
|
|
grant truncate on table "medreport"."orders" to "anon";
|
|
|
|
grant update on table "medreport"."orders" to "anon";
|
|
|
|
grant select on table "medreport"."orders" to "authenticated";
|
|
|
|
grant delete on table "medreport"."orders" to "service_role";
|
|
|
|
grant insert on table "medreport"."orders" to "service_role";
|
|
|
|
grant select on table "medreport"."orders" to "service_role";
|
|
|
|
grant update on table "medreport"."orders" to "service_role";
|
|
|
|
grant delete on table "medreport"."role_permissions" to "anon";
|
|
|
|
grant insert on table "medreport"."role_permissions" to "anon";
|
|
|
|
grant references on table "medreport"."role_permissions" to "anon";
|
|
|
|
grant select on table "medreport"."role_permissions" to "anon";
|
|
|
|
grant trigger on table "medreport"."role_permissions" to "anon";
|
|
|
|
grant truncate on table "medreport"."role_permissions" to "anon";
|
|
|
|
grant update on table "medreport"."role_permissions" to "anon";
|
|
|
|
grant select on table "medreport"."role_permissions" to "authenticated";
|
|
|
|
grant delete on table "medreport"."role_permissions" to "service_role";
|
|
|
|
grant insert on table "medreport"."role_permissions" to "service_role";
|
|
|
|
grant select on table "medreport"."role_permissions" to "service_role";
|
|
|
|
grant update on table "medreport"."role_permissions" to "service_role";
|
|
|
|
grant delete on table "medreport"."roles" to "anon";
|
|
|
|
grant insert on table "medreport"."roles" to "anon";
|
|
|
|
grant references on table "medreport"."roles" to "anon";
|
|
|
|
grant select on table "medreport"."roles" to "anon";
|
|
|
|
grant trigger on table "medreport"."roles" to "anon";
|
|
|
|
grant truncate on table "medreport"."roles" to "anon";
|
|
|
|
grant update on table "medreport"."roles" to "anon";
|
|
|
|
grant select on table "medreport"."roles" to "authenticated";
|
|
|
|
grant select on table "medreport"."roles" to "service_role";
|
|
|
|
grant delete on table "medreport"."subscription_items" to "anon";
|
|
|
|
grant insert on table "medreport"."subscription_items" to "anon";
|
|
|
|
grant references on table "medreport"."subscription_items" to "anon";
|
|
|
|
grant select on table "medreport"."subscription_items" to "anon";
|
|
|
|
grant trigger on table "medreport"."subscription_items" to "anon";
|
|
|
|
grant truncate on table "medreport"."subscription_items" to "anon";
|
|
|
|
grant update on table "medreport"."subscription_items" to "anon";
|
|
|
|
grant select on table "medreport"."subscription_items" to "authenticated";
|
|
|
|
grant delete on table "medreport"."subscription_items" to "service_role";
|
|
|
|
grant insert on table "medreport"."subscription_items" to "service_role";
|
|
|
|
grant select on table "medreport"."subscription_items" to "service_role";
|
|
|
|
grant update on table "medreport"."subscription_items" to "service_role";
|
|
|
|
grant delete on table "medreport"."subscriptions" to "anon";
|
|
|
|
grant insert on table "medreport"."subscriptions" to "anon";
|
|
|
|
grant references on table "medreport"."subscriptions" to "anon";
|
|
|
|
grant select on table "medreport"."subscriptions" to "anon";
|
|
|
|
grant trigger on table "medreport"."subscriptions" to "anon";
|
|
|
|
grant truncate on table "medreport"."subscriptions" to "anon";
|
|
|
|
grant update on table "medreport"."subscriptions" to "anon";
|
|
|
|
grant select on table "medreport"."subscriptions" to "authenticated";
|
|
|
|
grant delete on table "medreport"."subscriptions" to "service_role";
|
|
|
|
grant insert on table "medreport"."subscriptions" to "service_role";
|
|
|
|
grant select on table "medreport"."subscriptions" to "service_role";
|
|
|
|
grant update on table "medreport"."subscriptions" to "service_role";
|
|
|
|
create policy "users can insert their params"
|
|
on "medreport"."account_params"
|
|
as permissive
|
|
for insert
|
|
to authenticated
|
|
with check ((account_id = auth.uid()));
|
|
|
|
|
|
create policy "users can read their params"
|
|
on "medreport"."account_params"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using ((account_id = auth.uid()));
|
|
|
|
|
|
create policy "accounts_read"
|
|
on "medreport"."accounts"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using (((( SELECT auth.uid() AS uid) = primary_owner_user_id) OR medreport.has_role_on_account(id) OR medreport.is_account_team_member(id)));
|
|
|
|
|
|
create policy "accounts_self_update"
|
|
on "medreport"."accounts"
|
|
as permissive
|
|
for update
|
|
to authenticated
|
|
using ((( SELECT auth.uid() AS uid) = primary_owner_user_id))
|
|
with check ((( SELECT auth.uid() AS uid) = primary_owner_user_id));
|
|
|
|
|
|
create policy "create_org_account"
|
|
on "medreport"."accounts"
|
|
as permissive
|
|
for insert
|
|
to authenticated
|
|
with check ((medreport.is_set('enable_team_accounts'::text) AND (is_personal_account = false)));
|
|
|
|
|
|
create policy "restrict_mfa_accounts"
|
|
on "medreport"."accounts"
|
|
as restrictive
|
|
for all
|
|
to authenticated
|
|
using (medreport.is_mfa_compliant());
|
|
|
|
|
|
create policy "super_admins_access_accounts"
|
|
on "medreport"."accounts"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using (medreport.is_super_admin());
|
|
|
|
|
|
create policy "accounts_memberships_delete"
|
|
on "medreport"."accounts_memberships"
|
|
as permissive
|
|
for delete
|
|
to authenticated
|
|
using (((user_id = ( SELECT auth.uid() AS uid)) OR medreport.can_action_account_member(account_id, user_id)));
|
|
|
|
|
|
create policy "accounts_memberships_read"
|
|
on "medreport"."accounts_memberships"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using (((( SELECT auth.uid() AS uid) = user_id) OR medreport.is_team_member(account_id, user_id)));
|
|
|
|
|
|
create policy "restrict_mfa_accounts_memberships"
|
|
on "medreport"."accounts_memberships"
|
|
as restrictive
|
|
for all
|
|
to authenticated
|
|
using (medreport.is_mfa_compliant());
|
|
|
|
|
|
create policy "super_admins_access_accounts_memberships"
|
|
on "medreport"."accounts_memberships"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using (medreport.is_super_admin());
|
|
|
|
|
|
create policy "analysis_all"
|
|
on "medreport"."analyses"
|
|
as permissive
|
|
for all
|
|
to service_role
|
|
using (true);
|
|
|
|
|
|
create policy "analysis_select"
|
|
on "medreport"."analyses"
|
|
as permissive
|
|
for select
|
|
to public
|
|
using (true);
|
|
|
|
|
|
create policy "analysis_elements_all"
|
|
on "medreport"."analysis_elements"
|
|
as permissive
|
|
for all
|
|
to service_role
|
|
using (true);
|
|
|
|
|
|
create policy "analysis_elements_select"
|
|
on "medreport"."analysis_elements"
|
|
as permissive
|
|
for select
|
|
to public
|
|
using (true);
|
|
|
|
|
|
create policy "analysis_groups_all"
|
|
on "medreport"."analysis_groups"
|
|
as permissive
|
|
for all
|
|
to service_role
|
|
using (true);
|
|
|
|
|
|
create policy "analysis_groups_select"
|
|
on "medreport"."analysis_groups"
|
|
as permissive
|
|
for select
|
|
to public
|
|
using (true);
|
|
|
|
|
|
create policy "analysis_all"
|
|
on "medreport"."analysis_orders"
|
|
as permissive
|
|
for all
|
|
to authenticated, service_role
|
|
using (true);
|
|
|
|
|
|
create policy "service_role_all"
|
|
on "medreport"."analysis_orders"
|
|
as permissive
|
|
for all
|
|
to service_role
|
|
using (true);
|
|
|
|
|
|
create policy "select_own"
|
|
on "medreport"."analysis_response_elements"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using ((( SELECT auth.uid() AS uid) IN ( SELECT analysis_responses.user_id
|
|
FROM medreport.analysis_responses
|
|
WHERE (analysis_responses.id = analysis_response_elements.analysis_response_id))));
|
|
|
|
|
|
create policy "service_role_all"
|
|
on "medreport"."analysis_response_elements"
|
|
as permissive
|
|
for all
|
|
to service_role
|
|
using (true);
|
|
|
|
|
|
create policy "select_own"
|
|
on "medreport"."analysis_responses"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using ((( SELECT auth.uid() AS uid) = user_id));
|
|
|
|
|
|
create policy "service_role_all"
|
|
on "medreport"."analysis_responses"
|
|
as permissive
|
|
for all
|
|
to service_role
|
|
using (true);
|
|
|
|
|
|
create policy "billing_customers_read_self"
|
|
on "medreport"."billing_customers"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using (((account_id = ( SELECT auth.uid() AS uid)) OR medreport.has_role_on_account(account_id)));
|
|
|
|
|
|
create policy "codes_all"
|
|
on "medreport"."codes"
|
|
as permissive
|
|
for all
|
|
to service_role
|
|
using (true);
|
|
|
|
|
|
create policy "public config can be read by authenticated users"
|
|
on "medreport"."config"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using (true);
|
|
|
|
|
|
create policy "authenticated_select"
|
|
on "medreport"."connected_online_providers"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using (true);
|
|
|
|
|
|
create policy "service_role_all"
|
|
on "medreport"."connected_online_providers"
|
|
as permissive
|
|
for all
|
|
to service_role
|
|
using (true);
|
|
|
|
|
|
create policy "own_all"
|
|
on "medreport"."connected_online_reservation"
|
|
as permissive
|
|
for all
|
|
to authenticated
|
|
using ((( SELECT auth.uid() AS uid) = user_id));
|
|
|
|
|
|
create policy "service_role_all"
|
|
on "medreport"."connected_online_reservation"
|
|
as permissive
|
|
for all
|
|
to service_role
|
|
using (true);
|
|
|
|
|
|
create policy "authenticated_select"
|
|
on "medreport"."connected_online_services"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using (true);
|
|
|
|
|
|
create policy "service_role_all"
|
|
on "medreport"."connected_online_services"
|
|
as permissive
|
|
for all
|
|
to service_role
|
|
using (true);
|
|
|
|
|
|
create policy "invitations_create_self"
|
|
on "medreport"."invitations"
|
|
as permissive
|
|
for insert
|
|
to authenticated
|
|
with check ((medreport.is_set('enable_team_accounts'::text) AND medreport.has_permission(( SELECT auth.uid() AS uid), account_id, 'invites.manage'::medreport.app_permissions) AND (medreport.has_more_elevated_role(( SELECT auth.uid() AS uid), account_id, role) OR medreport.has_same_role_hierarchy_level(( SELECT auth.uid() AS uid), account_id, role))));
|
|
|
|
|
|
create policy "invitations_delete"
|
|
on "medreport"."invitations"
|
|
as permissive
|
|
for delete
|
|
to authenticated
|
|
using ((medreport.has_role_on_account(account_id) AND medreport.has_permission(( SELECT auth.uid() AS uid), account_id, 'invites.manage'::medreport.app_permissions)));
|
|
|
|
|
|
create policy "invitations_read_self"
|
|
on "medreport"."invitations"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using (medreport.has_role_on_account(account_id));
|
|
|
|
|
|
create policy "invitations_update"
|
|
on "medreport"."invitations"
|
|
as permissive
|
|
for update
|
|
to authenticated
|
|
using ((medreport.has_permission(( SELECT auth.uid() AS uid), account_id, 'invites.manage'::medreport.app_permissions) AND medreport.has_more_elevated_role(( SELECT auth.uid() AS uid), account_id, role)))
|
|
with check ((medreport.has_permission(( SELECT auth.uid() AS uid), account_id, 'invites.manage'::medreport.app_permissions) AND medreport.has_more_elevated_role(( SELECT auth.uid() AS uid), account_id, role)));
|
|
|
|
|
|
create policy "restrict_mfa_invitations"
|
|
on "medreport"."invitations"
|
|
as restrictive
|
|
for all
|
|
to authenticated
|
|
using (medreport.is_mfa_compliant());
|
|
|
|
|
|
create policy "super_admins_access_invitations"
|
|
on "medreport"."invitations"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using (medreport.is_super_admin());
|
|
|
|
|
|
create policy "read_all"
|
|
on "medreport"."medreport_product_groups"
|
|
as permissive
|
|
for select
|
|
to public
|
|
using (true);
|
|
|
|
|
|
create policy "Enable read access for all users"
|
|
on "medreport"."medreport_products_analyses_relations"
|
|
as permissive
|
|
for select
|
|
to public
|
|
using (true);
|
|
|
|
|
|
create policy "Users can read their own nonces"
|
|
on "medreport"."nonces"
|
|
as permissive
|
|
for select
|
|
to public
|
|
using ((user_id = ( SELECT auth.uid() AS uid)));
|
|
|
|
|
|
create policy "notifications_read_self"
|
|
on "medreport"."notifications"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using (((account_id = ( SELECT auth.uid() AS uid)) OR medreport.has_role_on_account(account_id)));
|
|
|
|
|
|
create policy "notifications_update_self"
|
|
on "medreport"."notifications"
|
|
as permissive
|
|
for update
|
|
to authenticated
|
|
using (((account_id = ( SELECT auth.uid() AS uid)) OR medreport.has_role_on_account(account_id)));
|
|
|
|
|
|
create policy "restrict_mfa_notifications"
|
|
on "medreport"."notifications"
|
|
as restrictive
|
|
for all
|
|
to authenticated
|
|
using (medreport.is_mfa_compliant());
|
|
|
|
|
|
create policy "order_items_read_self"
|
|
on "medreport"."order_items"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using ((EXISTS ( SELECT 1
|
|
FROM medreport.orders
|
|
WHERE ((orders.id = order_items.order_id) AND ((orders.account_id = ( SELECT auth.uid() AS uid)) OR medreport.has_role_on_account(orders.account_id))))));
|
|
|
|
|
|
create policy "restrict_mfa_order_items"
|
|
on "medreport"."order_items"
|
|
as restrictive
|
|
for all
|
|
to authenticated
|
|
using (medreport.is_mfa_compliant());
|
|
|
|
|
|
create policy "super_admins_access_order_items"
|
|
on "medreport"."order_items"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using (medreport.is_super_admin());
|
|
|
|
|
|
create policy "orders_read_self"
|
|
on "medreport"."orders"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using ((((account_id = ( SELECT auth.uid() AS uid)) AND medreport.is_set('enable_account_billing'::text)) OR (medreport.has_role_on_account(account_id) AND medreport.is_set('enable_team_account_billing'::text))));
|
|
|
|
|
|
create policy "restrict_mfa_orders"
|
|
on "medreport"."orders"
|
|
as restrictive
|
|
for all
|
|
to authenticated
|
|
using (medreport.is_mfa_compliant());
|
|
|
|
|
|
create policy "super_admins_access_orders"
|
|
on "medreport"."orders"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using (medreport.is_super_admin());
|
|
|
|
|
|
create policy "restrict_mfa_role_permissions"
|
|
on "medreport"."role_permissions"
|
|
as restrictive
|
|
for all
|
|
to authenticated
|
|
using (medreport.is_mfa_compliant());
|
|
|
|
|
|
create policy "role_permissions_read"
|
|
on "medreport"."role_permissions"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using (true);
|
|
|
|
|
|
create policy "super_admins_access_role_permissions"
|
|
on "medreport"."role_permissions"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using (medreport.is_super_admin());
|
|
|
|
|
|
create policy "roles_read"
|
|
on "medreport"."roles"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using (true);
|
|
|
|
|
|
create policy "restrict_mfa_subscription_items"
|
|
on "medreport"."subscription_items"
|
|
as restrictive
|
|
for all
|
|
to authenticated
|
|
using (medreport.is_mfa_compliant());
|
|
|
|
|
|
create policy "subscription_items_read_self"
|
|
on "medreport"."subscription_items"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using ((EXISTS ( SELECT 1
|
|
FROM medreport.subscriptions
|
|
WHERE ((subscriptions.id = subscription_items.subscription_id) AND ((subscriptions.account_id = ( SELECT auth.uid() AS uid)) OR medreport.has_role_on_account(subscriptions.account_id))))));
|
|
|
|
|
|
create policy "super_admins_access_subscription_items"
|
|
on "medreport"."subscription_items"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using (medreport.is_super_admin());
|
|
|
|
|
|
create policy "restrict_mfa_subscriptions"
|
|
on "medreport"."subscriptions"
|
|
as restrictive
|
|
for all
|
|
to authenticated
|
|
using (medreport.is_mfa_compliant());
|
|
|
|
|
|
create policy "subscriptions_read_self"
|
|
on "medreport"."subscriptions"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using (((medreport.has_role_on_account(account_id) AND medreport.is_set('enable_team_account_billing'::text)) OR ((account_id = ( SELECT auth.uid() AS uid)) AND medreport.is_set('enable_account_billing'::text))));
|
|
|
|
|
|
create policy "super_admins_access_subscriptions"
|
|
on "medreport"."subscriptions"
|
|
as permissive
|
|
for select
|
|
to authenticated
|
|
using (medreport.is_super_admin());
|
|
|
|
|
|
CREATE TRIGGER add_current_user_to_new_account AFTER INSERT ON medreport.accounts FOR EACH ROW WHEN ((new.is_personal_account = false)) EXECUTE FUNCTION kit.add_current_user_to_new_account();
|
|
|
|
CREATE TRIGGER protect_account_fields BEFORE UPDATE ON medreport.accounts FOR EACH ROW EXECUTE FUNCTION kit.protect_account_fields();
|
|
|
|
CREATE TRIGGER set_slug_from_account_name BEFORE INSERT ON medreport.accounts FOR EACH ROW WHEN (((new.name IS NOT NULL) AND (new.slug IS NULL) AND (new.is_personal_account = false))) EXECUTE FUNCTION kit.set_slug_from_account_name();
|
|
|
|
CREATE TRIGGER update_slug_from_account_name BEFORE UPDATE ON medreport.accounts FOR EACH ROW WHEN (((new.name IS NOT NULL) AND ((new.name)::text <> (old.name)::text) AND (new.is_personal_account = false))) EXECUTE FUNCTION kit.set_slug_from_account_name();
|
|
|
|
CREATE TRIGGER prevent_account_owner_membership_delete_check BEFORE DELETE ON medreport.accounts_memberships FOR EACH ROW EXECUTE FUNCTION kit.prevent_account_owner_membership_delete();
|
|
|
|
CREATE TRIGGER prevent_memberships_update_check BEFORE UPDATE ON medreport.accounts_memberships FOR EACH ROW EXECUTE FUNCTION kit.prevent_memberships_update();
|
|
|
|
CREATE TRIGGER analysis_change_record_timestamps AFTER INSERT OR DELETE OR UPDATE ON medreport.analyses FOR EACH ROW EXECUTE FUNCTION medreport.trigger_set_timestamps();
|
|
|
|
CREATE TRIGGER analysis_elements_change_record_timestamps AFTER INSERT OR DELETE OR UPDATE ON medreport.analysis_elements FOR EACH ROW EXECUTE FUNCTION medreport.trigger_set_timestamps();
|
|
|
|
CREATE TRIGGER analysis_groups_change_record_timestamps AFTER INSERT OR DELETE OR UPDATE ON medreport.analysis_groups FOR EACH ROW EXECUTE FUNCTION medreport.trigger_set_timestamps();
|
|
|
|
CREATE TRIGGER codes_change_record_timestamps AFTER INSERT OR DELETE OR UPDATE ON medreport.codes FOR EACH ROW EXECUTE FUNCTION medreport.trigger_set_timestamps();
|
|
|
|
CREATE TRIGGER connected_online_providers_change_record_timestamps AFTER INSERT OR UPDATE ON medreport.connected_online_providers FOR EACH ROW EXECUTE FUNCTION medreport.trigger_set_timestamps();
|
|
|
|
CREATE TRIGGER connected_online_services_change_record_timestamps AFTER INSERT OR UPDATE ON medreport.connected_online_services FOR EACH ROW EXECUTE FUNCTION medreport.trigger_set_timestamps();
|
|
|
|
CREATE TRIGGER only_team_accounts_check BEFORE INSERT OR UPDATE ON medreport.invitations FOR EACH ROW EXECUTE FUNCTION kit.check_team_account();
|
|
|
|
CREATE TRIGGER check_not_already_tied_to_connected_online BEFORE INSERT OR UPDATE ON medreport.medreport_products_analyses_relations FOR EACH ROW EXECUTE FUNCTION medreport.check_tied_to_connected_online();
|
|
|
|
CREATE TRIGGER check_not_already_tied_to_analysis BEFORE INSERT OR UPDATE ON medreport.medreport_products_external_services_relations FOR EACH ROW EXECUTE FUNCTION medreport.check_tied_to_analysis_item();
|
|
|
|
CREATE TRIGGER update_notification_dismissed_status BEFORE UPDATE ON medreport.notifications FOR EACH ROW EXECUTE FUNCTION kit.update_notification_dismissed_status();
|
|
|
|
|
|
revoke delete on table "public"."account_params" from "anon";
|
|
|
|
revoke insert on table "public"."account_params" from "anon";
|
|
|
|
revoke references on table "public"."account_params" from "anon";
|
|
|
|
revoke select on table "public"."account_params" from "anon";
|
|
|
|
revoke trigger on table "public"."account_params" from "anon";
|
|
|
|
revoke truncate on table "public"."account_params" from "anon";
|
|
|
|
revoke update on table "public"."account_params" from "anon";
|
|
|
|
revoke delete on table "public"."account_params" from "authenticated";
|
|
|
|
revoke insert on table "public"."account_params" from "authenticated";
|
|
|
|
revoke references on table "public"."account_params" from "authenticated";
|
|
|
|
revoke select on table "public"."account_params" from "authenticated";
|
|
|
|
revoke trigger on table "public"."account_params" from "authenticated";
|
|
|
|
revoke truncate on table "public"."account_params" from "authenticated";
|
|
|
|
revoke update on table "public"."account_params" from "authenticated";
|
|
|
|
revoke delete on table "public"."account_params" from "service_role";
|
|
|
|
revoke insert on table "public"."account_params" from "service_role";
|
|
|
|
revoke references on table "public"."account_params" from "service_role";
|
|
|
|
revoke select on table "public"."account_params" from "service_role";
|
|
|
|
revoke trigger on table "public"."account_params" from "service_role";
|
|
|
|
revoke truncate on table "public"."account_params" from "service_role";
|
|
|
|
revoke update on table "public"."account_params" from "service_role";
|
|
|
|
revoke delete on table "public"."accounts" from "anon";
|
|
|
|
revoke insert on table "public"."accounts" from "anon";
|
|
|
|
revoke references on table "public"."accounts" from "anon";
|
|
|
|
revoke select on table "public"."accounts" from "anon";
|
|
|
|
revoke trigger on table "public"."accounts" from "anon";
|
|
|
|
revoke truncate on table "public"."accounts" from "anon";
|
|
|
|
revoke update on table "public"."accounts" from "anon";
|
|
|
|
revoke delete on table "public"."accounts" from "authenticated";
|
|
|
|
revoke insert on table "public"."accounts" from "authenticated";
|
|
|
|
revoke select on table "public"."accounts" from "authenticated";
|
|
|
|
revoke update on table "public"."accounts" from "authenticated";
|
|
|
|
revoke delete on table "public"."accounts" from "service_role";
|
|
|
|
revoke insert on table "public"."accounts" from "service_role";
|
|
|
|
revoke select on table "public"."accounts" from "service_role";
|
|
|
|
revoke update on table "public"."accounts" from "service_role";
|
|
|
|
revoke delete on table "public"."accounts_memberships" from "anon";
|
|
|
|
revoke insert on table "public"."accounts_memberships" from "anon";
|
|
|
|
revoke references on table "public"."accounts_memberships" from "anon";
|
|
|
|
revoke select on table "public"."accounts_memberships" from "anon";
|
|
|
|
revoke trigger on table "public"."accounts_memberships" from "anon";
|
|
|
|
revoke truncate on table "public"."accounts_memberships" from "anon";
|
|
|
|
revoke update on table "public"."accounts_memberships" from "anon";
|
|
|
|
revoke delete on table "public"."accounts_memberships" from "authenticated";
|
|
|
|
revoke insert on table "public"."accounts_memberships" from "authenticated";
|
|
|
|
revoke select on table "public"."accounts_memberships" from "authenticated";
|
|
|
|
revoke update on table "public"."accounts_memberships" from "authenticated";
|
|
|
|
revoke delete on table "public"."accounts_memberships" from "service_role";
|
|
|
|
revoke insert on table "public"."accounts_memberships" from "service_role";
|
|
|
|
revoke select on table "public"."accounts_memberships" from "service_role";
|
|
|
|
revoke update on table "public"."accounts_memberships" from "service_role";
|
|
|
|
revoke delete on table "public"."analyses" from "anon";
|
|
|
|
revoke insert on table "public"."analyses" from "anon";
|
|
|
|
revoke references on table "public"."analyses" from "anon";
|
|
|
|
revoke select on table "public"."analyses" from "anon";
|
|
|
|
revoke trigger on table "public"."analyses" from "anon";
|
|
|
|
revoke truncate on table "public"."analyses" from "anon";
|
|
|
|
revoke update on table "public"."analyses" from "anon";
|
|
|
|
revoke delete on table "public"."analyses" from "authenticated";
|
|
|
|
revoke insert on table "public"."analyses" from "authenticated";
|
|
|
|
revoke references on table "public"."analyses" from "authenticated";
|
|
|
|
revoke select on table "public"."analyses" from "authenticated";
|
|
|
|
revoke trigger on table "public"."analyses" from "authenticated";
|
|
|
|
revoke truncate on table "public"."analyses" from "authenticated";
|
|
|
|
revoke update on table "public"."analyses" from "authenticated";
|
|
|
|
revoke delete on table "public"."analyses" from "service_role";
|
|
|
|
revoke insert on table "public"."analyses" from "service_role";
|
|
|
|
revoke references on table "public"."analyses" from "service_role";
|
|
|
|
revoke select on table "public"."analyses" from "service_role";
|
|
|
|
revoke trigger on table "public"."analyses" from "service_role";
|
|
|
|
revoke truncate on table "public"."analyses" from "service_role";
|
|
|
|
revoke update on table "public"."analyses" from "service_role";
|
|
|
|
revoke delete on table "public"."analysis_elements" from "anon";
|
|
|
|
revoke insert on table "public"."analysis_elements" from "anon";
|
|
|
|
revoke references on table "public"."analysis_elements" from "anon";
|
|
|
|
revoke select on table "public"."analysis_elements" from "anon";
|
|
|
|
revoke trigger on table "public"."analysis_elements" from "anon";
|
|
|
|
revoke truncate on table "public"."analysis_elements" from "anon";
|
|
|
|
revoke update on table "public"."analysis_elements" from "anon";
|
|
|
|
revoke delete on table "public"."analysis_elements" from "authenticated";
|
|
|
|
revoke insert on table "public"."analysis_elements" from "authenticated";
|
|
|
|
revoke references on table "public"."analysis_elements" from "authenticated";
|
|
|
|
revoke select on table "public"."analysis_elements" from "authenticated";
|
|
|
|
revoke trigger on table "public"."analysis_elements" from "authenticated";
|
|
|
|
revoke truncate on table "public"."analysis_elements" from "authenticated";
|
|
|
|
revoke update on table "public"."analysis_elements" from "authenticated";
|
|
|
|
revoke delete on table "public"."analysis_elements" from "service_role";
|
|
|
|
revoke insert on table "public"."analysis_elements" from "service_role";
|
|
|
|
revoke references on table "public"."analysis_elements" from "service_role";
|
|
|
|
revoke select on table "public"."analysis_elements" from "service_role";
|
|
|
|
revoke trigger on table "public"."analysis_elements" from "service_role";
|
|
|
|
revoke truncate on table "public"."analysis_elements" from "service_role";
|
|
|
|
revoke update on table "public"."analysis_elements" from "service_role";
|
|
|
|
revoke delete on table "public"."analysis_groups" from "anon";
|
|
|
|
revoke insert on table "public"."analysis_groups" from "anon";
|
|
|
|
revoke references on table "public"."analysis_groups" from "anon";
|
|
|
|
revoke select on table "public"."analysis_groups" from "anon";
|
|
|
|
revoke trigger on table "public"."analysis_groups" from "anon";
|
|
|
|
revoke truncate on table "public"."analysis_groups" from "anon";
|
|
|
|
revoke update on table "public"."analysis_groups" from "anon";
|
|
|
|
revoke delete on table "public"."analysis_groups" from "authenticated";
|
|
|
|
revoke insert on table "public"."analysis_groups" from "authenticated";
|
|
|
|
revoke references on table "public"."analysis_groups" from "authenticated";
|
|
|
|
revoke select on table "public"."analysis_groups" from "authenticated";
|
|
|
|
revoke trigger on table "public"."analysis_groups" from "authenticated";
|
|
|
|
revoke truncate on table "public"."analysis_groups" from "authenticated";
|
|
|
|
revoke update on table "public"."analysis_groups" from "authenticated";
|
|
|
|
revoke delete on table "public"."analysis_groups" from "service_role";
|
|
|
|
revoke insert on table "public"."analysis_groups" from "service_role";
|
|
|
|
revoke references on table "public"."analysis_groups" from "service_role";
|
|
|
|
revoke select on table "public"."analysis_groups" from "service_role";
|
|
|
|
revoke trigger on table "public"."analysis_groups" from "service_role";
|
|
|
|
revoke truncate on table "public"."analysis_groups" from "service_role";
|
|
|
|
revoke update on table "public"."analysis_groups" from "service_role";
|
|
|
|
revoke delete on table "public"."analysis_orders" from "anon";
|
|
|
|
revoke insert on table "public"."analysis_orders" from "anon";
|
|
|
|
revoke references on table "public"."analysis_orders" from "anon";
|
|
|
|
revoke select on table "public"."analysis_orders" from "anon";
|
|
|
|
revoke trigger on table "public"."analysis_orders" from "anon";
|
|
|
|
revoke truncate on table "public"."analysis_orders" from "anon";
|
|
|
|
revoke update on table "public"."analysis_orders" from "anon";
|
|
|
|
revoke delete on table "public"."analysis_orders" from "authenticated";
|
|
|
|
revoke insert on table "public"."analysis_orders" from "authenticated";
|
|
|
|
revoke references on table "public"."analysis_orders" from "authenticated";
|
|
|
|
revoke select on table "public"."analysis_orders" from "authenticated";
|
|
|
|
revoke trigger on table "public"."analysis_orders" from "authenticated";
|
|
|
|
revoke truncate on table "public"."analysis_orders" from "authenticated";
|
|
|
|
revoke update on table "public"."analysis_orders" from "authenticated";
|
|
|
|
revoke delete on table "public"."analysis_orders" from "service_role";
|
|
|
|
revoke insert on table "public"."analysis_orders" from "service_role";
|
|
|
|
revoke references on table "public"."analysis_orders" from "service_role";
|
|
|
|
revoke select on table "public"."analysis_orders" from "service_role";
|
|
|
|
revoke trigger on table "public"."analysis_orders" from "service_role";
|
|
|
|
revoke truncate on table "public"."analysis_orders" from "service_role";
|
|
|
|
revoke update on table "public"."analysis_orders" from "service_role";
|
|
|
|
revoke delete on table "public"."analysis_response_elements" from "anon";
|
|
|
|
revoke insert on table "public"."analysis_response_elements" from "anon";
|
|
|
|
revoke references on table "public"."analysis_response_elements" from "anon";
|
|
|
|
revoke select on table "public"."analysis_response_elements" from "anon";
|
|
|
|
revoke trigger on table "public"."analysis_response_elements" from "anon";
|
|
|
|
revoke truncate on table "public"."analysis_response_elements" from "anon";
|
|
|
|
revoke update on table "public"."analysis_response_elements" from "anon";
|
|
|
|
revoke delete on table "public"."analysis_response_elements" from "authenticated";
|
|
|
|
revoke insert on table "public"."analysis_response_elements" from "authenticated";
|
|
|
|
revoke references on table "public"."analysis_response_elements" from "authenticated";
|
|
|
|
revoke select on table "public"."analysis_response_elements" from "authenticated";
|
|
|
|
revoke trigger on table "public"."analysis_response_elements" from "authenticated";
|
|
|
|
revoke truncate on table "public"."analysis_response_elements" from "authenticated";
|
|
|
|
revoke update on table "public"."analysis_response_elements" from "authenticated";
|
|
|
|
revoke delete on table "public"."analysis_response_elements" from "service_role";
|
|
|
|
revoke insert on table "public"."analysis_response_elements" from "service_role";
|
|
|
|
revoke references on table "public"."analysis_response_elements" from "service_role";
|
|
|
|
revoke select on table "public"."analysis_response_elements" from "service_role";
|
|
|
|
revoke trigger on table "public"."analysis_response_elements" from "service_role";
|
|
|
|
revoke truncate on table "public"."analysis_response_elements" from "service_role";
|
|
|
|
revoke update on table "public"."analysis_response_elements" from "service_role";
|
|
|
|
revoke delete on table "public"."analysis_responses" from "anon";
|
|
|
|
revoke insert on table "public"."analysis_responses" from "anon";
|
|
|
|
revoke references on table "public"."analysis_responses" from "anon";
|
|
|
|
revoke select on table "public"."analysis_responses" from "anon";
|
|
|
|
revoke trigger on table "public"."analysis_responses" from "anon";
|
|
|
|
revoke truncate on table "public"."analysis_responses" from "anon";
|
|
|
|
revoke update on table "public"."analysis_responses" from "anon";
|
|
|
|
revoke delete on table "public"."analysis_responses" from "authenticated";
|
|
|
|
revoke insert on table "public"."analysis_responses" from "authenticated";
|
|
|
|
revoke references on table "public"."analysis_responses" from "authenticated";
|
|
|
|
revoke select on table "public"."analysis_responses" from "authenticated";
|
|
|
|
revoke trigger on table "public"."analysis_responses" from "authenticated";
|
|
|
|
revoke truncate on table "public"."analysis_responses" from "authenticated";
|
|
|
|
revoke update on table "public"."analysis_responses" from "authenticated";
|
|
|
|
revoke delete on table "public"."analysis_responses" from "service_role";
|
|
|
|
revoke insert on table "public"."analysis_responses" from "service_role";
|
|
|
|
revoke references on table "public"."analysis_responses" from "service_role";
|
|
|
|
revoke select on table "public"."analysis_responses" from "service_role";
|
|
|
|
revoke trigger on table "public"."analysis_responses" from "service_role";
|
|
|
|
revoke truncate on table "public"."analysis_responses" from "service_role";
|
|
|
|
revoke update on table "public"."analysis_responses" from "service_role";
|
|
|
|
revoke delete on table "public"."billing_customers" from "anon";
|
|
|
|
revoke insert on table "public"."billing_customers" from "anon";
|
|
|
|
revoke references on table "public"."billing_customers" from "anon";
|
|
|
|
revoke select on table "public"."billing_customers" from "anon";
|
|
|
|
revoke trigger on table "public"."billing_customers" from "anon";
|
|
|
|
revoke truncate on table "public"."billing_customers" from "anon";
|
|
|
|
revoke update on table "public"."billing_customers" from "anon";
|
|
|
|
revoke select on table "public"."billing_customers" from "authenticated";
|
|
|
|
revoke delete on table "public"."billing_customers" from "service_role";
|
|
|
|
revoke insert on table "public"."billing_customers" from "service_role";
|
|
|
|
revoke select on table "public"."billing_customers" from "service_role";
|
|
|
|
revoke update on table "public"."billing_customers" from "service_role";
|
|
|
|
revoke delete on table "public"."codes" from "anon";
|
|
|
|
revoke insert on table "public"."codes" from "anon";
|
|
|
|
revoke references on table "public"."codes" from "anon";
|
|
|
|
revoke select on table "public"."codes" from "anon";
|
|
|
|
revoke trigger on table "public"."codes" from "anon";
|
|
|
|
revoke truncate on table "public"."codes" from "anon";
|
|
|
|
revoke update on table "public"."codes" from "anon";
|
|
|
|
revoke delete on table "public"."codes" from "authenticated";
|
|
|
|
revoke insert on table "public"."codes" from "authenticated";
|
|
|
|
revoke references on table "public"."codes" from "authenticated";
|
|
|
|
revoke select on table "public"."codes" from "authenticated";
|
|
|
|
revoke trigger on table "public"."codes" from "authenticated";
|
|
|
|
revoke truncate on table "public"."codes" from "authenticated";
|
|
|
|
revoke update on table "public"."codes" from "authenticated";
|
|
|
|
revoke delete on table "public"."codes" from "service_role";
|
|
|
|
revoke insert on table "public"."codes" from "service_role";
|
|
|
|
revoke references on table "public"."codes" from "service_role";
|
|
|
|
revoke select on table "public"."codes" from "service_role";
|
|
|
|
revoke trigger on table "public"."codes" from "service_role";
|
|
|
|
revoke truncate on table "public"."codes" from "service_role";
|
|
|
|
revoke update on table "public"."codes" from "service_role";
|
|
|
|
revoke delete on table "public"."config" from "anon";
|
|
|
|
revoke insert on table "public"."config" from "anon";
|
|
|
|
revoke references on table "public"."config" from "anon";
|
|
|
|
revoke select on table "public"."config" from "anon";
|
|
|
|
revoke trigger on table "public"."config" from "anon";
|
|
|
|
revoke truncate on table "public"."config" from "anon";
|
|
|
|
revoke update on table "public"."config" from "anon";
|
|
|
|
revoke select on table "public"."config" from "authenticated";
|
|
|
|
revoke select on table "public"."config" from "service_role";
|
|
|
|
revoke delete on table "public"."connected_online_providers" from "anon";
|
|
|
|
revoke insert on table "public"."connected_online_providers" from "anon";
|
|
|
|
revoke references on table "public"."connected_online_providers" from "anon";
|
|
|
|
revoke select on table "public"."connected_online_providers" from "anon";
|
|
|
|
revoke trigger on table "public"."connected_online_providers" from "anon";
|
|
|
|
revoke truncate on table "public"."connected_online_providers" from "anon";
|
|
|
|
revoke update on table "public"."connected_online_providers" from "anon";
|
|
|
|
revoke delete on table "public"."connected_online_providers" from "authenticated";
|
|
|
|
revoke insert on table "public"."connected_online_providers" from "authenticated";
|
|
|
|
revoke references on table "public"."connected_online_providers" from "authenticated";
|
|
|
|
revoke select on table "public"."connected_online_providers" from "authenticated";
|
|
|
|
revoke trigger on table "public"."connected_online_providers" from "authenticated";
|
|
|
|
revoke truncate on table "public"."connected_online_providers" from "authenticated";
|
|
|
|
revoke update on table "public"."connected_online_providers" from "authenticated";
|
|
|
|
revoke delete on table "public"."connected_online_providers" from "service_role";
|
|
|
|
revoke insert on table "public"."connected_online_providers" from "service_role";
|
|
|
|
revoke references on table "public"."connected_online_providers" from "service_role";
|
|
|
|
revoke select on table "public"."connected_online_providers" from "service_role";
|
|
|
|
revoke trigger on table "public"."connected_online_providers" from "service_role";
|
|
|
|
revoke truncate on table "public"."connected_online_providers" from "service_role";
|
|
|
|
revoke update on table "public"."connected_online_providers" from "service_role";
|
|
|
|
revoke delete on table "public"."connected_online_reservation" from "anon";
|
|
|
|
revoke insert on table "public"."connected_online_reservation" from "anon";
|
|
|
|
revoke references on table "public"."connected_online_reservation" from "anon";
|
|
|
|
revoke select on table "public"."connected_online_reservation" from "anon";
|
|
|
|
revoke trigger on table "public"."connected_online_reservation" from "anon";
|
|
|
|
revoke truncate on table "public"."connected_online_reservation" from "anon";
|
|
|
|
revoke update on table "public"."connected_online_reservation" from "anon";
|
|
|
|
revoke delete on table "public"."connected_online_reservation" from "authenticated";
|
|
|
|
revoke insert on table "public"."connected_online_reservation" from "authenticated";
|
|
|
|
revoke references on table "public"."connected_online_reservation" from "authenticated";
|
|
|
|
revoke select on table "public"."connected_online_reservation" from "authenticated";
|
|
|
|
revoke trigger on table "public"."connected_online_reservation" from "authenticated";
|
|
|
|
revoke truncate on table "public"."connected_online_reservation" from "authenticated";
|
|
|
|
revoke update on table "public"."connected_online_reservation" from "authenticated";
|
|
|
|
revoke delete on table "public"."connected_online_reservation" from "service_role";
|
|
|
|
revoke insert on table "public"."connected_online_reservation" from "service_role";
|
|
|
|
revoke references on table "public"."connected_online_reservation" from "service_role";
|
|
|
|
revoke select on table "public"."connected_online_reservation" from "service_role";
|
|
|
|
revoke trigger on table "public"."connected_online_reservation" from "service_role";
|
|
|
|
revoke truncate on table "public"."connected_online_reservation" from "service_role";
|
|
|
|
revoke update on table "public"."connected_online_reservation" from "service_role";
|
|
|
|
revoke delete on table "public"."connected_online_services" from "anon";
|
|
|
|
revoke insert on table "public"."connected_online_services" from "anon";
|
|
|
|
revoke references on table "public"."connected_online_services" from "anon";
|
|
|
|
revoke select on table "public"."connected_online_services" from "anon";
|
|
|
|
revoke trigger on table "public"."connected_online_services" from "anon";
|
|
|
|
revoke truncate on table "public"."connected_online_services" from "anon";
|
|
|
|
revoke update on table "public"."connected_online_services" from "anon";
|
|
|
|
revoke delete on table "public"."connected_online_services" from "authenticated";
|
|
|
|
revoke insert on table "public"."connected_online_services" from "authenticated";
|
|
|
|
revoke references on table "public"."connected_online_services" from "authenticated";
|
|
|
|
revoke select on table "public"."connected_online_services" from "authenticated";
|
|
|
|
revoke trigger on table "public"."connected_online_services" from "authenticated";
|
|
|
|
revoke truncate on table "public"."connected_online_services" from "authenticated";
|
|
|
|
revoke update on table "public"."connected_online_services" from "authenticated";
|
|
|
|
revoke delete on table "public"."connected_online_services" from "service_role";
|
|
|
|
revoke insert on table "public"."connected_online_services" from "service_role";
|
|
|
|
revoke references on table "public"."connected_online_services" from "service_role";
|
|
|
|
revoke select on table "public"."connected_online_services" from "service_role";
|
|
|
|
revoke trigger on table "public"."connected_online_services" from "service_role";
|
|
|
|
revoke truncate on table "public"."connected_online_services" from "service_role";
|
|
|
|
revoke update on table "public"."connected_online_services" from "service_role";
|
|
|
|
revoke delete on table "public"."invitations" from "anon";
|
|
|
|
revoke insert on table "public"."invitations" from "anon";
|
|
|
|
revoke references on table "public"."invitations" from "anon";
|
|
|
|
revoke select on table "public"."invitations" from "anon";
|
|
|
|
revoke trigger on table "public"."invitations" from "anon";
|
|
|
|
revoke truncate on table "public"."invitations" from "anon";
|
|
|
|
revoke update on table "public"."invitations" from "anon";
|
|
|
|
revoke delete on table "public"."invitations" from "authenticated";
|
|
|
|
revoke insert on table "public"."invitations" from "authenticated";
|
|
|
|
revoke select on table "public"."invitations" from "authenticated";
|
|
|
|
revoke update on table "public"."invitations" from "authenticated";
|
|
|
|
revoke delete on table "public"."invitations" from "service_role";
|
|
|
|
revoke insert on table "public"."invitations" from "service_role";
|
|
|
|
revoke select on table "public"."invitations" from "service_role";
|
|
|
|
revoke update on table "public"."invitations" from "service_role";
|
|
|
|
revoke delete on table "public"."medreport_product_groups" from "anon";
|
|
|
|
revoke insert on table "public"."medreport_product_groups" from "anon";
|
|
|
|
revoke references on table "public"."medreport_product_groups" from "anon";
|
|
|
|
revoke select on table "public"."medreport_product_groups" from "anon";
|
|
|
|
revoke trigger on table "public"."medreport_product_groups" from "anon";
|
|
|
|
revoke truncate on table "public"."medreport_product_groups" from "anon";
|
|
|
|
revoke update on table "public"."medreport_product_groups" from "anon";
|
|
|
|
revoke delete on table "public"."medreport_product_groups" from "authenticated";
|
|
|
|
revoke insert on table "public"."medreport_product_groups" from "authenticated";
|
|
|
|
revoke references on table "public"."medreport_product_groups" from "authenticated";
|
|
|
|
revoke select on table "public"."medreport_product_groups" from "authenticated";
|
|
|
|
revoke trigger on table "public"."medreport_product_groups" from "authenticated";
|
|
|
|
revoke truncate on table "public"."medreport_product_groups" from "authenticated";
|
|
|
|
revoke update on table "public"."medreport_product_groups" from "authenticated";
|
|
|
|
revoke delete on table "public"."medreport_product_groups" from "service_role";
|
|
|
|
revoke insert on table "public"."medreport_product_groups" from "service_role";
|
|
|
|
revoke references on table "public"."medreport_product_groups" from "service_role";
|
|
|
|
revoke select on table "public"."medreport_product_groups" from "service_role";
|
|
|
|
revoke trigger on table "public"."medreport_product_groups" from "service_role";
|
|
|
|
revoke truncate on table "public"."medreport_product_groups" from "service_role";
|
|
|
|
revoke update on table "public"."medreport_product_groups" from "service_role";
|
|
|
|
revoke delete on table "public"."medreport_products" from "anon";
|
|
|
|
revoke insert on table "public"."medreport_products" from "anon";
|
|
|
|
revoke references on table "public"."medreport_products" from "anon";
|
|
|
|
revoke select on table "public"."medreport_products" from "anon";
|
|
|
|
revoke trigger on table "public"."medreport_products" from "anon";
|
|
|
|
revoke truncate on table "public"."medreport_products" from "anon";
|
|
|
|
revoke update on table "public"."medreport_products" from "anon";
|
|
|
|
revoke delete on table "public"."medreport_products" from "authenticated";
|
|
|
|
revoke insert on table "public"."medreport_products" from "authenticated";
|
|
|
|
revoke references on table "public"."medreport_products" from "authenticated";
|
|
|
|
revoke select on table "public"."medreport_products" from "authenticated";
|
|
|
|
revoke trigger on table "public"."medreport_products" from "authenticated";
|
|
|
|
revoke truncate on table "public"."medreport_products" from "authenticated";
|
|
|
|
revoke update on table "public"."medreport_products" from "authenticated";
|
|
|
|
revoke delete on table "public"."medreport_products" from "service_role";
|
|
|
|
revoke insert on table "public"."medreport_products" from "service_role";
|
|
|
|
revoke references on table "public"."medreport_products" from "service_role";
|
|
|
|
revoke select on table "public"."medreport_products" from "service_role";
|
|
|
|
revoke trigger on table "public"."medreport_products" from "service_role";
|
|
|
|
revoke truncate on table "public"."medreport_products" from "service_role";
|
|
|
|
revoke update on table "public"."medreport_products" from "service_role";
|
|
|
|
revoke delete on table "public"."medreport_products_analyses_relations" from "anon";
|
|
|
|
revoke insert on table "public"."medreport_products_analyses_relations" from "anon";
|
|
|
|
revoke references on table "public"."medreport_products_analyses_relations" from "anon";
|
|
|
|
revoke select on table "public"."medreport_products_analyses_relations" from "anon";
|
|
|
|
revoke trigger on table "public"."medreport_products_analyses_relations" from "anon";
|
|
|
|
revoke truncate on table "public"."medreport_products_analyses_relations" from "anon";
|
|
|
|
revoke update on table "public"."medreport_products_analyses_relations" from "anon";
|
|
|
|
revoke delete on table "public"."medreport_products_analyses_relations" from "authenticated";
|
|
|
|
revoke insert on table "public"."medreport_products_analyses_relations" from "authenticated";
|
|
|
|
revoke references on table "public"."medreport_products_analyses_relations" from "authenticated";
|
|
|
|
revoke select on table "public"."medreport_products_analyses_relations" from "authenticated";
|
|
|
|
revoke trigger on table "public"."medreport_products_analyses_relations" from "authenticated";
|
|
|
|
revoke truncate on table "public"."medreport_products_analyses_relations" from "authenticated";
|
|
|
|
revoke update on table "public"."medreport_products_analyses_relations" from "authenticated";
|
|
|
|
revoke delete on table "public"."medreport_products_analyses_relations" from "service_role";
|
|
|
|
revoke insert on table "public"."medreport_products_analyses_relations" from "service_role";
|
|
|
|
revoke references on table "public"."medreport_products_analyses_relations" from "service_role";
|
|
|
|
revoke select on table "public"."medreport_products_analyses_relations" from "service_role";
|
|
|
|
revoke trigger on table "public"."medreport_products_analyses_relations" from "service_role";
|
|
|
|
revoke truncate on table "public"."medreport_products_analyses_relations" from "service_role";
|
|
|
|
revoke update on table "public"."medreport_products_analyses_relations" from "service_role";
|
|
|
|
revoke delete on table "public"."medreport_products_external_services_relations" from "anon";
|
|
|
|
revoke insert on table "public"."medreport_products_external_services_relations" from "anon";
|
|
|
|
revoke references on table "public"."medreport_products_external_services_relations" from "anon";
|
|
|
|
revoke select on table "public"."medreport_products_external_services_relations" from "anon";
|
|
|
|
revoke trigger on table "public"."medreport_products_external_services_relations" from "anon";
|
|
|
|
revoke truncate on table "public"."medreport_products_external_services_relations" from "anon";
|
|
|
|
revoke update on table "public"."medreport_products_external_services_relations" from "anon";
|
|
|
|
revoke delete on table "public"."medreport_products_external_services_relations" from "authenticated";
|
|
|
|
revoke insert on table "public"."medreport_products_external_services_relations" from "authenticated";
|
|
|
|
revoke references on table "public"."medreport_products_external_services_relations" from "authenticated";
|
|
|
|
revoke select on table "public"."medreport_products_external_services_relations" from "authenticated";
|
|
|
|
revoke trigger on table "public"."medreport_products_external_services_relations" from "authenticated";
|
|
|
|
revoke truncate on table "public"."medreport_products_external_services_relations" from "authenticated";
|
|
|
|
revoke update on table "public"."medreport_products_external_services_relations" from "authenticated";
|
|
|
|
revoke delete on table "public"."medreport_products_external_services_relations" from "service_role";
|
|
|
|
revoke insert on table "public"."medreport_products_external_services_relations" from "service_role";
|
|
|
|
revoke references on table "public"."medreport_products_external_services_relations" from "service_role";
|
|
|
|
revoke select on table "public"."medreport_products_external_services_relations" from "service_role";
|
|
|
|
revoke trigger on table "public"."medreport_products_external_services_relations" from "service_role";
|
|
|
|
revoke truncate on table "public"."medreport_products_external_services_relations" from "service_role";
|
|
|
|
revoke update on table "public"."medreport_products_external_services_relations" from "service_role";
|
|
|
|
revoke delete on table "public"."nonces" from "anon";
|
|
|
|
revoke insert on table "public"."nonces" from "anon";
|
|
|
|
revoke references on table "public"."nonces" from "anon";
|
|
|
|
revoke select on table "public"."nonces" from "anon";
|
|
|
|
revoke trigger on table "public"."nonces" from "anon";
|
|
|
|
revoke truncate on table "public"."nonces" from "anon";
|
|
|
|
revoke update on table "public"."nonces" from "anon";
|
|
|
|
revoke delete on table "public"."nonces" from "authenticated";
|
|
|
|
revoke insert on table "public"."nonces" from "authenticated";
|
|
|
|
revoke references on table "public"."nonces" from "authenticated";
|
|
|
|
revoke select on table "public"."nonces" from "authenticated";
|
|
|
|
revoke trigger on table "public"."nonces" from "authenticated";
|
|
|
|
revoke truncate on table "public"."nonces" from "authenticated";
|
|
|
|
revoke update on table "public"."nonces" from "authenticated";
|
|
|
|
revoke delete on table "public"."nonces" from "service_role";
|
|
|
|
revoke insert on table "public"."nonces" from "service_role";
|
|
|
|
revoke references on table "public"."nonces" from "service_role";
|
|
|
|
revoke select on table "public"."nonces" from "service_role";
|
|
|
|
revoke trigger on table "public"."nonces" from "service_role";
|
|
|
|
revoke truncate on table "public"."nonces" from "service_role";
|
|
|
|
revoke update on table "public"."nonces" from "service_role";
|
|
|
|
revoke delete on table "public"."notifications" from "anon";
|
|
|
|
revoke insert on table "public"."notifications" from "anon";
|
|
|
|
revoke references on table "public"."notifications" from "anon";
|
|
|
|
revoke select on table "public"."notifications" from "anon";
|
|
|
|
revoke trigger on table "public"."notifications" from "anon";
|
|
|
|
revoke truncate on table "public"."notifications" from "anon";
|
|
|
|
revoke update on table "public"."notifications" from "anon";
|
|
|
|
revoke select on table "public"."notifications" from "authenticated";
|
|
|
|
revoke update on table "public"."notifications" from "authenticated";
|
|
|
|
revoke insert on table "public"."notifications" from "service_role";
|
|
|
|
revoke select on table "public"."notifications" from "service_role";
|
|
|
|
revoke update on table "public"."notifications" from "service_role";
|
|
|
|
revoke delete on table "public"."order_items" from "anon";
|
|
|
|
revoke insert on table "public"."order_items" from "anon";
|
|
|
|
revoke references on table "public"."order_items" from "anon";
|
|
|
|
revoke select on table "public"."order_items" from "anon";
|
|
|
|
revoke trigger on table "public"."order_items" from "anon";
|
|
|
|
revoke truncate on table "public"."order_items" from "anon";
|
|
|
|
revoke update on table "public"."order_items" from "anon";
|
|
|
|
revoke select on table "public"."order_items" from "authenticated";
|
|
|
|
revoke delete on table "public"."order_items" from "service_role";
|
|
|
|
revoke insert on table "public"."order_items" from "service_role";
|
|
|
|
revoke select on table "public"."order_items" from "service_role";
|
|
|
|
revoke update on table "public"."order_items" from "service_role";
|
|
|
|
revoke delete on table "public"."orders" from "anon";
|
|
|
|
revoke insert on table "public"."orders" from "anon";
|
|
|
|
revoke references on table "public"."orders" from "anon";
|
|
|
|
revoke select on table "public"."orders" from "anon";
|
|
|
|
revoke trigger on table "public"."orders" from "anon";
|
|
|
|
revoke truncate on table "public"."orders" from "anon";
|
|
|
|
revoke update on table "public"."orders" from "anon";
|
|
|
|
revoke select on table "public"."orders" from "authenticated";
|
|
|
|
revoke delete on table "public"."orders" from "service_role";
|
|
|
|
revoke insert on table "public"."orders" from "service_role";
|
|
|
|
revoke select on table "public"."orders" from "service_role";
|
|
|
|
revoke update on table "public"."orders" from "service_role";
|
|
|
|
revoke delete on table "public"."role_permissions" from "anon";
|
|
|
|
revoke insert on table "public"."role_permissions" from "anon";
|
|
|
|
revoke references on table "public"."role_permissions" from "anon";
|
|
|
|
revoke select on table "public"."role_permissions" from "anon";
|
|
|
|
revoke trigger on table "public"."role_permissions" from "anon";
|
|
|
|
revoke truncate on table "public"."role_permissions" from "anon";
|
|
|
|
revoke update on table "public"."role_permissions" from "anon";
|
|
|
|
revoke select on table "public"."role_permissions" from "authenticated";
|
|
|
|
revoke delete on table "public"."role_permissions" from "service_role";
|
|
|
|
revoke insert on table "public"."role_permissions" from "service_role";
|
|
|
|
revoke select on table "public"."role_permissions" from "service_role";
|
|
|
|
revoke update on table "public"."role_permissions" from "service_role";
|
|
|
|
revoke delete on table "public"."roles" from "anon";
|
|
|
|
revoke insert on table "public"."roles" from "anon";
|
|
|
|
revoke references on table "public"."roles" from "anon";
|
|
|
|
revoke select on table "public"."roles" from "anon";
|
|
|
|
revoke trigger on table "public"."roles" from "anon";
|
|
|
|
revoke truncate on table "public"."roles" from "anon";
|
|
|
|
revoke update on table "public"."roles" from "anon";
|
|
|
|
revoke select on table "public"."roles" from "authenticated";
|
|
|
|
revoke select on table "public"."roles" from "service_role";
|
|
|
|
revoke delete on table "public"."subscription_items" from "anon";
|
|
|
|
revoke insert on table "public"."subscription_items" from "anon";
|
|
|
|
revoke references on table "public"."subscription_items" from "anon";
|
|
|
|
revoke select on table "public"."subscription_items" from "anon";
|
|
|
|
revoke trigger on table "public"."subscription_items" from "anon";
|
|
|
|
revoke truncate on table "public"."subscription_items" from "anon";
|
|
|
|
revoke update on table "public"."subscription_items" from "anon";
|
|
|
|
revoke select on table "public"."subscription_items" from "authenticated";
|
|
|
|
revoke delete on table "public"."subscription_items" from "service_role";
|
|
|
|
revoke insert on table "public"."subscription_items" from "service_role";
|
|
|
|
revoke select on table "public"."subscription_items" from "service_role";
|
|
|
|
revoke update on table "public"."subscription_items" from "service_role";
|
|
|
|
revoke delete on table "public"."subscriptions" from "anon";
|
|
|
|
revoke insert on table "public"."subscriptions" from "anon";
|
|
|
|
revoke references on table "public"."subscriptions" from "anon";
|
|
|
|
revoke select on table "public"."subscriptions" from "anon";
|
|
|
|
revoke trigger on table "public"."subscriptions" from "anon";
|
|
|
|
revoke truncate on table "public"."subscriptions" from "anon";
|
|
|
|
revoke update on table "public"."subscriptions" from "anon";
|
|
|
|
revoke select on table "public"."subscriptions" from "authenticated";
|
|
|
|
revoke delete on table "public"."subscriptions" from "service_role";
|
|
|
|
revoke insert on table "public"."subscriptions" from "service_role";
|
|
|
|
revoke select on table "public"."subscriptions" from "service_role";
|
|
|
|
revoke update on table "public"."subscriptions" from "service_role";
|
|
|
|
drop view if exists "public"."user_account_workspace";
|
|
|
|
drop view if exists "public"."user_accounts";
|
|
|
|
drop function if exists "public"."verify_nonce"(p_token text, p_purpose text, p_user_id uuid, p_required_scopes text[], p_max_verification_attempts integer, p_ip inet, p_user_agent text);
|
|
|
|
drop table "public"."account_params";
|
|
|
|
drop table "public"."accounts";
|
|
|
|
drop table "public"."accounts_memberships";
|
|
|
|
drop table "public"."analyses";
|
|
|
|
drop table "public"."analysis_elements";
|
|
|
|
drop table "public"."analysis_groups";
|
|
|
|
drop table "public"."analysis_orders";
|
|
|
|
drop table "public"."analysis_response_elements";
|
|
|
|
drop table "public"."analysis_responses";
|
|
|
|
drop table "public"."billing_customers";
|
|
|
|
drop table "public"."codes";
|
|
|
|
drop table "public"."config";
|
|
|
|
drop table "public"."connected_online_providers";
|
|
|
|
drop table "public"."connected_online_reservation";
|
|
|
|
drop table "public"."connected_online_services";
|
|
|
|
drop table "public"."invitations";
|
|
|
|
drop table "public"."medreport_product_groups";
|
|
|
|
drop table "public"."medreport_products";
|
|
|
|
drop table "public"."medreport_products_analyses_relations";
|
|
|
|
drop table "public"."medreport_products_external_services_relations";
|
|
|
|
drop table "public"."nonces";
|
|
|
|
drop table "public"."notifications";
|
|
|
|
drop table "public"."order_items";
|
|
|
|
drop table "public"."orders";
|
|
|
|
drop table "public"."role_permissions";
|
|
|
|
drop table "public"."roles";
|
|
|
|
drop table "public"."subscription_items";
|
|
|
|
drop table "public"."subscriptions";
|
|
|
|
drop sequence if exists "public"."billing_customers_id_seq";
|
|
|
|
drop sequence if exists "public"."invitations_id_seq";
|
|
|
|
drop type "public"."analysis_order_status";
|
|
|
|
-- drop type "public"."app_permissions";
|
|
|
|
drop type "public"."billing_provider";
|
|
|
|
drop type "public"."notification_channel";
|
|
|
|
drop type "public"."notification_type";
|
|
|
|
drop type "public"."payment_status";
|
|
|
|
drop type "public"."subscription_item_type";
|
|
|
|
drop type "public"."subscription_status";
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.has_permission(user_id uuid, account_id uuid, permission_name medreport.app_permissions)
|
|
RETURNS boolean
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$begin
|
|
return exists(
|
|
select
|
|
1
|
|
from
|
|
medreport.accounts_memberships
|
|
join medreport.role_permissions on
|
|
accounts_memberships.account_role =
|
|
role_permissions.role
|
|
where
|
|
accounts_memberships.user_id = has_permission.user_id
|
|
and accounts_memberships.account_id = has_permission.account_id
|
|
and role_permissions.permission = has_permission.permission_name);
|
|
|
|
end;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.has_personal_code(account_id uuid)
|
|
RETURNS boolean
|
|
LANGUAGE plpgsql
|
|
AS $function$BEGIN
|
|
RETURN EXISTS (
|
|
SELECT 1
|
|
FROM medreport.accounts
|
|
WHERE id = account_id
|
|
AND personal_code IS NOT NULL
|
|
AND personal_code <> ''
|
|
);
|
|
END;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.revoke_nonce(p_id uuid, p_reason text DEFAULT NULL::text)
|
|
RETURNS boolean
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
SET search_path TO ''
|
|
AS $function$DECLARE
|
|
v_affected_rows INTEGER;
|
|
BEGIN
|
|
UPDATE medreport.nonces
|
|
SET
|
|
revoked = TRUE,
|
|
revoked_reason = p_reason
|
|
WHERE
|
|
id = p_id
|
|
AND used_at IS NULL
|
|
AND NOT revoked
|
|
RETURNING 1 INTO v_affected_rows;
|
|
|
|
RETURN v_affected_rows > 0;
|
|
END;$function$
|
|
;
|
|
|
|
CREATE OR REPLACE FUNCTION medreport.upsert_subscription(target_account_id uuid, target_customer_id character varying, target_subscription_id text, active boolean, status medreport.subscription_status, billing_provider medreport.billing_provider, cancel_at_period_end boolean, currency character varying, period_starts_at timestamp with time zone, period_ends_at timestamp with time zone, line_items jsonb, trial_starts_at timestamp with time zone DEFAULT NULL::timestamp with time zone, trial_ends_at timestamp with time zone DEFAULT NULL::timestamp with time zone)
|
|
RETURNS medreport.subscriptions
|
|
LANGUAGE plpgsql
|
|
SET search_path TO ''
|
|
AS $function$declare
|
|
new_subscription medreport.subscriptions;
|
|
new_billing_customer_id int;
|
|
begin
|
|
insert into medreport.billing_customers(
|
|
account_id,
|
|
provider,
|
|
customer_id)
|
|
values (
|
|
target_account_id,
|
|
billing_provider,
|
|
target_customer_id)
|
|
on conflict (
|
|
account_id,
|
|
provider,
|
|
customer_id)
|
|
do update set
|
|
provider = excluded.provider
|
|
returning
|
|
id into new_billing_customer_id;
|
|
|
|
insert into medreport.subscriptions(
|
|
account_id,
|
|
billing_customer_id,
|
|
id,
|
|
active,
|
|
status,
|
|
billing_provider,
|
|
cancel_at_period_end,
|
|
currency,
|
|
period_starts_at,
|
|
period_ends_at,
|
|
trial_starts_at,
|
|
trial_ends_at)
|
|
values (
|
|
target_account_id,
|
|
new_billing_customer_id,
|
|
target_subscription_id,
|
|
active,
|
|
status,
|
|
billing_provider,
|
|
cancel_at_period_end,
|
|
currency,
|
|
period_starts_at,
|
|
period_ends_at,
|
|
trial_starts_at,
|
|
trial_ends_at)
|
|
on conflict (
|
|
id)
|
|
do update set
|
|
active = excluded.active,
|
|
status = excluded.status,
|
|
cancel_at_period_end = excluded.cancel_at_period_end,
|
|
currency = excluded.currency,
|
|
period_starts_at = excluded.period_starts_at,
|
|
period_ends_at = excluded.period_ends_at,
|
|
trial_starts_at = excluded.trial_starts_at,
|
|
trial_ends_at = excluded.trial_ends_at
|
|
returning
|
|
* into new_subscription;
|
|
|
|
-- Upsert subscription items and delete ones that are not in the line_items array
|
|
with item_data as (
|
|
select
|
|
(line_item ->> 'id')::varchar as line_item_id,
|
|
(line_item ->> 'product_id')::varchar as prod_id,
|
|
(line_item ->> 'variant_id')::varchar as var_id,
|
|
(line_item ->> 'type')::medreport.subscription_item_type as type,
|
|
(line_item ->> 'price_amount')::numeric as price_amt,
|
|
(line_item ->> 'quantity')::integer as qty,
|
|
(line_item ->> 'interval')::varchar as intv,
|
|
(line_item ->> 'interval_count')::integer as intv_count
|
|
from
|
|
jsonb_array_elements(line_items) as line_item
|
|
),
|
|
line_item_ids as (
|
|
select line_item_id from item_data
|
|
),
|
|
deleted_items as (
|
|
delete from
|
|
medreport.subscription_items
|
|
where
|
|
medreport.subscription_items.subscription_id = new_subscription.id
|
|
and medreport.subscription_items.id not in (select line_item_id from line_item_ids)
|
|
returning *
|
|
)
|
|
insert into medreport.subscription_items(
|
|
id,
|
|
subscription_id,
|
|
product_id,
|
|
variant_id,
|
|
type,
|
|
price_amount,
|
|
quantity,
|
|
interval,
|
|
interval_count)
|
|
select
|
|
line_item_id,
|
|
target_subscription_id,
|
|
prod_id,
|
|
var_id,
|
|
type,
|
|
price_amt,
|
|
qty,
|
|
intv,
|
|
intv_count
|
|
from
|
|
item_data
|
|
on conflict (id)
|
|
do update set
|
|
product_id = excluded.product_id,
|
|
variant_id = excluded.variant_id,
|
|
price_amount = excluded.price_amount,
|
|
quantity = excluded.quantity,
|
|
interval = excluded.interval,
|
|
type = excluded.type,
|
|
interval_count = excluded.interval_count;
|
|
|
|
return new_subscription;
|
|
|
|
end;$function$
|
|
; |