AdFlo

The client-facing back-office platform from Aurum Ventura Enterprise LLC. It started as a multi-tenant document manager: folders and tags, metadata, filename/metadata and document-text search (text-based PDF, DOCX, XLSX, PPTX, CSV, TXT; no OCR), private file storage, versioning, expiring share links and an audit trail — on Next.js, Supabase (Postgres, Auth, Storage), Tailwind and shadcn/ui-style components.

AdFlo is software, not a website: opening the site address goes straight into the app (/ redirects to /app/overview, the Home screen).

• With Supabase configured – real sign-in, workspaces and (for Documents) a real database and storage.
• Without Supabase – the app still opens, as a preview labelled "Preview" in the top bar. Every service starts empty and keeps its records in the visitor's own browser, so nothing is shared between visitors and nothing is stored on the server. The server-memory API (/api/dms/dev/*) remains for development only and is refused in production unless DMS_ALLOW_DEV_MODE=true.
• Old demo links – /demo/... redirects to the same page under /app/....

Platform modules

The app is growing from a document manager into a client portal of eleven service modules (src/lib/modules/registry.ts). All eleven exist: 01 Documents, 02 Invoices (src/lib/invoices/, src/components/invoices/), 03 Licenses & Renewals (src/lib/licenses/, src/components/licenses/), 04 Vendors (src/lib/vendors/, src/components/vendors/), 05 CRM & Data (src/lib/contacts/, src/components/contacts/), 06 Projects (src/lib/projects/, src/components/projects/), 07 Forms & Paperwork (src/lib/forms/, src/components/forms/) and 08 Reporting (src/lib/datasets/, src/components/reporting/) and 09 Requests (src/lib/admin/, src/components/admin/) 10 Tidy up files (src/lib/files/, src/components/files/) and 11 Settings (src/lib/setup/, src/components/setup/). Every service starts empty and keeps its records in the browser until the database is connected (see the limits below).

Shared import engine (src/lib/import/): parseCsv (RFC 4180 quoting, BOM, CRLF, comma/semicolon/tab, limits of 5,000 rows, 50 columns and 2,000 characters per cell, control characters stripped, line numbers kept for error reports), suggestMapping / mapRows (match source columns to target fields by name and alias), and runImport (validate each row, then sort into accepted, rejected and duplicate). CRM & Data is its first user; modules 08 (Data Entry) and 10 (File Reset) are meant to reuse it. Files are read in the browser and never uploaded.

• Navigation: a left sidebar on wide screens (logo, one icon per module, the current module highlighted, Settings apart at the bottom) and a menu button that opens the same list as a drawer on a phone.
• Pages: /app/overview (Home), /app (Documents), /app/invoices, /app/licenses, /app/vendors, /app/contacts, /app/projects, /app/forms, /app/reporting, /app/admin, /app/file-reset, /app/setup. With a database they sit behind sign-in; without one they open as a preview.
• Invoices, licenses, vendors, contacts, projects, forms and report data, requests and recurring tasks are not persisted yet. They start empty and are held in component state; there is no table or API for them until the database step. Reminders are computed (next reminder date, alerts, renewal tasks, overdue notices) but no email is sent yet. Licenses can be pre-filled from an uploaded PDF, Word, text or CSV file (/api/licenses/parse reads the number, issuer, holder and dates next to their labels; photos and scans are not read, and the file itself is not stored). Vendors get required documents by category, can only be submitted for approval once every required document is in and unexpired, and are checked for duplicates (normalized name, similar name, tax ID, contact email, shared company email domain) when added and across the list. CRM & Data imports in three steps (choose or paste a file, map columns, review) and shows every rejected row by file line before anything changes; matching contacts are skipped or updated, and the contact list can be de-duplicated and exported. Scheduled synchronization with other systems needs the backend and is not built. Reporting guesses column types (dates as 3/14/2026 or ISO, money as $1,200.50 or (150.25)), rejects rows that break the rules with the file line and reason, drops exact repeats, then builds grouped reports (dates by month) and exports CSV or a real .xlsx workbook (written with fflate; text is stored as plain text, so =CMD() can never run as a formula). Scheduled summaries show the next send date and a preview but nothing is emailed until there is a backend. Requests suggests a category and priority from fixed keyword rules (not an AI service, and it shows which words matched), holds anything that moves money, changes access, deletes or commits the business until a person approves it, fills a request from a pasted email (subject, sender, body; quoted replies dropped), creates one request per due recurring task (never a pile after missed runs), shows a daily digest on screen, and exports an calendar of due dates. No email is sent or read and no calendar is connected: those need a backend. Tidy up files reads a folder chosen in the browser, hashes every file there (SHA-256, files up to 50 MB, 500 MB in total) so identical files are found without uploading anything, and builds a plan: standardized names, duplicates moved to a review folder (never deleted), misplaced files moved to the right folder, and optionally everything sorted into suggested folders. Each change is shown with its reason and must be approved before the plan CSV, a move script and an undo script can be downloaded. The scripts are POSIX , quote every path, use , skip anything missing or that would overwrite, and contain no delete command; the tests run them under a real shell against names with quotes, , backticks, a leading dash and , and check that undo restores the exact original tree. Paths with , drive letters or control characters are skipped. A pasted list can be used instead of a folder (duplicates are then only "likely"). Settings is a five-step wizard (business profile, modules with industry and team-size recommendations, templates, existing data, automatic checks). Progress is saved in the browser after every change (, validated and cleaned on load, so damaged or hand-edited data is ignored). Finishing installs the chosen templates for real: forms appear in Forms & Paperwork, recurring tasks in Requests and starter records in Licenses & Renewals. An unfinished or edited setup installs nothing. Choices are per browser, not per account, until the database is connected. Forms are built in the app, validated on submit, routed to a named approver when required, and turned into a text document by filling placeholders in a single pass (submitted text is never re-expanded); submissions export to CSV. Projects flag overdue tasks and repeating tasks schedule their next occurrence when completed, with a plain-text status report. Invoices support recurring series ("Create next" drafts the following invoice) and a CSV report whose cells are neutralised against spreadsheet formulas.

Contents

1. Quick start (no credentials)
2. Supabase setup
3. Environment variables
4. Architecture and security model
5. Testing
6. Deployment
7. Implemented features
8. Limitations and what is not verified

Quick start (no credentials)

npm install
npm run dev          # http://localhost:3000  (opens the app in preview mode)

Requires Node 20.19+ (developed on Node 22). Without Supabase variables the app opens in an empty preview mode where records are held in the browser (they reset on refresh). Nothing is stored on the server.

Supabase setup

1. Create a project at supabase.com.
2. Apply the migrations in supabase/migrations/ in order — with the CLI (supabase link --project-ref <ref> && supabase db push) or by pasting each file into the SQL editor. They create the tables, helper functions, RLS policies, RPCs, audit triggers, the private documents bucket and its storage policies.
3. Auth (Authentication › URL Configuration): set Site URL to your app URL and add https://<your-domain>/auth/callback (and http://localhost:3000/auth/callback) to Redirect URLs. Email + password sign-in is used; keep "Confirm email" on for production.
4. Keys (Project Settings › API): copy the URL and the publishable/anon key into .env.local (see .env.example). Add SUPABASE_SERVICE_ROLE_KEY only if you want share links; it is read solely by /share/[token] on the server.
5. Sign up at /signup, then create your workspace on first sign-in. The creator becomes owner and the five system folders are seeded.
6. Adding teammates: owners and admins open Team in the workspace header and invite by email address. No email is sent — tell the person to sign up (or sign in) at your app with that exact address. When they do, /app calls dms_accept_invites(), which joins them only if their address is confirmed in Supabase Auth, so keep "Confirm email" on (with it off, anyone could claim an invite by typing the address). Invites expire after 14 days. A person who already has a workspace keeps it as their primary one; the new membership exists but there is no workspace switcher yet.
7. Scheduled cleanup (recommended): set CRON_SECRET (16+ random characters) and SUPABASE_SERVICE_ROLE_KEY, then call /api/maintenance/cleanup-uploads daily — see Deployment.
8. Content search backfill (only if documents existed before migration …0007): they start as "not indexed yet" and are searchable by name only. Editors opening the workspace index a few at a time automatically; to do it in bulk call /api/maintenance/reindex — see Content search.

The bucket limits uploads to 50 MB and a list of MIME types. If you raise DMS_MAX_FILE_SIZE_MB or add file types, update the bucket row in supabase/migrations/20261023000004_dms_storage.sql (or the dashboard) too.

Environment variables

See .env.example. Browser-visible: NEXT_PUBLIC_SUPABASE_URL, NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY (RLS protects data). Server-only: SUPABASE_SERVICE_ROLE_KEY (share links and cleanup), CRON_SECRET (protects the cleanup route). Policy knobs: DMS_PENDING_UPLOAD_MAX_AGE_HOURS (default 24, minimum 3), DMS_MAX_FILE_SIZE_MB, DMS_MAX_FILES_PER_BATCH, DMS_ALLOWED_EXTENSIONS, DMS_EXTRA_FILE_TYPES, DMS_ALLOW_DEV_MODE.

Architecture and security model

UI components ──► useDocumentManager (state) ──► DmsApi interface
                                                   ├─ http-api  ─► /api/dms/* route handlers ─► DocumentService ─► Supabase adapters
                                                   └─ local-api ─► DocumentService ─► in-memory adapters   (public demo)

• src/lib/dms/service.ts – all business rules: role checks, upload validation, quota, workspace scoping, versioning, share links, audit calls. Pure TypeScript over two ports (DocumentRepository, ObjectStorage), so it is unit-tested without a database.
• src/lib/dms/supabase/* – adapters that run as the signed-in user (never the service role), so Postgres RLS is the tenant boundary. UI filtering is not relied on.
• Uploads go browser → Supabase Storage directly via a signed upload URL (no server body-size limit). Sequence: initiate (server validates name/type/size, atomically reserves quota in Postgres, creates a pending version) → browser PUT with real progress and cancel → finalize (server checks the object exists, its size equals what was declared, and magic bytes match the extension; otherwise it deletes the object and the pending record). Storage RLS only allows creating an object for a pending version the caller opened, so quota cannot be bypassed by talking to Storage directly.
• Downloads/previews use 60-second signed URLs minted after an authorization check. Storage object keys never reach the browser; buckets are never public.
• Keys are <workspace>/<document>/v<n>-<nonce>.<ext>, built server-side. User file names are sanitised for display only and never used in paths.
• Share links: 256-bit random token, only its SHA-256 hash is stored, 5 min–7 days, resolved by a service-role-only SQL function and redirected to a 60-second signed URL. Editors can list a document's live links and revoke their own (admins: any); revocation takes effect immediately. Creation, revocation and every use of a link are audit-logged (a use has no actor, since link holders are anonymous). The URL itself is shown once.
• Team: invites and role changes are SECURITY DEFINER RPCs (workspace_members and workspace_invites have no client write grants). Rules, enforced in both the service and SQL: admins manage viewers/editors only; only owners grant or touch admin/owner; nobody changes their own role or removes themselves; a workspace always keeps one owner (row locks serialise concurrent owner changes). Invites match a confirmed email only.
• Abandoned uploads: dms_cleanup_stale_uploads() (service-role only) deletes pending versions/documents older than the threshold (≥ 3 h, because signed upload URLs last 2 h) and returns their storage keys; the route then deletes the objects and reports counts.
• Rate limiting: mutating /api/dms requests are limited per signed-in user (120/min), with stricter limits on upload initiation (60/min), share-link creation (20/min) and invites (20/h); is limited per client address (60/min). Exceeding a limit returns with . (see limitations).

Database layout: workspaces, workspace_members, document_folders, documents, document_versions (with status), document_tags, document_tag_assignments, audit_logs, plus document_share_links. Child rows use composite foreign keys so they cannot point into another workspace. documents also holds content_text, a generated search_vector (GIN-indexed, content only) and the content_status / content_reason / content_indexed_at / content_version columns described under Content search. Clients cannot write any of them directly.

Content search

Extracted text is stored per document and searched with PostgreSQL full-text search.

• When: right after finalize returns (Next's after()), the server reads the stored file as the signed-in user, extracts text and calls dms_set_document_content. The upload never waits for it and never fails because of it. The document is searchable by name immediately and by content once content_status becomes indexed (the UI polls briefly and shows the state).
• What is read (src/lib/dms/extract/): the text layer of PDFs (PDF.js via unpdf, scripts, XFA and font loading off), DOCX, XLSX, PPTX, CSV and TXT. There is no OCR: scanned PDFs and images have no searchable text and say so. Legacy .doc/.xls are not accepted by the app.
• Limits (untrusted input): files over 25 MB are not read; archives are capped at 3,000 entries, 40 MB per entry, 80 MB in total and a 150× expansion ratio (checked while inflating, so a lying header cannot bypass them); XML is parsed with a small non-expanding tokenizer (no DTD/entities); PDFs stop after 300 pages; each file has a 20 s budget; stored text is capped at 400,000 characters (reason = truncated). Password-protected and malformed files are marked, not guessed at.
• Honest status: pending (name only), indexed, unsupported (no extractable text, too large, scanned…) or failed (retryable). Only indexed documents ever match on content. A new version clears the previous version's text immediately; a slow extraction of an older version is discarded (content_version must equal current_version).
• Query syntax (websearch_to_tsquery('simple')): words are ANDed, "quoted phrases", or, -exclude. No stemming or stop-words (simple), so it works across languages but "rent" does not match "rented". A document matches if every word is in its name/metadata/tags or the whole query matches its text; a query split across the two (one word in the name, another in the text) does not match. Sort Best match puts name/metadata matches first, then text rank.
• Snippets are cut from the stored text by ts_headline, which drops markup; matches are wrapped in U+E000/U+E001 (stripped from stored text so they cannot be forged) and rendered as React text nodes, never HTML.
• Backfill: opening the workspace as an editor indexes up to 5 pending documents in the background. For bulk work, (same + as the cleanup route) indexes unindexed documents across all workspaces within a 45 s budget and returns counts only; repeat until is 0. It is not scheduled for you.

Testing

npm run typecheck && npm run lint
npm test            # 276 unit/component tests (vitest)
npm run test:db     # migrations + RLS against a throwaway local PostgreSQL 16 (237 PASS lines, including security and content-search regressions)
npm run test:e2e    # Playwright: demo page, dev-mode server flows, content search, tablet/phone layout (32 tests)

• tests/unit – service rules (roles, workspace isolation, upload validation, download authorization, folder filtering, search, rename/move/star, delete/restore/purge, usage and quota), team rules and share-link revocation, validation helpers, proxy/route authentication, rate limiting, the share and cleanup routes, the Supabase adapters (SupabaseRepository/SupabaseStorage against a recording fake client: RPC arguments, column mapping, tenant scoping, error mapping, signed-URL handling), and component tests for empty/error/loading states, the workspace flows and the Team dialog.
• supabase/tests/run.sh – boots a temporary Postgres 16, stubs Supabase's auth/storage schemas and roles, applies every migration, and exercises RLS as different users (cross-workspace reads/writes, role limits, protected columns, storage policies, quota, audit triggers, share-link resolution, invites and acceptance incl. unverified/expired/ wrong-address cases, role/removal rules, link revocation and use auditing, stale-upload cleanup, content search: who may store text, stale versions, status rules, phrase/OR/exclusion, snippets, isolation, relevance, work queues). team.sql, hardening.sql and content.sql are included from rls.sql. Needs the PostgreSQL 16 server binaries (PG_BIN overrides the path).
• tests/e2e – needs a Chromium; set PLAYWRIGHT_CHROMIUM_PATH or run npx playwright install chromium. layout.spec.ts checks 820 px and 360 px for page overflow, panel fit and touch-target size, and writes screenshots to test-results/layout/. The first visit to each page compiles it in the dev server, so a cold run is slower.

Deployment

Any Node host works (Vercel, Fly, Docker, …):

1. Apply the migrations and configure Auth as above.
2. Set the environment variables on the host (do not set DMS_ALLOW_DEV_MODE).
3. npm run build && npm start (Vercel: import the document-management directory).
4. Smoke test with two accounts in two workspaces: sign up, create workspaces, upload, download, preview, share link; confirm account B cannot see account A's documents, and that GET /api/dms/bootstrap is 401 when signed out.

Scheduling the upload cleanup

GET|POST /api/maintenance/cleanup-uploads removes abandoned pending uploads. It needs CRON_SECRET (≥ 16 characters) and SUPABASE_SERVICE_ROLE_KEY, and authenticates with Authorization: Bearer <CRON_SECRET> (or an x-cron-secret header). It responds with counts only. The route is outside /api/dms, so it is not behind user login — the secret is the only gate; without CRON_SECRET it returns 503.

• Vercel: vercel.json schedules it daily at 03:17 UTC; Vercel sends the bearer header automatically when CRON_SECRET is set as a project environment variable.
• Elsewhere: curl -fsS -X POST -H "Authorization: Bearer $CRON_SECRET" https://your.app/api/maintenance/cleanup-uploads from any scheduler (cron, GitHub Actions, Supabase pg_cron + pg_net).

If this directory is moved to its own repo, supabase/ and tests/ move with it unchanged.

Implemented features

Responsive UI (desktop/tablet/mobile) with hero + demo page · folder cards with counts and filtering · document grid with type icon, size, date, category, tags, star · search (filename, tags, category, extension, uploader) · category/type/tag filters · sort · All/ Recent/Starred/Trash views · incremental "Load more" · floating upload dialog (drag-and-drop, browse, validation, per-file progress, cancel, retry, destination folder, storage usage) · floating details panel (full-screen on phones) with PDF/image/CSV/text preview and "download to view" fallback · download · expiring share links · rename · move · category · tags · star · optional expiration date · version history, upload new version, download old versions · soft delete with confirmation, restore, permanent delete · custom folders (create/rename/delete; system folders protected) · per-workspace storage usage and quota · audit log · Supabase auth (sign in/up/out, onboarding) · RLS on every table and the bucket · team management (invite by email, change roles, remove members, pending invites; owners/admins only) · share-link list and revoke, with creation/revocation/use audited · scheduled cleanup of abandoned uploads · per-user and per-address rate limiting.

Limitations and what is not verified

• Not verified against a hosted Supabase project. I could not reach one. The SQL is tested on a real local Postgres 16 with stubbed auth/storage schemas, so the RLS and RPC logic is exercised for real, but Supabase Auth, PostgREST, Storage's signed-URL endpoints and the browser→Storage signed upload (the multipart body mirrors @supabase/storage-js' uploadToSignedUrl, checked against its source) have not been run end to end. SupabaseRepository/SupabaseStorage are tested against a fake client (they verify what we ask the database/Storage to do, not how Supabase answers). Run the two-account smoke test above before trusting a deployment.
• Content search covers only what could be extracted. Scanned/image-only PDFs, images and password-protected files have no searchable text (no OCR). Documents are searchable by name alone until indexed, and the details panel shows each document's state. Extraction runs inside the Next.js server process after the upload response; on a serverless host it is bounded by the platform's function duration (maxDuration is set to 60 s on the routes that index), and a killed run leaves the document pending for the backfill to pick up. The migration, RPCs, extraction and queries are tested on local PostgreSQL 16 and in-process only; extraction of real files through Supabase Storage and PostgREST has not been run end to end. Search ranking is PostgreSQL's ts_rank_cd, not tuned. Text is stored in the same table as the metadata, so anyone who can read a document row can read its text via the API (they can already download the file).
• Mutations are mostly one round trip each; multi-step operations rely on the RPCs (quota reservation, completing/aborting uploads, tag replacement, purge, team changes). Deleting storage objects after a purge or cleanup is best-effort: a failure leaves an orphaned object, never a dangling record, and the cleanup route only reports such failures. There is no scan for objects that have no database row at all.
• The cleanup route and its schedule are not exercised against a real Supabase project, and nothing triggers it by itself: you must schedule it.
• Signed upload URLs are valid for Supabase's fixed 2 hours.
• Rate limiting is in memory and per server instance. On a multi-instance or serverless deployment each instance counts separately (effective limit = limit × instances) and counters reset on restart. It blunts loops and casual abuse; it is not a hard guarantee. Use a CDN/WAF or a shared store (e.g. Redis) for that. create_workspace, login and signup are not limited by this app (Supabase Auth applies its own limits).
• : invites send no email (the inviter must tell the person); they are matched on a address, so this depends on "Confirm email" being on. There is no workspace switcher: a user who belongs to several workspaces always opens their oldest one. Users cannot leave a workspace, change their own role, or transfer ownership in one step (promote a second owner, then have them demote you). The Team UI is hidden from editors and viewers. Removing a member does not delete what they uploaded or revoke share links they created (an admin can revoke those).

Security

See SECURITY_REVIEW.md for the pre-release review: fixed findings, open items and the deployment requirements it depends on (notably: keep Supabase Auth "Confirm email" on, set CRON_SECRET, apply migration …0006_dms_security_hardening.sql).