Skip to main content

First-Pass Data Shape

Purpose​

This document defines the first implementation-grade data shape for the initial agency product slice.

It is not a final Convex schema, but it is specific enough that backend implementation should not need to invent core fields from scratch.

First-Wave Goal​

Support the first believable workflow:

  • create a client account
  • assign agency staff access to that client
  • invite portal users
  • create and view projects
  • show account-level billing and document summaries
  • enforce org-side RBAC cleanly

Data Shape Rules​

  • prefer explicit fields over generic JSON blobs
  • keep summaries separate from source records when the source record would become too large or too noisy
  • use account-level anchors first, then optional project links
  • keep RBAC state separate from business entities

1. clientAccounts​

Purpose:

  • canonical client relationship record for the agency

Recommended first-pass fields:

  • organizationId
  • name
  • slug or stable internal key if needed later
  • status recommended values: active, paused, inactive, archived
  • serviceModel recommended values: project, retainer, mixed
  • primaryContactName
  • primaryContactEmail
  • primaryContactPhone optional
  • timezone optional
  • website optional
  • notesSummary optional short internal summary
  • healthState optional recommended values: healthy, watch, at_risk
  • startDate optional
  • ownerUserId optional main internal relationship owner
  • portalEnabled
  • billingProfileId optional
  • documentsSummaryId optional if summary docs become separate later
  • latestProjectUpdateAt optional
  • latestDocumentActivityAt optional
  • latestBillingActivityAt optional
  • archivedAt optional

Recommended indexes to plan for:

  • by organization
  • by organization and status
  • by organization and owner

2. clientContacts​

Purpose:

  • store contact people tied to a client account

Recommended first-pass fields:

  • organizationId
  • clientAccountId
  • name
  • email
  • phone optional
  • title optional
  • kind recommended values: primary, billing, collaborator, viewer
  • isPrimary
  • portalUserId optional transitional link only during the single-company-to-grants migration
  • billingContact
  • active

Recommended indexes to plan for:

  • by client account
  • by client account and active
  • by email if needed for invite lookup within tenant boundaries

Portal-access rule:

  • steady-state portal access should resolve through a separate portalAccessGrants table keyed by clientContactId, not from clientContacts.portalUserId

2A. portalUsers​

Purpose:

  • store one org-scoped portal identity for one external person inside one agency organization

Recommended steady-state fields:

  • organizationId
  • userId
  • email
  • name
  • createdAt
  • updatedAt

Steady-state rule:

  • do not use portalUsers as the client-company membership record

2B. portalAccessGrants​

Purpose:

  • store company-specific portal access grants linking one portalUser to one clientAccount and one clientContact

Recommended first-pass fields:

  • organizationId
  • portalUserId
  • userId
  • clientAccountId
  • clientContactId
  • role
  • status
  • createdAt
  • updatedAt
  • acceptedAt
  • grantedByUserId
  • grantedByEmail
  • sourceInviteId optional

Recommended indexes to plan for:

  • by portal user
  • by user
  • by client account
  • by client contact
  • by organization and portal user
  • by organization and client account

3. projects​

Purpose:

  • client-visible delivery objects under a client account

Recommended first-pass fields:

  • organizationId
  • clientAccountId
  • name
  • status recommended values: planned, active, blocked, complete, archived
  • summary
  • ownerUserId optional
  • startDate optional
  • targetDate optional
  • completedAt optional
  • visibility recommended value for first wave: client-visible by default unless archived or explicitly internal workflow only
  • latestClientVisibleUpdateAt optional

Recommended indexes to plan for:

  • by client account
  • by client account and status
  • by organization and owner

4. projectUpdates​

Purpose:

  • draft and published project updates

Recommended first-pass fields:

  • organizationId
  • clientAccountId
  • projectId
  • title optional
  • body
  • status recommended values: draft, published
  • createdBy
  • updatedBy
  • publishedBy optional
  • publishedAt optional

Recommended indexes to plan for:

  • by project
  • by project and status
  • by client account and publishedAt

5. timeEntries​

Purpose:

  • atomic work logs for staff activity

Recommended first-pass fields:

  • organizationId
  • userId
  • clientAccountId
  • projectId optional
  • date
  • minutes
  • billable
  • notes optional
  • approvalState recommended values: draft, submitted, approved

Recommended indexes to plan for:

  • by user and date
  • by client account and date
  • by project and date

6. clientBillingProfiles​

Purpose:

  • app-owned mapping between client account and Stripe customer context

Recommended first-pass fields:

  • organizationId
  • clientAccountId
  • stripeCustomerId
  • billingEmail optional
  • billingContactName optional
  • defaultCurrency optional
  • billingState recommended values: none, active, past_due, attention
  • latestInvoiceAt optional
  • latestSubscriptionAt optional

Recommended indexes to plan for:

  • by client account
  • by stripe customer id

7. invoiceRecords​

Purpose:

  • mirror Stripe invoice state needed by the app

Recommended first-pass fields:

  • organizationId
  • clientAccountId
  • billingProfileId
  • stripeInvoiceId
  • status
  • currency
  • amountDue
  • amountPaid
  • dueDate optional
  • hostedInvoiceUrl optional
  • pdfUrl optional
  • serviceLabel optional
  • projectId optional
  • sentAt optional
  • paidAt optional
  • voidedAt optional

Recommended indexes to plan for:

  • by client account
  • by client account and status
  • by stripe invoice id

8. subscriptionRecords​

Purpose:

  • mirror Stripe subscription state needed by the app

Recommended first-pass fields:

  • organizationId
  • clientAccountId
  • billingProfileId
  • stripeSubscriptionId
  • status
  • serviceLabel
  • currentPeriodStart optional
  • currentPeriodEnd optional
  • cancelAtPeriodEnd
  • projectId optional

Recommended indexes to plan for:

  • by client account
  • by client account and status
  • by stripe subscription id

9. documents​

Purpose:

  • storage-backed files and shared artifacts

Recommended first-pass fields:

  • organizationId
  • clientAccountId
  • projectId optional
  • storageId
  • name
  • kind recommended values: file, contract, deliverable, billing_doc, upload_request_response
  • sourceSide recommended values: agency, client
  • uploadedByUserId optional
  • uploadedByPortalUserId optional
  • clientVisible
  • visibilityScope recommended first-wave values: shared_client_workspace, internal_agency
  • mimeType optional
  • sizeBytes optional
  • uploadedAt

Recommended indexes to plan for:

  • by client account
  • by project
  • by client account and clientVisible

10. clientAccessAssignments​

Purpose:

  • define selected-client access for non-owner staff

Recommended first-pass fields:

  • organizationId
  • memberUserId
  • clientAccountId
  • active
  • assignedBy
  • assignedAt

Recommended indexes to plan for:

  • by member user
  • by client account
  • by organization and member user

11. orgRolePolicies​

Purpose:

  • define the organization's default capability sets for non-owner roles

Recommended first-pass fields:

  • organizationId
  • adminCapabilities
  • accountManagerCapabilities
  • staffCapabilities
  • updatedBy
  • updatedAt
  • version

Recommended rule:

  • keep one policy record per organization
  • owner is not modeled as an editable capability set here

12. orgPermissionOverrides​

Purpose:

  • sparse per-user exceptions layered on top of the role policy

Recommended first-pass fields:

  • organizationId
  • memberUserId
  • allowCapabilities
  • denyCapabilities
  • note optional
  • updatedBy
  • updatedAt

Recommended rule:

  • only create this row when the user differs from the role template

13. First-Wave Summary Shapes​

These do not all need dedicated tables immediately, but the UI needs these data shapes.

Client account summary shape​

  • client account core fields
  • primary contact
  • active project count
  • latest project update timestamp
  • billing summary snapshot if allowed
  • documents summary snapshot

Billing summary shape​

  • latest invoice status
  • outstanding invoice count
  • active subscription state if any
  • latest billing activity timestamp

Documents summary shape​

  • recent documents count
  • last upload timestamp
  • pending request count if requests exist

Portal dashboard summary shape​

  • active projects summary
  • recent published updates
  • billing summary
  • recent shared files

First-Wave Data Boundaries​

  • do not store raw permission state on client accounts, projects, or documents
  • do not store full Stripe objects in app tables
  • do not store giant dashboard blobs when summary shapes can be assembled from smaller records
  • do not collapse client contacts and portal users into one table without preserving the portal auth boundary

Implementation Bias​

Build the first schema around these tables and fields unless a concrete implementation constraint proves a smaller shape is better.

The goal is to start serious, not to start abstract.