Data Model

Memo stores relational data in Neon Postgres (schema memo), high-volume telemetry in a MongoDB Atlas time-series collection, and project files in Cloudflare R2.

URL space + slug rules#

Memo owns the entire root URL space of memo.matters.ai. Two disjoint surfaces share it:

Visitor slugs are at the root — this is what makes share URLs short (memo.matters.ai/acme-deck instead of memo.matters.ai/v/acme-deck). The trade-off is that a slug like api would collide with the /api/* surface, so memo enforces a global reservation list at link create time.

SLUG_RESERVED#

Defined in src/lib/validation/link.ts. Any link-create request with slug ∈ SLUG_RESERVED is rejected with "this slug is reserved by memo; pick a different one". The list is the union of:

• Every named top-level app surface that doesn't have a trailing-slash variant: 404, 500, health, sign-in, sign-up, favicon.ico, robots.txt, sitemap.xml.
• Every top-level path prefix used by an app surface: api, oauth, u, well-known (the .well-known/* set), _actions, _astro, _image, _server-islands (Astro internals).
• Historical schemes kept for defense-in-depth so they can't be revived as visitor slugs: app, v.

The [vitest suite](/memo/operations#verify-pipeline) enforces parity between SLUG_RESERVED and isVisitorPath (in src/lib/routing.ts) — every named app surface MUST be reserved, and that contract is checked on every CI run.

Slug format#

/^[a-z0-9][a-z0-9-_]{1,63}$/

• 2–64 chars total.
• First char must be a letter or digit (no leading - or _).
• Allowed body chars: a–z, 0–9, _, -.
• Slugs are stored case-insensitively (citext), but the schema also lowercases the input via z.string().trim().toLowerCase() so the cookie name and DB key match.

Validation lives in src/lib/validation/link.ts (linkCreateSchema / linkUpdateSchema) and is re-applied at every entrypoint: the owner UI's link drawer, the /api/owner/links POST, and the MCP create_link tool.

Postgres — schema memo#

The full Drizzle schema lives in apps/memo/src/lib/db/schema.ts and is mirrored by a clean baseline apps/memo/drizzle/0000_init.sql (+ meta/0000_snapshot.json) followed by apps/memo/drizzle/0001_slimy_morg.sql (the collaboration tables — project_members + project_invites). The migration chain was collapsed to the 0000 baseline after the prior snapshots drifted/became non-linear, then resumed linearly from there. The pre-Phase-1 → current incremental ALTERs are archived in apps/memo/drizzle-legacy/ for upgrading an existing database; a live DB is kept in sync with schema.ts via drizzle-kit push. See the Runbook for apply paths. The schema requires two Postgres extensions which the migration enables on bootstrap:

CREATE EXTENSION IF NOT EXISTS "citext";    -- case-insensitive username, email, slug
CREATE EXTENSION IF NOT EXISTS "pgcrypto";  -- gen_random_uuid() defaults

users#

projects#

owner_id stays the creator forever; shared access is additive via the two tables below. A user can act on a project when requireProjectAccess(projectId, userId) passes — i.e. they own it or have a project_members row. accessibleProjectIds(userId) returns the owned ∪ member union that backs the dashboard's "shared with me" listings. See Collaboration.

project_members#

One row per user granted shared access to a project. Graded roles now ship — owner / admin / editor / viewer (src/lib/auth/roles.ts); new invites default to editor. The DB column default stays 'owner' for backward-compat with rows created before RBAC. See Collaboration.

UNIQUE on (project_id, user_id); indexed on user_id (powers accessibleProjectIds).

project_invites#

A pending invite for an email that has no memo account yet. Resolved into a project_members row on signup via acceptPendingInvites (src/lib/auth/owner.ts).

UNIQUE on (project_id, email) and UNIQUE on token.

versions#

Each Publish creates a new immutable row. Soft-delete via current_version_id only.

files#

Unique on (version_id, path).

links#

The wide table — one row per shareable URL.

Notable columns:

• slug citext unique — global slug across all owners, enforces the URL space.
• version_id ON DELETE RESTRICT — refuses to drop a version someone is still linking to.
• password_hash text, password_salt text — argon2id hash with per-row salt and env pepper.
• allow_emails text[], block_emails text[], allow_domains text[], block_domains text[] — composable allowlist/blocklist.
• require_agreement_id → agreements.id ON DELETE SET NULL.
• expires_at timestamptz — checked by every gate; flipped to inactive by the expire-links cron.
• watermark, allow_downloads, active — booleans.
• welcome_*, custom_button_*, branding — per-link white-label fields.

agreements#

NDA templates owned by a user, immutable once created (v1).

signatures#

A signature is captured against a link gate OR a space gate — exactly one of link_id / space_id is set (link_id is nullable; a space id must not go into it, since it FKs to links).

visits#

The canonical "this person engaged with the deck" row. One row per gate-JWT lifecycle (sub UUID = the cookie). Multiple visits per email + per persistent-id are normal — visits are sessions, not people.

Indexes: visits_link_idx on link_id, visits_space_idx on space_id, visits_project_idx on project_id, visits_started_idx on started_at, visits_persistent_idx on visitor_persistent_id.

visit_signals#

Derived behavioural classifications, one row per (visit_id, kind). The Activity tab's signal chips and the people page's per-row badges read from this table. Per-visit signals (skim, deep_read) are emitted inline by /api/beacon on page_leave; cross-visit signals (return, share_detected) are emitted by the derive-cross-visit-signals cron every 10 minutes.

Indexes:

• visit_signals_visit_kind_idx — UNIQUE on (visit_id, kind). Enables idempotent inserts: ON CONFLICT DO NOTHING (per-visit) and ON CONFLICT (visit_id, kind) DO UPDATE (cross-visit). Also speeds up "all signals for one visit" lookups.
• visit_signals_kind_idx — on kind. Speeds up cohort queries like "all deep_read visits in the last 7 days".

personal_access_tokens#

Machine-to-memo bearer tokens minted by the owner at /settings/tokens.

oauth_clients#

Third-party apps that have registered via RFC 7591 Dynamic Client Registration.

oauth_authorization_codes#

Short-lived (10 min) one-shot codes minted at /oauth/authorize, redeemed at /oauth/token.

oauth_access_tokens#

The bearer tokens /api/mcp accepts. Hashed exactly like PATs.

oauth_refresh_tokens#

Single-use refresh tokens. Memo rotates them on every successful refresh (sets rotated_at on the old row, mints a new one).

visit_recordings (v3)#

One row per visit with a self-hosted rrweb recording. The actual event blob lives in R2 at recordings/<projectId>/<visitId>.json.gz — this side-table holds the lifecycle metadata so the Activity UI can light up the Replay column without touching R2.

webhook_endpoints (v3)#

Outbound webhook subscriptions. Secret stored hash-only — raw shown once at create.

webhook_deliveries (v3)#

Append-only delivery log. The cron picks up delivered_at IS NULL AND next_attempt_at <= now() rows.

projects (v3 column)#

projects gained a notes_md text NULL column in v3 — owner-only Markdown rendered above the editor as project context.

audit_logs#

Append-only ledger of every state-changing action.

Indexed on (target_type, target_id) and ts.

Phase 1 — Spaces, Teams, Diligence, Q&A, provider tokens#

These tables back the data-room surface. Full feature docs: Spaces, Teams & Diligence, Q&A, Integrations.

spaces#

Multi-project data room. Mirrors the links access-control columns so the gate code is shared.

Indexes: spaces_slug_idx UNIQUE on slug, spaces_owner_idx on owner_id.

space_projects#

Membership join — which projects are in a space and their order.

Composite UNIQUE on (space_id, project_id) + per-column indexes.

space_visitor_groups / space_group_emails#

Named email cohorts within a space. space_visitor_groups (space_id, group_name, permission text default "view") and space_group_emails (space_id, group_name, email), each with a composite UNIQUE.

space_audit_logs#

Immutable data-room access trail. bigserial PK, space_id → spaces.id CASCADE, actor_email?, action, target_type, target_id, meta jsonb, ts. Indexed on space_id and ts. The native viewer writes a project.viewed row per served project.

teams#

links gained visibility text (default "public") + team_id uuid? → teams.id ON DELETE SET NULL to drive checkTeamAccess.

diligence_items / diligence_comments#

Per-space due-diligence checklist. diligence_items: space_id CASCADE, title, description?, status (default open), category?, priority int, assignee_email?, due_date?, related_file_path?, timestamps (indexed on space_id, status). diligence_comments: item_id → diligence_items.id CASCADE, author_email, body, created_at.

qa_items#

In-viewer Q&A. visit_id → visits.id CASCADE, link_id → links.id CASCADE, question, answer?, answered_at?, page_path?, created_at. Indexed on visit_id, link_id. See Q&A.

user_provider_tokens#

Encrypted cloud-storage OAuth tokens (Drive/Dropbox/Box). user_id CASCADE, provider, provider_account_id?, access_token_encrypted (AES-256-GCM), refresh_token_encrypted?, scopes[], token_type, expires_at?, timestamps. UNIQUE (user_id, provider) (user_provider_idx) → atomic upsert. See Integrations.

MongoDB — time-series events#

{
  ts: ISODate,           // timeField, server-stamped at ingest; used for the 90-day TTL
  visit_id: "uuid",      // metaField (clusters one visit together)
  link_id: "uuid|null",  // denormalised; null for space visits
  space_id: "uuid|null", // set for space visits (served at /s/<slug>/p/<id>/)
  project_id: "uuid",    // denormalised so cross-link/space queries are cheap
  type:
    | "page_view"
    | "page_leave"
    | "scroll_depth"
    | "click_zone"
    | "focus_change"
    | "gate_passed"      // reserved
    | "download"         // reserved
    | "section_enter"    // memo v3.x — IntersectionObserver crossed 50% visibility
    | "section_exit"     // memo v3.x — element left viewport; payload.ms = dwell
    | "media_progress",  // <video>/<audio> timeupdate; ≥80% emits the video_watched signal
  payload: { ... }       // per-type shape
}

Per-type payload shapes (payload.*):

Collection setup (in src/lib/db/mongo.ts):

• Created lazily on first read/write per warm function container.
• timeseries.timeField: "ts" + metaField: "visit_id" + granularity: "seconds".
• expireAfterSeconds: 90 × 24 × 3600 → 90-day TTL.

Recommended secondary index (run once per environment):

db.events.createIndex({ project_id: 1, type: 1, ts: -1 })

This makes /api/owner/projects/<id>/analytics/sections (which does find({ project_id, type: "section_exit" })) O(matches) instead of a partial scan.

Why Mongo here:

1. Volume + retention. 50 visitors × 200 events × 90 days fits comfortably in M0; we never query past 90 days.
2. Independent from FullStory (memo's predecessor recording provider) and now LogRocket — even if recording goes dark, the funnel + scroll/click + section data survives.
3. Time-series semantics out of the box, with TTL on ts.

See Observability for the full beacon flow, per-event payload contracts, sample queries, and aggregation patterns.

R2 — key layout#

staging/<projectId>/<path>                          # writable editing buffer
<userId>/<projectId>/<versionId>/<path>             # immutable published assets
signatures/<signatureId>.pdf                        # signed-NDA PDFs (rendered async)

• staging/ is wiped per project on every Publish (we copy what compiled successfully into the version prefix, the staging dir keeps the source-of-truth).
• The version prefix is content-addressed at the version level — same <userId>/<projectId>/<versionId>/ is never overwritten, so cached CDNs are safe in a future v2.
• Path-traversal is enforced before any R2 write by normalizeProjectPath() in lib/path-safety.ts. The regex restricts each segment to [A-Za-z0-9._-]+.