Skip to main content

TUASESOR Database Schema

Generated automatically from the linked Supabase database. Do not edit this file manually. Run npm run db:schema instead.

Scope​

  • PostgreSQL schemas: tuasesor, audit
  • Contains structural metadata only
  • Contains no table rows, OAuth tokens, passwords, or customer records
  • Database migrations remain the historical record of schema changes

Summary​

  • Tables: 20
  • Functions declared in exported schemas: 20
  • Tables with RLS enabled: 13

Table audit.events​

Operational audit and activity stream events across TUASESOR workspaces.

  • Row Level Security: not enabled

Columns​

NameTypeNullableDefaultAdditional definition
iduuidNo"gen_random_uuid"()NOT NULL
workspace_iduuidYes--
actor_profile_iduuidYes--
event_typetextNo-NOT NULL
entity_typetextYes--
entity_idtextYes--
severitytextNo'info'::"text"NOT NULL
metadata_jsonjsonbNo'{}'::"jsonb"NOT NULL
created_attimestamp with time zoneNo"now"()NOT NULL

Constraints​

  • "events_actor_profile_id_fkey": FOREIGN KEY ("actor_profile_id") REFERENCES "tuasesor"."profiles"("id") ON DELETE SET NULL
  • "events_pkey": PRIMARY KEY ("id")
  • "events_workspace_id_fkey": FOREIGN KEY ("workspace_id") REFERENCES "tuasesor"."workspaces"("id") ON DELETE SET NULL

Table tuasesor.accounting_accounts​

Workspace-scoped accounting account catalog used to classify expense report items.

  • Row Level Security: enabled

Columns​

NameTypeNullableDefaultAdditional definition
iduuidNo"gen_random_uuid"()NOT NULL
workspace_iduuidNo-NOT NULL
codetextYes--
nametextNo-NOT NULL
account_typetextNo-NOT NULL
is_activebooleanNotrueNOT NULL
created_attimestamp with time zoneNo"now"()NOT NULL
updated_attimestamp with time zoneNo"now"()NOT NULL

Constraints​

  • "accounting_accounts_pkey": PRIMARY KEY ("id")
  • "accounting_accounts_workspace_code_key": UNIQUE ("workspace_id", "code")
  • "accounting_accounts_workspace_id_fkey": FOREIGN KEY ("workspace_id") REFERENCES "tuasesor"."workspaces"("id") ON DELETE CASCADE

Indexes​

  • "idx_accounting_accounts_workspace_id": USING "btree" ("workspace_id")

RLS policies​

  • "accounting_accounts_select_workspace_members": FOR SELECT TO "authenticated" USING ((EXISTS ( SELECT 1 FROM "tuasesor"."workspace_members" "workspace_member" WHERE (("workspace_member"."workspace_id" = "accounting_accounts"."workspace_id") AND ("workspace_member"."profile_id" = "auth"."uid"())))))

Grants​

  • ALL TO "service_role"
  • SELECT TO "authenticated"

Triggers​

  • "trigger_accounting_accounts_updated_at": BEFORE UPDATE ON "tuasesor"."accounting_accounts" FOR EACH ROW EXECUTE FUNCTION "public"."update_updated_at_column"()

Table tuasesor.business_lines​

Workspace-scoped catalog for business lines used in expense reports.

  • Row Level Security: enabled

Columns​

NameTypeNullableDefaultAdditional definition
iduuidNo"gen_random_uuid"()NOT NULL
workspace_iduuidNo-NOT NULL
codetextYes--
nametextNo-NOT NULL
is_activebooleanNotrueNOT NULL
created_attimestamp with time zoneNo"now"()NOT NULL
updated_attimestamp with time zoneNo"now"()NOT NULL

Constraints​

  • "business_lines_pkey": PRIMARY KEY ("id")
  • "business_lines_workspace_code_key": UNIQUE ("workspace_id", "code")
  • "business_lines_workspace_id_fkey": FOREIGN KEY ("workspace_id") REFERENCES "tuasesor"."workspaces"("id") ON DELETE CASCADE

Indexes​

  • "idx_business_lines_workspace_id": USING "btree" ("workspace_id")

RLS policies​

  • "business_lines_select_workspace_members": FOR SELECT TO "authenticated" USING ((EXISTS ( SELECT 1 FROM "tuasesor"."workspace_members" "workspace_member" WHERE (("workspace_member"."workspace_id" = "business_lines"."workspace_id") AND ("workspace_member"."profile_id" = "auth"."uid"())))))

Grants​

  • ALL TO "service_role"
  • SELECT TO "authenticated"

Triggers​

  • "trigger_business_lines_updated_at": BEFORE UPDATE ON "tuasesor"."business_lines" FOR EACH ROW EXECUTE FUNCTION "public"."update_updated_at_column"()

Table tuasesor.cost_centers​

Workspace-scoped catalog for cost centers used in expense reports.

  • Row Level Security: enabled

Columns​

NameTypeNullableDefaultAdditional definition
iduuidNo"gen_random_uuid"()NOT NULL
workspace_iduuidNo-NOT NULL
codetextYes--
nametextNo-NOT NULL
is_activebooleanNotrueNOT NULL
created_attimestamp with time zoneNo"now"()NOT NULL
updated_attimestamp with time zoneNo"now"()NOT NULL

Constraints​

  • "cost_centers_pkey": PRIMARY KEY ("id")
  • "cost_centers_workspace_code_key": UNIQUE ("workspace_id", "code")
  • "cost_centers_workspace_id_fkey": FOREIGN KEY ("workspace_id") REFERENCES "tuasesor"."workspaces"("id") ON DELETE CASCADE

Indexes​

  • "idx_cost_centers_workspace_id": USING "btree" ("workspace_id")

RLS policies​

  • "cost_centers_select_workspace_members": FOR SELECT TO "authenticated" USING ((EXISTS ( SELECT 1 FROM "tuasesor"."workspace_members" "workspace_member" WHERE (("workspace_member"."workspace_id" = "cost_centers"."workspace_id") AND ("workspace_member"."profile_id" = "auth"."uid"())))))

Grants​

  • ALL TO "service_role"
  • SELECT TO "authenticated"

Triggers​

  • "trigger_cost_centers_updated_at": BEFORE UPDATE ON "tuasesor"."cost_centers" FOR EACH ROW EXECUTE FUNCTION "public"."update_updated_at_column"()

Table tuasesor.document_types​

  • Row Level Security: not enabled

Columns​

NameTypeNullableDefaultAdditional definition
idintegerNo-NOT NULL
codetextYes--
nametextYes--

Constraints​

  • "document_types_code_key": UNIQUE ("code")
  • "document_types_pkey": PRIMARY KEY ("id")

Grants​

  • ALL TO "anon"
  • ALL TO "authenticated"
  • ALL TO "service_role"

Table tuasesor.documents​

  • Row Level Security: enabled

Columns​

NameTypeNullableDefaultAdditional definition
iduuidNo"gen_random_uuid"()NOT NULL
nametextNo-NOT NULL
sourcetextNo-NOT NULL
external_idtextYes--
storage_pathtextYes--
web_view_linktextYes--
mime_typetextYes--
size_bytesbigintYes--
modified_timetimestamp with time zoneYes--
document_type_idintegerYes--
created_attimestamp with time zoneNo"now"()NOT NULL
updated_attimestamp with time zoneNo"now"()NOT NULL
drive_file_idtextYes--
modified_attimestamp with time zoneYes--
providertextYes'google'::"text"-
profile_iduuidYes--
typetextYes--
drive_parent_idtextYes--
sync_statustextYes'active'::"text"-
sync_batch_iduuidYes--
web_content_linktextYes--
workspace_iduuidYes--
workspace_source_iduuidYes--
entity_typetextYes--
entity_idtextYes--

Constraints​

  • "documents_document_type_id_fkey": FOREIGN KEY ("document_type_id") REFERENCES "tuasesor"."document_types"("id") DEFERRABLE
  • "documents_pkey": PRIMARY KEY ("id")
  • "documents_profile_id_fkey": FOREIGN KEY ("profile_id") REFERENCES "tuasesor"."profiles"("id") ON DELETE SET NULL
  • "documents_workspace_id_fkey": FOREIGN KEY ("workspace_id") REFERENCES "tuasesor"."workspaces"("id") ON DELETE SET NULL
  • "documents_workspace_source_id_fkey": FOREIGN KEY ("workspace_source_id") REFERENCES "tuasesor"."workspace_sources"("id") ON DELETE SET NULL
  • "unique_drive_file": UNIQUE ("drive_file_id")
  • CONSTRAINT "check_source" CHECK (("source" = ANY (ARRAY['google_drive'::"text", 'internal_upload'::"text"])))
  • CONSTRAINT "documents_sync_status_check" CHECK (("sync_status" = ANY (ARRAY['active'::"text", 'deleted_in_drive'::"text"])))
  • CONSTRAINT "documents_type_check" CHECK (("type" = ANY (ARRAY['file'::"text", 'folder'::"text"])))

Indexes​

  • "idx_documents_entity": USING "btree" ("entity_type", "entity_id")
  • "idx_documents_parent": USING "btree" ("drive_parent_id")
  • "idx_documents_sync_status": USING "btree" ("sync_status")
  • "idx_documents_type": USING "btree" ("type")
  • "idx_documents_user_id": USING "btree" ("profile_id")
  • "idx_documents_workspace_id": USING "btree" ("workspace_id")
  • "idx_documents_workspace_source_id": USING "btree" ("workspace_source_id")
  • UNIQUE "idx_documents_drive_file_id": USING "btree" ("drive_file_id") WHERE ("drive_file_id" IS NOT NULL)
  • UNIQUE "idx_documents_external_id": USING "btree" ("external_id") WHERE ("external_id" IS NOT NULL)

RLS policies​

No row-level policies were detected for this table in the exported schema.

Grants​

  • ALL TO "service_role"

Table tuasesor.expense_report_items​

Individual expense lines declared inside an expense report.

  • Row Level Security: enabled

Columns​

NameTypeNullableDefaultAdditional definition
iduuidNo"gen_random_uuid"()NOT NULL
expense_report_iduuidNo-NOT NULL
business_line_iduuidYes--
cost_center_iduuidYes--
accounting_account_iduuidYes--
expense_datedateNo-NOT NULL
supplier_nametextYes--
document_type_idintegerYes--
document_numbertextYes--
total_amountnumeric(14,2)No-NOT NULL
descriptiontextYes--
additional_notestextYes--
created_attimestamp with time zoneNo"now"()NOT NULL
updated_attimestamp with time zoneNo"now"()NOT NULL

Constraints​

  • "expense_report_items_accounting_account_id_fkey": FOREIGN KEY ("accounting_account_id") REFERENCES "tuasesor"."accounting_accounts"("id") ON DELETE SET NULL
  • "expense_report_items_additional_notes_length_check": CHECK ((("additional_notes" IS NULL) OR ("char_length"("additional_notes") <= 500))) NOT VALID
  • "expense_report_items_business_line_id_fkey": FOREIGN KEY ("business_line_id") REFERENCES "tuasesor"."business_lines"("id") ON DELETE SET NULL
  • "expense_report_items_cost_center_id_fkey": FOREIGN KEY ("cost_center_id") REFERENCES "tuasesor"."cost_centers"("id") ON DELETE SET NULL
  • "expense_report_items_description_length_check": CHECK ((("description" IS NULL) OR ("char_length"("description") <= 500))) NOT VALID
  • "expense_report_items_document_number_length_check": CHECK ((("document_number" IS NULL) OR ("char_length"("document_number") <= 100))) NOT VALID
  • "expense_report_items_document_type_id_fkey": FOREIGN KEY ("document_type_id") REFERENCES "tuasesor"."document_types"("id") ON DELETE SET NULL
  • "expense_report_items_expense_report_id_fkey": FOREIGN KEY ("expense_report_id") REFERENCES "tuasesor"."expense_reports"("id") ON DELETE CASCADE
  • "expense_report_items_pkey": PRIMARY KEY ("id")
  • "expense_report_items_supplier_name_length_check": CHECK ((("supplier_name" IS NULL) OR ("char_length"("supplier_name") <= 150))) NOT VALID
  • CONSTRAINT "expense_report_items_total_amount_check" CHECK (("total_amount" > (0)::numeric))

Indexes​

  • "idx_expense_report_items_expense_date": USING "btree" ("expense_date")
  • "idx_expense_report_items_report_expense_date": USING "btree" ("expense_report_id", "expense_date" DESC)
  • "idx_expense_report_items_report_id": USING "btree" ("expense_report_id")

RLS policies​

  • "expense_report_items_delete_draft_workspace_writers": FOR DELETE TO "authenticated" USING ((EXISTS ( SELECT 1 FROM "tuasesor"."expense_reports" "expense_report" WHERE (("expense_report"."id" = "expense_report_items"."expense_report_id") AND ("expense_report"."status" = 'draft'::"text") AND "tuasesor"."can_write_workspace"("expense_report"."workspace_id")))))
  • "expense_report_items_insert_draft_workspace_writers": FOR INSERT TO "authenticated" WITH CHECK ((EXISTS ( SELECT 1 FROM "tuasesor"."expense_reports" "expense_report" WHERE (("expense_report"."id" = "expense_report_items"."expense_report_id") AND ("expense_report"."status" = 'draft'::"text") AND "tuasesor"."can_write_workspace"("expense_report"."workspace_id")))))
  • "expense_report_items_select_workspace_members": FOR SELECT TO "authenticated" USING ((EXISTS ( SELECT 1 FROM ("tuasesor"."expense_reports" "expense_report" JOIN "tuasesor"."workspace_members" "workspace_member" ON (("workspace_member"."workspace_id" = "expense_report"."workspace_id"))) WHERE (("expense_report"."id" = "expense_report_items"."expense_report_id") AND ("workspace_member"."profile_id" = "auth"."uid"())))))
  • "expense_report_items_update_draft_workspace_writers": FOR UPDATE TO "authenticated" USING ((EXISTS ( SELECT 1 FROM "tuasesor"."expense_reports" "expense_report" WHERE (("expense_report"."id" = "expense_report_items"."expense_report_id") AND ("expense_report"."status" = 'draft'::"text") AND "tuasesor"."can_write_workspace"("expense_report"."workspace_id"))))) WITH CHECK ((EXISTS ( SELECT 1 FROM "tuasesor"."expense_reports" "expense_report" WHERE (("expense_report"."id" = "expense_report_items"."expense_report_id") AND ("expense_report"."status" = 'draft'::"text") AND "tuasesor"."can_write_workspace"("expense_report"."workspace_id")))))

Grants​

  • ALL TO "service_role"
  • SELECT,INSERT,DELETE,UPDATE TO "authenticated"

Triggers​

  • "trigger_expense_report_items_updated_at": BEFORE UPDATE ON "tuasesor"."expense_report_items" FOR EACH ROW EXECUTE FUNCTION "public"."update_updated_at_column"()
  • "trigger_prevent_expense_report_item_parent_change": BEFORE UPDATE OF "expense_report_id" ON "tuasesor"."expense_report_items" FOR EACH ROW EXECUTE FUNCTION "tuasesor"."prevent_expense_report_item_parent_change"()
  • "trigger_validate_expense_report_item_catalogs": BEFORE INSERT OR UPDATE OF "expense_report_id", "business_line_id", "cost_center_id", "accounting_account_id", "document_type_id" ON "tuasesor"."expense_report_items" FOR EACH ROW EXECUTE FUNCTION "tuasesor"."validate_expense_report_item_catalogs"()

Table tuasesor.expense_reports​

Business domain table representing complete expense reports submitted within a workspace.

  • Row Level Security: enabled

Columns​

NameTypeNullableDefaultAdditional definition
iduuidNo"gen_random_uuid"()NOT NULL
workspace_iduuidNo-NOT NULL
report_numbertextNo-NOT NULL
report_yearintegerNo-NOT NULL
submitted_by_profile_iduuidYes--
statustextNo'draft'::"text"NOT NULL
submitted_attimestamp with time zoneYes--
reviewed_attimestamp with time zoneYes--
reimbursed_attimestamp with time zoneYes--
notestextYes--
created_attimestamp with time zoneNo"now"()NOT NULL
updated_attimestamp with time zoneNo"now"()NOT NULL

Constraints​

  • "expense_reports_pkey": PRIMARY KEY ("id")
  • "expense_reports_submitted_by_profile_id_fkey": FOREIGN KEY ("submitted_by_profile_id") REFERENCES "tuasesor"."profiles"("id") ON DELETE SET NULL
  • "expense_reports_workspace_id_fkey": FOREIGN KEY ("workspace_id") REFERENCES "tuasesor"."workspaces"("id") ON DELETE CASCADE
  • "expense_reports_workspace_year_report_number_key": UNIQUE ("workspace_id", "report_year", "report_number")
  • CONSTRAINT "expense_reports_report_year_check" CHECK ((("report_year" >= 2000) AND ("report_year" <= 2100)))
  • CONSTRAINT "expense_reports_status_check" CHECK (("status" = ANY (ARRAY['draft'::"text", 'submitted'::"text", 'under_review'::"text", 'approved'::"text", 'rejected'::"text", 'reimbursed'::"text", 'cancelled'::"text"])))

Indexes​

  • "idx_expense_reports_report_year": USING "btree" ("report_year")
  • "idx_expense_reports_status": USING "btree" ("status")
  • "idx_expense_reports_workspace_created_at": USING "btree" ("workspace_id", "created_at" DESC)
  • "idx_expense_reports_workspace_id": USING "btree" ("workspace_id")
  • "idx_expense_reports_workspace_profile_created_at": USING "btree" ("workspace_id", "submitted_by_profile_id", "created_at" DESC) WHERE ("submitted_by_profile_id" IS NOT NULL)
  • "idx_expense_reports_workspace_status_created_at": USING "btree" ("workspace_id", "status", "created_at" DESC)

RLS policies​

  • "expense_reports_insert_workspace_writers": FOR INSERT TO "authenticated" WITH CHECK ("tuasesor"."can_write_workspace"("workspace_id"))
  • "expense_reports_select_workspace_members": FOR SELECT TO "authenticated" USING ((EXISTS ( SELECT 1 FROM "tuasesor"."workspace_members" "workspace_member" WHERE (("workspace_member"."workspace_id" = "expense_reports"."workspace_id") AND ("workspace_member"."profile_id" = "auth"."uid"())))))
  • "expense_reports_update_workspace_writers": FOR UPDATE TO "authenticated" USING ("tuasesor"."can_write_workspace"("workspace_id")) WITH CHECK ("tuasesor"."can_write_workspace"("workspace_id"))

Grants​

  • ALL TO "service_role"
  • SELECT,INSERT,UPDATE TO "authenticated"

Triggers​

  • "trigger_expense_reports_updated_at": BEFORE UPDATE ON "tuasesor"."expense_reports" FOR EACH ROW EXECUTE FUNCTION "public"."update_updated_at_column"()

Table tuasesor.integration_credentials​

Private operational metadata for an integration OAuth credential set. Secret material is stored through the configured secret-store infrastructure, currently Supabase Vault.

  • Row Level Security: enabled

Columns​

NameTypeNullableDefaultAdditional definition
integration_iduuidNo-NOT NULL
expires_attimestamp with time zoneYes--
created_attimestamp with time zoneNo"now"()NOT NULL
updated_attimestamp with time zoneNo"now"()NOT NULL

Constraints​

  • "integration_credentials_integration_id_fkey": FOREIGN KEY ("integration_id") REFERENCES "tuasesor"."integrations"("id") ON DELETE CASCADE
  • "integration_credentials_pkey": PRIMARY KEY ("integration_id")

RLS policies​

No row-level policies were detected for this table in the exported schema.

Triggers​

  • "trigger_delete_integration_credential_secret": BEFORE DELETE ON "tuasesor"."integration_credentials" FOR EACH ROW EXECUTE FUNCTION "tuasesor"."delete_integration_credential_secret"()
  • "trigger_integration_credentials_updated_at": BEFORE UPDATE ON "tuasesor"."integration_credentials" FOR EACH ROW EXECUTE FUNCTION "public"."update_updated_at_column"()

Table tuasesor.integrations​

  • Row Level Security: enabled

Columns​

NameTypeNullableDefaultAdditional definition
iduuidNo"gen_random_uuid"()NOT NULL
owner_profile_iduuidYes--
providertextNo-NOT NULL
created_attimestamp with time zoneNo"now"()NOT NULL
metadatajsonbYes'{}'::"jsonb"-
provider_account_idtextNo-NOT NULL
scopes"text"[]Yes'{}'::"text"[]-
owner_typetextNo-NOT NULL
owner_workspace_iduuidYes--
connected_by_profile_iduuidYes--
statustextNo'active'::"text"NOT NULL
status_changed_attimestamp with time zoneNo"now"()NOT NULL
updated_attimestamp with time zoneNo"now"()NOT NULL

Constraints​

  • "integrations_connected_by_profile_id_fkey": FOREIGN KEY ("connected_by_profile_id") REFERENCES "tuasesor"."profiles"("id") ON DELETE SET NULL
  • "integrations_id_owner_profile_id_key": UNIQUE ("id", "owner_profile_id")
  • "integrations_id_owner_workspace_id_key": UNIQUE ("id", "owner_workspace_id")
  • "integrations_owner_profile_id_fkey": FOREIGN KEY ("owner_profile_id") REFERENCES "tuasesor"."profiles"("id") ON DELETE CASCADE
  • "integrations_owner_workspace_id_fkey": FOREIGN KEY ("owner_workspace_id") REFERENCES "tuasesor"."workspaces"("id") ON DELETE CASCADE
  • "integrations_pkey": PRIMARY KEY ("id")
  • CONSTRAINT "integrations_owner_type_check" CHECK (((("owner_type" = 'profile'::"text") AND ("owner_profile_id" IS NOT NULL) AND ("owner_workspace_id" IS NULL)) OR (("owner_type" = 'workspace'::"text") AND ("owner_profile_id" IS NULL) AND ("owner_workspace_id" IS NOT NULL))))
  • CONSTRAINT "integrations_provider_account_id_not_blank_check" CHECK (("btrim"("provider_account_id") <> ''::"text"))
  • CONSTRAINT "integrations_provider_not_blank_check" CHECK (("btrim"("provider") <> ''::"text"))
  • CONSTRAINT "integrations_status_check" CHECK (("status" = ANY (ARRAY['active'::"text", 'disconnected'::"text", 'reauthorization_required'::"text"])))

Indexes​

  • "idx_integrations_connected_by_profile_id": USING "btree" ("connected_by_profile_id")
  • "idx_integrations_metadata": USING "gin" ("metadata")
  • "idx_integrations_owner_profile_id": USING "btree" ("owner_profile_id")
  • "idx_integrations_owner_workspace_id": USING "btree" ("owner_workspace_id")
  • "idx_integrations_scopes": USING "gin" ("scopes")
  • UNIQUE "integrations_profile_owner_provider_account_uidx": USING "btree" ("owner_profile_id", "provider", "provider_account_id") WHERE ("owner_type" = 'profile'::"text")
  • UNIQUE "integrations_workspace_owner_provider_account_uidx": USING "btree" ("owner_workspace_id", "provider", "provider_account_id") WHERE ("owner_type" = 'workspace'::"text")

RLS policies​

No row-level policies were detected for this table in the exported schema.

Grants​

  • SELECT,INSERT,DELETE,UPDATE TO "service_role"

Triggers​

  • "trigger_integrations_status_changed_at": BEFORE UPDATE ON "tuasesor"."integrations" FOR EACH ROW EXECUTE FUNCTION "tuasesor"."set_integration_status_changed_at"()
  • "trigger_integrations_updated_at": BEFORE UPDATE ON "tuasesor"."integrations" FOR EACH ROW EXECUTE FUNCTION "public"."update_updated_at_column"()

Table tuasesor.normalized_documents​

Structured knowledge layer used by cortexa-sdk for normalized and persistent document intelligence.

  • Row Level Security: enabled

Columns​

NameTypeNullableDefaultAdditional definition
iduuidNo"gen_random_uuid"()NOT NULL
document_iduuidYes--
sourcetextNo-NOT NULL
external_idtextNo-NOT NULL
titletextYes--
filenametextYes--
mime_typetextYes--
content_texttextYes--
metadata_jsonjsonbYes--
created_attimestamp with time zoneNo"now"()NOT NULL
updated_attimestamp with time zoneNo"now"()NOT NULL
normalized_attimestamp with time zoneNo"now"()NOT NULL
workspace_iduuidYes--

Constraints​

  • "normalized_documents_document_id_fkey": FOREIGN KEY ("document_id") REFERENCES "tuasesor"."documents"("id") ON DELETE SET NULL
  • "normalized_documents_pkey": PRIMARY KEY ("id")
  • "normalized_documents_workspace_id_fkey": FOREIGN KEY ("workspace_id") REFERENCES "tuasesor"."workspaces"("id") ON DELETE SET NULL

Indexes​

  • "idx_normalized_documents_external_id": USING "btree" ("external_id")
  • "idx_normalized_documents_mime_type": USING "btree" ("mime_type")
  • "idx_normalized_documents_source": USING "btree" ("source")
  • "idx_normalized_documents_workspace_id": USING "btree" ("workspace_id")

RLS policies​

No row-level policies were detected for this table in the exported schema.

Grants​

  • ALL TO "anon"
  • ALL TO "authenticated"
  • ALL TO "service_role"

Triggers​

  • "trigger_normalized_documents_updated_at": BEFORE UPDATE ON "tuasesor"."normalized_documents" FOR EACH ROW EXECUTE FUNCTION "public"."update_updated_at_column"()

Table tuasesor.oauth_transactions​

Ephemeral single-use OAuth authorization context owned and validated by TUASESOR.

  • Row Level Security: enabled

Columns​

NameTypeNullableDefaultAdditional definition
iduuidNo"gen_random_uuid"()NOT NULL
state_hashtextNo-NOT NULL
providertextNo-NOT NULL
operationtextNo-NOT NULL
actor_profile_iduuidNo-NOT NULL
owner_typetextNo-NOT NULL
owner_profile_iduuidYes--
owner_workspace_iduuidYes--
workspace_member_iduuidYes--
target_integration_iduuidYes--
created_attimestamp with time zoneNo"now"()NOT NULL
expires_attimestamp with time zoneNo("now"() + '00:10:00'::interval)NOT NULL
consumed_attimestamp with time zoneYes--

Constraints​

  • "oauth_transactions_actor_profile_id_fkey": FOREIGN KEY ("actor_profile_id") REFERENCES "tuasesor"."profiles"("id") ON DELETE CASCADE
  • "oauth_transactions_owner_profile_id_fkey": FOREIGN KEY ("owner_profile_id") REFERENCES "tuasesor"."profiles"("id") ON DELETE CASCADE
  • "oauth_transactions_owner_workspace_id_fkey": FOREIGN KEY ("owner_workspace_id") REFERENCES "tuasesor"."workspaces"("id") ON DELETE CASCADE
  • "oauth_transactions_pkey": PRIMARY KEY ("id")
  • "oauth_transactions_state_hash_key": UNIQUE ("state_hash")
  • "oauth_transactions_target_profile_integration_fkey": FOREIGN KEY ("target_integration_id", "owner_profile_id") REFERENCES "tuasesor"."integrations"("id", "owner_profile_id") ON DELETE CASCADE
  • "oauth_transactions_target_workspace_integration_fkey": FOREIGN KEY ("target_integration_id", "owner_workspace_id") REFERENCES "tuasesor"."integrations"("id", "owner_workspace_id") ON DELETE CASCADE
  • "oauth_transactions_workspace_member_fkey": FOREIGN KEY ("workspace_member_id", "owner_workspace_id") REFERENCES "tuasesor"."workspace_members"("id", "workspace_id") ON DELETE CASCADE
  • CONSTRAINT "oauth_transactions_consumed_at_check" CHECK ((("consumed_at" IS NULL) OR ("consumed_at" >= "created_at")))
  • CONSTRAINT "oauth_transactions_operation_check" CHECK (("operation" = ANY (ARRAY['connect'::"text", 'reconnect'::"text"])))
  • CONSTRAINT "oauth_transactions_operation_target_check" CHECK (((("operation" = 'connect'::"text") AND ("target_integration_id" IS NULL)) OR (("operation" = 'reconnect'::"text") AND ("target_integration_id" IS NOT NULL))))
  • CONSTRAINT "oauth_transactions_owner_type_check" CHECK (((("owner_type" = 'profile'::"text") AND ("owner_profile_id" IS NOT NULL) AND ("owner_workspace_id" IS NULL) AND ("workspace_member_id" IS NULL) AND ("actor_profile_id" = "owner_profile_id")) OR (("owner_type" = 'workspace'::"text") AND ("owner_profile_id" IS NULL) AND ("owner_workspace_id" IS NOT NULL) AND ("workspace_member_id" IS NOT NULL))))
  • CONSTRAINT "oauth_transactions_provider_check" CHECK ((("provider" <> ''::"text") AND ("provider" = "btrim"("provider"))))
  • CONSTRAINT "oauth_transactions_state_hash_check" CHECK (("state_hash" ~ '^[0-9a-f]{64}$'::"text"))
  • CONSTRAINT "oauth_transactions_ttl_check" CHECK (("expires_at" = ("created_at" + '00:10:00'::interval)))

Indexes​

  • "oauth_transactions_expires_at_idx": USING "btree" ("expires_at")

RLS policies​

No row-level policies were detected for this table in the exported schema.

Grants​

  • SELECT,INSERT,DELETE,UPDATE TO "service_role"

Table tuasesor.profile_specialties​

Many-to-many relationship between profiles and professional specialties.

  • Row Level Security: not enabled

Columns​

NameTypeNullableDefaultAdditional definition
iduuidNo"gen_random_uuid"()NOT NULL
profile_iduuidNo-NOT NULL
specialty_iduuidNo-NOT NULL
is_primarybooleanNofalseNOT NULL
years_experienceintegerYes--
metadata_jsonjsonbNo'{}'::"jsonb"NOT NULL
created_attimestamp with time zoneNo"now"()NOT NULL

Constraints​

  • "profile_specialties_pkey": PRIMARY KEY ("id")
  • "profile_specialties_profile_id_fkey": FOREIGN KEY ("profile_id") REFERENCES "tuasesor"."profiles"("id") ON DELETE CASCADE
  • "profile_specialties_profile_id_specialty_id_key": UNIQUE ("profile_id", "specialty_id")
  • "profile_specialties_specialty_id_fkey": FOREIGN KEY ("specialty_id") REFERENCES "tuasesor"."specialties"("id") ON DELETE CASCADE

Indexes​

  • "idx_profile_specialties_profile_id": USING "btree" ("profile_id")
  • "idx_profile_specialties_specialty_id": USING "btree" ("specialty_id")

Grants​

  • ALL TO "service_role"
  • SELECT TO "authenticated"

Table tuasesor.profiles​

Application-level user profiles associated 1:1 with auth.users.

  • Row Level Security: not enabled

Columns​

NameTypeNullableDefaultAdditional definition
iduuidNo-NOT NULL
emailtextNo-NOT NULL
full_nametextYes--
avatar_urltextYes--
global_roletextNo'member'::"text"NOT NULL
metadata_jsonjsonbNo'{}'::"jsonb"NOT NULL
created_attimestamp with time zoneNo"now"()NOT NULL
updated_attimestamp with time zoneNo"now"()NOT NULL
profile_typetextNo'member'::"text"NOT NULL
statustextNo'pending'::"text"NOT NULL

Constraints​

  • "profiles_auth_user_id_fkey": FOREIGN KEY ("id") REFERENCES "auth"."users"("id") ON DELETE CASCADE
  • "profiles_email_key": UNIQUE ("email")
  • "profiles_pkey": PRIMARY KEY ("id")
  • CONSTRAINT "profiles_status_check" CHECK (("status" = ANY (ARRAY['pending'::"text", 'active'::"text", 'disabled'::"text"])))

Indexes​

  • "idx_profiles_status": USING "btree" ("status")

Grants​

  • ALL TO "service_role"
  • SELECT TO "authenticated"

Table tuasesor.specialties​

Professional specialties catalog used for marketplace identity and expertise classification.

  • Row Level Security: not enabled

Columns​

NameTypeNullableDefaultAdditional definition
iduuidNo"gen_random_uuid"()NOT NULL
codetextNo-NOT NULL
nametextNo-NOT NULL
categorytextYes--
created_attimestamp with time zoneNo"now"()NOT NULL

Constraints​

  • "specialties_code_key": UNIQUE ("code")
  • "specialties_pkey": PRIMARY KEY ("id")

Indexes​

  • "idx_specialties_code": USING "btree" ("code")

Grants​

  • ALL TO "service_role"
  • SELECT TO "authenticated"

Table tuasesor.transactions​

  • Row Level Security: not enabled

Columns​

NameTypeNullableDefaultAdditional definition
iduuidNo"gen_random_uuid"()NOT NULL
document_iduuidYes--
typetextNo-NOT NULL
datedateNo-NOT NULL
amountnumeric(12,2)No-NOT NULL
descriptiontextYes--
document_numbertextYes--
statustextYes'pending'::"text"-
profile_iduuidYes--
created_attimestamp with time zoneNo"now"()NOT NULL

Constraints​

  • "transactions_document_id_fkey": FOREIGN KEY ("document_id") REFERENCES "tuasesor"."documents"("id") DEFERRABLE
  • "transactions_pkey": PRIMARY KEY ("id")
  • "transactions_profile_id_fkey": FOREIGN KEY ("profile_id") REFERENCES "tuasesor"."profiles"("id") ON DELETE SET NULL
  • CONSTRAINT "check_status" CHECK (("status" = ANY (ARRAY['pending'::"text", 'reviewed'::"text"])))
  • CONSTRAINT "check_type" CHECK (("type" = ANY (ARRAY['income'::"text", 'expense'::"text"])))

Grants​

  • ALL TO "anon"
  • ALL TO "authenticated"
  • ALL TO "service_role"

Table tuasesor.workspace_member_permissions​

Explicit workspace and workspace-source permission overrides for workspace memberships.

  • Row Level Security: enabled

Columns​

NameTypeNullableDefaultAdditional definition
iduuidNo"gen_random_uuid"()NOT NULL
workspace_member_iduuidNo-NOT NULL
permission_keytextNo-NOT NULL
grantedbooleanNotrueNOT NULL
created_attimestamp with time zoneNo"now"()NOT NULL
workspace_iduuidNo-NOT NULL
workspace_source_iduuidYes--

Constraints​

  • "workspace_member_permissions_member_workspace_fkey": FOREIGN KEY ("workspace_member_id", "workspace_id") REFERENCES "tuasesor"."workspace_members"("id", "workspace_id") ON DELETE CASCADE
  • "workspace_member_permissions_pkey": PRIMARY KEY ("id")
  • "workspace_member_permissions_source_workspace_fkey": FOREIGN KEY ("workspace_source_id", "workspace_id") REFERENCES "tuasesor"."workspace_sources"("id", "workspace_id") ON DELETE CASCADE
  • CONSTRAINT "workspace_member_permissions_permission_key_check" CHECK (("permission_key" = ANY (ARRAY['documents.read'::"text", 'documents.download'::"text", 'documents.analyze'::"text", 'workspace_sources.manage'::"text", 'integrations.manage'::"text", 'workspace_access.manage'::"text"])))
  • CONSTRAINT "workspace_member_permissions_scope_check" CHECK ((("workspace_source_id" IS NULL) OR ("permission_key" = ANY (ARRAY['documents.read'::"text", 'documents.download'::"text", 'documents.analyze'::"text"]))))

Indexes​

  • UNIQUE "workspace_member_permissions_source_scope_uidx": USING "btree" ("workspace_member_id", "workspace_source_id", "permission_key") WHERE ("workspace_source_id" IS NOT NULL)
  • UNIQUE "workspace_member_permissions_workspace_scope_uidx": USING "btree" ("workspace_member_id", "permission_key") WHERE ("workspace_source_id" IS NULL)

RLS policies​

No row-level policies were detected for this table in the exported schema.

Grants​

  • ALL TO "service_role"

Table tuasesor.workspace_members​

Workspace-scoped memberships and permissions associated to profiles.

  • Row Level Security: enabled

Columns​

NameTypeNullableDefaultAdditional definition
iduuidNo"gen_random_uuid"()NOT NULL
workspace_iduuidNo-NOT NULL
profile_iduuidNo-NOT NULL
roletextNo-NOT NULL
created_attimestamp with time zoneNo"now"()NOT NULL

Constraints​

  • "unique_workspace_user": UNIQUE ("workspace_id", "profile_id")
  • "workspace_members_id_workspace_id_key": UNIQUE ("id", "workspace_id")
  • "workspace_members_profile_id_fkey": FOREIGN KEY ("profile_id") REFERENCES "tuasesor"."profiles"("id") ON DELETE CASCADE
  • "workspace_users_pkey": PRIMARY KEY ("id")
  • "workspace_users_workspace_id_fkey": FOREIGN KEY ("workspace_id") REFERENCES "tuasesor"."workspaces"("id") ON DELETE CASCADE
  • CONSTRAINT "check_workspace_user_role" CHECK (("role" = ANY (ARRAY['owner'::"text", 'admin'::"text", 'member'::"text", 'viewer'::"text", 'client'::"text"])))

Indexes​

  • "idx_workspace_users_role": USING "btree" ("role")
  • "idx_workspace_users_user_id": USING "btree" ("profile_id")
  • "idx_workspace_users_workspace_id": USING "btree" ("workspace_id")

RLS policies​

  • "workspace_members_select_own_profile": FOR SELECT TO "authenticated" USING (("profile_id" = "auth"."uid"()))

Grants​

  • ALL TO "service_role"
  • SELECT TO "authenticated"

Triggers​

  • "trigger_revoke_workspace_access_delegation_on_role_change": AFTER UPDATE OF "role" ON "tuasesor"."workspace_members" FOR EACH ROW WHEN (("old"."role" IS DISTINCT FROM "new"."role")) EXECUTE FUNCTION "tuasesor"."revoke_workspace_access_delegation_on_role_change"()

Table tuasesor.workspace_sources​

Provider-specific documentary integrations associated to a workspace.

  • Row Level Security: not enabled

Columns​

NameTypeNullableDefaultAdditional definition
iduuidNo"gen_random_uuid"()NOT NULL
workspace_iduuidNo-NOT NULL
providertextNo-NOT NULL
external_idtextNo-NOT NULL
root_pathtextYes--
metadata_jsonjsonbYes--
is_activebooleanNotrueNOT NULL
last_synced_attimestamp with time zoneYes--
created_by_profile_iduuidYes--
created_attimestamp with time zoneNo"now"()NOT NULL
updated_attimestamp with time zoneNo"now"()NOT NULL
integration_iduuidNo-NOT NULL

Constraints​

  • "unique_workspace_provider_external": UNIQUE ("workspace_id", "provider", "external_id")
  • "workspace_sources_created_by_profile_id_fkey": FOREIGN KEY ("created_by_profile_id") REFERENCES "tuasesor"."profiles"("id") ON DELETE SET NULL
  • "workspace_sources_id_workspace_id_key": UNIQUE ("id", "workspace_id")
  • "workspace_sources_integration_workspace_fkey": FOREIGN KEY ("integration_id", "workspace_id") REFERENCES "tuasesor"."integrations"("id", "owner_workspace_id") DEFERRABLE INITIALLY DEFERRED
  • "workspace_sources_pkey": PRIMARY KEY ("id")
  • "workspace_sources_workspace_id_fkey": FOREIGN KEY ("workspace_id") REFERENCES "tuasesor"."workspaces"("id") ON DELETE CASCADE

Indexes​

  • "idx_workspace_sources_created_by_profile_id": USING "btree" ("created_by_profile_id")
  • "idx_workspace_sources_external_id": USING "btree" ("external_id")
  • "idx_workspace_sources_integration_workspace": USING "btree" ("integration_id", "workspace_id")
  • "idx_workspace_sources_is_active": USING "btree" ("is_active")
  • "idx_workspace_sources_provider": USING "btree" ("provider")
  • "idx_workspace_sources_workspace_id": USING "btree" ("workspace_id")

Grants​

  • ALL TO "anon"
  • ALL TO "authenticated"
  • ALL TO "service_role"

Triggers​

  • "trigger_workspace_sources_updated_at": BEFORE UPDATE ON "tuasesor"."workspace_sources" FOR EACH ROW EXECUTE FUNCTION "public"."update_updated_at_column"()

Table tuasesor.workspaces​

Operational documentary context authorized within TUASESOR.

  • Row Level Security: enabled

Columns​

NameTypeNullableDefaultAdditional definition
iduuidNo"gen_random_uuid"()NOT NULL
nametextNo-NOT NULL
descriptiontextYes--
created_byuuidYes--
created_attimestamp with time zoneNo"now"()NOT NULL
updated_attimestamp with time zoneNo"now"()NOT NULL

Constraints​

  • "workspaces_pkey": PRIMARY KEY ("id")

Indexes​

  • "idx_workspaces_created_by": USING "btree" ("created_by")

RLS policies​

  • "workspaces_select_for_members": FOR SELECT TO "authenticated" USING ((EXISTS ( SELECT 1 FROM "tuasesor"."workspace_members" "workspace_member" WHERE (("workspace_member"."workspace_id" = "workspaces"."id") AND ("workspace_member"."profile_id" = "auth"."uid"())))))

Grants​

  • ALL TO "anon"
  • ALL TO "authenticated"
  • ALL TO "service_role"

Triggers​

  • "trigger_workspaces_updated_at": BEFORE UPDATE ON "tuasesor"."workspaces" FOR EACH ROW EXECUTE FUNCTION "public"."update_updated_at_column"()

Functions​

  • "tuasesor"."can_write_workspace"("p_workspace_id" "uuid") RETURNS boolean LANGUAGE "sql" STABLE SECURITY DEFINER SET "search_path" TO 'pg_catalog', 'tuasesor' AS $$ SELECT auth.uid() IS NOT NULL AND p_workspace_id IS NOT NULL AND EXISTS ( SELECT 1 FROM tuasesor.workspace_members AS workspace_member WHERE workspace_member.workspace_id = p_workspace_id AND workspace_member.profile_id = auth.uid() AND workspace_member.role IN ( 'owner', 'admin', 'member' ) ); $$
  • "tuasesor"."claim_oauth_transaction"("p_state_hash" "text", "p_provider" "text") RETURNS TABLE("transaction_id" "uuid", "provider" "text", "operation" "text", "actor_profile_id" "uuid", "owner_type" "text", "owner_profile_id" "uuid", "owner_workspace_id" "uuid", "target_integration_id" "uuid") LANGUAGE "plpgsql" SECURITY DEFINER SET "search_path" TO 'pg_catalog', 'tuasesor' AS $_$ DECLARE v_actor_profile_id uuid := auth.uid(); v_provider text; v_claimed tuasesor.oauth_transactions%ROWTYPE; BEGIN -- Do not distinguish malformed, unknown, foreign, consumed or expired state. IF v_actor_profile_id IS NULL OR p_state_hash IS NULL OR p_state_hash !~ '^[0-9a-f]{64}$' OR p_provider IS NULL OR btrim(p_provider) = '' THEN RAISE EXCEPTION 'OAuth transaction is invalid or expired' USING ERRCODE = 'P0001'; END IF; v_provider := btrim(p_provider); UPDATE tuasesor.oauth_transactions AS oauth_transaction SET consumed_at = now() WHERE oauth_transaction.state_hash = p_state_hash AND oauth_transaction.provider = v_provider AND oauth_transaction.actor_profile_id = v_actor_profile_id AND oauth_transaction.consumed_at IS NULL AND oauth_transaction.expires_at > now() AND ( ( oauth_transaction.owner_type = 'profile' AND oauth_transaction.owner_profile_id = v_actor_profile_id AND oauth_transaction.owner_workspace_id IS NULL AND oauth_transaction.workspace_member_id IS NULL ) OR ( oauth_transaction.owner_type = 'workspace' AND oauth_transaction.owner_profile_id IS NULL AND EXISTS ( SELECT 1 FROM tuasesor.workspace_members AS workspace_member WHERE workspace_member.id = oauth_transaction.workspace_member_id AND workspace_member.workspace_id = oauth_transaction.owner_workspace_id AND workspace_member.profile_id = v_actor_profile_id ) AND tuasesor.has_workspace_permission( oauth_transaction.owner_workspace_id, 'integrations.manage' ) ) ) AND ( ( oauth_transaction.operation = 'connect' AND oauth_transaction.target_integration_id IS NULL ) OR ( oauth_transaction.operation = 'reconnect' AND EXISTS ( SELECT 1 FROM tuasesor.integrations AS integration WHERE integration.id = oauth_transaction.target_integration_id AND integration.provider = oauth_transaction.provider AND ( ( oauth_transaction.owner_type = 'profile' AND integration.owner_type = 'profile' AND integration.owner_profile_id = oauth_transaction.owner_profile_id ) OR ( oauth_transaction.owner_type = 'workspace' AND integration.owner_type = 'workspace' AND integration.owner_workspace_id = oauth_transaction.owner_workspace_id ) ) ) ) ) RETURNING oauth_transaction.* INTO v_claimed; IF NOT FOUND THEN RAISE EXCEPTION 'OAuth transaction is invalid or expired' USING ERRCODE = 'P0001'; END IF; RETURN QUERY SELECT v_claimed.id, v_claimed.provider, v_claimed.operation, v_claimed.actor_profile_id, v_claimed.owner_type, v_claimed.owner_profile_id, v_claimed.owner_workspace_id, v_claimed.target_integration_id; END; $_$
  • "tuasesor"."create_expense_report"("p_workspace_id" "uuid", "p_report_number" "text", "p_report_year" integer, "p_notes" "text", "p_items" "jsonb") RETURNS "tuasesor"."expense_reports" LANGUAGE "plpgsql" SET "search_path" TO 'pg_catalog', 'tuasesor' AS $$ DECLARE v_profile_id uuid; v_report tuasesor.expense_reports; v_item jsonb; v_business_line_id uuid; v_cost_center_id uuid; v_accounting_account_id uuid; v_document_type_id integer; BEGIN v_profile_id := auth.uid(); IF v_profile_id IS NULL THEN RAISE EXCEPTION 'Authentication is required' USING ERRCODE = '42501'; END IF; IF p_workspace_id IS NULL THEN RAISE EXCEPTION 'workspace_id is required' USING ERRCODE = '22023'; END IF; IF NOT tuasesor.can_write_workspace(p_workspace_id) THEN RAISE EXCEPTION 'Not authorized to write in this workspace' USING ERRCODE = '42501'; END IF; IF p_report_number IS NULL OR btrim(p_report_number) = '' THEN RAISE EXCEPTION 'report_number is required' USING ERRCODE = '22023'; END IF; IF p_report_year < 2000 OR p_report_year > 2100 THEN RAISE EXCEPTION 'report_year must be between 2000 and 2100' USING ERRCODE = '22023'; END IF; IF p_items IS NULL OR jsonb_typeof(p_items) <> 'array' THEN RAISE EXCEPTION 'items must be a JSON array' USING ERRCODE = '22023'; END IF; INSERT INTO tuasesor.expense_reports ( workspace_id, report_number, report_year, submitted_by_profile_id, status, notes ) VALUES ( p_workspace_id, btrim(p_report_number), p_report_year, v_profile_id, 'draft', nullif(btrim(p_notes), '') ) RETURNING * INTO v_report; FOR v_item IN SELECT value FROM jsonb_array_elements(p_items) LOOP v_business_line_id := nullif(v_item ->> 'business_line_id', '')::uuid; v_cost_center_id := nullif(v_item ->> 'cost_center_id', '')::uuid; v_accounting_account_id := nullif(v_item ->> 'accounting_account_id', '')::uuid; v_document_type_id := nullif(v_item ->> 'document_type_id', '')::integer; IF v_business_line_id IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM tuasesor.business_lines AS business_line WHERE business_line.id = v_business_line_id AND business_line.workspace_id = p_workspace_id ) THEN RAISE EXCEPTION 'Business line does not belong to the workspace' USING ERRCODE = '23503'; END IF; IF v_cost_center_id IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM tuasesor.cost_centers AS cost_center WHERE cost_center.id = v_cost_center_id AND cost_center.workspace_id = p_workspace_id ) THEN RAISE EXCEPTION 'Cost center does not belong to the workspace' USING ERRCODE = '23503'; END IF; IF v_accounting_account_id IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM tuasesor.accounting_accounts AS accounting_account WHERE accounting_account.id = v_accounting_account_id AND accounting_account.workspace_id = p_workspace_id ) THEN RAISE EXCEPTION 'Accounting account does not belong to the workspace' USING ERRCODE = '23503'; END IF; IF v_document_type_id IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM tuasesor.document_types AS document_type WHERE document_type.id = v_document_type_id ) THEN RAISE EXCEPTION 'Document type does not exist' USING ERRCODE = '23503'; END IF; INSERT INTO tuasesor.expense_report_items ( expense_report_id, business_line_id, cost_center_id, accounting_account_id, expense_date, supplier_name, document_type_id, document_number, total_amount, description, additional_notes ) VALUES ( v_report.id, v_business_line_id, v_cost_center_id, v_accounting_account_id, (v_item ->> 'expense_date')::date, nullif(btrim(v_item ->> 'supplier_name'), ''), v_document_type_id, nullif(btrim(v_item ->> 'document_number'), ''), (v_item ->> 'total_amount')::numeric, nullif(btrim(v_item ->> 'description'), ''), nullif(btrim(v_item ->> 'additional_notes'), '') ); END LOOP; RETURN v_report; END; $$
  • "tuasesor"."create_expense_report_item"("p_workspace_id" "uuid", "p_expense_report_id" "uuid", "p_expense_date" "date", "p_total_amount" numeric, "p_supplier_name" "text", "p_document_type_id" integer, "p_document_number" "text", "p_description" "text", "p_business_line_id" "uuid", "p_cost_center_id" "uuid", "p_accounting_account_id" "uuid", "p_additional_notes" "text") RETURNS "tuasesor"."expense_report_items" LANGUAGE "plpgsql" SET "search_path" TO 'pg_catalog', 'tuasesor' AS $$ DECLARE v_report tuasesor.expense_reports; v_item tuasesor.expense_report_items; BEGIN IF auth.uid() IS NULL THEN RAISE EXCEPTION 'Authentication is required' USING ERRCODE = '42501'; END IF; IF p_workspace_id IS NULL OR p_expense_report_id IS NULL THEN RAISE EXCEPTION 'Workspace and expense report are required' USING ERRCODE = '22023'; END IF; SELECT expense_report.* INTO v_report FROM tuasesor.expense_reports AS expense_report WHERE expense_report.id = p_expense_report_id AND expense_report.workspace_id = p_workspace_id; IF NOT FOUND THEN RAISE EXCEPTION 'Expense report was not found' USING ERRCODE = 'P0002'; END IF; IF NOT tuasesor.can_write_workspace(p_workspace_id) THEN RAISE EXCEPTION 'Not authorized to write in this workspace' USING ERRCODE = '42501'; END IF; IF v_report.status <> 'draft' THEN RAISE EXCEPTION 'Expense report is not editable' USING ERRCODE = '55000'; END IF; IF p_expense_date IS NULL THEN RAISE EXCEPTION 'expense_date is required' USING ERRCODE = '22023'; END IF; IF p_total_amount IS NULL OR p_total_amount <= 0 OR p_total_amount > 999999999999.99 OR p_total_amount <> round(p_total_amount, 2) THEN RAISE EXCEPTION 'total_amount is invalid' USING ERRCODE = '22023'; END IF; IF p_business_line_id IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM tuasesor.business_lines AS business_line WHERE business_line.id = p_business_line_id AND business_line.workspace_id = p_workspace_id ) THEN RAISE EXCEPTION 'Business line does not belong to the workspace' USING ERRCODE = '23503'; END IF; IF p_cost_center_id IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM tuasesor.cost_centers AS cost_center WHERE cost_center.id = p_cost_center_id AND cost_center.workspace_id = p_workspace_id ) THEN RAISE EXCEPTION 'Cost center does not belong to the workspace' USING ERRCODE = '23503'; END IF; IF p_accounting_account_id IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM tuasesor.accounting_accounts AS accounting_account WHERE accounting_account.id = p_accounting_account_id AND accounting_account.workspace_id = p_workspace_id ) THEN RAISE EXCEPTION 'Accounting account does not belong to the workspace' USING ERRCODE = '23503'; END IF; IF p_document_type_id IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM tuasesor.document_types AS document_type WHERE document_type.id = p_document_type_id ) THEN RAISE EXCEPTION 'Document type does not exist' USING ERRCODE = '23503'; END IF; INSERT INTO tuasesor.expense_report_items ( expense_report_id, business_line_id, cost_center_id, accounting_account_id, expense_date, supplier_name, document_type_id, document_number, total_amount, description, additional_notes ) VALUES ( p_expense_report_id, p_business_line_id, p_cost_center_id, p_accounting_account_id, p_expense_date, nullif(btrim(p_supplier_name), ''), p_document_type_id, nullif(btrim(p_document_number), ''), p_total_amount, nullif(btrim(p_description), ''), nullif(btrim(p_additional_notes), '') ) RETURNING * INTO v_item; RETURN v_item; END; $$
  • "tuasesor"."create_oauth_transaction"("p_provider" "text", "p_operation" "text", "p_owner_type" "text", "p_owner_id" "uuid", "p_state_hash" "text", "p_target_integration_id" "uuid" DEFAULT NULL::"uuid") RETURNS "uuid" LANGUAGE "plpgsql" SECURITY DEFINER SET "search_path" TO 'pg_catalog', 'tuasesor' AS $_$ DECLARE v_actor_profile_id uuid := auth.uid(); v_workspace_member_id uuid; v_provider text; v_transaction_id uuid; BEGIN IF v_actor_profile_id IS NULL THEN RAISE EXCEPTION 'Authentication required' USING ERRCODE = '42501'; END IF; IF NOT EXISTS ( SELECT 1 FROM tuasesor.profiles AS profile WHERE profile.id = v_actor_profile_id ) THEN RAISE EXCEPTION 'Authenticated profile is not available' USING ERRCODE = '42501'; END IF; IF p_provider IS NULL OR btrim(p_provider) = '' THEN RAISE EXCEPTION 'OAuth provider is required' USING ERRCODE = '22023'; END IF; v_provider := btrim(p_provider); IF p_state_hash IS NULL OR p_state_hash !~ '^[0-9a-f]{64}$' THEN RAISE EXCEPTION 'OAuth state hash must be a lowercase SHA-256 hex digest' USING ERRCODE = '22023'; END IF; IF p_operation IS NULL OR p_operation NOT IN ('connect', 'reconnect') THEN RAISE EXCEPTION 'Unsupported OAuth operation' USING ERRCODE = '22023'; END IF; IF p_owner_type IS NULL OR p_owner_type NOT IN ('profile', 'workspace') THEN RAISE EXCEPTION 'Unsupported OAuth owner type' USING ERRCODE = '22023'; END IF; IF p_owner_id IS NULL THEN RAISE EXCEPTION 'OAuth owner is required' USING ERRCODE = '22023'; END IF; IF ( p_operation = 'connect' AND p_target_integration_id IS NOT NULL ) OR ( p_operation = 'reconnect' AND p_target_integration_id IS NULL ) THEN RAISE EXCEPTION 'OAuth operation and target integration are inconsistent' USING ERRCODE = '22023'; END IF; -- Opportunistic cleanup is sufficient because this table can only grow -- when new OAuth transactions are created. No scheduler is required. DELETE FROM tuasesor.oauth_transactions WHERE expires_at <= now(); IF p_owner_type = 'profile' THEN -- Personal integrations are always owned by the authenticated actor. IF p_owner_id <> v_actor_profile_id THEN RAISE EXCEPTION 'OAuth owner is not authorized' USING ERRCODE = '42501'; END IF; IF p_operation = 'reconnect' AND NOT EXISTS ( SELECT 1 FROM tuasesor.integrations AS integration WHERE integration.id = p_target_integration_id AND integration.owner_type = 'profile' AND integration.owner_profile_id = v_actor_profile_id AND integration.provider = v_provider ) THEN RAISE EXCEPTION 'OAuth reconnect target is invalid' USING ERRCODE = '22023'; END IF; INSERT INTO tuasesor.oauth_transactions ( state_hash, provider, operation, actor_profile_id, owner_type, owner_profile_id, target_integration_id ) VALUES ( p_state_hash, v_provider, p_operation, v_actor_profile_id, 'profile', v_actor_profile_id, p_target_integration_id ) RETURNING id INTO v_transaction_id; RETURN v_transaction_id; END IF; -- Workspace ownership must resolve the actor's current concrete membership. SELECT workspace_member.id INTO v_workspace_member_id FROM tuasesor.workspace_members AS workspace_member WHERE workspace_member.workspace_id = p_owner_id AND workspace_member.profile_id = v_actor_profile_id LIMIT 1; IF v_workspace_member_id IS NULL OR NOT tuasesor.has_workspace_permission( p_owner_id, 'integrations.manage' ) THEN RAISE EXCEPTION 'OAuth owner is not authorized' USING ERRCODE = '42501'; END IF; IF p_operation = 'reconnect' AND NOT EXISTS ( SELECT 1 FROM tuasesor.integrations AS integration WHERE integration.id = p_target_integration_id AND integration.owner_type = 'workspace' AND integration.owner_workspace_id = p_owner_id AND integration.provider = v_provider ) THEN RAISE EXCEPTION 'OAuth reconnect target is invalid' USING ERRCODE = '22023'; END IF; INSERT INTO tuasesor.oauth_transactions ( state_hash, provider, operation, actor_profile_id, owner_type, owner_workspace_id, workspace_member_id, target_integration_id ) VALUES ( p_state_hash, v_provider, p_operation, v_actor_profile_id, 'workspace', p_owner_id, v_workspace_member_id, p_target_integration_id ) RETURNING id INTO v_transaction_id; RETURN v_transaction_id; END; $_$
  • "tuasesor"."delete_expense_report_item"("p_workspace_id" "uuid", "p_expense_report_id" "uuid", "p_item_id" "uuid") RETURNS "uuid" LANGUAGE "plpgsql" SET "search_path" TO 'pg_catalog', 'tuasesor' AS $$ DECLARE v_report tuasesor.expense_reports; v_deleted_item_id uuid; BEGIN IF auth.uid() IS NULL THEN RAISE EXCEPTION 'Authentication is required' USING ERRCODE = '42501'; END IF; IF p_workspace_id IS NULL OR p_expense_report_id IS NULL OR p_item_id IS NULL THEN RAISE EXCEPTION 'Workspace, expense report and item are required' USING ERRCODE = '22023'; END IF; SELECT expense_report.* INTO v_report FROM tuasesor.expense_reports AS expense_report WHERE expense_report.id = p_expense_report_id AND expense_report.workspace_id = p_workspace_id; IF NOT FOUND THEN RAISE EXCEPTION 'Expense report was not found' USING ERRCODE = 'P0002'; END IF; IF NOT tuasesor.can_write_workspace(p_workspace_id) THEN RAISE EXCEPTION 'Not authorized to write in this workspace' USING ERRCODE = '42501'; END IF; IF v_report.status <> 'draft' THEN RAISE EXCEPTION 'Expense report is not editable' USING ERRCODE = '55000'; END IF; DELETE FROM tuasesor.expense_report_items WHERE id = p_item_id AND expense_report_id = p_expense_report_id RETURNING id INTO v_deleted_item_id; IF NOT FOUND THEN RAISE EXCEPTION 'Expense report item was not found' USING ERRCODE = 'P0002'; END IF; RETURN v_deleted_item_id; END; $$
  • "tuasesor"."delete_integration_credential_secret"() RETURNS "trigger" LANGUAGE "plpgsql" SECURITY DEFINER SET "search_path" TO 'pg_catalog', 'tuasesor' AS $$ DECLARE v_secret_name text := 'tuasesor-oauth-integration-' \|\| OLD.integration_id::text; BEGIN DELETE FROM vault.secrets WHERE name = v_secret_name; RETURN OLD; END; $$
  • "tuasesor"."delete_integration_credentials"("p_integration_id" "uuid") RETURNS "void" LANGUAGE "plpgsql" SECURITY DEFINER SET "search_path" TO 'pg_catalog', 'tuasesor' AS $$ DECLARE v_secret_name text; BEGIN IF p_integration_id IS NULL THEN RAISE EXCEPTION 'Integration id is required' USING ERRCODE = '22023'; END IF; v_secret_name := 'tuasesor-oauth-integration-' \|\| p_integration_id::text; DELETE FROM tuasesor.integration_credentials WHERE integration_id = p_integration_id; -- Idempotent orphan cleanup if metadata was already absent. IF NOT FOUND THEN DELETE FROM vault.secrets WHERE name = v_secret_name; END IF; END; $$
  • "tuasesor"."get_integration_credentials"("p_integration_id" "uuid") RETURNS TABLE("access_token" "text", "refresh_token" "text", "expires_at" timestamp with time zone) LANGUAGE "plpgsql" SECURITY DEFINER SET "search_path" TO 'pg_catalog', 'tuasesor' AS $$ DECLARE v_secret_name text; v_secret_text text; v_secret_payload jsonb; v_expires_at timestamptz; v_access_token text; v_refresh_token text; BEGIN IF p_integration_id IS NULL THEN RAISE EXCEPTION 'Integration id is required' USING ERRCODE = '22023'; END IF; v_secret_name := 'tuasesor-oauth-integration-' \|\| p_integration_id::text; SELECT credential.expires_at, secret.decrypted_secret INTO v_expires_at, v_secret_text FROM tuasesor.integration_credentials AS credential LEFT JOIN vault.decrypted_secrets AS secret ON secret.name = v_secret_name WHERE credential.integration_id = p_integration_id; IF NOT FOUND THEN RETURN; END IF; IF v_secret_text IS NULL THEN RAISE EXCEPTION 'OAuth credential metadata exists but secret material is missing' USING ERRCODE = '55000'; END IF; BEGIN v_secret_payload := v_secret_text::jsonb; EXCEPTION WHEN others THEN RAISE EXCEPTION 'OAuth credential secret payload is invalid' USING ERRCODE = '55000'; END; IF jsonb_typeof(v_secret_payload) <> 'object' THEN RAISE EXCEPTION 'OAuth credential secret payload is invalid' USING ERRCODE = '55000'; END IF; v_access_token := v_secret_payload ->> 'access_token'; v_refresh_token := v_secret_payload ->> 'refresh_token'; IF v_access_token IS NULL OR btrim(v_access_token) = '' THEN RAISE EXCEPTION 'OAuth credential secret payload has no valid access token' USING ERRCODE = '55000'; END IF; IF v_refresh_token IS NOT NULL AND btrim(v_refresh_token) = '' THEN RAISE EXCEPTION 'OAuth credential secret payload has an invalid refresh token' USING ERRCODE = '55000'; END IF; RETURN QUERY SELECT v_access_token, v_refresh_token, v_expires_at; END; $$
  • "tuasesor"."handle_auth_user_created"() RETURNS "trigger" LANGUAGE "plpgsql" SECURITY DEFINER SET "search_path" TO 'public' AS $$ DECLARE resolved_full_name text; resolved_avatar_url text; resolved_auth_metadata jsonb; BEGIN IF new.email IS NULL OR btrim(new.email) = '' THEN RAISE EXCEPTION 'Cannot synchronize auth user % without an email address', new.id; END IF; resolved_full_name := coalesce( nullif(new.raw_user_meta_data ->> 'full_name', ''), nullif(new.raw_user_meta_data ->> 'name', '') ); resolved_avatar_url := coalesce( nullif(new.raw_user_meta_data ->> 'avatar_url', ''), nullif(new.raw_user_meta_data ->> 'picture', '') ); resolved_auth_metadata := jsonb_build_object( 'auth', jsonb_build_object( 'provider', new.raw_app_meta_data ->> 'provider', 'providers', coalesce( new.raw_app_meta_data -> 'providers', '[]'::jsonb ), 'provider_id', new.raw_user_meta_data ->> 'provider_id', 'email_verified', coalesce( (new.raw_user_meta_data ->> 'email_verified')::boolean, false ) ) ); INSERT INTO tuasesor.profiles ( id, email, full_name, avatar_url, metadata_json ) VALUES ( new.id, new.email, resolved_full_name, resolved_avatar_url, resolved_auth_metadata ) ON CONFLICT (id) DO UPDATE SET email = excluded.email, full_name = coalesce( excluded.full_name, tuasesor.profiles.full_name ), avatar_url = coalesce( excluded.avatar_url, tuasesor.profiles.avatar_url ), metadata_json = coalesce(tuasesor.profiles.metadata_json, '{}'::jsonb) \|\| excluded.metadata_json, updated_at = now(); RETURN new; END; $$
  • "tuasesor"."has_workspace_permission"("p_workspace_id" "uuid", "p_permission_key" "text") RETURNS boolean LANGUAGE "sql" STABLE SECURITY DEFINER SET "search_path" TO 'pg_catalog', 'tuasesor' AS $$ with actor_membership as ( select workspace_member.id, workspace_member.role from tuasesor.workspace_members as workspace_member where workspace_member.workspace_id = p_workspace_id and workspace_member.profile_id = auth.uid() limit 1 ), workspace_override as ( select permission.granted from tuasesor.workspace_member_permissions as permission join actor_membership on actor_membership.id = permission.workspace_member_id where permission.workspace_id = p_workspace_id and permission.workspace_source_id is null and permission.permission_key = p_permission_key limit 1 ) select case when auth.uid() is null then false when p_workspace_id is null then false when p_permission_key is null then false when p_permission_key not in ( 'documents.read', 'documents.download', 'documents.analyze', 'workspace_sources.manage', 'integrations.manage', 'workspace_access.manage' ) then false when not exists ( select 1 from actor_membership ) then false when p_permission_key = 'workspace_access.manage' then coalesce( ( select case when actor_membership.role = 'owner' then true when actor_membership.role = 'admin' and exists ( select 1 from workspace_override ) then ( select workspace_override.granted from workspace_override ) else false end from actor_membership ), false ) when exists ( select 1 from workspace_override ) then ( select workspace_override.granted from workspace_override ) when p_permission_key in ( 'documents.read', 'documents.download', 'documents.analyze', 'workspace_sources.manage', 'integrations.manage' ) then coalesce( ( select actor_membership.role in ('owner', 'admin') from actor_membership ), false ) else false end; $$
  • "tuasesor"."has_workspace_source_permission"("p_workspace_id" "uuid", "p_workspace_source_id" "uuid", "p_permission_key" "text") RETURNS boolean LANGUAGE "sql" STABLE SECURITY DEFINER SET "search_path" TO 'pg_catalog', 'tuasesor' AS $$ with actor_membership as ( select workspace_member.id, workspace_member.role from tuasesor.workspace_members as workspace_member where workspace_member.workspace_id = p_workspace_id and workspace_member.profile_id = auth.uid() limit 1 ), source_in_workspace as ( select workspace_source.id from tuasesor.workspace_sources as workspace_source where workspace_source.id = p_workspace_source_id and workspace_source.workspace_id = p_workspace_id limit 1 ), source_override as ( select permission.granted from tuasesor.workspace_member_permissions as permission join actor_membership on actor_membership.id = permission.workspace_member_id where permission.workspace_id = p_workspace_id and permission.workspace_source_id = p_workspace_source_id and permission.permission_key = p_permission_key limit 1 ), workspace_override as ( select permission.granted from tuasesor.workspace_member_permissions as permission join actor_membership on actor_membership.id = permission.workspace_member_id where permission.workspace_id = p_workspace_id and permission.workspace_source_id is null and permission.permission_key = p_permission_key limit 1 ) select case when auth.uid() is null then false when p_workspace_id is null then false when p_workspace_source_id is null then false when p_permission_key is null then false when p_permission_key not in ( 'documents.read', 'documents.download', 'documents.analyze' ) then false when not exists ( select 1 from actor_membership ) then false when not exists ( select 1 from source_in_workspace ) then false when exists ( select 1 from source_override ) then ( select source_override.granted from source_override ) when exists ( select 1 from workspace_override ) then ( select workspace_override.granted from workspace_override ) else coalesce( ( select actor_membership.role in ('owner', 'admin') from actor_membership ), false ) end; $$
  • "tuasesor"."prevent_expense_report_item_parent_change"() RETURNS "trigger" LANGUAGE "plpgsql" SET "search_path" TO 'pg_catalog', 'tuasesor' AS $$ BEGIN IF NEW.expense_report_id IS DISTINCT FROM OLD.expense_report_id THEN RAISE EXCEPTION 'expense_report_id cannot be changed' USING ERRCODE = '22023'; END IF; RETURN NEW; END; $$
  • "tuasesor"."reset_workspace_member_permission"("p_workspace_id" "uuid", "p_workspace_member_id" "uuid", "p_permission_key" "text", "p_workspace_source_id" "uuid" DEFAULT NULL::"uuid") RETURNS boolean LANGUAGE "plpgsql" SECURITY DEFINER SET "search_path" TO 'pg_catalog', 'tuasesor' AS $$ declare v_actor_role text; v_target_role text; v_permission_id uuid; begin if auth.uid() is null then raise exception 'Authentication required' using errcode = '42501'; end if; if p_workspace_id is null or p_workspace_member_id is null or p_permission_key is null then raise exception 'Workspace, target membership and permission key are required' using errcode = '22023'; end if; if p_permission_key not in ( 'documents.read', 'documents.download', 'documents.analyze', 'workspace_sources.manage', 'integrations.manage', 'workspace_access.manage' ) then raise exception 'Unsupported workspace permission key' using errcode = '22023'; end if; if p_workspace_source_id is not null and p_permission_key not in ( 'documents.read', 'documents.download', 'documents.analyze' ) then raise exception 'Permission key cannot be scoped to a workspace source' using errcode = '22023'; end if; select workspace_member.role into v_actor_role from tuasesor.workspace_members as workspace_member where workspace_member.workspace_id = p_workspace_id and workspace_member.profile_id = auth.uid(); if v_actor_role is null then raise exception 'Insufficient workspace permission' using errcode = '42501'; end if; if p_permission_key = 'workspace_access.manage' then if v_actor_role <> 'owner' then raise exception 'Only workspace owners may manage workspace_access.manage' using errcode = '42501'; end if; elsif not tuasesor.has_workspace_permission( p_workspace_id, 'workspace_access.manage' ) then raise exception 'Insufficient workspace permission' using errcode = '42501'; end if; select workspace_member.role into v_target_role from tuasesor.workspace_members as workspace_member where workspace_member.id = p_workspace_member_id and workspace_member.workspace_id = p_workspace_id; if v_target_role is null then raise exception 'Target membership does not belong to the workspace' using errcode = '22023'; end if; if v_actor_role <> 'owner' and v_target_role = 'owner' then raise exception 'Delegated administrators cannot modify owner permissions' using errcode = '42501'; end if; if p_workspace_source_id is not null and not exists ( select 1 from tuasesor.workspace_sources as workspace_source where workspace_source.id = p_workspace_source_id and workspace_source.workspace_id = p_workspace_id ) then raise exception 'Workspace source does not belong to the workspace' using errcode = '22023'; end if; delete from tuasesor.workspace_member_permissions where workspace_id = p_workspace_id and workspace_member_id = p_workspace_member_id and permission_key = p_permission_key and workspace_source_id is not distinct from p_workspace_source_id returning id into v_permission_id; if v_permission_id is null then return false; end if; insert into audit.events ( workspace_id, actor_profile_id, event_type, entity_type, entity_id, severity, metadata_json ) values ( p_workspace_id, auth.uid(), 'workspace.permission_override.reset', 'workspace_member_permission', v_permission_id::text, 'info', jsonb_build_object( 'workspace_member_id', p_workspace_member_id, 'permission_key', p_permission_key, 'workspace_source_id', p_workspace_source_id ) ); return true; end; $$
  • "tuasesor"."revoke_workspace_access_delegation_on_role_change"() RETURNS "trigger" LANGUAGE "plpgsql" SECURITY DEFINER SET "search_path" TO 'pg_catalog', 'tuasesor' AS $$ declare v_permission_id uuid; v_previous_granted boolean; begin if old.role is not distinct from new.role then return new; end if; delete from tuasesor.workspace_member_permissions where workspace_id = new.workspace_id and workspace_member_id = new.id and permission_key = 'workspace_access.manage' and workspace_source_id is null returning id, granted into v_permission_id, v_previous_granted; if v_permission_id is not null then insert into audit.events ( workspace_id, actor_profile_id, event_type, entity_type, entity_id, severity, metadata_json ) values ( new.workspace_id, auth.uid(), 'workspace.permission_override.revoked_on_role_change', 'workspace_member_permission', v_permission_id::text, 'info', jsonb_build_object( 'workspace_member_id', new.id, 'permission_key', 'workspace_access.manage', 'previous_granted', v_previous_granted, 'previous_role', old.role, 'new_role', new.role, 'workspace_source_id', null ) ); end if; return new; end; $$
  • "tuasesor"."set_integration_status_changed_at"() RETURNS "trigger" LANGUAGE "plpgsql" SET "search_path" TO 'pg_catalog', 'tuasesor' AS $$ BEGIN IF NEW.status IS DISTINCT FROM OLD.status THEN NEW.status_changed_at := now(); ELSE NEW.status_changed_at := OLD.status_changed_at; END IF; RETURN NEW; END; $$
  • "tuasesor"."set_workspace_member_permission"("p_workspace_id" "uuid", "p_workspace_member_id" "uuid", "p_permission_key" "text", "p_granted" boolean, "p_workspace_source_id" "uuid" DEFAULT NULL::"uuid") RETURNS "uuid" LANGUAGE "plpgsql" SECURITY DEFINER SET "search_path" TO 'pg_catalog', 'tuasesor' AS $$ declare v_actor_role text; v_target_role text; v_permission_id uuid; begin if auth.uid() is null then raise exception 'Authentication required' using errcode = '42501'; end if; if p_workspace_id is null or p_workspace_member_id is null or p_permission_key is null or p_granted is null then raise exception 'Workspace, target membership, permission key and granted value are required' using errcode = '22023'; end if; if p_permission_key not in ( 'documents.read', 'documents.download', 'documents.analyze', 'workspace_sources.manage', 'integrations.manage', 'workspace_access.manage' ) then raise exception 'Unsupported workspace permission key' using errcode = '22023'; end if; if p_workspace_source_id is not null and p_permission_key not in ( 'documents.read', 'documents.download', 'documents.analyze' ) then raise exception 'Permission key cannot be scoped to a workspace source' using errcode = '22023'; end if; select workspace_member.role into v_actor_role from tuasesor.workspace_members as workspace_member where workspace_member.workspace_id = p_workspace_id and workspace_member.profile_id = auth.uid(); if v_actor_role is null then raise exception 'Insufficient workspace permission' using errcode = '42501'; end if; if p_permission_key = 'workspace_access.manage' then if v_actor_role <> 'owner' then raise exception 'Only workspace owners may manage workspace_access.manage' using errcode = '42501'; end if; elsif not tuasesor.has_workspace_permission( p_workspace_id, 'workspace_access.manage' ) then raise exception 'Insufficient workspace permission' using errcode = '42501'; end if; select workspace_member.role into v_target_role from tuasesor.workspace_members as workspace_member where workspace_member.id = p_workspace_member_id and workspace_member.workspace_id = p_workspace_id; if v_target_role is null then raise exception 'Target membership does not belong to the workspace' using errcode = '22023'; end if; if v_actor_role <> 'owner' and v_target_role = 'owner' then raise exception 'Delegated administrators cannot modify owner permissions' using errcode = '42501'; end if; if p_permission_key = 'workspace_access.manage' and v_target_role <> 'admin' then raise exception 'workspace_access.manage may only be delegated to admin memberships' using errcode = '22023'; end if; if p_workspace_source_id is not null and not exists ( select 1 from tuasesor.workspace_sources as workspace_source where workspace_source.id = p_workspace_source_id and workspace_source.workspace_id = p_workspace_id ) then raise exception 'Workspace source does not belong to the workspace' using errcode = '22023'; end if; if p_workspace_source_id is null then insert into tuasesor.workspace_member_permissions ( workspace_id, workspace_member_id, workspace_source_id, permission_key, granted ) values ( p_workspace_id, p_workspace_member_id, null, p_permission_key, p_granted ) on conflict ( workspace_member_id, permission_key ) where workspace_source_id is null do update set granted = excluded.granted returning id into v_permission_id; else insert into tuasesor.workspace_member_permissions ( workspace_id, workspace_member_id, workspace_source_id, permission_key, granted ) values ( p_workspace_id, p_workspace_member_id, p_workspace_source_id, p_permission_key, p_granted ) on conflict ( workspace_member_id, workspace_source_id, permission_key ) where workspace_source_id is not null do update set granted = excluded.granted returning id into v_permission_id; end if; insert into audit.events ( workspace_id, actor_profile_id, event_type, entity_type, entity_id, severity, metadata_json ) values ( p_workspace_id, auth.uid(), 'workspace.permission_override.set', 'workspace_member_permission', v_permission_id::text, 'info', jsonb_build_object( 'workspace_member_id', p_workspace_member_id, 'permission_key', p_permission_key, 'granted', p_granted, 'workspace_source_id', p_workspace_source_id ) ); return v_permission_id; end; $$
  • "tuasesor"."store_integration_credentials"("p_integration_id" "uuid", "p_access_token" "text", "p_refresh_token" "text" DEFAULT NULL::"text", "p_expires_at" timestamp with time zone DEFAULT NULL::timestamp with time zone) RETURNS "void" LANGUAGE "plpgsql" SECURITY DEFINER SET "search_path" TO 'pg_catalog', 'tuasesor' AS $$ DECLARE v_secret_name text; v_secret_id uuid; v_secret_payload text; BEGIN IF p_integration_id IS NULL THEN RAISE EXCEPTION 'Integration id is required' USING ERRCODE = '22023'; END IF; IF p_access_token IS NULL OR btrim(p_access_token) = '' THEN RAISE EXCEPTION 'OAuth access token is required' USING ERRCODE = '22023'; END IF; IF p_refresh_token IS NOT NULL AND btrim(p_refresh_token) = '' THEN RAISE EXCEPTION 'OAuth refresh token cannot be empty' USING ERRCODE = '22023'; END IF; -- Serialize credential mutations for one integration so concurrent first -- writes cannot race while creating the deterministic Vault secret. PERFORM 1 FROM tuasesor.integrations AS integration WHERE integration.id = p_integration_id FOR UPDATE; IF NOT FOUND THEN RAISE EXCEPTION 'OAuth integration does not exist' USING ERRCODE = '22023'; END IF; v_secret_name := 'tuasesor-oauth-integration-' \|\| p_integration_id::text; v_secret_payload := jsonb_build_object( 'access_token', p_access_token, 'refresh_token', p_refresh_token )::text; SELECT secret.id INTO v_secret_id FROM vault.secrets AS secret WHERE secret.name = v_secret_name; IF v_secret_id IS NULL THEN v_secret_id := vault.create_secret( v_secret_payload, v_secret_name, 'TUASESOR OAuth credential set for integration ' \|\| p_integration_id::text, NULL ); ELSE PERFORM vault.update_secret( v_secret_id, v_secret_payload, NULL, NULL, NULL ); END IF; INSERT INTO tuasesor.integration_credentials ( integration_id, expires_at ) VALUES ( p_integration_id, p_expires_at ) ON CONFLICT (integration_id) DO UPDATE SET expires_at = EXCLUDED.expires_at; END; $$
  • "tuasesor"."update_expense_report_item"("p_workspace_id" "uuid", "p_expense_report_id" "uuid", "p_item_id" "uuid", "p_expense_date" "date", "p_total_amount" numeric, "p_supplier_name" "text", "p_document_type_id" integer, "p_document_number" "text", "p_description" "text", "p_business_line_id" "uuid", "p_cost_center_id" "uuid", "p_accounting_account_id" "uuid", "p_additional_notes" "text") RETURNS "tuasesor"."expense_report_items" LANGUAGE "plpgsql" SET "search_path" TO 'pg_catalog', 'tuasesor' AS $$ DECLARE v_report tuasesor.expense_reports; v_item tuasesor.expense_report_items; BEGIN IF auth.uid() IS NULL THEN RAISE EXCEPTION 'Authentication is required' USING ERRCODE = '42501'; END IF; IF p_workspace_id IS NULL OR p_expense_report_id IS NULL OR p_item_id IS NULL THEN RAISE EXCEPTION 'Workspace, expense report and item are required' USING ERRCODE = '22023'; END IF; SELECT expense_report.* INTO v_report FROM tuasesor.expense_reports AS expense_report WHERE expense_report.id = p_expense_report_id AND expense_report.workspace_id = p_workspace_id; IF NOT FOUND THEN RAISE EXCEPTION 'Expense report was not found' USING ERRCODE = 'P0002'; END IF; IF NOT tuasesor.can_write_workspace(p_workspace_id) THEN RAISE EXCEPTION 'Not authorized to write in this workspace' USING ERRCODE = '42501'; END IF; IF v_report.status <> 'draft' THEN RAISE EXCEPTION 'Expense report is not editable' USING ERRCODE = '55000'; END IF; IF p_expense_date IS NULL THEN RAISE EXCEPTION 'expense_date is required' USING ERRCODE = '22023'; END IF; IF p_total_amount IS NULL OR p_total_amount <= 0 OR p_total_amount > 999999999999.99 OR p_total_amount <> round(p_total_amount, 2) THEN RAISE EXCEPTION 'total_amount is invalid' USING ERRCODE = '22023'; END IF; IF p_business_line_id IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM tuasesor.business_lines AS business_line WHERE business_line.id = p_business_line_id AND business_line.workspace_id = p_workspace_id ) THEN RAISE EXCEPTION 'Business line does not belong to the workspace' USING ERRCODE = '23503'; END IF; IF p_cost_center_id IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM tuasesor.cost_centers AS cost_center WHERE cost_center.id = p_cost_center_id AND cost_center.workspace_id = p_workspace_id ) THEN RAISE EXCEPTION 'Cost center does not belong to the workspace' USING ERRCODE = '23503'; END IF; IF p_accounting_account_id IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM tuasesor.accounting_accounts AS accounting_account WHERE accounting_account.id = p_accounting_account_id AND accounting_account.workspace_id = p_workspace_id ) THEN RAISE EXCEPTION 'Accounting account does not belong to the workspace' USING ERRCODE = '23503'; END IF; IF p_document_type_id IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM tuasesor.document_types AS document_type WHERE document_type.id = p_document_type_id ) THEN RAISE EXCEPTION 'Document type does not exist' USING ERRCODE = '23503'; END IF; UPDATE tuasesor.expense_report_items SET business_line_id = p_business_line_id, cost_center_id = p_cost_center_id, accounting_account_id = p_accounting_account_id, expense_date = p_expense_date, supplier_name = nullif(btrim(p_supplier_name), ''), document_type_id = p_document_type_id, document_number = nullif(btrim(p_document_number), ''), total_amount = p_total_amount, description = nullif(btrim(p_description), ''), additional_notes = nullif(btrim(p_additional_notes), '') WHERE id = p_item_id AND expense_report_id = p_expense_report_id RETURNING * INTO v_item; IF NOT FOUND THEN RAISE EXCEPTION 'Expense report item was not found' USING ERRCODE = 'P0002'; END IF; RETURN v_item; END; $$
  • "tuasesor"."validate_expense_report_item_catalogs"() RETURNS "trigger" LANGUAGE "plpgsql" SET "search_path" TO 'pg_catalog', 'tuasesor' AS $$ DECLARE v_workspace_id uuid; BEGIN SELECT expense_report.workspace_id INTO v_workspace_id FROM tuasesor.expense_reports AS expense_report WHERE expense_report.id = NEW.expense_report_id; IF NOT FOUND THEN RAISE EXCEPTION 'Expense report was not found' USING ERRCODE = '23503'; END IF; IF NEW.business_line_id IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM tuasesor.business_lines AS business_line WHERE business_line.id = NEW.business_line_id AND business_line.workspace_id = v_workspace_id ) THEN RAISE EXCEPTION 'Business line does not belong to the expense report workspace' USING ERRCODE = '23503'; END IF; IF NEW.cost_center_id IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM tuasesor.cost_centers AS cost_center WHERE cost_center.id = NEW.cost_center_id AND cost_center.workspace_id = v_workspace_id ) THEN RAISE EXCEPTION 'Cost center does not belong to the expense report workspace' USING ERRCODE = '23503'; END IF; IF NEW.accounting_account_id IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM tuasesor.accounting_accounts AS accounting_account WHERE accounting_account.id = NEW.accounting_account_id AND accounting_account.workspace_id = v_workspace_id ) THEN RAISE EXCEPTION 'Accounting account does not belong to the expense report workspace' USING ERRCODE = '23503'; END IF; IF NEW.document_type_id IS NOT NULL AND NOT EXISTS ( SELECT 1 FROM tuasesor.document_types AS document_type WHERE document_type.id = NEW.document_type_id ) THEN RAISE EXCEPTION 'Document type does not exist' USING ERRCODE = '23503'; END IF; RETURN NEW; END; $$

Schema grants​

  • USAGE ON SCHEMA "tuasesor" TO "anon"
  • USAGE ON SCHEMA "tuasesor" TO "authenticated"
  • USAGE ON SCHEMA "tuasesor" TO "service_role"