TUASESOR Persistence And Database Security

This document is the authoritative overview of persistence, data ownership, tenant isolation, database authorization, lifecycle, and schema evolution for this repository. It explains the durable model and its current limitations; it is not a schema dump or a substitute for reviewing migrations and generated schema evidence.

Purpose

Persistence is the business-system boundary for TUASESOR-owned state. The current application depends on it for application profiles, profile admission status, external integration state, workspace membership checks, workspace-scoped expense reports, workspace-owned receipt sources, recoverable receipt-upload attempts, and operational receipt document metadata. Integration ownership supports explicit profile or workspace scope. The Google runtime consumes that hardened contract for profile-owned My Space and workspace-owned Rendiciones receipt authorization. The schema also contains broader foundations for normalized content, transactions, professional specialties, and accounting catalogs, but a table's presence does not make an unrelated product workflow active.

TUASESOR uses Supabase PostgreSQL. Product tables live in the non-public tuasesor schema. Supabase Auth remains authoritative for authentication identities, while TUASESOR owns the application profile and business interpretation associated with each identity.

The database is responsible for durable records, relationships, integrity constraints, grants, RLS policies, and transactional database functions. It does not replace:

• Supabase Auth identity and session validation;
• server-side workflow validation and authorization;
• Google authorization and Google Drive as the authority for live external resources;
• product decisions about collection purpose, retention, correction, export, blocking, and deletion;
• reusable Cortexa auth and connector behavior implemented outside this repository;
• organizational, contractual, privacy, or legal decisions.

Material claims in this document are based on the cumulative migrations, the generated schema export, current server-side access paths, database tests and scripts, and the canonical documentation corpus. Where those sources differ, the difference is stated rather than silently resolved.

Persistence Architecture

The ordinary application access path uses Supabase clients created with the public project key and the caller's session cookies. OAuth integration persistence and the expense-receipt recovery boundary additionally use narrowly scoped server-only service-role access after TUASESOR has resolved the authorized owner, workspace, source, report, item, or concrete integration required by the operation. Infrastructure privilege does not replace user, workspace, or integration authorization.

The persistence layers are:

1. Supabase Auth identity. auth.users is Supabase-owned. Server code calls getUser() to validate the session.
2. TUASESOR application profile. tuasesor.profiles is the application record linked one-to-one to the Auth identity by the same UUID.
3. External integration and credential boundary. tuasesor.integrations stores durable provider connection state with explicit profile or workspace ownership; tuasesor.integration_credentials stores non-secret credential metadata, while OAuth access and refresh tokens are encrypted outside the durable entity through the current secret-store implementation.
4. Workspace authorization context. workspaces, workspace_members, and related permission rows represent participation in a business workspace.
5. Workspace-owned business state. Expense reports and accounting catalogs carry mandatory workspace ownership.
6. Provider-owned resources. Google Drive files and folders used by My Space are fetched live and are not currently inserted into local document tables.
7. Controlled receipt persistence. workspace_sources, expense_receipt_upload_attempts, documents, and document_links participate in the narrow Rendiciones receipt path through controlled application/RPC boundaries. Receipt binaries remain externally owned by Google Drive.
8. Closed or foundational persistence. normalized_documents, transactions, professional-classification structures, and broader document-processing capabilities remain closed, transitional, or inactive unless a current workflow explicitly uses them.

Server-side repositories and adapters select the tuasesor schema explicitly. The expense-report repository uses authenticated user context and remains constrained by grants and RLS. The OAuth integration repository uses privileged service-role access but constrains operations by explicit owner, integration identity, provider, and provider-account identity; the credential adapter addresses only a concrete authorized integration through narrow credential RPCs.

Database changes are represented as ordered SQL migrations under database/migrations/. The generated schema copies under database/schema/ and docs/database/ are produced from the linked Supabase database. Each SQL pair and Markdown pair is byte-identical. The current export includes the integration/document hardening, protected membership reads, centralized workspace write helper, secure expense-item mutations, membership-scoped workspace discovery, the migration 043 granular permission contract, transactional permission audit events, and role-change delegation revocation. It therefore provides point-in-time structural evidence for the linked database, but it does not prove that another environment matches or replace historical migration replay validation.

Repository database sources have distinct roles:

Source	Role
database/migrations/	Ordered canonical implementation history. Historical replay and current migration validation must be read cumulatively; early migrations do not represent the current desired model.
database/schema/ and docs/database/schema.*	Generated structural snapshot of the linked database; useful for grants, policies, constraints, functions, and drift analysis after freshness is checked against active migrations and runtime assumptions.
database/rollback/	Manually maintained emergency reversal aid, not desired current state.
database/scripts/	Operational schema-export and Markdown-generation tooling.
database/tests/	Transactional database characterization and integrity checks; no automated runner is configured.
database/seeds/	Test or historical data setup, not schema authority. One seed targets the removed legacy model.
database/real-data/	Manually curated environment-specific data SQL. It is sensitive operational input, not a migration or generic seed, and must be reviewed against the current schema before use.
app/, server/, and lib/	Current application access layer, repositories, services, adapters, and authenticated Supabase clients.
docs/database/	Generated structural copies plus explanatory material that can lag implementation.

Schema And Ownership Boundaries

Schema or namespace	Purpose and ownership	Access boundary
tuasesor	TUASESOR-owned operational schema containing application, business, integration, and foundational records.	Schema usage is granted to anon, authenticated, and service_role; table grants and RLS determine actual row and operation access.
auth	Supabase-owned authentication namespace. TUASESOR consumes auth.users and auth.uid() but does not own the schema.	Supabase manages identities and sessions. A TUASESOR trigger reacts to Auth user changes.
audit	TUASESOR-owned audit/event schema. audit.events is actively used by the granular permission subsystem for transactional override and role-change lifecycle events.	Current coverage is deliberately narrow: permission-management functions and the membership role-change trigger write events, but TUASESOR does not yet have comprehensive cross-domain audit history.
public	Shared PostgreSQL namespace, not the TUASESOR business schema. A TUASESOR migration created public.update_updated_at_column(), which several triggers use.	It is a technical dependency of those triggers; system or unrelated public objects are not TUASESOR-owned merely because they share the namespace.

Supabase infrastructure schemas such as Storage and Realtime are not TUASESOR-owned, and current source does not show them as persistence paths for the implemented workflows.

Within tuasesor, ownership is not uniform:

• integrations supports explicit profile or workspace ownership through owner_type, with exactly the corresponding owner key required.
• Expense reports and accounting catalogs are workspace-owned and have mandatory workspace keys.
• Expense-report items are owned by their parent report.
• Profile specialties are owned by their profile relationship.
• Specialties and document types are global reference data.
• documents has nullable profile, workspace, and workspace-source references, so it has no single enforced ownership boundary.
• normalized_documents has a nullable workspace reference.
• transactions has only a nullable profile reference and no workspace key.
• Workspace sources are workspace-owned and require a workspace-owned integration. The Rendiciones receipt backend now consumes this boundary: migration 047 establishes source purpose/root semantics and closes direct ordinary-user access, while migration 048 defines controlled expense_receipts source configuration against an exact eligible Google integration. General source-administration UI remains pending.

Domain Overview

Domain	Current classification	Evidence-based interpretation
Authentication identities and profiles	Active	Auth identities are synchronized into profiles; application entry checks profile existence, email, and active status.
Workspaces and memberships	Active security foundation, incomplete administration	Membership rows drive expense-report and catalog authorization. The application discovers the caller's authorized workspaces through GET /api/workspaces and membership-scoped workspace reads. Workspace and membership administration remain unimplemented.
Roles and permissions	Granular database contract active; product wiring partial	Expense-report writes still use the coarse role matrix. Separately, migration 043 implements scoped granular permission evaluation, controlled override mutation, delegated administration, fail-closed semantics, and lifecycle-safe revocation of workspace_access.manage.
External integration and OAuth credential foundation	Database contract active; profile- and workspace-owned Google backend adapted	Migrations 044–046 implement explicit profile/workspace ownership, OAuth transaction state, durable connection lifecycle, and encrypted-at-rest secret storage. The Google runtime consumes these boundaries for profile-owned My Space and workspace-owned Rendiciones receipt authorization. Complete workspace connection-management UX remains pending.
Expense reports and items	Active draft-management workflow with receipt-upload backend; later lifecycle incomplete	The UI and authenticated HTTP API support workspace-scoped report listing, detail, initial draft creation, item totals, catalog reads, and secure item creation, update, and deletion while the report remains draft. A controlled backend path now validates, uploads, persists metadata for, and associates expense receipts. Formal submission, review, approval, rejection, reimbursement, cancellation, and broader document-management workflows remain unimplemented.
Business lines, cost centers, and accounting accounts	Active schema support, restricted	The create RPC validates them and authenticated members may read them. No current catalog-administration flow is present.
Document types	Global reference foundation	The expense-report RPC validates optional document types. It is not evidence of a document-processing workflow.
Operational documents	Narrow controlled receipt-metadata path active; direct ordinary-user access closed	Migration 050 uses documents only through controlled receipt finalization to persist provider-neutral metadata for externally owned files. Ordinary anonymous/authenticated direct table access remains closed; this does not establish a generic document repository or processing subsystem.
Normalized documents and derived content	Present but closed and unused	The table exists, but no current source calls it and RLS has no user policy. OCR, extraction, normalization, embeddings, and knowledge workflows are not implemented.
Workspace sources	Active receipt backend; administration UX pending	Migrations 044, 047, and 048 establish workspace-owned integration binding, provider-root identity, explicit purpose/root semantics, direct-table closure, and controlled expense_receipts source configuration. The Rendiciones backend consumes this source; general workspace source-management UI remains pending.
Transactions	Historical or transitional foundation	The table remains in the schema but has no current service or product access path and lacks a workspace key.
Specialties and profile specialties	Foundation, not an active workflow	Tables exist without current services or UI persistence paths.
Audit and event history	Active foundation with narrow coverage	audit.events records permission override set/reset operations and automatic delegation revocation on membership role changes. Comprehensive report, credential, membership, and general mutation history remains unimplemented.

Client, task, calendar, history, and control-tower screens do not establish corresponding persistence domains. Current repository evidence for those surfaces is local or static presentation data.

Core Entities

Profile

Purpose. A profile is TUASESOR's application-level record for a Supabase Auth identity. It stores email, display attributes, application role/type metadata, and the admission status used by the web application.

Ownership. TUASESOR owns the profile record; Supabase owns the corresponding authentication identity. The profile primary key equals auth.users.id.

Relationships. A profile can own integrations and profile-specialty relationships, participate in workspaces through memberships, and be referenced by documents, workspace sources, transactions, and expense reports.

Lifecycle. An Auth trigger creates or updates profile identity metadata. Profile status is pending, active, or disabled. Application middleware and the primary auth callback admit only active profiles with an email. Deleting the Auth user cascades to the profile.

Business And Consistency Rules. Profile email is unique and required. The Auth synchronization function preserves TUASESOR-managed global role, profile type, and status. Profile RLS is not enabled; all authenticated users have table-level SELECT access to all profile columns in the exported schema.

Workspace And Membership

Purpose. A workspace is the intended business collaboration and tenant context. A membership connects a profile to a workspace and stores its business role. Permission rows attach fine-grained keys to a membership.

Ownership. Workspace-owned aggregates use workspace_id. Memberships belong jointly to a workspace and profile. workspaces.created_by is nullable and is not backed by a foreign key in the exported schema, so it is provenance metadata rather than a reliable ownership constraint.

Relationships. Workspaces relate to memberships, workspace sources, catalogs, expense reports, and optional document or normalized-document associations. A membership owns its permission rows.

Lifecycle. Deleting a workspace cascades to memberships, workspace sources, catalogs, expense reports, and report items. Deleting a profile cascades its memberships and permissions but does not delete the workspace or its business records.

Business And Consistency Rules. A profile can have only one membership per workspace. Allowed role labels are owner, admin, member, viewer, and client. The database continues to evaluate those roles for expense-report writes through tuasesor.can_write_workspace(uuid): owner, admin, and member may write, while viewer and client are read-only. Granular permission rows are independently workspace-scoped, may optionally target a workspace source, use a controlled six-key vocabulary, and are evaluated through dedicated database functions rather than direct table access.

Integration

Purpose. An integration is TUASESOR's durable representation of an external-provider connection. It stores provider identity, granted scopes, connection lifecycle, ownership, provenance, and provider metadata. OAuth access and refresh tokens are no longer columns of the durable integration entity.

Ownership. Migration 044 defines explicit personal and workspace ownership. owner_type = 'profile' requires owner_profile_id and forbids owner_workspace_id; owner_type = 'workspace' requires owner_workspace_id and forbids owner_profile_id. connected_by_profile_id records the profile that established the connection when available; it is provenance and does not replace the ownership key. Provider-account identity likewise never replaces TUASESOR ownership.

Relationships. tuasesor.integration_credentials is a private one-to-one credential-metadata record keyed by integration_id. It stores token expiry and operational timestamps, while the access and refresh token material is stored outside the durable integration row through the current encrypted secret-store implementation. Workspace sources remain a separate model.

Credential storage. Migration 046 introduces a server-only credential boundary. Access and refresh tokens are stored encrypted at rest in Supabase Vault under the deterministic name tuasesor-oauth-integration-<integration_id>. The TUASESOR domain schema stores neither a Vault secret UUID nor a secret-backend discriminator. Supabase Vault is therefore the current infrastructure implementation, not a domain identity or permanent product dependency.

tuasesor.store_integration_credentials(...), tuasesor.get_integration_credentials(...), and tuasesor.delete_integration_credentials(...) are SECURITY DEFINER functions executable by service_role only. anon and authenticated cannot execute them, and direct table privileges on integration_credentials are revoked from anon, authenticated, and service_role. No credential-listing RPC exists.

Lifecycle. The durable integration lifecycle is independent from credential material. Removing credentials leaves the integration record intact. Physical deletion of an integration cascades through integration_credentials and removes the deterministic Vault secret. Migration 045 separately establishes single-use OAuth transaction state with an explicit short-lived lifecycle.

Current runtime status. The Google runtime is adapted to the contract introduced by migrations 044 through 046 for both explicit owner modes. Profile-owned integrations support My Space, while workspace-owned integrations can authorize the Rendiciones receipt flow with the required Drive scope. The runtime uses owner-aware durable integration lookup, a separate server-only service-role repository, narrow credential RPCs through the credential secret-store adapter, hashed single-use OAuth transaction state, exact reconnect targeting, multiple external accounts per owner/provider, and durable disconnect semantics that preserve the integration row.

Business And Consistency Rules. Ownership is structurally exclusive between profile and workspace. Provider and non-null provider-account identifiers cannot be blank. Connection status is constrained to the supported lifecycle states. OAuth secret material must not be returned through ordinary table reads, browser clients, logs, examples, generated documentation, or user-context database access.

Expense Report Aggregate

Purpose. An expense report, or rendicion, is the workspace-owned header for expense declarations. Expense-report items are its financial line records.

Ownership. workspace_id is mandatory on the report. Items inherit workspace ownership through their mandatory parent report. submitted_by_profile_id is optional attribution, not the tenant key. The creation RPC sets it to auth.uid() while creating a draft; despite its name, it therefore currently behaves as creator/actor attribution on that path, not proof of formal submission. Direct authorized table writes can set another valid profile or null, so the field remains semantically overloaded and transitional.

Relationships. A report belongs to a workspace and contains many items. Items may reference workspace catalogs and the global document-type catalog.

Lifecycle. Allowed status values describe draft, submission, review, approval, rejection, reimbursement, and cancellation. The database stores related timestamps but does not enforce state transitions or timestamp/status coherence. Individual expense items can be created, updated, or deleted only while their parent report remains draft. Report deletion cascades to items, although ordinary authenticated users have neither a DELETE grant nor a DELETE policy for report headers.

Business And Consistency Rules. Report number is unique within workspace and reporting year; the reporting year is limited to 2000 through 2100; item totals must be positive. The application computes the report total from its items rather than storing a header total. The report-creation RPC can insert a header and supplied items atomically. Individual item RPCs require an authorized writer role, verify the parent report and workspace, preserve the item's parent relationship, and enforce same-workspace catalog references through database constraints and triggers.

Workspace Catalogs And Document Types

Purpose. Business lines, cost centers, and accounting accounts classify expense items inside a workspace. Document types provide global classification.

Ownership. The three accounting catalogs have mandatory workspace ownership. Document types are global application reference data.

Relationships. Expense items may reference any of these classifications.

Lifecycle. Workspace catalogs have is_active flags and are cascade-deleted with their workspace. Deleting a referenced catalog or document type sets the item reference to null. No catalog-administration or archival workflow is implemented.

Business And Consistency Rules. Catalog code is unique within a workspace when non-null. Expense-item creation and update validate that business lines, cost centers, and accounting accounts belong to the same workspace as the parent report. The validate_expense_report_item_catalogs trigger applies this rule to direct table writes as well as RPC-driven mutations, preventing cross-workspace catalog references.

Document And Normalized Document

Purpose. documents can represent operational file/folder metadata. normalized_documents can represent extracted or transformed content derived from a source.

Ownership. Document profile, workspace, and workspace-source references remain nullable in the legacy-compatible model, so documents do not have one universal ownership rule. When workspace_source_id is populated, migration 047 requires workspace_id and enforces that the document and source belong to the same workspace through a composite foreign key. Normalized-document workspace and source-document references remain nullable.

Relationships. A document may reference a document type, profile, workspace, and workspace source. A normalized document may reference a document and workspace. Transactions may reference a document.

Lifecycle. Document sync status can mark a provider record as deleted, but no current synchronization workflow uses it. Profile and workspace references retain their legacy nullable lifecycle behavior. Migration 047 changes workspace-source deletion semantics for associated documents to ON DELETE RESTRICT, preventing a configured source from disappearing while documents still depend on it. Deleting a document sets a normalized document's source reference to null but may be restricted if a transaction still references it.

Business And Consistency Rules. Source, type, and sync status have limited allowed values. Migration 047 scopes external_id uniqueness to workspace_source_id for sourced documents instead of enforcing global external-identifier uniqueness; drive_file_id remains available as a legacy lookup identifier but is no longer globally unique. Normalized records require a source and external identifier but have no implemented deduplication constraint across them. Both document tables remain closed to ordinary user access by RLS; documents also has ordinary table privileges revoked.

Transitional And Foundational Entities

transactions models a basic income/expense record with a review status, optional profile, and optional document, but no workspace ownership. specialties and profile_specialties model professional classification. workspace_sources is an active database contract for workspace-owned external document roots: migration 047 adds explicit purpose and root_type, deterministic active-source semantics, and direct browser-role closure; migration 048 adds controlled expense_receipts source configuration against a concrete workspace-owned Google integration. Source-administration UI remains pending, but the Rendiciones receipt backend now consumes the configured source. The remaining transitional entities in this section must not be interpreted as active product capabilities.

Identity, Profiles, And Workspaces

The identity chain is:

Supabase Auth identity
        |
        | same UUID, one-to-one
        v
TUASESOR profile
        |
        | membership
        v
TUASESOR workspace

These concepts are not interchangeable:

• The Auth identity proves who authenticated.
• The profile stores TUASESOR identity metadata and application admission status.
• The membership states that a profile participates in a workspace.
• The membership role supplies the current coarse read/write authorization for expense reports; the granular permission subsystem is separately enforced by dedicated evaluators and controlled mutation functions, but expense-report policies do not currently consume those granular keys.
• The workspace owns business aggregates such as expense reports and catalogs.
• A provider account identifies an external account bound to a concrete integration. The integration separately identifies its TUASESOR owner as either a profile or workspace; provider-account identity and the profile that performed consent do not replace that ownership boundary.

A profile ID is mandatory for profile-owned integrations, memberships, and profile specialties; workspace-owned integrations instead require a workspace owner and do not use a profile owner key. Profile references remain nullable on documents, transactions, workspace sources, and expense-report attribution.

The Auth-to-profile trigger uses the Auth UUID as the profile primary key. Deleting the Auth user deletes the profile. That deletion also removes integrations, memberships, and profile-specialty relationships; it instead nulls profile references on documents, transactions, workspace sources, and submitted expense reports.

Application admission and database authorization are separate. The page middleware and primary application callback require an active profile, but API paths bypass that middleware check and the expense-report and Google APIs validate only the Auth user. RLS policies also use auth.uid() without checking profile status. The repository therefore does not prove that profile disabling blocks every API or database operation for an otherwise valid session.

Multi-Tenancy And Workspace Isolation

The implemented workspace isolation model combines:

• mandatory workspace_id values on workspace-owned records;
• a unique profile/workspace membership;
• self-only RLS and read-only authenticated grants on membership rows;
• membership-scoped RLS for reading workspaces, expense reports, expense items, and accounting catalogs;
• role-aware report write policies through can_write_workspace;
• invoker-rights RPC authorization for individual expense-item mutations;
• explicit workspace filters in the expense-report repository and services;
• workspace-scoped uniqueness for report numbers and catalog codes;
• database enforcement of immutable item parentage and same-workspace catalog references.

The authentication and authorization chain is:

auth.uid() -> tuasesor.profiles -> tuasesor.workspace_members -> workspace-scoped authorization

Supabase Auth proves the caller identity. The matching profile is the TUASESOR application identity. Membership establishes participation in a workspace. For expense reports, every authenticated member may read, but only owner, admin, and member may insert or update. viewer and client are read-only, missing membership is rejected, and the helper's explicit allowlist makes unsupported roles fail closed. PostgreSQL grants permit the operation class; RLS remains the final row boundary. Repository filters and service validation are defense in depth, not substitutes for those policies.

tuasesor.can_write_workspace(uuid) centralizes the coarse writer-role decision for owner, admin, and member. Expense-report INSERT and UPDATE policies and the report-creation RPC reuse that decision. Individual item RPCs resolve the parent report, require the supplied workspace to match that parent, require a writer role, and reject mutations unless the report remains draft. Item visibility and authorization derive tenant ownership through the parent report. The prevent_expense_report_item_parent_change trigger rejects reassignment to another report, while catalog validation prevents workspace-owned classifications from crossing tenant boundaries.

Migration 038 revokes anonymous and authenticated mutation privileges on workspace_members, regrants authenticated SELECT only, enables RLS, and exposes only the caller's own membership rows. Migration 042 adds membership-scoped workspace reads so authorized workspace choices can be discovered without opening workspace administration. Migration 043 adds workspace/source-scoped granular permissions, controlled evaluators and mutation RPCs, transactional audit events, and automatic invalidation of delegated workspace_access.manage authority when a membership role changes. The current generated snapshot includes these migration 043 controls as part of its later cumulative state.

Additional limitations are:

• granular permissions are implemented at the database boundary, but current expense-report workflows continue to use the coarse role matrix and no ordinary application UI currently administers permission overrides;
• no ordinary application workflow creates workspaces or administers memberships; those operations require a separately controlled privileged process;
• the selected workspace is browser-held request context derived from the caller's authorized memberships, not a server-owned active-workspace session;
• profile-owned Google resources do not change when the client switches workspace;
• documents and normalized documents have nullable workspace ownership;
• transactions have no workspace key;
• direct expense-report writes can provide submitting-profile attribution that the policies do not tie to auth.uid().

The current boundaries are therefore:

Boundary	Records
Profile-scoped	My Space integrations; profile-specialty relationships; some optional legacy associations
Workspace-scoped	Workspaces; workspace-owned integrations; expense reports; items through their parent; business lines; cost centers; accounting accounts; memberships; workspace sources; receipt-upload attempts
Global reference	Specialties; document types
Externally owned	Live Google Drive resources
Mixed or unresolved	Documents; normalized documents; transactions
Closed to ordinary users	Documents; normalized documents

Authentication, Grants, And Row-Level Security

Database access requires all applicable layers:

1. Supabase Auth establishes the caller and exposes auth.uid().
2. PostgreSQL schema and table grants permit an operation.
3. RLS limits rows when enabled.
4. Application services validate workflow inputs and apply query scoping.
5. Provider authorization limits external operations separately.

The current Google runtime constructs its privileged client only inside the server-only integration persistence boundary and only after the application has resolved and authorized the product owner. No table currently uses FORCE ROW LEVEL SECURITY, so database-owner and other bypass-capable roles must be treated as privileged infrastructure rather than tenant principals. Historical service_role access is broad on parts of the schema, but migration 046 specifically revokes direct service_role access to integration_credentials and exposes only the narrow credential RPC contract for OAuth secret operations. Infrastructure privilege does not replace profile/workspace ownership validation.

The table below reflects the current generated export reconciled with the cumulative active migrations and application access paths. A future conflict must be resolved through the source-of-truth hierarchy rather than by assuming that either an old migration or an unverified export is current.

Surface	Grants and RLS state	Effective ordinary-user model
integrations	Ordinary anonymous/authenticated direct access is closed by the current ownership model; privileged service access remains available	Durable connection rows are no longer an ordinary credential-bearing CRUD surface. Application authorization and controlled server-side orchestration must select the allowed integration before any privileged credential operation.
expense_reports	Authenticated SELECT, INSERT, UPDATE; no DELETE grant; RLS enabled	All workspace members may read. owner, admin, and member may insert or update through can_write_workspace; viewer and client are read-only. Ordinary authenticated callers cannot delete report headers.
expense_report_items	Authenticated SELECT, INSERT, UPDATE, DELETE; RLS enabled	All workspace members may read through the parent report. owner, admin, and member may create, update, or delete items only while the parent report remains draft; viewer and client are read-only. Parent immutability and catalog-workspace consistency are additionally enforced by triggers.
Business lines, cost centers, accounting accounts	Authenticated SELECT only; RLS enabled	Workspace-member reads only; writes require privileged access.
documents	Only service_role has direct table privileges; RLS enabled with no ordinary policy	Anonymous and authenticated callers cannot access the table directly. The receipt workflow writes document metadata only through its controlled server/RPC finalization boundary.
normalized_documents	Historical ALL grants to anonymous and authenticated roles; RLS enabled with no policy	RLS closes rows to ordinary roles despite broad grants. Privileged bypass remains possible.
workspaces	Historical ALL grants to anonymous and authenticated roles; RLS enabled; authenticated membership-scoped SELECT policy	Authenticated callers may read only workspaces where auth.uid() has a membership. Anonymous callers receive no rows through RLS. The historical grants remain broader than necessary and should not be interpreted as workspace-administration permission.
profiles	Authenticated SELECT; no RLS	Every authenticated user can select all profile rows and columns exposed by the table grant.
workspace_members	No anonymous grant; authenticated SELECT only; RLS enabled with self-profile SELECT	Ordinary callers can inspect only their own memberships and cannot mutate membership rows. The current generated snapshot confirms this active migration state.
workspace_member_permissions	No anonymous or authenticated direct table privileges; RLS enabled with no ordinary table policies; service_role retains privileged access	Ordinary callers cannot read or mutate permission rows directly. Effective permissions are evaluated through controlled functions, and overrides are changed through dedicated authenticated RPCs subject to workspace, role, scope, and delegation rules.
workspace_sources	Only service_role has direct table privileges; RLS enabled with no ordinary table policy	Migration 047 closes direct browser access. Migration 048 adds controlled authenticated configuration for expense_receipts, requiring an authorized workspace actor, workspace_sources.manage, and an eligible exact workspace-owned integration. General administration UI remains pending.
document_links	Only service_role has direct table privileges; RLS enabled with no ordinary table policy	Direct table access is closed. Authenticated receipt association mutations use controlled SECURITY DEFINER RPCs that validate authentication, workspace write access, draft state, document/source eligibility, and workspace integrity.
expense_receipt_upload_attempts	Direct table access is service-role only; controlled authenticated/service-role RPCs expose lifecycle operations	Migration 050 stores recoverable upload state and hashes, not receipt binaries or raw idempotency keys. pending, uploaded, and completed distinguish provider-side-effect uncertainty, durable provider acknowledgement, and completed business association.
transactions	Anonymous and authenticated ALL; no RLS	Broad access to financial records; unsafe even though no current app path uses it.
document_types and its sequence	Anonymous and authenticated ALL; no RLS	Global catalog is writable by ordinary roles, which is broader than its lookup purpose requires.
specialties, profile_specialties	Authenticated SELECT; no RLS	Global specialty reads may be intentional, but profile-specialty associations are visible across profiles.

Anonymous schema usage alone does not grant table access, but several historical table grants explicitly grant anonymous access. RLS and grants must always be reviewed together: a grant without a policy can be broad, while RLS without the necessary grant can make an intended operation unavailable.

Roles And Permissions

Workspace roles are business data, not PostgreSQL roles. The stored business roles are owner, admin, member, viewer, and client.

Migration 043 establishes the granular permission storage model. A permission row belongs to a workspace and workspace membership and may optionally target a workspace_source. The legacy metadata_json field is removed. Same-workspace composite foreign keys prevent a membership or source from being associated with a permission row from another workspace, and partial unique constraints distinguish workspace-level overrides from source-level overrides.

The controlled permission vocabulary is:

• source-capable: documents.read, documents.download, documents.analyze;
• workspace-only: workspace_sources.manage, integrations.manage, workspace_access.manage.

For source-capable permissions, a source-specific override takes precedence over a workspace-level override. For ordinary permission keys, an explicit applicable override takes precedence over the role default, and absence of both fails closed. owner and admin have positive role defaults for document, source-management, and integration-management permissions; member, viewer, and client do not.

workspace_access.manage is intentionally stricter:

• owner always has the capability by role;
• admin has it only through an explicit workspace-level delegation;
• member, viewer, and client do not receive it, even if stale data were somehow present.

Permission overrides are not administered through direct table writes. set_workspace_member_permission(...) and reset_workspace_member_permission(...) are controlled SECURITY DEFINER RPCs with restricted search paths and explicit execution grants. Ordinary permission changes require the actor to hold workspace_access.manage. Only an owner may mutate workspace_access.manage itself, and a new delegation may be assigned only to a current admin. A delegated admin cannot modify owner permissions. Source-scoped changes also require the source to belong to the same workspace.

A membership role transition invalidates prior administrative delegation. The trigger_revoke_workspace_access_delegation_on_role_change trigger runs after an actual role change, deletes any workspace-level workspace_access.manage override for that membership, and records the revocation transactionally in audit.events. Re-promoting the same membership to admin does not reactivate the former delegation; an owner must explicitly delegate it again.

The expense-report authorization contract remains intentionally separate and coarse:

Business role	Read expense reports and items	Create or update expense reports	Create, update, or delete items in draft reports
owner	Yes	Yes	Yes
admin	Yes	Yes	Yes
member	Yes	Yes	Yes
viewer	Yes	No	No
client	Yes	No	No
No membership	No	No	No

The coarse write decision occurs through can_write_workspace. Expense-report INSERT and UPDATE policies and the report-creation RPC use that workspace-role decision. Item mutations use the create_expense_report_item, update_expense_report_item, and delete_expense_report_item SECURITY INVOKER RPCs and additionally require a parent report that remains draft.

Catalog SELECT policies also remain membership-based. The granular permission subsystem is active at the database boundary, but current product workflows expose only selected uses of it. In particular, controlled receipt-source configuration evaluates workspace_sources.manage; expense-report reading and item mutation still use the coarse membership/role matrix. There is no ordinary permission-administration UI, and broader integration/source/document permission wiring must remain explicit rather than assumed.

Membership addition or removal immediately changes membership-based authorization for existing records. Changing a role immediately changes coarse expense-report/item write access and also invalidates any previous workspace_access.manage delegation. Formal submit, review, approve, reject, reimburse, cancel, and other action/state-specific report permissions remain future authorization work rather than implied capabilities.

External Integrations And Credentials

Google is the currently implemented provider integration in application source, while migrations 044 through 046 establish a more general durable ownership, OAuth transaction, and credential-storage contract.

The durable integration record stores:

• explicit profile or workspace ownership;
• the profile that connected the integration when applicable;
• provider and provider-account identity;
• granted scopes;
• connection lifecycle/status;
• provider-specific metadata and operational timestamps.

OAuth secret material is deliberately separated:

• tuasesor.integration_credentials stores only the integration key, token expiry, and timestamps;
• access and refresh tokens are stored encrypted at rest in Supabase Vault;
• the deterministic current secret name is tuasesor-oauth-integration-<integration_id>;
• only the three narrow service-role credential RPCs may store, retrieve, or remove a concrete integration's credential set;
• there is no ordinary-user credential table access and no secret enumeration RPC.

The secret payload is treated as one credential set so access-token and refresh-token replacement occur together. Refresh-token absence is representable; an empty token value is rejected.

Supabase Vault is an infrastructure implementation rather than part of the TUASESOR domain model. No Vault UUID is persisted in the application schema. This keeps the durable integration model independent from the current secret backend and permits a future infrastructure migration, such as to Azure Key Vault, without redesigning integration ownership or exposing backend identifiers to product code.

Migration 046 also contains a guarded legacy migration path. Existing plaintext credentials are validated, copied to encrypted storage, verified, and only then are the legacy token columns removed. Inconsistent legacy credential states abort the transaction instead of being silently discarded. The paired rollback performs the inverse operation only after every encrypted credential can be validated and represented safely in the pre-046 model.

Stable security rules are:

• provider-account identifiers never replace TUASESOR ownership;
• browser and ordinary authenticated database roles must not read or mutate OAuth secret material;
• service-level credential access must remain server-side, narrow, and exceptional;
• authorization to use an integration is a separate application concern from privileged infrastructure access to its secret;
• credentials, authorization codes, client secrets, raw provider payloads, and service-role credentials must not appear in client responses, logs, production fixtures, examples, or documentation;
• secret-store implementation details must not leak into TUASESOR domain identity.

The database and current application runtime now evidence adoption of the privileged credential boundary and a product disconnect workflow. Credential-access auditing, complete operational key-management procedures, backup controls, and production secret-rotation procedures remain unevidenced.

External Resources And Local Persistence

My Space reads Google Drive metadata live on each request. The connector returns a non-recursive listing, optionally narrowed by a parent folder or configured root. TUASESOR maps provider results into a response containing identifiers, names, MIME types, file/folder type, parent relationships, timestamps, size, and provider links.

My Space remains profile-scoped at the product level. It can resolve multiple active Google integrations for the authenticated profile, requires explicit integrationId selection when more than one is available, and never derives provider ownership from the browser-selected workspace. Separately, the Rendiciones receipt flow uses an explicitly workspace-owned Google integration and configured expense_receipts source; that backend path does not reuse the My Space profile connection implicitly.

For My Space, if a Google resource is removed, a later live listing should reflect the provider result because Google remains authoritative. The My Space Drive route returns a transient projection and does not insert or update documents, workspace_sources, normalized_documents, or another local Drive-resource index. The Rendiciones receipt path is deliberately different: it persists only the configured source, recoverable upload state, provider-neutral document metadata, and expense-item association needed for that workflow while the receipt binary remains in Google Drive. Provider revocation or refresh failure produces reconnect behavior; provider downtime produces retry/recovery-oriented behavior.

External identifiers, file names, folder relationships, MIME types, timestamps, sizes, and links are not credentials, but they can be personal or confidential. The receipt workflow now persists only the subset required for its explicit business purpose and recovery contract. Persisting additional My Space resource metadata or broader document-derived data still requires an explicit owner, deduplication rule, authorization model, retention expectation, and source-removal behavior.

Documents And Derived Data

The current application implements one narrow document path: validated expense-receipt upload to an explicit workspace-owned Google Drive source plus controlled local persistence of recovery and document metadata. It does not implement TUASESOR-owned binary file storage, generic synchronization, general download, OCR, parsing, extraction, normalization, classification, embedding, semantic retrieval, or derived summaries.

The present persistence boundary is:

• documents remains a mixed legacy-compatible operational metadata structure globally, but migration 050 now uses it through the narrow receipt-finalization path with an explicit workspace source and same-workspace guarantees.
• expense_receipt_upload_attempts records recoverable receipt-upload lifecycle state. It stores SHA-256 hashes for idempotency/content identity and provider result metadata, but not the binary or raw Idempotency-Key.
• normalized_documents can hold extracted text and arbitrary metadata, but it remains closed by RLS and unused.
• document_types is a global lookup used by the expense-report creation contract; it does not establish a file-processing pipeline.
• legacy storage_path, sync fields, provider links, and polymorphic entity fields remain broader schema capabilities; their presence does not expand the narrow receipt contract into generic storage, synchronization, or document management.
• the repository consumes Cortexa auth and the Google Drive connector for authorization, My Space listing, and controlled receipt upload, but it does not currently depend on a Cortexa document SDK or knowledge service.

The schema alone does not define what happens to derived content when a source is removed. A document deletion sets normalized_documents.document_id to null, which can leave derived text detached from its source. Workspace deletion also sets normalized workspace ownership to null. No implemented workflow decides whether detached derived data is deleted, blocked, re-owned, or retained. Ordinary direct access to these persistence surfaces must remain closed, and broader document/derived-content workflows must not be opened until their lifecycle and authorization model are explicit.

Major Business Workflows

Profile Creation And Admission

Supabase creates or updates an Auth user. A security-definer trigger upserts the matching profile using the Auth UUID and synchronizes selected identity metadata while preserving TUASESOR role, type, and status. The web admission flow then requires a profile with an email and active status.

This workflow creates personal identity metadata and application status. It is transactional with the Auth trigger invocation, but it has no history table and its active-status check is not enforced uniformly across API and database paths.

Google Connection And Live Drive Browsing

An authenticated user starts a separate Google authorization flow under an explicit owner context. Profile-owned authorization supports My Space, while workspace-owned authorization supports the Rendiciones receipt source and requests the required drive.file scope. TUASESOR generates high-entropy OAuth state, persists only its hash through the migration 045 single-use transaction contract, and exchanges the authorization response through Cortexa auth after atomically claiming that transaction. Persistence uses an owner-aware integration repository plus the separate credential secret store rather than a constructor-scoped profile adapter. Later provider requests resolve the exact active integration required by the product flow; My Space lists Google-authoritative resources live, while the receipt workflow resolves its configured workspace source before upload. The backend disconnect endpoint targets an exact owned integration, invokes provider revocation before local credential removal, and preserves the durable integration lifecycle record. These backend capabilities do not imply that complete user-facing workspace connection/source management is implemented.

The runtime adapter now implements this lifecycle. Disconnect targets an exact owned integration, lets Cortexa revoke provider access first, marks the durable integration disconnected, removes credential material, and preserves the integration row for lifecycle history and safe reconnect behavior.

Workspace Receipt Source And Upload

An authorized workspace actor can configure the active expense_receipts source through configure_expense_receipt_workspace_source(...). The database requires an authenticated actor with workspace_sources.manage, an exact active workspace-owned Google integration for the same workspace, provider google, the required drive.file scope, and an explicit external folder root. Reconfiguring the same logical root preserves source identity, while another active source for the same purpose is deactivated.

Receipt upload begins with an authenticated database preflight before any provider side effect. The preflight validates workspace write access, report/item ownership, and draft state. Application validation additionally restricts supported receipt input to PDF, JPEG, or PNG. Migration 050 then creates or reuses a hashed idempotent upload attempt, the server uploads the binary through the Google Drive connector, a service-role-only step durably records the provider result, and authenticated finalization creates or reuses provider-neutral documents metadata and associates it with the expense item. An uploaded attempt can be finalized without uploading again, a completed attempt can reuse its completed document result, and a pending attempt blocks blind provider retry because the external side effect may be uncertain. OAuth tokens, receipt binaries, raw idempotency keys, provider credentials, and provider URLs are not persisted in the receipt-attempt entity.

Workspace Receipt Access

Migration 051 adds controlled receipt access without opening direct authenticated access to document tables. list_expense_report_item_receipts(...) validates membership, report/item ownership, and source-scoped documents.read before returning minimal receipt metadata. authorize_expense_receipt_download(...) validates the exact report/item/document association and source-scoped documents.download before server code may resolve provider details.

Permission administration remains owned by the migration 043 model. Workspace- and source-scoped overrides take effect immediately, explicit denial overrides inherited access according to the existing evaluator precedence, and controlled set/reset operations are transactionally audited in audit.events. Receipt download outcomes are recorded separately through the service-role-only record_expense_receipt_access_event(...) boundary without document content, provider URLs, tokens, or credentials. The application then streams the provider-owned binary server-side; TUASESOR does not persist the receipt binary.

Expense-Report Reading

The current GET API requires a workspace identifier and a valid Auth user. The repository filters by that workspace and returns reports, submitting-profile display data, and item amounts. RLS independently checks membership. The UI computes display totals from returned items.

The client-supplied workspace identifier is query context, not authorization proof. Effective isolation depends on the membership predicates and RLS. Active migrations protect membership rows from ordinary mutation, and the current generated snapshot confirms that state in the linked database at export time.

Expense-Report Creation And Item Management

The HTTP API exposes authenticated workspace-scoped listing and detail reads for expense reports. The detail flow also loads the report's expense items and the active catalogs available to the caller within the selected workspace. The client-supplied workspace identifier remains request context rather than authorization proof; application services, RPC authorization, grants, constraints, triggers, and RLS independently enforce access and integrity.

Initial report creation validates the workspace, report number, year, and optional notes, then creates a draft header through the invoker-rights report RPC. Full atomic creation with supplied items remains supported at the service and RPC level, although the current creation dialog begins with an empty draft.

Expense-item creation, update, and deletion are active through the create_expense_report_item, update_expense_report_item, and delete_expense_report_item SECURITY INVOKER RPCs. These operations require an authenticated caller with an owner, admin, or member role in the supplied workspace and a parent report whose status remains draft. The mutation path also verifies parent ownership, prevents moving an item to another report, and validates that workspace-owned catalogs belong to the same workspace. viewer and client roles remain read-only.

Ordinary authenticated callers cannot delete expense-report headers. Formal submission, review, approval, rejection, reimbursement, cancellation, document handling, and transition-specific actor rules remain unimplemented. In the current creation path, submitted_by_profile_id records creator or actor attribution rather than proof that the report has entered a formally submitted state.

Workspace Discovery And Permission Administration

GET /api/workspaces derives the available workspace list from the authenticated profile's self-readable memberships and membership-scoped workspace reads. The browser selects only among that returned list and stores the selected workspace ID in localStorage as presentation and request context. This does not create a server-owned active-workspace session and does not replace authorization checks on each operation.

No current application workflow creates workspaces or administers memberships. Active migrations prevent ordinary authenticated callers from writing membership rows, so membership administration still requires a separately controlled privileged path that is not implemented here. The granular permission evaluation and mutation contract now exists in SQL, but no ordinary application workflow exposes that administration surface. Expense-report operations continue to apply the coarse role matrix.

Constraints And Data Integrity

Important implemented integrity rules include:

• UUID or identity primary keys on all major entities;
• a one-to-one profile/Auth foreign key with cascade deletion;
• one membership per profile/workspace plus scope-aware uniqueness for workspace-level and source-level permission overrides;
• mandatory workspace foreign keys for reports and accounting catalogs;
• report number uniqueness within workspace and year;
• report-year range and allowed report-status checks;
• positive expense-item amounts stored with two-decimal precision;
• expense-item text limits of 150 characters for supplier name, 100 for document number, and 500 each for description and additional notes;
• allowed transaction type/status and document source/type/sync values;
• one integration per owner, provider, and provider-account identity, allowing multiple external accounts for the same provider under one profile or workspace owner;
• one workspace source per workspace/provider/external identifier;
• one specialty assignment per profile/specialty;
• cascades from workspaces to reports and from reports to items;
• SET NULL behavior for optional attribution and classification references;
• immutable expense-item parent relationships;
• same-workspace validation for business-line, cost-center, and accounting-account references.

The four expense-item text-length checks introduced by migration 041 are declared NOT VALID. PostgreSQL therefore avoids validating all historical rows when the migration is installed, while still enforcing those checks for newly inserted or subsequently updated rows. A separate validation operation would be required before treating historical rows as proven compliant with those constraints.

Integrity is not authorization. A valid foreign key proves that a referenced row exists, not that the caller may use it. Expense-item authorization remains enforced through grants, RLS, writer-role checks, and the parent report's draft status. The prevent_expense_report_item_parent_change trigger prevents moving an existing item to another report, and validate_expense_report_item_catalogs rejects workspace-owned catalog references that do not belong to the parent report's workspace. These protections apply to direct table mutations as well as RPC-driven operations.

Known consistency gaps include:

• report attribution is not constrained to the authenticated profile or even to a workspace member on direct report writes;
• document and workspace-source references do not enforce matching workspace ownership;
• workspaces.created_by has no foreign key;
• status values do not enforce legal state transitions or matching lifecycle timestamps;
• no stored report total is checked against item totals;
• nullable catalog codes allow multiple uncoded rows despite workspace/code uniqueness;
• normalized source/external identifiers have indexes but no deduplication constraint;
• document external identifiers use global rather than workspace/provider-scoped uniqueness.

The report and item RPCs add workflow validation, but they do not replace database enforcement. Authorized roles retain direct report INSERT and UPDATE grants, so creator attribution remains weaker on direct report writes. Item parentage and catalog/workspace consistency, by contrast, are now enforced at the database level regardless of whether the mutation enters through an RPC or a direct authorized table operation.

Indexes And Performance

Material indexes support:

• workspace-key lookups for reports and accounting catalogs;
• report listing by workspace and creation time, status, or submitting profile;
• item lookup by report and expense date;
• profile/workspace membership joins used by RLS;
• profile and provider-account integration lookup;
• document lookup by workspace, source, external identifier, parent, and sync state;
• normalized-document lookup by workspace, source, external identifier, and MIME type.

Unique constraints also create indexes for report numbering, membership, integration identity, workspace source identity, and catalog codes. Several older document indexes overlap with unique constraints or retain legacy names. Indexes improve access paths and enforce some uniqueness; they are not tenant-isolation or authorization controls.

Functions, RPCs, Triggers, And Transactions

can_write_workspace

This stable SQL helper is SECURITY DEFINER, uses a restricted search path, revokes PUBLIC execution, and grants execution to authenticated. It returns true only when auth.uid() identifies an owner, admin, or member of the supplied workspace; null callers, null workspaces, missing memberships, viewer, client, and unsupported roles return false.

Definer execution lets the helper inspect workspace_members after that table is protected by self-only RLS, but the decision remains bound to the authenticated caller through auth.uid(). Its elevation and ownership are security-sensitive: broadening the function, its search path, or its execute grants could weaken every policy and RPC that reuses it.

Granular Permission Evaluators

has_workspace_permission(workspace_id, permission_key) and has_workspace_source_permission(workspace_id, workspace_source_id, permission_key) are fail-closed SECURITY DEFINER evaluators with restricted search paths. PUBLIC execution is revoked and execution is granted explicitly to the intended database roles.

has_workspace_permission resolves workspace-scoped permission state and role defaults. has_workspace_source_permission accepts only the source-capable document keys, verifies that the source belongs to the supplied workspace, and applies source override, then workspace override, then role default, then deny precedence.

Unknown keys, missing memberships, unauthenticated callers, cross-workspace sources, and invalid scope combinations return false rather than broadening access.

Granular Permission Mutation RPCs

set_workspace_member_permission(...) and reset_workspace_member_permission(...) are SECURITY DEFINER functions with restricted search paths. They provide the controlled mutation boundary for workspace_member_permissions.

Successful changes are transactional with their audit records:

• workspace.permission_override.set;
• workspace.permission_override.reset.

An explicit false override is semantically distinct from resetting an override. workspace_access.manage can be changed only by an owner; setting it requires the target membership to currently be admin, while reset remains available to the owner for stale-delegation cleanup after a role transition.

Membership Role-Change Permission Lifecycle Trigger

revoke_workspace_access_delegation_on_role_change() is a SECURITY DEFINER trigger function attached to workspace_members through trigger_revoke_workspace_access_delegation_on_role_change.

Any actual role transition deletes a workspace-level workspace_access.manage override for the changed membership. If an override is removed, the same transaction records workspace.permission_override.revoked_on_role_change in audit.events, including the target membership, previous grant value, previous role, new role, and permission key.

This prevents delegated administrative authority from surviving demotion or silently reactivating after a later re-promotion.

create_expense_report

This function is SECURITY INVOKER, has an explicit restricted search path, revokes PUBLIC execution, and grants execution to authenticated. It:

• obtains the profile from auth.uid();
• rejects unauthenticated calls or callers for whom can_write_workspace returns false;
• validates report fields and any supplied item array;
• verifies that workspace catalogs belong to the requested workspace;
• inserts the header and supplied items in one database transaction;
• returns the created report;
• relies on caller grants and RLS rather than bypassing them.

Any exception aborts the aggregate creation. The function does not allocate report numbers, consult granular permission keys, enforce status transitions, or create audit history.

Expense-Report Item Mutation RPCs

create_expense_report_item, update_expense_report_item, and delete_expense_report_item are SECURITY INVOKER functions with restricted search paths. PUBLIC execution is revoked and authenticated execution is granted explicitly.

Their shared authorization and workflow contract requires:

• an authenticated caller;
• a parent report belonging to the supplied workspace;
• an owner, admin, or member role accepted by can_write_workspace;
• a parent report whose status remains draft.

Creation and update validate the supported item fields, positive monetary values, text-length limits, and workspace-owned catalog references. Update preserves the existing parent report. Delete removes only the selected item and does not provide report-header deletion.

Because these functions execute as the caller, table grants, RLS, constraints, and triggers continue to apply. An RPC success therefore depends on every applicable enforcement layer rather than on function-level authorization alone.

Expense-Report Item Integrity Triggers

prevent_expense_report_item_parent_change rejects attempts to reassign an existing item to another expense report.

validate_expense_report_item_catalogs verifies that any referenced business line, cost center, or accounting account belongs to the same workspace as the parent report. This trigger applies to direct table mutations as well as RPC-driven operations.

These triggers do not generate report numbering, authorize lifecycle transitions, or create immutable audit history.

handle_auth_user_created

This Auth trigger function is SECURITY DEFINER. It synchronizes Auth identity metadata into tuasesor.profiles after relevant Auth inserts or updates and preserves TUASESOR-managed fields. Definer execution is necessary to cross the Auth/application boundary, but it is elevated and security-sensitive. The repository provides timestamps, not an audit trail of profile changes.

Updated-Time Triggers

The public.update_updated_at_column() helper updates timestamps for workspaces, workspace sources, normalized documents, accounting catalogs, expense reports, and expense items. Other tables either lack updated_at or update it through explicit application or function logic. A timestamp records when a row changed, not who changed it or why.

Receipt Source Configuration And Upload RPCs

configure_expense_receipt_workspace_source(...) is the controlled authenticated boundary introduced by migration 048. It validates the caller, workspace_sources.manage, workspace/integration ownership, provider, integration lifecycle, required Drive scope, source purpose, root type, and deterministic active-source semantics without copying credentials into workspace_sources.

preflight_expense_receipt_upload(...) is introduced by migration 049. It runs in authenticated caller context, performs no provider I/O and no document persistence, and validates the workspace, report, item, writer authorization, and draft state before an external upload can begin.

Migration 050 adds the receipt-upload recovery persistence contract. Beginning an attempt binds a SHA-256 idempotency-key hash to the logical workspace/source/report/item/content identity; the privileged provider-result step changes a pending attempt to uploaded only after the provider result is known; authenticated finalization creates or reuses the operational document row, invokes the controlled receipt-association contract, and moves the attempt to completed atomically. A failed business finalization therefore leaves an acknowledged provider upload recoverable rather than silently uploading a duplicate.

Database Authorization Test Evidence

database/tests/001_expense_reports_workspace_isolation.sql is a transactional characterization test that ends with ROLLBACK and depends on historical controlled profile, workspace, and report seed prerequisites. It is therefore not autonomous from a zero-state local reset. It covers membership-scoped reads, cross-workspace denial, unauthorized report creation, catalog rejection, report constraints, and the coarse role matrix.

database/tests/002_expense_report_item_mutations.sql was designed as an autonomous zero-state transactional characterization test for expense-item mutations. Its current fixture setup predates the migration 031 Auth-to-profile synchronization trigger and explicitly inserts profile rows after inserting auth.users. That fixture pattern is now stale and can conflict with the automatically created profile rows. Until that fixture is modernized, the test must not be described as currently reproducible from a zero-state reset.

database/tests/003_secure_oauth_credentials.sql is an autonomous zero-state transactional regression for migration 046. Its profile fixture follows the current Auth trigger contract. The test validates ordinary-role denial, service-role credential RPC behavior, encrypted Vault storage, stable secret replacement, credential deletion semantics, physical integration cleanup, and transactional fixture rollback.

database/tests/004_oauth_ownership_and_transactions.sql is an autonomous zero-state transactional characterization for migrations 044 and 045. It validates owner-scoped Google-account cardinality, profile and workspace ownership constraints, ordinary-role privilege boundaries, single-use OAuth transactions, actor and provider isolation, expiry, exact reconnect targeting, workspace permission enforcement, and permission revocation between transaction creation and claim.

database/tests/005_expense_receipt_document_contract.sql is an autonomous transactional characterization for migration 047. Its 30 assertions validate the workspace-source purpose/root contract, closure of direct ordinary-role access to workspace_sources and document_links, RLS activation, deterministic active-source purpose rules, receipt association eligibility, workspace integrity, controlled link/unlink RPC behavior, idempotency, draft-state restrictions, and protection against deleting an expense item while receipt links remain.

database/tests/006_expense_receipt_workspace_source_configuration.sql characterizes migration 048 source configuration, including workspace/integration ownership, required provider/scope/lifecycle rules, permission enforcement, deterministic active-source behavior, source identity reuse, and credential isolation.

database/tests/007_expense_receipt_upload_preflight.sql characterizes migration 049 preflight behavior, including authenticated workspace/report/item validation, writer authorization, draft-state enforcement, and the requirement that preflight itself creates no provider or document side effect.

database/tests/008_expense_receipt_upload_persistence.sql characterizes migration 050 recovery and persistence semantics, including hashed idempotency/content identity, pending/uploaded/completed lifecycle behavior, privileged provider-result recording, controlled document finalization, association rollback behavior, and duplicate-upload protection.

Application-level OAuth lifecycle behavior is characterized separately by server/auth/tests/oauth-lifecycle.characterization.ts. Its runner, server/auth/tests/run-oauth-lifecycle-characterization.ps1, compiles the real SupabaseAuthStorageAdapter together with the installed @cortexa/auth contract and exercises synthetic repository, secret-store, and provider boundaries. The scenarios cover new connection activation ordering, exact reconnect, refresh-token preservation when the provider omits a replacement token, refresh persistence, revoke-first disconnect ordering, failed revocation, failed secret deletion after revocation, and failed initial credential persistence.

These assets are durable evidence of the intended authorization, credential, ownership, source-configuration, preflight, document-association, upload-recovery, and lifecycle contracts, but they are not proof that every deployed environment currently enforces them. The repository has no root package test command or CI workflow that executes the SQL and application characterization suites automatically. Their execution therefore remains an explicit validation step.

Auditability And Traceability

Implemented evidence now includes both ordinary operational timestamps and a narrow transactional audit-event subsystem:

• many entities have created_at; some also have trigger-maintained updated_at;
• expense reports have submission, review, and reimbursement timestamps;
• submitted_by_profile_id records personal, audit-relevant actor metadata, although the current draft-creation path uses the field as creator attribution rather than proof of formal submission;
• profile synchronization records the current identity metadata;
• integration lifecycle and non-secret credential expiry metadata are stored; OAuth access and refresh token material is encrypted separately through the current secret-store implementation;
• audit.events records successful granular permission override set/reset operations;
• membership role changes that revoke workspace_access.manage produce workspace.permission_override.revoked_on_role_change;
• permission mutation and lifecycle-revocation audit events participate in the same transaction as the corresponding database change;
• ordered migrations and generated schema exports provide structural change evidence;
• current Google handlers log sanitized error categories and aggregate result counts.

The permission audit model remains deliberately narrow. It does not constitute a comprehensive TUASESOR audit trail. The database does not yet provide general mutation history across all business domains, complete membership history, credential-access history, report lifecycle history, universal before/after values, or general soft-delete history.

Permission events use actor_profile_id where an authenticated actor is available; lifecycle-trigger events can also represent privileged operational changes where no authenticated actor is present. Event metadata captures the permission target and relevant lifecycle context, but that coverage must not be generalized to unrelated persistence domains.

Provider logs, Supabase platform logs, backups, and hosting logs may exist operationally, but the repository neither configures nor verifies them as application audit evidence. Timestamps and operational logs alone do not establish complete auditability.

Retention, Blocking, Correction, Export, And Deletion

The repository defines no retention periods. Foreign-key actions and status flags are technical lifecycle mechanics, not business or legal retention rules.

Observable behavior includes:

• Auth-user deletion cascades to the profile, integrations, memberships, membership permissions, and profile specialties.
• Profile deletion nulls document, transaction, workspace-source, and expense-submitter references.
• Workspace deletion cascades to memberships, workspace sources, accounting catalogs, expense reports, and report items.
• Workspace deletion sets document and normalized-document workspace references to null, so those rows can survive detached from the tenant boundary.
• Expense-report deletion cascades to items, but ordinary authenticated users cannot delete reports through current grants/policies.
• Catalog deletion preserves expense items and sets classification references to null.
• Document deletion detaches normalized documents and can be blocked by referencing transactions.
• Product disconnect does not physically delete the integration. The Google disconnect endpoint revokes provider authorization first, transitions the durable integration to disconnected, and removes credential material. Physical integration deletion remains a separate database lifecycle operation and may still occur through applicable owner cascades.
• Provider revocation triggers reconnect behavior; it does not automatically erase the stored integration.
• profile.status can block normal page admission; is_active marks catalogs and workspace sources; document sync status can mark provider deletion. None is a general soft-delete system.

Database UPDATE privileges exist for integrations and expense reports/items. The integration adapter uses them for credential refresh, while current application routes expose no general report/item correction workflow. The report-list API and live Drive response expose limited data to the current UI; no general subject-data or tenant export mechanism is implemented. Removing the final membership or deleting profiles can leave workspace business data without an ordinary access path.

Retention needs for financial, labor, contractual, or evidentiary records cannot be inferred from foreign keys. Any material retention or deletion conclusion requires product ownership and, where Chilean legal requirements are involved, validation by a Chilean lawyer or the designated responsible role.

Privacy And Data Sensitivity

Data category	Subjects and purpose	Source and ownership	Current access and impact
Profile identity	Users; application admission and display	Supabase Auth source, TUASESOR profile record	Authenticated users can currently select all profiles. Disclosure may expose personal identity and application metadata.
Workspace membership and permissions	Users and organizations; collaboration authorization	TUASESOR workspace boundary	Memberships are self-readable and non-writable to ordinary callers. Granular permission rows are closed to direct anonymous/authenticated table access and are evaluated or mutated only through controlled database functions.
OAuth credentials and provider metadata	Connected profiles/workspaces and external accounts; provider access	Google-issued secret material plus TUASESOR-owned integration state	Access and refresh tokens are encrypted at rest outside the durable integration row and exposed only through narrow service-role RPCs. Runtime authorization must remain separate from infrastructure credential access.
Live Drive metadata	Users, clients, beneficiaries, counterparties; My Space browsing	Google-authoritative, transient TUASESOR response	Names, identifiers, links, structure, and timestamps may disclose personal or confidential activity.
Expense reports and items	Employees, contractors, suppliers, clients, or beneficiaries; expense operation	TUASESOR workspace-owned	May contain financial, accounting, labor-related, contractual, and confidential business information.
Operational receipt document metadata	Receipt subjects and counterparties; Rendiciones evidence	TUASESOR workspace/source metadata; Google-owned binary	The narrow receipt path persists provider identifiers, filename/MIME/size, recovery state, and business association metadata through controlled boundaries. This metadata can be financially or personally sensitive even though the binary remains in Google Drive.
Derived document content	Potentially any document subject; future processing	Mixed/unresolved local ownership	normalized_documents remains closed and unused. Extracted text, summaries, embeddings, and similar derived data are not implemented.
Transactions	Financial actors; historical financial foundation	Nullable profile, no workspace	No active flow, but broad anonymous/authenticated grants create material exposure if rows exist.
Workspace sources	Connected organizations/accounts; provider-source configuration	Workspace-owned external root tied to an explicit integration	Migrations 047 and 048 close direct browser-role access, classify sources by business purpose and provider root_type, and provide controlled expense_receipts source configuration. Provider/source identifiers remain potentially confidential and must be exposed only through the intended workflow.

Implemented privacy-relevant controls include explicit profile/workspace integration ownership, closed ordinary-user direct access to credential material, encrypted-at-rest OAuth secret storage, purpose-specific Drive scopes—read-only for My Space and drive.file for workspace-owned receipt upload—live My Space provider reads without unnecessary local resource duplication, controlled minimal receipt metadata persistence, hashed rather than raw receipt idempotency keys, protected membership predicates, membership-scoped reads, role-aware expense-report writes, scoped permission evaluators and mutation RPCs, transactional permission audit events, lifecycle-safe delegation revocation, and closed ordinary-user document/normalized-content table surfaces.

Current gaps include global authenticated profile visibility, no implemented workspace/membership or permission-administration UI, broad grants on dormant sensitive tables, no automated migration/deployment validation in CI, incomplete workspace isolation outside the expense-report path, credential-access auditing and operational key-rotation procedures that remain incomplete or unevidenced, audit coverage limited primarily to the granular permission subsystem, no retention schedule, no source-removal propagation, and no comprehensive export, correction, blocking, or deletion workflows.

Data minimization, purpose limitation, least privilege, secure defaults, explicit ownership, proportional auditability, and controlled lifecycle are design requirements. Current implementation satisfies them only on selected paths. Provider contractual roles, processing regions, international transfers, incident response, and legal-controller/processor classifications are organizational matters not established by this repository. Technical controls do not constitute a claim of legal compliance.

Migration Strategy

Canonical migration history is stored in database/migrations/ using three-digit ordered names. The sequence begins with a legacy public/company model, moves operational tables into tuasesor, aligns Auth profiles and workspaces, and then adds integrity, grants, RLS, RPCs, integration and document security, protected membership reads, centralized workspace write authorization, secure expense-item mutations, membership-scoped workspace reads, the migration 043 granular permission/audit lifecycle contract, integration ownership and OAuth lifecycle hardening through migrations 044–046, the migration 047 expense-receipt document contract, migration 048 controlled receipt-source configuration, migration 049 upload preflight, and migration 050 recoverable receipt-upload persistence/finalization. Migrations must be interpreted cumulatively; early files do not represent the current desired model.

The canonical migration directory is historical forward-change evidence. During TUA-056.5, a clean local replay of the complete canonical sequence from migration 001 through migration 046 completed successfully. Migration 046 was additionally validated against controlled pre-046 credential data through a forward migration, exact credential retrieval through the service-role RPC boundary, encrypted Vault storage verification, defensive rollback to the legacy representation, exact restored-value verification, Vault cleanup, and a final clean canonical 001 through 046 reset. These validations remain manual and do not imply automated deployment or universal rollback coverage.

For local Supabase validation, database/scripts/sync-supabase-migrations.ps1 prepares a derived staging area under supabase/. The script:

1. deletes and recreates the ignored supabase/migrations/ directory;
2. writes 039_local_baseline_prerequisites.sql with the required local extension and timestamp-trigger helper;
3. copies the tracked boundary-040 structural baseline database/baselines/040_baseline_schema.sql to supabase/migrations/040_baseline_schema.sql;
4. appends the tracked boundary-040 cross-schema companion database/baselines/040_cross_schema_objects.sql to that generated staging migration;
5. copies canonical migrations numbered after 040 in filename order;
6. writes an intentionally empty ignored supabase/seed.sql.

The boundary-040 staging migration is composed from two tracked artifacts with different responsibilities. 040_baseline_schema.sql is the structural baseline for TUASESOR-owned schemas at that boundary. TUA-066 originally anchored it to the exact database/schema/schema.sql artifact from Git commit 8547f0166ec19985283cfc18c61830c0e3984d3b, whose canonical migration history ends at 040. That historical artifact has Git blob 0571cbf2f9447ca1739694581a2085403e70a4b3, but the exporter used at that historical point was scoped only to the tuasesor schema. It therefore omitted the already-canonical audit schema and audit.events table introduced by migrations 015 and 018. TUA-078 adds a bounded reconstruction of those audit-owned boundary-040 objects while preserving the original commit and blob as provenance for the imported tuasesor snapshot. The repaired baseline is validated through clean migration replay rather than treated as a byte-identical immutable copy of that historical export.

040_cross_schema_objects.sql is a separate tracked companion for TUASESOR-owned objects whose physical attachment lies outside the schemas covered by that structural export. At boundary 040 it preserves auth.users.on_auth_user_created, whose definition originates from canonical migration 031 and executes tuasesor.handle_auth_user_created().

The companion does not make auth a TUASESOR-owned schema and must not be used as a reason to dump or version Supabase-managed schemas wholesale. The public.update_updated_at_column() dependency remains deliberately provided by the generated 039_local_baseline_prerequisites.sql, before the structural baseline creates triggers that reference it.

database/schema/schema.sql remains the generated point-in-time structural snapshot of the current linked schema and must not be used as a historical migration baseline. In particular, the current snapshot contains effects from migrations later than 040, including the integration ownership and OAuth changes through migration 046.

supabase/config.toml is tracked local-development configuration. The generated supabase/migrations/, supabase/seed.sql, and .temp/ contents are ignored through supabase/.gitignore. They are disposable local staging artifacts, not canonical migration source.

During TUA-066, the corrected staging workflow was validated with two consecutive local supabase db reset executions using Docker Desktop and Supabase CLI 2.109.1. Both resets successfully applied the derived sequence 039 prerequisite, historical baseline 040, and canonical migrations 041 through 046. The resulting local migration ledger contained 039 through 046 in order, with no collisions caused by replaying effects already contained in the baseline.

TUA-070 extended that workflow to preserve the boundary-040 cross-schema contract explicitly. A clean local reset reproduced exactly one auth.users.on_auth_user_created trigger with the migration-031 definition and the expected tuasesor.handle_auth_user_created() function. Transactional insert/update characterization confirmed that Auth identity changes synchronize tuasesor.profiles while preserving TUASESOR-managed profile fields and metadata.

The autonomous SQL regressions 003_secure_oauth_credentials.sql and 004_oauth_ownership_and_transactions.sql passed against the rebuilt local database during TUA-070, confirming that the corrected baseline/staging contract did not regress the integration credential and OAuth ownership boundaries.

After TUA-070 closed, TUA-007.1 validated migration 047 against that corrected derived local staging workflow. A reset successfully applied migrations 039 through 047, exactly one auth.users.on_auth_user_created trigger remained, and the unmodified 30-assertion 005_expense_receipt_document_contract.sql characterization passed 30/30. The migration-047 rollback then completed successfully and explicit post-rollback verification confirmed the boundary-046 structure: document_links was absent, workspace_sources.purpose and workspace_sources.root_type were absent, and workspace_sources RLS was disabled. A final reset reapplied 039 through 047, and the local migration ledger was aligned from 039 through 047. No remote database changes were performed as part of this validation. During TUA-007.2 implementation, the canonical local migration ledger was advanced through 049. Migration 050 was exercised separately in targeted local validation together with its rollback and persistence characterization, but it was intentionally not registered in the canonical local ledger during that targeted exercise. Final TUA-007.2 end-to-end validation remains a separate completion step. No remote database changes were performed as part of this local validation.

The repository does not evidence:

• automated migration application in CI or deployment;
• a migration ledger or documented production deployment command;
• universal transactional wrappers around migration files;
• automated compatibility or rollback testing;
• automatic execution of the SQL characterization tests.

Validation assets include:

• database/tests/001_expense_reports_workspace_isolation.sql, covering report isolation, role authorization, direct writes, RPC behavior, catalogs, and core report constraints;
• database/tests/002_expense_report_item_mutations.sql, covering secure item CRUD, draft restrictions, read-only roles, workspace isolation, input integrity, immutable parentage, and database protections, with a known stale profile-fixture pattern that must be modernized before treating the test as zero-state reproducible;
• database/tests/003_secure_oauth_credentials.sql, covering the migration 046 credential boundary, ordinary-role denial, encrypted Vault persistence, same-secret replacement, credential deletion semantics, integration cleanup, and transactional fixture rollback;
• database/tests/004_oauth_ownership_and_transactions.sql, covering migrations 044 and 045 ownership, multi-account cardinality, profile/workspace isolation, OAuth transaction binding, expiry, reconnect targeting, and permission-change behavior;
• database/tests/005_expense_receipt_document_contract.sql, covering migration 047 workspace-source purpose/root semantics, ordinary-role table closure, RLS, deterministic active-source selection by purpose, receipt link/unlink integrity, draft-only mutation behavior, idempotency, and linked-expense-item deletion protection;
• database/tests/006_expense_receipt_workspace_source_configuration.sql, covering migration 048 controlled source configuration, exact workspace integration ownership, required provider/scope/lifecycle checks, permission enforcement, and active-source identity semantics;
• database/tests/007_expense_receipt_upload_preflight.sql, covering migration 049 authenticated workspace/report/item/draft validation before provider side effects;
• database/tests/008_expense_receipt_upload_persistence.sql, covering migration 050 hashed idempotency/content identity, recoverable upload lifecycle, privileged provider-result recording, controlled document finalization, association rollback, and duplicate-upload protection;
• server/auth/tests/oauth-lifecycle.characterization.ts and server/auth/tests/run-oauth-lifecycle-characterization.ps1, covering the TUASESOR/Cortexa application lifecycle contract for connect, refresh, reconnect, disconnect, refresh-token preservation, and provider/storage failure states.

These validation assets are explicit manual validation steps rather than a unified root package test suite or CI job. Test 001 requires historical controlled seed prerequisites. Test 002 currently carries known fixture debt. Tests 003 and 004 create their own fixtures and have been validated from a clean local canonical schema. Test 005 was validated against the corrected derived local staging sequence through migration 047. Tests 006–008 are task-specific characterization assets for TUA-007.2; migration 050 and test 008 have targeted local validation evidence, while final end-to-end TUA-007.2 revalidation remains an explicit completion step. The OAuth lifecycle and document-layer characterizations remain separate reproducible manual checks rather than CI gates.

Rollback And Reversibility

database/rollback/ contains manually maintained emergency reversal scripts for integration/document hardening, workspace write-authorization changes, migration 043's granular permission model, migration 047's expense-receipt document contract, and migrations 048–050 for receipt source configuration, preflight, and recoverable upload persistence. They are operational recovery aids, not the desired current architecture.

The integration/document rollback removes owner policies, disables RLS, makes integration ownership nullable, and restores broader anonymous/authenticated privileges. The earlier workspace-authorization rollbacks remove the centralized helper, reopen direct membership access, and return expense-report authorization toward weaker membership-only behavior.

The migration 043 rollback is deliberately defensive. It refuses to continue if source-specific permission overrides exist because the legacy permission model cannot represent that scope without losing information. When its preconditions are satisfied, it removes the granular permission evaluators and mutation functions, removes the membership role-change lifecycle trigger/function, restores the legacy permission columns and uniqueness model, disables permission-table RLS, and restores legacy authenticated SELECT access. Existing audit history is preserved.

The updated migration 043 rollback has been manually validated locally through a complete rollback, pre-043 structural verification, canonical migration reapply, and post-043 structural verification roundtrip, including the lifecycle trigger/function.

Migration 046 has a separate defensive rollback for the credential-storage transformation. It validates that every credential row has exactly one decryptable deterministic Vault secret before restoring the legacy credential columns, verifies exact access-token, refresh-token, and expiry restoration, removes the represented Vault secrets only after restoration succeeds, and then removes the migration 046 credential table and RPC surface. During TUA-056.5 this rollback was validated against controlled pre-046 credential data through a forward migration and exact forward/rollback roundtrip. Migrations 044 and 045 do not have corresponding rollback scripts; rollback coverage in this repository is selective rather than universal.

Migration 047 also has a defensive rollback to the boundary-046 document model. Before changing schema state it refuses to continue if any document_links would be lost, if any workspace source contains migration-047 purpose or root_type metadata, or if current document identifiers can no longer satisfy the legacy global external_id or drive_file_id uniqueness rules. When those preconditions hold, it removes the receipt association RPCs, integrity and deletion-protection triggers, and document_links; restores the pre-047 document/source foreign key and global identifier uniqueness contract; removes workspace-source purpose/root metadata; disables workspace_sources RLS; and restores its pre-047 ordinary-role grants. TUA-007.1 manually validated this rollback and explicitly verified the resulting boundary-046 structure before a final 039 through 047 reset.

Migrations 048, 049, and 050 each have paired rollback scripts. Their presence provides a reviewable recovery path for the new receipt-source/upload boundaries but does not imply that rollback is always safe once external provider side effects or production data exist. Migration 050 in particular must be considered together with provider-upload state and its recovery semantics rather than treated as a purely local table reversal.

Rollback scripts restore selected database definitions, not a guaranteed previous functional product state. They do not reverse every migration, restore external-provider state, recover deleted data, prove application compatibility, or substitute for verified backups. Rollback decisions must account for security exposure, data representation loss, incompatible application code, and external-provider state. A forward fix or verified database backup/restore may be safer than intentionally reopening a weaker historical security model.

Generated Schema Documentation

Generated structural artifacts exist at:

• database/schema/schema.sql
• database/schema/schema.md
• docs/database/schema.sql
• docs/database/schema.md

npm run db:schema runs database/scripts/export-schema.ps1. The script uses the repository Supabase CLI through npx, requires Docker and linked-project metadata, exports only the TUASESOR-owned tuasesor and audit schemas from the linked database, normalizes generated SQL line endings, validates required structural markers, generates Markdown, installs both copies, and verifies the generated outputs. That ownership boundary is intentional: objects created by TUASESOR but physically attached to Supabase-managed schemas are not captured by expanding the ordinary schema export. Historical boundary-specific exceptions belong in explicit tracked baseline companions such as database/baselines/040_cross_schema_objects.sql.

These files are versioned generated evidence and must not be edited manually. Regenerate them after material schema, grant, RLS, function, trigger, or audit-schema changes. Freshness should be validated by:

1. confirming the intended Supabase project is linked;
2. rerunning the export;
3. reviewing the generated diff;
4. confirming the two SQL copies and two Markdown copies remain identical;
5. checking that the latest active migration effects and runtime assumptions appear;
6. investigating any linked-database state that cannot be traced to canonical migrations.

The current generated artifacts are a point-in-time linked-dev snapshot whose structure includes effects later than migration 043, including the hardened integration ownership, OAuth transaction, and credential-storage surfaces introduced by migrations 044 through 046. They also include secure expense-item mutation RPCs and triggers, membership-scoped workspace reads, scoped workspace_member_permissions, granular permission evaluators and controlled mutation functions, audit.events, and the role-change delegation lifecycle function and trigger. The SQL copies are identical to each other, as are the Markdown copies. Their structural contents must not be treated as proof of a historical migration number unless freshness is explicitly verified.

The generated artifacts remain point-in-time structural evidence. They do not prove that another environment matches, that every historical migration can reconstruct identical state automatically, or that application behavior exercises every available database capability.

scripts/sync-docusaurus-docs.ps1 copies the canonical documentation and generated Markdown schema into documentation/docs/ for publication. Those publication files are synchronized artifacts, not independent sources of truth, and may lag until the sync script is run. The canonical narrative remains this file; the structural source remains the verified generated schema pair.

database/schema.dbml is not produced by the current export script and still describes the removed company/user model; it is historical and stale. docs/database/data-dictionary.md is explanatory documentation and may also lag. Neither overrides migrations, the verified export, or current code.

Generated schema artifacts answer detailed structural questions about columns, constraints, indexes, policies, functions, grants, and triggers. This document explains ownership, domain status, authorization, lifecycle, and design implications.

Active, Inactive, And Planned Persistence

Classification	Persistence area
Active and used	Profiles and Auth synchronization; membership-scoped workspace discovery; expense-report listing, detail, draft creation, item totals, catalog reads, secure item create/update/delete; transactional permission override/audit operations; workspace receipt-source configuration; receipt-upload preflight, recovery persistence, provider-result acknowledgement, document finalization, and expense-item association
Active but restricted or partially wired	Protected workspace memberships and coarse expense-report role authorization; granular permission evaluators and controlled mutation RPCs; workspace accounting catalogs and global document types; workspace-owned Google receipt integrations; controlled workspace_sources, documents, document_links, and expense_receipt_upload_attempts boundaries; report lifecycle fields beyond draft without implemented transition workflows
Present but closed to ordinary users	Direct table surfaces for documents, normalized documents, workspace sources, document links, receipt-upload attempts, granular permission rows, and integration credential material
Present but unused or transitional	Transactions; specialties; profile specialties
Historical	Legacy company/user model in early migrations, old seed, and stale DBML
Foundational or described as future; not implemented	Workspace and membership administration UI/workflow; complete workspace integration/source management UX; broader granular integration/document permission wiring; formal submission, review, approval, rejection, reimbursement, and cancellation authorization; generic document ingestion/synchronization and document-management UI; OCR, extraction, normalization, embeddings, knowledge retrieval, derived summaries, and complete retention/deletion propagation

This classification is based on current imports, repositories, routes, policies, and UI behavior. It must be reassessed when a real access path is added or removed.

Known Limitations And Validation Needs

Confirmed implementation and security risks

• Profiles are readable across all authenticated users without RLS.
• Transactions and document types retain broad anonymous/authenticated privileges without RLS; migration 047 removed that exposure from workspace_sources by revoking ordinary-role table privileges and enabling RLS.
• Profile-specialty rows remain globally readable by authenticated users; granular permission rows no longer have direct ordinary-user table access.
• Direct expense-report/item writes bypass some RPC ownership and catalog-consistency checks.
• No table has forced RLS; privileged roles can bypass policies.
• API authentication does not uniformly enforce active-profile status.
• Expense-report authorization remains coarse and role-based; the granular permission subsystem exists separately but is not yet wired into report action/state-specific authorization.
• The schema-export script does not yet assert the entire authorization surface, including every migration 043 evaluator, mutation grant, and lifecycle-trigger invariant, so linked-database drift still requires explicit diff and behavioral review.

Structural and lifecycle gaps

• Historical local staging/replay was manually validated through migration 047 during TUA-007.1. During TUA-007.2 the canonical local ledger advanced through 049, while migration 050 received targeted local validation without being registered in that ledger. The repository still has no automated zero-state migration replay or compatibility gate in CI.
• No automated structural-equivalence check compares a clean historical replay with the linked-dev generated snapshot.
• Document and normalized-content ownership and deletion propagation are unresolved.
• Workspace deletion can leave detached document and derived-content rows.
• No complete retention, correction, export, blocking, or deletion workflow exists.
• Permission overrides now have transactional audit events, but no comprehensive cross-domain audit trail or complete membership/credential history exists.
• OAuth token encryption at rest plus profile-owned My Space and workspace-owned Rendiciones authorization backends are implemented. Credential-access auditing, operational key rotation, backup protection, production access review, complete workspace connection/source administration UX, and user-facing multi-account management remain incomplete or unevidenced.

Validation needs

• Confirm migration application and ledger practices for each environment.
• Verify the membership helper, membership policy, role-aware expense-report policies, granular permission evaluators, permission-table closure, controlled mutation RPCs, and role-change lifecycle trigger after material authorization changes or schema exports.
• Verify whether broad dormant-table grants are intentional; current access patterns do not justify them.
• Define a controlled workspace and membership-administration path without reopening ordinary client writes.
• Wire the existing granular permission contract into product workflows only where those workflows require it, and separately define action/state-specific report authorization beyond the current coarse writer roles.
• Keep ordinary direct document and derived-content surfaces closed. Before broadening beyond the narrow receipt workflow, define ownership, authorization, retention, source-removal, deletion, and derived-data lifecycle explicitly.
• Validate retention and deletion needs with product, security, privacy, and the appropriate Chilean legal responsible role where material.

No planned remediation is claimed merely because a gap is documented here.

Documentation Corpus Map

ARCHITECTURE.md

Use ARCHITECTURE.md for system responsibilities, architectural boundaries, integration contracts, dependency direction, and cross-cutting security invariants.

REPOSITORY.md

Use REPOSITORY.md for repository organization, module placement, migration and documentation locations, and contributor navigation.

DATABASE.md

Use this document for persistence architecture, ownership, major entities, grants and RLS, tenant isolation, lifecycle, migration responsibilities, and data-protection implications.

ECOSYSTEM.md

Use ECOSYSTEM.md for capability ownership, TUASESOR/Cortexa boundaries, and relationships with Supabase, Google, and future platform modules.

Database Schema Snapshots

Use the generated database schema reference and SQL export for detailed structural inspection. Use data-dictionary.md only as descriptive supporting material and verify it against current evidence.

The copies under documentation/docs/ are publication copies, not canonical sources when they diverge from docs/.

Guidance For Developers And AI Agents

Contributors and AI agents should:

1. inspect current migrations, the generated export, and application access paths before changing persistence;
2. identify whether new data is profile-owned, workspace-owned, global reference data, parent-aggregate-owned, provider-owned, or externally authoritative;
3. require explicit non-null ownership for credentials and other sensitive records unless a documented transition requires otherwise;
4. define table grants and RLS together, including separate SELECT, INSERT, UPDATE, and DELETE behavior;
5. protect the tables used by RLS predicates, especially membership and ownership relationships;
6. never treat a client-selected workspace ID or application filter as authorization proof;
7. enforce business roles and permissions when a workflow depends on them, while keeping them distinct from PostgreSQL roles;
8. prefer least-privilege ordinary access and keep service-level bypass outside normal user flows;
9. keep external credentials server-side and absent from responses, logs, fixtures, examples, and documentation;
10. treat metadata, extracted text, classifications, summaries, and other derived data as potentially personal or confidential;
11. define purpose, source authority, deduplication, retention, correction, export, blocking, source-removal, and deletion behavior before adding persistence;
12. use incremental, reviewable migrations rather than ad hoc database changes;
13. validate data-preservation and security effects before deployment and treat rollback as an operational risk decision;
14. regenerate schema documentation after material structural or authorization changes and review migration/export drift;
15. distinguish active flows from closed, unused, historical, transitional, and planned structures;
16. update this document only when persistence technology, ownership, domains, major relationships, grants/RLS, credential storage, external-resource persistence, lifecycle, auditability, or migration strategy materially changes.

Durable principles supported by the current architecture are:

• profile and workspace ownership are distinct boundaries;
• integration operations must authorize the explicit profile or workspace owner before accessing a concrete integration; infrastructure credential privilege must not substitute for that application authorization;
• ordinary anonymous or authenticated access to credential secret material must remain prohibited;
• workspace-owned data must rely on protected membership plus applicable role/permission decisions, not workspace identifiers alone;
• external providers remain authoritative for live resources unless TUASESOR deliberately imports and owns a local representation;
• database constraints protect integrity but do not replace authorization;
• application validation and filtering do not replace database security;
• unused sensitive persistence should be closed by default;
• migrations and generated artifacts must be reviewable evidence, and generated files must not be edited manually;
• privacy, security, auditability, retention, and deletion must be considered proportionally whenever persistence changes.

Current implementation does not satisfy every principle uniformly; the limitations above define where additional validation or hardening is required.