Private File Access and Retention

SABLE's documents, feedback, and portal-request-attachments Storage buckets are private. A server route verifies the current viewer and its organization before it returns a signed URL. The URL is a delivery capability for an already-authorized request; it is not a durable entitlement or a retention policy.

Current guarantees

File class	Server gate before a URL is minted	URL lifetime	Current cleanup / retention behavior
Library document	Verified session and organization; restricted documents require admin/lead; other members require current project membership; excluded Library documents are unavailable to non-leads	10 minutes	No business retention period is defined here. SABLE can delete its owned copy through the document tombstone/purge workflow; that does not delete an upstream Drive original.
Feedback screenshot	Verified organization and admin/lead role	5 minutes	A rejected upload is deleted immediately. A ready screenshot receives a 90-day retention_expires_at; sable.feedback.retention deletes expired objects daily.
Portal request attachment	Verified organization, current client/project scope, attachment visibility, and ready state	60 seconds	Unpublished draft attachments enter the cleanup workflow after 24 hours. This is draft cleanup, not a declared retention period for published files.

Every JSON route response is Cache-Control: no-store. No server-side signed-URL reuse is implemented: each authorized download request mints a fresh signed URL. This preserves an authorization check on every application request and prevents sharing a URL response between users or organizations.

The boundary on revocation

Removing a required project or client scope prevents the next scoped document or portal request from minting a URL. Excluding a document prevents a non-lead request, and removing a lead/admin role prevents a feedback screenshot request. The three signed-download routes do not consult portal publication rows, so withdrawing a publication alone is not a revocation mechanism for a URL issued by one of these routes. None of these controls can recall a file the browser has already downloaded, nor can they revoke a signed URL that has already been issued.

Supabase Storage response caching is independent of signed-token expiry. With Smart CDN, a cached response for one exact signed URL can remain available until the object's Storage cache TTL expires; deleting the object invalidates CDN entries asynchronously and does not clear an already-cached browser copy. Do not treat a 10-, 5-, or 1-minute signature as an equal browser-retention promise.

This is the present technical contract, not an approved business-retention policy. It intentionally does not establish how long contracts, ordinary documents, or published portal attachments may be retained after their business purpose ends. The data owner must make that decision separately.

Default recommendation until a data owner decides

Keep the current fresh-per-request behavior and private buckets. Do not add browser persistence, shared runtime caching, Redis, or signed-URL coalescing. A cache key that is not proven to include the verified user, organization, exact access scope, file identity/version, and revocation state can turn a performance change into an authorization bypass.

If a future retention/access decision requires strict ongoing authorization, use an authenticated streaming route with an explicit private no-store response and accept that previously downloaded copies remain outside SABLE's control. If reuse is explicitly acceptable for a named file class, design it with a per-user, per-object-version bound; clear client state at sign-out; authorize before returning a hit; and invalidate it after every access, publication, role, membership, or content change. Validate actual Storage headers and access-change behavior with synthetic objects before changing a bucket cache-control setting.

Evidence and measurement, September 12, 2026

The local synthetic route fixtures exercise two authorized download requests and an access revocation: each authorized request makes a signing call, while the post-revocation request receives no URL and makes no signing call. They cover Library documents, portal attachments, and feedback screenshots without retrieving any production object. A separate synthetic, stubbed-authentication navigation fixture traces the reviewed Projects load: it makes one /api/portfolio request and one /api/account-directory request for a single scope, with zero duplicate endpoint requests. Those payloads are intentionally different, so consolidating them would need an API-contract review, not a cache.

A read-only pg_stat_statements query filtered only on the three attachment/catalog table names returned aggregate statement IDs, not query text or application data. Its largest cumulative groups were three IDs with 202 calls each (mean execution times 24.361 ms, 7.257 ms, and 2.947 ms), and one with 104 calls (6.191 ms). Statistics have no navigation correlation or known reset window, so they do not prove duplicate in-flight application reads. They are evidence to add route tracing or a bounded synthetic trace, not evidence to add a cache.

For a later measurement window, retain only query ID, calls, rows, total execution time, mean execution time, stats_reset, route name, and a synthetic actor/object fixture identifier. Do not record signed URLs, object paths, document titles, or user-controlled values in a cache experiment.

Retrieval-index measurement: what is known

pg_stat_database.stats_reset was NULL on September 12, so the available counters are cumulative and cannot describe a bounded interval. The HNSW index had 43,683 block reads and 592,065 block hits (93.13%); the full-text index had 10,661 reads and 228,669 hits (95.55%). Their scan counters were 10 each. This says that the indexes are in use and mostly served from PostgreSQL's buffer cache. It does not establish disk latency, an application request rate, or a need for Redis, more database memory, or a read replica.

The safe query-ID aggregate found retrieval-associated statement IDs with materially different cumulative means: ID 212585157409014160 had 21 calls at 1,062.691 ms mean execution time, and ID -488015451049157026 had 10 calls at 2,054.873 ms. Other IDs were much lower. These are candidates for a bounded trace and query-plan review, not a p95. pg_stat_statements exposes aggregate mean and total time; it cannot honestly produce end-to-end authenticated-navigation p95 or connect a statement to a user, document, or browser event.

Use the following read-only snapshots before and after a fixed test window. They return metadata and aggregate counters only; do not select query text or application rows.

select datname, stats_reset
from pg_stat_database
where datname = current_database();

select i.indexrelname as index_name, i.idx_scan, i.idx_tup_read, i.idx_tup_fetch,
       io.idx_blks_read, io.idx_blks_hit,
       round((100.0 * io.idx_blks_hit / nullif(io.idx_blks_hit + io.idx_blks_read, 0))::numeric, 2) as hit_pct
from pg_stat_user_indexes as i
join pg_statio_user_indexes as io on io.relid = i.relid and io.indexrelid = i.indexrelid
where i.relname = 'knowledge_chunks'
  and i.indexrelname in ('knowledge_chunks_embedding_hnsw_idx', 'knowledge_chunks_fts_idx');

select queryid, calls, rows,
       round(total_exec_time::numeric, 3) as total_exec_ms,
       round(mean_exec_time::numeric, 3) as mean_exec_ms
from pg_stat_statements
where query like '%knowledge_chunks%'
order by calls desc, total_exec_time desc
limit 20;

To establish a real p95, instrument an authenticated staging or production trace that records only route name, status, duration, scope class, and a random request ID. Correlate it with the before/ after aggregate snapshots. Do not put a signed URL, Storage path, title, query string, or identity into telemetry.

Related routes and jobs

• web/api/doc.js
• web/api/feedback-attachment.js
• web/api/portal-attachments.js
• trigger/feedback.ts and trigger/document-purge.ts
• Documents and Cleanup