Client Drive Implementation Plan

Purpose

This document is the technical source of truth for moving from the current flat documents table to the drive model.

Read this together with ../product/client-drive-spec.md.

Current State

The legacy starting point was a flat uploaded-file register.

That is no longer the live collaboration model.

Current backend surface:

packages/backend/convex/org/drive.ts
packages/backend/convex/portal/workspace.ts
driveItems, fileAssets, richDocuments, fileRequests, driveAccessLinks, driveExternalUploadSubmissions, driveAccessLinkChallenges, and driveAccessLinkSessions in packages/backend/convex/schema.ts

Current shipped baseline:

real nested drive items are live
request-targeted destinations are live
scoped contributor upload links are live for org users
scoped contributor upload links are live for portal clients inside visible shared destinations
portal clients can list, copy, and revoke contributor links in portal files UI
org users can now see whether a contributor link was created by agency staff or by a portal client and can copy or reissue link URLs from first-class UI
legacy-link token reissue is now supported directly in the backend and the current deployment has no remaining tokenless contributor links
outside contributors complete email-OTP verification without portal login
uploads remain destination-scoped, upload-only, and provenance-labeled

Target Tables

The drive should be modeled with separate tables for container metadata, uploaded assets, rich-doc bodies, and request workflow.

`driveItems`

Owns the main drive tree and listing metadata.

Proposed fields:

driveItems: defineTable({
  organizationId: v.id("organizations"),
  clientAccountId: v.id("clientAccounts"),
  projectId: v.optional(v.id("projects")),
  parentId: v.optional(v.id("driveItems")),
  itemType: v.union(v.literal("folder"), v.literal("file"), v.literal("doc")),
  title: v.string(),
  normalizedTitle: v.string(),
  visibility: v.union(v.literal("shared"), v.literal("internal")),
  sourceSide: v.union(v.literal("agency"), v.literal("client"), v.literal("system")),
  createdByUserId: v.optional(v.id("users")),
  createdByPortalUserId: v.optional(v.id("portalUsers")),
  updatedByUserId: v.optional(v.id("users")),
  updatedByPortalUserId: v.optional(v.id("portalUsers")),
  createdAt: v.number(),
  updatedAt: v.number(),
  archivedAt: v.optional(v.number()),
  legacyDocumentId: v.optional(v.id("documents")),
})
  .index("by_client_parent", ["clientAccountId", "parentId"])
  .index("by_client_visibility_parent", ["clientAccountId", "visibility", "parentId"])
  .index("by_project_parent", ["projectId", "parentId"])
  .index("by_org_updated", ["organizationId", "updatedAt"])
  .index("by_client_updated", ["clientAccountId", "updatedAt"])

Design notes:

parentId enables real nested folders
itemType makes files and rich docs share one listing model
normalizedTitle supports consistent search/filter matching
legacyDocumentId exists only for migration/audit and should be removed after the old table is retired

`fileAssets`

Owns uploaded binary metadata.

Proposed fields:

fileAssets: defineTable({
  driveItemId: v.id("driveItems"),
  storageId: v.id("_storage"),
  mimeType: v.optional(v.string()),
  sizeBytes: v.optional(v.number()),
  uploadedAt: v.number(),
})
  .index("by_drive_item", ["driveItemId"])

Rules:

only driveItems with itemType === "file" may have a fileAssets row
uploaded file metadata stays separate from the tree listing metadata

`richDocuments`

Owns editor-backed document bodies.

Proposed fields:

richDocuments: defineTable({
  driveItemId: v.id("driveItems"),
  editorStateJson: v.string(),
  renderedHtml: v.string(),
  plainText: v.string(),
  excerpt: v.optional(v.string()),
  lastEditedAt: v.number(),
})
  .index("by_drive_item", ["driveItemId"])

Rules:

only driveItems with itemType === "doc" may have a richDocuments row
the rich-text editor remains the canonical serializer for both stored JSON and rendered HTML

`fileRequests`

Owns lightweight request workflow.

Proposed fields:

fileRequests: defineTable({
  organizationId: v.id("organizations"),
  clientAccountId: v.id("clientAccounts"),
  projectId: v.optional(v.id("projects")),
  targetFolderId: v.optional(v.id("driveItems")),
  title: v.string(),
  description: v.optional(v.string()),
  status: v.union(
    v.literal("requested"),
    v.literal("submitted"),
    v.literal("reviewed"),
    v.literal("closed"),
  ),
  requestedByUserId: v.id("users"),
  dueAt: v.optional(v.number()),
  submittedAt: v.optional(v.number()),
  reviewedAt: v.optional(v.number()),
  closedAt: v.optional(v.number()),
  createdAt: v.number(),
  updatedAt: v.number(),
})
  .index("by_client_account", ["clientAccountId"])
  .index("by_client_account_status", ["clientAccountId", "status"])
  .index("by_project", ["projectId"])

Optional follow-up:

add a fileRequestFulfillments join table if one request needs clean many-to-many linkage to many drive items

`driveAccessLinks`

Owns scoped upload-link access for contributors outside the normal portal grant set.

Proposed fields:

driveAccessLinks: defineTable({
  organizationId: v.id("organizations"),
  clientAccountId: v.id("clientAccounts"),
  projectId: v.optional(v.id("projects")),
  requestId: v.optional(v.id("fileRequests")),
  targetFolderId: v.id("driveItems"),
  accessType: v.union(v.literal("upload")),
  verificationMethod: v.union(v.literal("email_otp")),
  tokenHash: v.string(),
  tokenPreview: v.string(),
  title: v.string(),
  instructions: v.optional(v.string()),
  allowMultipleSubmissions: v.boolean(),
  maxFiles: v.optional(v.number()),
  expiresAt: v.number(),
  revokedAt: v.optional(v.number()),
  lastUsedAt: v.optional(v.number()),
  createdByUserId: v.id("users"),
  createdAt: v.number(),
  updatedAt: v.number(),
})
  .index("by_token_hash", ["tokenHash"])
  .index("by_client_account_created_at", ["clientAccountId", "createdAt"])
  .index("by_request", ["requestId"])
  .index("by_target_folder", ["targetFolderId"])

Rules:

a link points to one upload destination, not a whole drive browser
a link always targets one explicit shared folder
a request-origin link may attach to a request so the upload lands in the expected folder and request context
the first version uses email_otp verification only for outside contributors
a request without a destination folder should not be eligible for upload-link creation until the destination is set
authenticated portal clients may create links only for shared folders and request destinations already visible in their current portal scope
portal contributor-link management is first-class portal UI, but the scope remains destination-bound and upload-only

Provenance Extension

The current drive provenance model distinguishes agency, client, and system.

The backend slice should make this concrete:

add external to driveItems.sourceSide
add a dedicated driveExternalUploadSubmissions table that records which link created which drive item and which verified email submitted it

Proposed fields:

driveExternalUploadSubmissions: defineTable({
  organizationId: v.id("organizations"),
  clientAccountId: v.id("clientAccounts"),
  driveAccessLinkId: v.id("driveAccessLinks"),
  driveItemId: v.id("driveItems"),
  projectId: v.optional(v.id("projects")),
  requestId: v.optional(v.id("fileRequests")),
  targetFolderId: v.id("driveItems"),
  verificationMethod: v.union(v.literal("email_otp")),
  submittedByEmail: v.string(),
  submittedByName: v.optional(v.string()),
  submittedAt: v.number(),
  createdAt: v.number(),
})

Non-negotiable rule:

the agency must be able to tell whether a file came from a portal user or an outside upload link and which verified email submitted it

Table Mapping From `documents`

The current documents row should migrate to two target records:

one driveItems row with itemType: "file"
one fileAssets row linked to that drive item

Field mapping:

Current `documents` field	Target location	Mapping
`organizationId`	`driveItems.organizationId`	direct
`clientAccountId`	`driveItems.clientAccountId`	direct
`projectId`	`driveItems.projectId`	direct
`name`	`driveItems.title`	direct
`name`	`driveItems.normalizedTitle`	normalized lowercase/trimmed
`sourceSide`	`driveItems.sourceSide`	direct
`uploadedByUserId`	`driveItems.createdByUserId`	direct
`uploadedByPortalUserId`	`driveItems.createdByPortalUserId`	direct
`clientVisible` / `visibilityScope`	`driveItems.visibility`	`shared` for client-visible/shared workspace, otherwise `internal`
`uploadedAt`	`driveItems.createdAt`	direct
`uploadedAt`	`driveItems.updatedAt`	direct
`_id`	`driveItems.legacyDocumentId`	direct during migration only
`storageId`	`fileAssets.storageId`	direct
`mimeType`	`fileAssets.mimeType`	direct
`sizeBytes`	`fileAssets.sizeBytes`	direct
`uploadedAt`	`fileAssets.uploadedAt`	direct

Current documents.kind does not map cleanly to the new core item type because it mixes content classification with storage shape.

Migration rule:

all legacy rows migrate to driveItems.itemType = "file"
the old kind field should become optional legacy metadata only if product needs it later
the new drive model should treat classification and item type as separate concerns

Recommended Runtime Surfaces

Registered Convex surface direction:

keep the current same-runtime family in one file during transition
move toward org/drive.ts as the canonical registered org surface after migration

Target org responsibilities:

list drive children by folder
list recent drive items by visible client scope
create folder
create rich doc
generate upload URL
register uploaded file as a drive item
move item
rename item
archive item
manage requests
create, list, revoke, and inspect scoped upload links

Target portal responsibilities:

list shared drive children
upload file to shared drive
create shared rich doc
read shared rich docs
list and fulfill requests

Target external-upload responsibilities:

resolve a scoped upload link
verify the submitter by email OTP
upload directly into the target folder or request destination without exposing the wider drive

Migration Strategy

Use an explicit widen-migrate-narrow rollout.

Do not keep the repo on a permanent dual model.

Deploy 1. Widen

Add new tables and write-path support.

Required steps:

add driveItems, fileAssets, richDocuments, and fileRequests
keep the existing documents table temporarily
add new helper functions for drive writes
switch every new org and portal file upload to write into the new drive tables
if any still-live UI depends on documents, dual-write into documents only for the migration window

Important guardrail:

dual-write exists only to keep the current shipped flat surfaces alive during the migration window
remove it as soon as reads switch to drive tables

Migration. Backfill

Backfill every current documents row into the new model.

Recommended migration function behavior:

iterate documents in batches
for each row, create one driveItems record with itemType: "file"
create the matching fileAssets row
store legacyDocumentId on the drive item during migration
skip rows already migrated by checking legacyDocumentId

Use the migrations component for resumable batching if production data volume justifies it.

Deploy 2. Cut Over Reads

After backfill completes:

move org drive finder reads to driveItems + fileAssets
move customer drive tab reads to driveItems + fileAssets + richDocuments
move portal shared-drive reads to the new tables
stop reading documents in app and portal surfaces
remove temporary dual-write

Deploy 3. Narrow

Once new reads and writes are stable:

delete the old documents table from the schema
remove org/documents.ts legacy-only behaviors or rename the surface cleanly to org/drive.ts
remove legacyDocumentId from driveItems if no longer needed
update tests, seed data, and docs to the final drive vocabulary

UI Rollout Contract

The UI should roll forward in this order:

rename the agency-facing surface to Drive
ship the client Drive tab on top of current data if needed
ship the org global drive finder on top of current data if needed
switch those surfaces to the new tables once the migration lands
add nested folders and rich-doc creation flows on the new backend

This lets the visible product language and IA move into the correct shape before the full backend migration finishes.

After the drive baseline is stable, add the scoped upload-link layer on top of the shared-drive and request model instead of creating a second parallel upload system.

Validation

Minimum validation before the old documents table is removed:

org drive finder returns the same legacy uploaded files after migration
portal shared drive still shows every client-visible legacy file
legacy uploads and new uploads do not create duplicate visible rows after cutover
customer drive tab can browse account-wide and project-linked items
rich-doc create and read paths work for both org and portal users
migration verification shows zero unmigrated documents rows remaining

Additional validation before scoped upload links ship:

an upload link can only write into its configured destination
guest upload flow never exposes sibling files, internal items, or unrelated folders
the created drive item preserves external-contributor provenance distinctly from normal portal uploads

Purpose​

Current State​

Target Tables​

driveItems​

fileAssets​

richDocuments​

fileRequests​

driveAccessLinks​

Provenance Extension​

Table Mapping From documents​

Recommended Runtime Surfaces​

Migration Strategy​

Deploy 1. Widen​

Migration. Backfill​

Deploy 2. Cut Over Reads​

Deploy 3. Narrow​

UI Rollout Contract​

Validation​

Purpose

Current State

Target Tables

`driveItems`

`fileAssets`

`richDocuments`

`fileRequests`

`driveAccessLinks`

Provenance Extension

Table Mapping From `documents`

Recommended Runtime Surfaces

Migration Strategy

Deploy 1. Widen

Migration. Backfill

Deploy 2. Cut Over Reads

Deploy 3. Narrow

UI Rollout Contract

Validation