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, profile-owned OAuth connections, workspace membership checks, and workspace-scoped expense reports. It also contains foundations for document records, normalized content, workspace sources, transactions, professional specialties, and accounting catalogs, but a table's presence does not make its 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 implemented access path uses Supabase clients created with the public project key and the caller's session cookies. Current product source does not construct a service-role client. Authenticated queries therefore execute with the caller's database context and are subject to the table grants and RLS policies that apply to authenticated.

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. Profile-owned integration. tuasesor.integrations stores a profile's external-provider account and OAuth credentials.
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. Closed or foundational persistence. Document, normalized-content, transaction, workspace-source, and professional-classification structures exist, but current code does not establish active end-to-end workflows for them.

Server-side repositories and adapters select the tuasesor schema explicitly. The expense-report repository scopes reads by a caller-supplied workspace ID in addition to database RLS. The OAuth storage adapter scopes every lookup, update, and delete by the authenticated profile, provider, and provider account where applicable. These application filters provide defense in depth but are not substitutes for database authorization.

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 contains the integration/document hardening, protected membership reads, centralized workspace write helper, role-aware expense-report policies, and aligned RPC represented by the active migration chain. It therefore confirms that those controls existed in the linked database at export time, but it does not prove that another environment matches or that the migration chain can recreate the database from zero.

Repository database sources have distinct roles:

Source	Role
database/migrations/	Ordered implementation history. It must be read cumulatively but currently has replayability gaps.
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-created schema foundation declared by a migration for future audit/event data.	No audit tables, policies, repositories, or current workflows are implemented. It is inactive.
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 is profile-owned and has a mandatory profile key.
• 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 but have no active access path and currently have unsafe broad grants.

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 | Coarse role enforcement active for expense-report writes; granular permissions unused | owner, admin, and member may write expense reports; viewer and client remain read-only. workspace_member_permissions is not consulted by current SQL or application code. | | Profile-owned Google integration | Active | OAuth connection, credential storage, token refresh, and live Drive listing use integrations. | | Expense reports and items | Active draft-management workflow; 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. Formal submission, review, approval, rejection, reimbursement, cancellation, and document 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 | Present but intentionally closed | No current application repository or API reads or writes documents; ordinary table privileges are revoked and RLS has no user policies. | | 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 | Present but unused | The schema can associate a provider root with a workspace, but current Google browsing remains profile-scoped and does not use this table. | | 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 | Planned foundation only | An empty schema declaration exists; no audit model is implemented. |

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 evaluates 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. A permission key is unique within a membership, but granular permission rows are not currently evaluated by SQL or application services.

Integration

Purpose. An integration stores a profile's external-provider account, OAuth access and refresh credentials, scopes, expiration, and provider metadata.

Ownership. profile_id is mandatory and is the TUASESOR ownership key. A provider account identifier is an external identity and never replaces profile ownership.

Relationships. The integration belongs to a profile. Current Google authorization and Drive browsing use it; workspace sources are a separate, currently unused model.

Lifecycle. The storage adapter can insert/upsert, update, and delete an account, while the auth service retrieves or refreshes provider sessions. Profile deletion cascades to integrations. No current product disconnect route or automatic deletion after provider revocation is evidenced.

Business And Consistency Rules. A profile has at most one row per provider, and a non-null provider account identifier is unique with its provider across stored integrations. Credential and provider-account fields remain nullable except for profile and provider. RLS enforces owner-only CRUD for authenticated users.

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 are all nullable, so ownership is unresolved. Normalized-document workspace and source-document references are also 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. Deleting a profile, workspace, or source sets corresponding document references to null. 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. External and Drive identifiers have global uniqueness rules when present. Normalized records require a source and external identifier but have no implemented deduplication constraint across them. Both tables are 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 models a workspace/provider root and optional associated profile. These are real schema structures but have no current application persistence path. Their broad or incomplete authorization must be treated as current risk, not as evidence of 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; permission rows express a finer model that is not yet enforced.
• The workspace owns business aggregates such as expense reports and catalogs.
• A provider account identifies an external account connected by a profile.

The profile ID is mandatory for integrations, memberships, and profile specialties. It is nullable on documents, transactions, workspace sources, and expense-report attribution. Workspace ID is mandatory for memberships, workspace sources, accounting catalogs, and expense reports, but nullable on documents and normalized documents.

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. The currently committed generated snapshot predates migrations 041 and 042 and must be regenerated before it can serve as point-in-time evidence for those controls.

Additional limitations are:

• granular permission keys are not evaluated, and the role matrix is coarse rather than action- or state-specific;
• 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	Integrations; profile-specialty relationships; some optional legacy associations
Workspace-scoped	Workspaces; expense reports; items through their parent; business lines; cost centers; accounting accounts; memberships; workspace sources
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.

No exported table is configured with FORCE ROW LEVEL SECURITY. Owners and bypass-capable privileged roles can therefore bypass ordinary policies. The exported schema grants service_role all privileges on TUASESOR tables and functions. Current product source does not use a service-role client, but operational use of that role is security-sensitive and must not be treated as an ordinary user workflow.

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	Authenticated CRUD; no anonymous table grant; RLS enabled; separate owner policies for SELECT, INSERT, UPDATE, DELETE	Profile owner only. UPDATE checks both old visibility and post-update ownership.
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 table privileges; RLS enabled with no ordinary policy	Intentionally closed to anonymous and authenticated users.
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	Authenticated SELECT; no RLS	All authenticated users can read all permission rows; only privileged roles can write.
workspace_sources	Anonymous and authenticated ALL; no RLS	Broad access to provider-source metadata; unsafe even though no current app path uses it.
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. Granular permission rows contain a permission key, a granted/denied flag, metadata, and a unique membership/key combination.

The current coarse database contract is:

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 in SQL through can_write_workspace. Expense-report INSERT and UPDATE policies and the report-creation RPC use that workspace-role decision. Item mutations are exposed through the create_expense_report_item, update_expense_report_item, and delete_expense_report_item SECURITY INVOKER RPCs. They execute under the authenticated caller and require access to the supplied workspace, a writer role, and a parent report whose status remains draft. Grants, RLS, constraints, and triggers remain independent enforcement layers. Ordinary authenticated callers have no expense-report DELETE capability.

Catalog SELECT policies remain membership-based for every allowed role. No repository code or policy reads workspace_member_permissions, so a granular permission change currently has no effect.

Membership addition or removal immediately changes read and write decisions for existing records. Changing a role immediately changes expense-report and item write access while preserving membership-based read access. viewer and client remain read-only. These role semantics do not implement operation-specific permissions or lifecycle decisions for submit, review, approve, reject, reimburse, or cancel; those remain future authorization work rather than implied capabilities.

External Integrations And Credentials

The active integration is Google. The server requests identity scopes plus read-only Drive access. The database stores:

• mandatory TUASESOR profile ownership and provider name;
• provider account identity;
• access and refresh tokens;
• token expiration;
• granted scopes;
• provider-specific metadata.

These records are confidential. The implemented protections are:

• explicit mandatory profile ownership;
• owner-only RLS for all four CRUD operations;
• no anonymous table grant;
• authenticated server-side lookup before Drive access;
• adapter queries constrained by profile and provider-account identity;
• credentials kept out of current client responses;
• sanitized Google-flow error logging rather than credential logging;
• a read-only Drive scope.

Stable security rules are:

• provider account identifiers never replace TUASESOR ownership;
• one profile must not read, refresh, update, or delete another profile's credentials;
• credentials, authorization codes, client secrets, and raw provider payloads must not appear in client responses, logs, examples, schema samples, or documentation;
• ordinary user access must remain subject to both least-privilege grants and owner RLS;
• service-level access must remain exceptional and controlled.

The repository does not evidence application-layer token encryption, a token-rotation procedure, credential-access auditing, a product disconnect endpoint, or automatic credential removal after provider revocation. Database-at-rest protection, backup handling, hosting region, and operational access controls cannot be concluded from this repository.

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.

The current route does not insert or update documents, workspace_sources, normalized_documents, or another local file index. Google remains authoritative for the resource, its availability, sharing state, and current metadata. The response and browser state are transient; the persistent TUASESOR record is the profile-owned integration.

If a Google resource is removed, the next live listing should reflect the provider result; there is no local resource record to delete. If access is revoked or a token cannot be refreshed, the current flow returns a reconnect outcome. No TUASESOR route or service in this repository deletes the integration as part of that outcome. Provider downtime produces a retry-oriented response.

External identifiers, file names, folder relationships, MIME types, timestamps, sizes, and links are not credentials, but they can be personal or confidential. Persisting them in the future requires a defined product purpose, owner, deduplication rule, authorization model, retention expectation, and source-removal behavior.

Documents And Derived Data

The current application does not implement document upload, file storage, synchronization into documents, download, OCR, parsing, extraction, normalization, classification, embedding, semantic retrieval, or derived summaries.

The present persistence boundary is:

• documents is a transitional operational metadata structure with provider-specific and mixed ownership fields. It is intentionally closed and unused by current application source.
• normalized_documents can hold extracted text and arbitrary metadata, but it is 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.
• storage_path, sync fields, provider links, and polymorphic entity fields are schema capabilities, not proof of active storage or association workflows.
• the repository consumes Cortexa auth and Google Drive connector packages, 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. These tables must remain closed until that lifecycle and the 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 Google consent. The server exchanges the authorization response through the Cortexa auth abstraction and stores the account through the profile-scoped Supabase adapter. Later Drive requests load that profile's integration, refresh the provider session when required, and list provider resources live.

The aggregate boundary is the profile-owned integration. TUASESOR retains credentials and connection metadata; Google retains authority over Drive resources. Adapter deletion exists, but a current end-user disconnect flow is not present.

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 administration requires a separately controlled privileged path that is not implemented here. Expense-report operations apply the coarse role matrix; granular workspace_member_permissions administration and evaluation remain unimplemented.

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 and one permission key per membership;
• 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 profile/provider and one stored account identifier per provider when non-null;
• 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.

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.

Database Authorization Test Evidence

database/tests/001_expense_reports_workspace_isolation.sql is a transactional characterization test that ends with ROLLBACK and depends on controlled expense-report seed data. 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 is an autonomous transactional characterization test. It creates controlled identities, profiles, workspaces, memberships, catalogs, and reports inside a transaction; changes the simulated authenticated caller through PostgreSQL configuration; and finishes with ROLLBACK, leaving no persistent fixture data.

Its covered scenarios are organized around:

• writer item creation, update, and deletion;
• rejection when the parent report is no longer draft;
• same-workspace catalog protection;
• read-only behavior for viewer and client;
• cross-workspace isolation;
• input validation and immutable parent relationships;
• additional mutation validation;
• final database-level protections.

These SQL assets are durable evidence of the intended authorization and integrity contract, but they are not proof that every deployed environment currently enforces it. The repository has no root package command or CI runner that executes these tests automatically. Their execution therefore remains an explicit database-validation step.

Auditability And Traceability

Implemented evidence is limited:

• 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, but the current draft-creation path overloads a submission-oriented name for creator attribution;
• profile synchronization records the current identity metadata;
• integration creation and token expiry are stored;
• ordered migrations and generated schema exports provide structural change evidence;
• current Google handlers log sanitized error categories and aggregate result counts.

The database does not store a general actor column for mutations, status history, credential-access history, membership history, immutable audit events, soft-delete history, or before/after values. submitted_by_profile_id plus timestamps provides limited attribution, not a complete immutable audit history. The audit schema is empty. Expense-report server errors currently include underlying causes in server logs, but application logs are operational evidence, not database audit records, and may themselves require sensitive-data controls.

Provider logs, Supabase platform logs, backups, and hosting logs may exist operationally, but the repository neither configures nor verifies them. Timestamps 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.
• Integration deletion is implemented by the storage adapter and by profile cascade, but no current product disconnect endpoint is evidenced.
• 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	Active migrations and the current generated snapshot make memberships self-readable and non-writable to ordinary callers. Granular permission rows remain globally readable to authenticated users.
OAuth credentials and provider metadata	Connected users/accounts; Google access	Google-issued, profile-owned TUASESOR record	Owner RLS is implemented. Compromise can enable provider access and is high impact.
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.
Documents and derived content	Potentially any document subject; future processing	Mixed/unresolved local ownership	Closed and unused today. If populated, file metadata, extracted text, and summaries may be highly sensitive.
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 with optional profile attribution	No active flow, but broad grants may expose confidential provider metadata.

Implemented privacy-relevant controls include explicit integration ownership, owner RLS, read-only Drive scope, server-side credential handling, live provider reads without unnecessary local resource duplication, protected membership predicates, membership-scoped reads, role-aware expense-report writes, and closed document/normalized-content user surfaces.

Current gaps include global authenticated profile visibility, no implemented membership-administration workflow, globally readable granular permission rows, broad grants on dormant sensitive tables, migration/export replayability gaps, incomplete workspace isolation outside the expense-report path, no application-layer token encryption evidence, no complete audit trail, 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, and membership-scoped workspace reads. Migrations must be interpreted cumulatively; early files do not represent the current desired model.

The canonical migration directory is historical forward-change evidence, but it cannot currently be assumed to recreate the exported database independently from zero. Two numbered migration files are empty, and later migrations depend on profile and Auth structures that appear in the linked-database export but are not fully established by the non-empty migration sequence. The export also contains some RLS state not introduced explicitly by the available historical files.

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 generated schema snapshot to 040_baseline_schema.sql;
4. copies canonical migrations numbered after 040 in filename order;
5. writes an intentionally empty ignored supabase/seed.sql.

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.

This baseline-plus-incrementals workflow enables local database resets and validation of newer migrations without claiming that the historical migration chain is independently replayable from zero. The generated snapshot represents the linked database at export time, while database/migrations/ remains the canonical forward history for changes introduced after that baseline.

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.

Both tests are explicit transactional database-validation steps rather than root package scripts or CI jobs. Migration success must therefore be checked deliberately against the intended database, application access paths, grants, RLS policies, constraints, triggers, RPC contracts, SQL characterization tests, and regenerated schema export.

Rollback And Reversibility

database/rollback/ contains manually maintained rollback scripts for the integration/document hardening and the three workspace write-authorization migrations. They are emergency operational aids, not the current architecture.

The integration/document rollback removes owner policies, disables RLS, makes integration ownership nullable, and restores broad anonymous/authenticated privileges. The workspace-authorization rollbacks remove the centralized helper, restore broad direct access to membership rows, and return expense-report RLS and RPC authorization to membership-only writes. These paths deliberately restore weaker security, including write access for read-only business roles if the full rollback is applied.

The scripts restore selected schema and policy definitions, not a verified previous functional state. They do not restore deleted or deduplicated data, reverse every migration, validate dependency order or application compatibility, or reconcile external-provider state. They should not be treated as routine deployment steps.

Rollback decisions must account for exposure, data loss, incompatible application code, and external-provider state. A forward fix or a verified database backup/restore may be safer than executing a schema rollback, especially where rollback would reopen credentials or documents.

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 a pinned Supabase CLI through npx, requires Docker and linked-project metadata, exports only the tuasesor schema from the linked database, validates critical integration/document security markers, generates Markdown, installs both copies, and verifies that corresponding copies have identical hashes.

These files are versioned generated evidence and must not be edited manually. Regenerate them after material schema, grant, RLS, function, or trigger 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 state present in the export but absent from migrations.

The currently committed artifacts contain the integration and document security state, protected self-readable memberships, can_write_workspace, and the role-aware expense-report structures present through migration 040. The SQL copies are identical to each other, as are the Markdown copies.

These artifacts predate migrations 041 and 042. They do not yet contain the secure expense-item mutation policies and RPCs, the item DELETE grant, immutable-parent and catalog-validation triggers, new item constraints, or the membership-scoped workspace SELECT policy. Until npm run db:schema is rerun against the intended Supabase project after those migrations are applied, the generated snapshot must not be treated as evidence that those newer controls exist in the linked database.

After regeneration, the diff must be reviewed for the expected effects of migrations 041 and 042, and the two SQL copies and two Markdown copies must again have matching hashes. Generated files remain point-in-time structural evidence only: they do not prove that another environment matches or that the historical migration chain can reconstruct the database from zero.

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, and grants. 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; profile-owned integrations and credential refresh; membership-scoped workspace discovery; expense-report listing, detail, draft creation, item totals, catalog reads, and secure item create/update/delete
Active but restricted or partially wired	Protected workspace memberships and coarse role authorization; workspace accounting catalogs and global document types; report lifecycle fields beyond draft without implemented transition workflows
Present but closed to ordinary users	Documents and normalized documents
Present but unused or transitional	Workspace sources; transactions; specialties; profile specialties; granular member permissions
Historical	Legacy company/user model in early migrations, old seed, and stale DBML
Foundational or described as future; not implemented	Workspace and membership administration; granular permission evaluation; formal submission, review, approval, rejection, reimbursement, and cancellation workflows; document ingestion and synchronization; OCR, extraction, normalization pipeline, 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, workspace sources, and document types retain broad anonymous/authenticated privileges without RLS.
• Profile-specialty and permission rows are globally readable by authenticated users.
• 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.
• The active role-based authorization is coarse; it does not consult granular permission rows or enforce action/state-specific report transitions.
• The schema-export script does not assert the membership helper, protected membership policy, role-aware expense-report policies, or aligned RPC, so future linked-database drift could pass its built-in validation.

Structural and lifecycle gaps

• The migration chain is not replayable from zero because required profile creation/foreign-key state is absent from non-empty migrations.
• The generated export contains RLS state for workspaces and normalized_documents that is not represented by the checked-in migration chain.
• 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.
• No comprehensive audit trail or membership/credential history exists.
• Credential encryption, rotation, backup protection, and operational access review are not evidenced.

Validation needs

• Confirm migration application and ledger practices for each environment.
• Verify the membership helper, membership policy, role-aware expense-report policies, and RPC after each export, and reconcile exported-only state into reviewable migrations before relying on clean provisioning.
• 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.
• Decide and enforce granular permission plus action/state-specific semantics beyond the current coarse write roles.
• Define ownership and lifecycle before opening document or derived-content tables.
• 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;
• profile-owned integrations must remain scoped to auth.uid() in grants, policies, and application queries;
• anonymous access to credential-bearing tables 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.