Canonical data model

web-tanstack/prisma/schema.prisma is the canonical application model and web-tanstack/prisma/migrations/ is the replayable database history. Objects Prisma cannot express are versioned there and mirrored in the idempotent operator repair web-tanstack/prisma/manual-indexes.sql.

ORM ownership

Web uses Prisma Client as its default query and transaction layer. Prefer generated select/include projections; keep parameterized raw SQL for PostgreSQL-specific locks, recursive/window queries, JSONPath, and exact microsecond cursors. TypedSQL is not in the build because its database-coupled generation cost is not justified by the remaining raw-query surface.

Core uses Drizzle’s parameterized SQL over Hyperdrive, but owns no parallel table schema or migration history. Both Workers depend on the same migrated PostgreSQL contract above.

flowchart LR
  Source["Source"] -->|"nullable provenance"| Resource["Resource"]
  Resource --> Translation["ResourceTranslation"]
  Resource --> Identifier["ResourceIdentifier"]
  User["User"] --> LibraryItem["UserResource"] --> Resource
  LibraryItem --> Placement["ResourcePlacement"]
  Placement --> Workspace["Workspace"]
  Placement --> Collection["Collection"]
  User --> File["UserFile"] -->|"raw blob"| Resource
  File -->|"active item"| LibraryItem
  Workspace -->|"owns through workspace_id"| Document["UserDocument"]

Resource content

Prisma model	Database table	Responsibility
Resource	resources	canonical URL/blob identity, lifecycle, provenance, representation/byte size, render-ready metadata, immediate subject, and resource-local tags and keywords
ResourceTranslation	resource_translations	localized title, summary, content
ResourceIdentifier	resource_identifiers	exact DOI, arXiv, or OpenAlex identity for a canonical Resource
Source	sources	monitored RSS, Twitter, or YouTube configuration and monitoring policy
UserResource	user_resources	the stable user-to-Resource item; profileSavedAt records an explicit profile save
ResourcePlacement	resource_placements	an item’s optional collection or workspace location
UserFile	user_files	private raw-blob ownership, original filename, and active library-item bridge

Resource identity uses independent axes:

Concern	Canonical owner
Product/content semantics and frontend composition	Resource.kind
Exact scholarly and platform-native identity	ResourceIdentifier
Special Hacker News, Twitter, or YouTube behavior	Resource.resourcePlatform
Feed/editorial classification (`blog	news`)
Primary representation format	Resource.fileType MIME
App-owned blob presence and quota size	Resource.storageKey / Resource.byteSize
Monitored provenance and feed/web policy	nullable Resource.sourceId → Source
Explicit profile save	UserResource.profileSavedAt
Collection/workspace location and source/result role	ResourcePlacement
Private raw-blob ownership	UserFile
Canonical textual body	ResourceTranslation.content as Markdown
Provider-independent kind-specific read data	versioned Resource.kindMetadata JSONB

The database has no second resource-type discriminator. The valid content identity pairs are:

• blog / null;
• forum / hackernews;
• post / twitter;
• video / youtube;
• paper / null;
• image / null;
• file / null.

fileType remains the representation axis; there is no PDF-specific kind. A readable PDF that cannot be academically identified remains file / null; the readable PDF subset of file participates in corpus search and content reads. HN/Twitter resources remain carrier Resources and link their immediate target through subject_resource_id; an academic target is a separate paper / null Resource. Source.kind remains a separate feed/editorial classification: both blog and news sources produce canonical blog Resources after successful processing. Deleting a source sets provenance to null and does not delete canonical content.

Provider payloads are transient Core acquisition data. The database stores only provider-independent ordered creators, versioned kind_metadata, exact resource_identifiers, scalar source_revision, and immediate subject_resource_id facts.

Paper identity and enrichment

resource_identifiers stores only exact external identity. The supported schemes are doi, arxiv, openalex, hackernews, twitter, and youtube; (scheme, value) identifies one canonical Resource. A Resource may retain multiple exact values for a scheme, with at most one marked preferred. URL variants and versioned arXiv identifiers normalize before reservation.

Exact paper lookups use the official OpenAlex REST API. The database does not mirror the OpenAlex graph, Zotero/CSL item model, full reference lists, or a normalized creator table. Core stores the compact paper authorship, venue, metric, relation snapshots, and external-PDF fields currently rendered by cards and readers in the canonical common fields and versioned Paper kind metadata. Product-owned title, summary, content, publication date, authorization, and blobs remain on the ordinary Resource model.

Citation formatting, BibTeX/RIS import/export, publisher-specific extraction, and PDF-to-TEI conversion belong at feature boundaries. Citation.js, Zotero Translation Server, or GROBID may implement those boundaries when the product surface exists; none of them shapes the canonical database in advance.

Use two date meanings:

• published_date: nullable source publication time;
• effective_at: published_date ?? scraped_date ?? created_at, used for discovery order and recency filters.

Library items, profile saves, and private blobs

user_resources is the stable user-to-Resource edge. Its nullable profile_saved_at records an explicit save to the profile. Uploading a file, creating an output, or adding a workspace source creates or reuses this edge without automatically adding it to the profile. Unsave clears the timestamp; file ownership and container placements remain intact. Profile visitors only see saved corpus Resources; saving a private file never makes its bytes public.

user_files links the private raw blob (resource_id) to its owner and active library item (user_resource_id). It records the original filename and origin_type (upload or generated). Physical representation, storage, and quota size belong to the raw Resource through file_type, storage_key or hosted_image_id, and byte_size. Origin records how the bytes entered the app; it does not determine how a workspace uses them.

When academic PDF enrichment resolves a canonical paper, Web atomically retargets or merges the owner’s UserResource, preserving explicit profile saves and placements, and repoints UserFile.userResourceId. UserFile.resourceId continues to own the raw blob, so deleting the upload can remove its bytes without deleting the canonical saved paper.

Podcast outputs retain their GenerationJob lifecycle. Deleting the file through media settings uses the same job deletion path as deleting the result, including cancellation and durable blob cleanup. Workspace output reads return job-owned files through jobs and other result placements through files.

Public corpus documents are ranked in Cloudflare AI Search. Viewer-private resources stay in PostgreSQL and are merged with public ranks in the Web Worker. Private image/file blobs never enter the public index or MCP content read context; enriched corpus PDF files do. The serving newsence-corpus-v6 index stores canonical kind and resource_platform metadata; null platforms use the none metadata sentinel.

Container placement and citations

resource_placements is the one container table. Every row references a UserResource and exactly one of collection_id or workspace_id. Composite foreign keys require the placement, library item, and container to share the same user_id. Workspace placements have a source or result role, unique per workspace, item, and role. The same generated file can be a result and a deliberately added source. Collection placements always use source and remain unique per collection and item.

Workspace-owned documents use user_documents.workspace_id. Workspace and collection placement remain independent: adding an item to a collection does not place it in a workspace, but both locations reuse the same library item.

AI and document citations are inline [n](URL) markers in generated prose. They are not container-junction rows and do not have a citation CRUD API.

Worker ownership

• Web owns product authorization, save/file/container writes, collections, resource HTTP/MCP DTOs, private search, and PostgreSQL hydration.
• Core owns monitored acquisition, enrichment, translations, resource-local entity annotations, and the public AI Search corpus.
• Think owns chat state, native Workspace scratch files, and tools. It calls Web through the DOMAIN service binding; Web calls Core for public discovery. Think does not query product resource tables directly.

See resource-access-inventory.md for the live read/write touchpoint inventory and worker-boundaries.md for cross-Worker rules.