Data Model

The Matters.AI Partners Portal persists all of its relational state in a single Neon Postgres 18 database. There is no Mongo or other store on this side — everything below lives in one Postgres instance and is the single source of truth for partner identity, deal registration, MDF, enablement assets, audit, and a thin cache of Salesforce account data.

Driver, connection, and casing#

The schema is defined once in apps/partners/lib/db/schema.ts with Drizzle ORM, and the connection is built in apps/partners/lib/db/client.ts.

import { Pool, neonConfig } from "@neondatabase/serverless";
import { drizzle } from "drizzle-orm/neon-serverless";
import ws from "ws";

import * as schema from "./schema";

export type DB = ReturnType<typeof drizzle<typeof schema>>;
let cached: DB | null = null;

export function db(): DB {
  if (cached) return cached;
  const url = process.env.PARTNERS_NEON_DATABASE_URL;
  if (!url) throw new Error("PARTNERS_NEON_DATABASE_URL is not set");
  if (typeof globalThis.WebSocket === "undefined") {
    neonConfig.webSocketConstructor = ws;
  }
  const pool = new Pool({ connectionString: url });
  cached = drizzle(pool, { schema, casing: "snake_case" });
  return cached;
}

Key facts that govern the whole schema:

Driver: drizzle-orm/neon-serverless over @neondatabase/serverless's Pool. In non-browser runtimes the ws polyfill is wired into neonConfig.webSocketConstructor so the WebSocket connection works in Node/edge serverless.
Connection string: read from PARTNERS_NEON_DATABASE_URL. The client is lazily constructed and module-cached — the first db() call builds the pool, every later call returns the same instance. A missing env var throws immediately rather than failing mid-query.
Casing: casing: "snake_case". This is why the Drizzle definitions use camelCase field keys (clerkUserId) but the actual Postgres columns are snake_case (clerk_user_id). The explicit text("clerk_user_id") column name in the schema and the inferred snake-cased key agree, so reads/writes round-trip cleanly.
Primary keys: two id strategies coexist. Most tables use uuid with either .defaultRandom() or .default(sql\gen_random_uuid()`)(both resolve to Postgresgen_random_uuid()); mdf_requestsis the lone exception, using a classicserial` integer PK.

Applying schema changes#

This database is manually managed. There is no committed migration chain in apps/partners — schema.ts is the source of truth and is reconciled against the live DB. Apply changes one of two ways:

pnpm db:push — Drizzle-Kit diffs schema.ts against Neon and pushes the delta. This now works on Postgres 18 after upgrading to drizzle-orm 0.45 / drizzle-kit 0.31.10; it needs an interactive TTY and emits a handful of benign array-default no-ops.
Direct DDL against Neon for surgical changes.

Because there is no boot-time migration, un-applied schema changes surface as 500s behind the portal's generic error boundary. See Operations for the full apply/rollback runbook and the cautions around it.

Identity & RBAC#

These tables back who a person is, which org/account they belong to, and what they are allowed to do. The account type columns here are the heart of portal RBAC — full semantics in RBAC.

partner_sessions#

The per-user, per-org identity row. A Clerk user who belongs to multiple orgs gets one row per org, so one org's tier / Salesforce ids / account type never bleed into another.

export const partnerSessions = pgTable(
  "partner_sessions",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    clerkUserId: text("clerk_user_id").notNull(),
    clerkOrgId: text("clerk_org_id").notNull(),
    email: text("email").notNull(),
    partnerStatus: text("partner_status").notNull().default("not_found"),
    sfContactId: text("sf_contact_id"),
    sfAccountId: text("sf_account_id"),
    partnerRole: text("partner_role").notNull().default("partner_user"),
    partnerType: text("partner_type"),
    partnerTier: text("partner_tier"),
    sfStatusCachedAt: timestamp("sf_status_cached_at"),
    accountType: text("account_type").notNull().default("pending"),
    accountTypeSource: text("account_type_source").notNull().default("auto"),
    accountTypeUpdatedBy: text("account_type_updated_by"),
    accountTypeSyncedAt: timestamp("account_type_synced_at"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    uniqueIndex("partner_sessions_user_org_unique").on(t.clerkUserId, t.clerkOrgId),
    index("ps_clerk_user_id_idx").on(t.clerkUserId),
    index("ps_clerk_org_id_idx").on(t.clerkOrgId),
    index("ps_email_idx").on(t.email),
  ]
);

Column	Type	Null	Default	Notes
`id`	`uuid` PK	no	`gen_random_uuid()`
`clerk_user_id`	`text`	no	—	Bridge from Clerk → portal.
`clerk_org_id`	`text`	no	—	The Clerk organization this row scopes to.
`email`	`text`	no	—	User email at sync time.
`partner_status`	`text`	no	`'not_found'`	Salesforce-derived partner status; `not_found` means no SF match.
`sf_contact_id`	`text`	yes	—	Cached Salesforce Contact id.
`sf_account_id`	`text`	yes	—	Cached Salesforce Account id.
`partner_role`	`text`	no	`'partner_user'`	In-org role.
`partner_type`	`text`	yes	—	Mirror of the partner account's type.
`partner_tier`	`text`	yes	—	Mirror of tier slug (`registered`/`silver`/`gold`), synced from SF `Partner_Tier__c`.
`sf_status_cached_at`	`timestamp`	yes	—	When the SF status fields were last refreshed.
`account_type`	`text`	no	`'pending'`	The RBAC account type (1 of 7). See RBAC.
`account_type_source`	`text`	no	`'auto'`	How the type was set — `auto` (domain/resolution) vs admin override.
`account_type_updated_by`	`text`	yes	—	Actor who last changed the type (for an admin override).
`account_type_synced_at`	`timestamp`	yes	—	Last time the type was mirrored into Clerk `privateMetadata`.
`created_at`	`timestamp`	no	`now()`
`updated_at`	`timestamp`	no	`now()`

Indexes / constraints:

partner_sessions_user_org_unique — composite UNIQUE on (clerk_user_id, clerk_org_id). This is the load-bearing constraint: it guarantees exactly one row per user per org and is the upsert target for session sync.
ps_clerk_user_id_idx on clerk_user_id, ps_clerk_org_id_idx on clerk_org_id, ps_email_idx on email.

The account_type* columns are the DB-truth half of the RBAC model; the Clerk privateMetadata mirror is the cache, reconciled via account_type_synced_at. The 7 account types and the read/write gate tiers are documented in RBAC.

partner_invitations#

Token-based invitations that pull a new user into a specific Salesforce account / Clerk org with a pre-assigned role.

export const partnerInvitations = pgTable(
  "partner_invitations",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    token: text("token").notNull().unique(),
    email: text("email").notNull(),
    role: text("role").notNull().default("partner_user"),
    sfAccountId: text("sf_account_id").notNull(),
    clerkOrgId: text("clerk_org_id").notNull(),
    invitedByClerkUserId: text("invited_by_clerk_user_id").notNull(),
    status: text("status").notNull().default("pending"),
    expiresAt: timestamp("expires_at").notNull(),
    acceptedAt: timestamp("accepted_at"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
  },
  (t) => [
    index("pi_token_idx").on(t.token),
    index("pi_email_idx").on(t.email),
    index("pi_org_idx").on(t.clerkOrgId),
  ]
);

Column	Type	Null	Default	Notes
`id`	`uuid` PK	no	`gen_random_uuid()`
`token`	`text`	no	—	UNIQUE. The invite token carried in the accept link.
`email`	`text`	no	—	Invitee address.
`role`	`text`	no	`'partner_user'`	Role granted on acceptance.
`sf_account_id`	`text`	no	—	Salesforce account the invitee joins.
`clerk_org_id`	`text`	no	—	Clerk org the invitee joins.
`invited_by_clerk_user_id`	`text`	no	—	Who sent the invite.
`status`	`text`	no	`'pending'`	`pending` → `accepted` (or expired).
`expires_at`	`timestamp`	no	—	Hard expiry.
`accepted_at`	`timestamp`	yes	—	Set when redeemed.
`created_at`	`timestamp`	no	`now()`

Indexes / constraints: token is UNIQUE (column-level). Plus non-unique indexes pi_token_idx (token), pi_email_idx (email), pi_org_idx (clerk_org_id).

On acceptance, the resulting user gets a partner_sessions row scoped to (clerk_user_id, clerk_org_id) with partner_role = role and sf_account_id carried over.

partner_access_requests#

Public "request access" submissions from prospective partners who do not yet have a Clerk account or org. An admin reviews each and, on approval, fires a Clerk invite.

export const partnerAccessRequests = pgTable("partner_access_requests", {
  id: uuid("id")
    .primaryKey()
    .default(sql`gen_random_uuid()`),
  email: text("email").notNull(),
  fullName: text("full_name").notNull(),
  companyName: text("company_name").notNull(),
  website: text("website"),
  message: text("message"),
  status: text("status").notNull().default("pending"),
  reviewedBy: text("reviewed_by"),
  reviewNote: text("review_note"),
  clerkInviteId: text("clerk_invite_id"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

Column	Type	Null	Default	Notes
`id`	`uuid` PK	no	`gen_random_uuid()`
`email`	`text`	no	—	Applicant email.
`full_name`	`text`	no	—
`company_name`	`text`	no	—
`website`	`text`	yes	—
`message`	`text`	yes	—	Free-text pitch.
`status`	`text`	no	`'pending'`	`pending` → `approved` / `rejected`.
`reviewed_by`	`text`	yes	—	Admin who acted.
`review_note`	`text`	yes	—	Internal note.
`clerk_invite_id`	`text`	yes	—	Clerk invitation id minted on approval — the ACID handoff into the Clerk side.
`created_at`	`timestamp`	no	`now()`
`updated_at`	`timestamp`	no	`now()`

No secondary indexes. clerk_invite_id being non-null marks a request that has crossed into the Clerk invite saga.

Deals (deal registration)#

The deal-registration surface. A partner submits an opportunity, it is deduped, optionally pushed to Salesforce, and annotated with notes over its life. Feature docs: Opportunities.

opportunities#

The wide deal-registration row.

export const opportunities = pgTable(
  "opportunities",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    portalLeadId: uuid("portal_lead_id").notNull().unique(),
    submittedByClerkUserId: text("submitted_by_clerk_user_id").notNull(),
    clerkOrgId: text("clerk_org_id").notNull(),
    sfAccountId: text("sf_account_id").notNull(),
    endCustomerName: text("end_customer_name").notNull(),
    endCustomerEmail: text("end_customer_email").notNull(),
    endCustomerPhone: text("end_customer_phone").notNull(),
    companyName: text("company_name").notNull(),
    companyType: text("company_type").notNull(),
    companySize: text("company_size").notNull(),
    primaryUseCases: text("primary_use_cases").array().notNull(),
    expectedArrUsd: integer("expected_arr_usd").notNull(),
    expectedCloseDate: timestamp("expected_close_date").notNull(),
    dealStage: text("deal_stage").notNull(),
    region: text("region").notNull(),
    competingProducts: text("competing_products").array().notNull().default([]),
    notes: text("notes"),
    sfLeadId: text("sf_lead_id"),
    sfOpportunityId: text("sf_opportunity_id"),
    sfStatus: text("sf_status").notNull().default("pending"),
    portalStatus: text("portal_status").notNull().default("registered"),
    dedupStatus: text("dedup_status").notNull().default("approved"),
    lastActivityAt: timestamp("last_activity_at").notNull().defaultNow(),
    inactivityWarningAt: timestamp("inactivity_warning_at"),
    cancelledAt: timestamp("cancelled_at"),
    extensionGrantedAt: timestamp("extension_granted_at"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("opp_portal_lead_id_idx").on(t.portalLeadId),
    index("opp_clerk_org_id_idx").on(t.clerkOrgId),
    index("opp_portal_status_idx").on(t.portalStatus),
    index("opp_last_activity_idx").on(t.lastActivityAt),
  ]
);

Column	Type	Null	Default	Notes
`id`	`uuid` PK	no	`gen_random_uuid()`
`portal_lead_id`	`uuid`	no	—	UNIQUE. Stable external id for the lead inside the portal.
`submitted_by_clerk_user_id`	`text`	no	—	Submitting partner user.
`clerk_org_id`	`text`	no	—	Owning org (scopes all reads).
`sf_account_id`	`text`	no	—	Partner's Salesforce account.
`end_customer_name`	`text`	no	—
`end_customer_email`	`text`	no	—
`end_customer_phone`	`text`	no	—
`company_name`	`text`	no	—	End-customer company.
`company_type`	`text`	no	—
`company_size`	`text`	no	—
`primary_use_cases`	`text[]`	no	—	Array; no default (must be supplied).
`expected_arr_usd`	`integer`	no	—	Whole USD.
`expected_close_date`	`timestamp`	no	—
`deal_stage`	`text`	no	—
`region`	`text`	no	—
`competing_products`	`text[]`	no	`'{}'` (empty)
`notes`	`text`	yes	—	Free-form.
`sf_lead_id`	`text`	yes	—	Set once pushed to SF as a Lead.
`sf_opportunity_id`	`text`	yes	—	Set once converted to an SF Opportunity.
`sf_status`	`text`	no	`'pending'`	Salesforce push state.
`portal_status`	`text`	no	`'registered'`	Portal-side lifecycle.
`dedup_status`	`text`	no	`'approved'`	Dedup verdict (default approved unless flagged).
`last_activity_at`	`timestamp`	no	`now()`	Drives the inactivity sweep.
`inactivity_warning_at`	`timestamp`	yes	—	Set when the warning fires.
`cancelled_at`	`timestamp`	yes	—
`extension_granted_at`	`timestamp`	yes	—	Deal-protection extension.
`created_at`	`timestamp`	no	`now()`
`updated_at`	`timestamp`	no	`now()`

Indexes / constraints: portal_lead_id UNIQUE (column-level) plus opp_portal_lead_id_idx. Non-unique indexes: opp_clerk_org_id_idx (org-scoped listings), opp_portal_status_idx (status filters), opp_last_activity_idx (powers the inactivity / deal-protection cron). The sf_account_id references the partner's account, joining logically to partner_accounts.sf_account_id.

opportunity_notes#

Threaded notes on an opportunity. Each note can mirror to a Salesforce Task.

export const opportunityNotes = pgTable(
  "opportunity_notes",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    opportunityId: uuid("opportunity_id").notNull(),
    authorClerkUserId: text("author_clerk_user_id").notNull(),
    body: text("body").notNull(),
    sfTaskId: text("sf_task_id"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
  },
  (t) => [index("on_opportunity_id_idx").on(t.opportunityId)]
);

Column	Type	Null	Default	Notes
`id`	`uuid` PK	no	`gen_random_uuid()`
`opportunity_id`	`uuid`	no	—	Logical FK → `opportunities.id` (no DB-level FK constraint).
`author_clerk_user_id`	`text`	no	—
`body`	`text`	no	—
`sf_task_id`	`text`	yes	—	SF Task id if the note was mirrored.
`created_at`	`timestamp`	no	`now()`

Indexes: on_opportunity_id_idx on opportunity_id — the only access path (fetch all notes for one deal).

MDF (Market Development Funds)#

A partner requests co-marketing funds against a campaign; an admin reviews, approves an amount, and the partner later claims it. Tier-level quarterly caps live on partner_tiers. Feature docs: MDF.

mdf_requests#

The one table in the whole schema with an integer serial primary key.

export const mdfRequests = pgTable("mdf_requests", {
  id: serial("id").primaryKey(),
  clerkOrgId: text("clerk_org_id").notNull(),
  submittedByClerkUserId: text("submitted_by_clerk_user_id").notNull(),
  sfAccountId: text("sf_account_id").notNull().default(""),
  campaignName: text("campaign_name").notNull(),
  campaignType: text("campaign_type").notNull(),
  campaignStartDate: timestamp("campaign_start_date").notNull(),
  campaignEndDate: timestamp("campaign_end_date").notNull(),
  description: text("description").notNull(),
  expectedLeads: integer("expected_leads"),
  expectedPipeline: integer("expected_pipeline_usd"),
  requestedAmountUsd: integer("requested_amount_usd").notNull(),
  attachmentR2Keys: text("attachment_r2_keys")
    .array()
    .notNull()
    .default(sql`'{}'::text[]`),
  status: text("status").notNull().default("submitted"),
  reviewNotes: text("review_notes"),
  approvedAmountUsd: integer("approved_amount_usd"),
  approvedAt: timestamp("approved_at"),
  claimedAt: timestamp("claimed_at"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

Column	Type	Null	Default	Notes
`id`	`serial` PK	no	auto-increment	Integer PK (not uuid).
`clerk_org_id`	`text`	no	—	Requesting org.
`submitted_by_clerk_user_id`	`text`	no	—
`sf_account_id`	`text`	no	`''`	Empty string default, not null.
`campaign_name`	`text`	no	—
`campaign_type`	`text`	no	—	`event` / `digital` / `content` / `co-marketing` / `other`.
`campaign_start_date`	`timestamp`	no	—
`campaign_end_date`	`timestamp`	no	—
`description`	`text`	no	—
`expected_leads`	`integer`	yes	—
`expected_pipeline_usd`	`integer`	yes	—	Column key is `expected_pipeline_usd` (field `expectedPipeline`).
`requested_amount_usd`	`integer`	no	—	Ask in whole USD.
`attachment_r2_keys`	`text[]`	no	`'{}'` (empty)	R2 object keys for supporting docs.
`status`	`text`	no	`'submitted'`	`submitted` / `under_review` / `approved` / `rejected` / `claimed`.
`review_notes`	`text`	yes	—
`approved_amount_usd`	`integer`	yes	—	May differ from requested.
`approved_at`	`timestamp`	yes	—
`claimed_at`	`timestamp`	yes	—
`created_at`	`timestamp`	no	`now()`
`updated_at`	`timestamp`	no	`now()`

Indexes / constraints: none beyond the serial PK. Listings are filtered by clerk_org_id / status in application code.

Enablement (content & agreements)#

Partner-facing assets — sales/technical/training collateral and signed legal agreements. Files live in Cloudflare R2; these tables hold metadata and the R2 keys. Feature docs: Enablement.

content_documents#

Tier-gated collateral library.

export const contentDocuments = pgTable("content_documents", {
  id: uuid("id")
    .primaryKey()
    .default(sql`gen_random_uuid()`),
  title: text("title").notNull(),
  description: text("description"),
  category: text("category").notNull(),
  r2Key: text("r2_key").notNull(),
  fileType: text("file_type").notNull(),
  fileSizeBytes: integer("file_size_bytes"),
  visibleToTiers: text("visible_to_tiers")
    .array()
    .notNull()
    .default(sql`'{}'::text[]`),
  uploadedByEmail: text("uploaded_by_email").notNull(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

Column	Type	Null	Default	Notes
`id`	`uuid` PK	no	`gen_random_uuid()`
`title`	`text`	no	—
`description`	`text`	yes	—
`category`	`text`	no	—	`sales` / `technical` / `training` / `legal` / `marketing`.
`r2_key`	`text`	no	—	Cloudflare R2 object key.
`file_type`	`text`	no	—	`pdf` / `pptx` / `docx` / `xlsx` / `video`.
`file_size_bytes`	`integer`	yes	—
`visible_to_tiers`	`text[]`	no	`'{}'` (empty)	Empty array = visible to all tiers; otherwise the tier slugs that may see it.
`uploaded_by_email`	`text`	no	—
`created_at`	`timestamp`	no	`now()`
`updated_at`	`timestamp`	no	`now()`

Indexes: none. visible_to_tiers is matched against the viewer's partner_sessions.partner_tier in application code; empty means unrestricted.

partner_agreements#

Per-org signed legal documents (contracts, addenda).

export const partnerAgreements = pgTable("partner_agreements", {
  id: uuid("id")
    .primaryKey()
    .default(sql`gen_random_uuid()`),
  clerkOrgId: text("clerk_org_id").notNull(),
  title: text("title").notNull(),
  agreementType: text("agreement_type").notNull(),
  r2Key: text("r2_key").notNull(),
  fileSizeBytes: integer("file_size_bytes"),
  signedDate: timestamp("signed_date"),
  expiresAt: timestamp("expires_at"),
  uploadedByEmail: text("uploaded_by_email").notNull(),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

Column	Type	Null	Default	Notes
`id`	`uuid` PK	no	`gen_random_uuid()`
`clerk_org_id`	`text`	no	—	Owning org.
`title`	`text`	no	—
`agreement_type`	`text`	no	—
`r2_key`	`text`	no	—	R2 object key for the document.
`file_size_bytes`	`integer`	yes	—
`signed_date`	`timestamp`	yes	—
`expires_at`	`timestamp`	yes	—
`uploaded_by_email`	`text`	no	—
`created_at`	`timestamp`	no	`now()`
`updated_at`	`timestamp`	no	`now()`

Indexes: none. Filtered by clerk_org_id.

partner_profiles#

Public-directory profile for a partner org. Logo and identity fields surfaced in the partner directory once approved.

export const partnerProfiles = pgTable(
  "partner_profiles",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    clerkOrgId: text("clerk_org_id").notNull().unique(),
    sfAccountId: text("sf_account_id").notNull().unique(),
    companyName: text("company_name").notNull(),
    logoR2Key: text("logo_r2_key"),
    tagline: text("tagline"),
    websiteUrl: text("website_url"),
    primaryContactName: text("primary_contact_name"),
    primaryContactEmail: text("primary_contact_email"),
    primaryContactPhone: text("primary_contact_phone"),
    headquartersCity: text("headquarters_city"),
    country: text("country"),
    regionsServed: text("regions_served").array().notNull().default([]),
    specializations: text("specializations").array().notNull().default([]),
    certifiedStaffCount: integer("certified_staff_count"),
    gstTaxId: text("gst_tax_id"),
    directoryApproved: boolean("directory_approved").notNull().default(false),
    directoryApprovedAt: timestamp("directory_approved_at"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [
    index("pp_clerk_org_id_idx").on(t.clerkOrgId),
    index("pp_sf_account_id_idx").on(t.sfAccountId),
  ]
);

Column	Type	Null	Default	Notes
`id`	`uuid` PK	no	`gen_random_uuid()`
`clerk_org_id`	`text`	no	—	UNIQUE — one profile per org.
`sf_account_id`	`text`	no	—	UNIQUE — one profile per SF account.
`company_name`	`text`	no	—
`logo_r2_key`	`text`	yes	—	R2 key for the logo.
`tagline`	`text`	yes	—
`website_url`	`text`	yes	—
`primary_contact_name`	`text`	yes	—
`primary_contact_email`	`text`	yes	—
`primary_contact_phone`	`text`	yes	—
`headquarters_city`	`text`	yes	—
`country`	`text`	yes	—
`regions_served`	`text[]`	no	`'{}'` (empty)
`specializations`	`text[]`	no	`'{}'` (empty)
`certified_staff_count`	`integer`	yes	—
`gst_tax_id`	`text`	yes	—	→ SF `mt_GST_Information__c`. Regulated identifier — consider app-level encryption before storing real data.
`company_email`	`text`	yes	—	→ SF `mt_Email__c`.
`industry`	`text`	yes	—	→ SF `mt_Industry__c` (50-value picklist).
`employee_count`	`text`	yes	—	→ SF `mt_Employee_Count__c` (picklist).
`cloud_spend_monthly`	`text`	yes	—	→ SF `mt_Cloud_Spend_Monthly__c` (picklist).
`cloud_spend_estimate_usd`	`integer`	yes	—	→ SF `mt_Cloud_Spend_Estimate__c`.
`pan`	`text`	yes	—	→ SF `mt_PAN__c`.
`ein`	`text`	yes	—	→ SF `mt_EIN__c`.
`description`	`text`	yes	—	→ SF `Description`.
`billing_street`	`text`	yes	—	→ SF `BillingStreet` (city→`BillingCity`, country→`BillingCountry`).
`billing_state`	`text`	yes	—	→ SF `BillingState`.
`billing_postal_code`	`text`	yes	—	→ SF `BillingPostalCode`.
`directory_approved`	`boolean`	no	`false`	Gate for public-directory visibility.
`directory_approved_at`	`timestamp`	yes	—
`created_at`	`timestamp`	no	`now()`
`updated_at`	`timestamp`	no	`now()`

Indexes / constraints: UNIQUE on both clerk_org_id and sf_account_id (column-level), plus non-unique pp_clerk_org_id_idx and pp_sf_account_id_idx. Both UNIQUE keys join logically to partner_accounts / partner_sessions.

Notifications#

notifications#

In-portal notification feed. Org-wide or per-user.

export const notifications = pgTable(
  "notifications",
  {
    id: uuid("id")
      .primaryKey()
      .default(sql`gen_random_uuid()`),
    clerkOrgId: text("clerk_org_id").notNull(),
    clerkUserId: text("clerk_user_id"),
    type: text("type").notNull(),
    title: text("title").notNull(),
    body: text("body").notNull(),
    href: text("href"),
    readAt: timestamp("read_at"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
  },
  (t) => [
    index("notif_org_idx").on(t.clerkOrgId),
    index("notif_user_idx").on(t.clerkUserId),
    index("notif_read_idx").on(t.readAt),
    index("notif_created_idx").on(t.createdAt),
  ]
);

Column	Type	Null	Default	Notes
`id`	`uuid` PK	no	`gen_random_uuid()`
`clerk_org_id`	`text`	no	—
`clerk_user_id`	`text`	yes	—	NULL = org-wide notification (every member sees it).
`type`	`text`	no	—	`deal_status` / `mdf_status` / `team_member` / `agreement` / `broadcast`.
`title`	`text`	no	—
`body`	`text`	no	—
`href`	`text`	yes	—	Click-through link.
`read_at`	`timestamp`	yes	—	NULL = unread.
`created_at`	`timestamp`	no	`now()`

Indexes: notif_org_idx (org-scoped feed), notif_user_idx (per-user feed), notif_read_idx (unread filter), notif_created_idx (recency order).

Audit#

activity_log#

Append-only audit trail for every state-changing / security-relevant action. Written via recordActivity. Full surface: Audit Log.

export const activityLog = pgTable(
  "activity_log",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    clerkOrgId: text("clerk_org_id"),
    actorClerkUserId: text("actor_clerk_user_id"),
    actorEmail: text("actor_email"),
    action: text("action").notNull(),
    targetType: text("target_type"),
    targetId: text("target_id"),
    summary: text("summary").notNull(),
    metadata: jsonb("metadata")
      .notNull()
      .default(sql`'{}'::jsonb`),
    ipAddress: text("ip_address"),
    userAgent: text("user_agent"),
    createdAt: timestamp("created_at").notNull().defaultNow(),
  },
  (t) => [
    index("al_org_idx").on(t.clerkOrgId),
    index("al_actor_idx").on(t.actorClerkUserId),
    index("al_action_idx").on(t.action),
    index("al_created_idx").on(t.createdAt),
    index("al_target_idx").on(t.targetType, t.targetId),
  ]
);

Column	Type	Null	Default	Notes
`id`	`uuid` PK	no	`gen_random_uuid()`
`clerk_org_id`	`text`	yes	—	NULL = system/global event (not org-scoped).
`actor_clerk_user_id`	`text`	yes	—	NULL = system/automated actor.
`actor_email`	`text`	yes	—	Actor email at action time.
`action`	`text`	no	—	Dotted verb, e.g. `account_type.changed`.
`target_type`	`text`	yes	—	Entity class acted on.
`target_id`	`text`	yes	—	Entity id.
`summary`	`text`	no	—	Human-readable description.
`metadata`	`jsonb`	no	`'{}'`	Action-specific payload (e.g. before/after values).
`ip_address`	`text`	yes	—
`user_agent`	`text`	yes	—
`created_at`	`timestamp`	no	`now()`

Indexes: al_org_idx (org filter), al_actor_idx (per-actor history), al_action_idx (filter by verb), al_created_idx (recency), and the composite al_target_idx on (target_type, target_id) for "everything that happened to this entity".

Salesforce cache & integration bookkeeping#

A thin local mirror of partner accounts (so the portal can list/filter partners without a live SF round-trip), the canonical tier catalog, and the idempotency ledger for inbound Salesforce callouts.

partner_accounts#

Local cache of partner Account records from Salesforce. Read-mostly. Full sync model: Salesforce.

export const partnerAccounts = pgTable("partner_accounts", {
  id: uuid("id")
    .primaryKey()
    .default(sql`gen_random_uuid()`),
  sfAccountId: text("sf_account_id").unique(),
  accountName: text("account_name").notNull(),
  partnerAccountName: text("partner_account_name"),
  websiteDomain: text("website_domain"),
  website: text("website"),
  partnerType: text("partner_type"),
  partnerTier: text("partner_tier"),
  industry: text("industry"),
  billingCountry: text("billing_country"),
  billingCity: text("billing_city"),
  accountOwner: text("account_owner"),
  portalStatus: text("portal_status").notNull().default("active"),
  createdAt: timestamp("created_at").notNull().defaultNow(),
  updatedAt: timestamp("updated_at").notNull().defaultNow(),
});

Column	Type	Null	Default	Notes
`id`	`uuid` PK	no	`gen_random_uuid()`
`sf_account_id`	`text`	yes	—	UNIQUE (but nullable — a portal-only account can exist before SF linkage). Upsert key for the SF sync.
`account_name`	`text`	no	—
`partner_account_name`	`text`	yes	—
`website_domain`	`text`	yes	—	Used for domain-based account-type gating.
`website`	`text`	yes	—
`partner_type`	`text`	yes	—
`partner_tier`	`text`	yes	—	Tier slug; joins to `partner_tiers.slug`.
`industry`	`text`	yes	—
`billing_country`	`text`	yes	—
`billing_city`	`text`	yes	—
`account_owner`	`text`	yes	—
`portal_status`	`text`	no	`'active'`
`created_at`	`timestamp`	no	`now()`
`updated_at`	`timestamp`	no	`now()`

Indexes / constraints: UNIQUE on sf_account_id (column-level) — the SF upsert target. sf_account_id is the join key used across partner_sessions, partner_profiles, opportunities, partner_invitations, and mdf_requests (all logical, no DB-level FKs).

partner_tiers#

Canonical tier catalog (the registered / silver / gold ladder), admin-managed. Drives tier-gated content visibility and MDF caps.

export const partnerTiers = pgTable(
  "partner_tiers",
  {
    id: uuid("id")
      .primaryKey()
      .default(sql`gen_random_uuid()`),
    slug: text("slug").notNull().unique(),
    displayName: text("display_name").notNull(),
    description: text("description"),
    arrRange: text("arr_range"),
    mdfQuarterlyLimitUsd: integer("mdf_quarterly_limit_usd"),
    dealProtection: text("deal_protection"),
    benefits: text("benefits")
      .array()
      .notNull()
      .default(sql`'{}'::text[]`),
    requirements: text("requirements")
      .array()
      .notNull()
      .default(sql`'{}'::text[]`),
    sortOrder: integer("sort_order").notNull().default(0),
    createdAt: timestamp("created_at").notNull().defaultNow(),
    updatedAt: timestamp("updated_at").notNull().defaultNow(),
  },
  (t) => [index("pt_slug_idx").on(t.slug), index("pt_sort_idx").on(t.sortOrder)]
);

Column	Type	Null	Default	Notes
`id`	`uuid` PK	no	`gen_random_uuid()`
`slug`	`text`	no	—	UNIQUE — `registered` / `silver` / `gold`. Matches `partner_sessions.partner_tier` (synced from SF `Partner_Tier__c`).
`display_name`	`text`	no	—
`description`	`text`	yes	—
`arr_range`	`text`	yes	—	Free-text ARR band entered by an admin — not computed.
`mdf_quarterly_limit_usd`	`integer`	yes	—	The MDF cap this tier may request per quarter.
`deal_protection`	`text`	yes	—
`benefits`	`text[]`	no	`'{}'` (empty)
`requirements`	`text[]`	no	`'{}'` (empty)
`sort_order`	`integer`	no	`0`	Display order.
`created_at`	`timestamp`	no	`now()`
`updated_at`	`timestamp`	no	`now()`

Indexes / constraints: UNIQUE on slug (column-level) plus pt_slug_idx; pt_sort_idx on sort_order for ordered listing.

callout_events#

Idempotency ledger for inbound Salesforce callouts (Apex → portal webhooks). Guarantees exactly-once processing.

export const calloutEvents = pgTable(
  "callout_events",
  {
    id: uuid("id").defaultRandom().primaryKey(),
    idempotencyKey: text("idempotency_key").notNull().unique(),
    objectType: text("object_type").notNull(),
    sfRecordId: text("sf_record_id").notNull(),
    payload: text("payload").notNull(),
    processedAt: timestamp("processed_at"),
    error: text("error"),
    receivedAt: timestamp("received_at").notNull().defaultNow(),
  },
  (t) => [
    index("ce_idempotency_key_idx").on(t.idempotencyKey),
    index("ce_sf_record_id_idx").on(t.sfRecordId),
  ]
);

Column	Type	Null	Default	Notes
`id`	`uuid` PK	no	`gen_random_uuid()`
`idempotency_key`	`text`	no	—	UNIQUE — dedupe key; a replayed callout collides and is skipped.
`object_type`	`text`	no	—	SF object class (e.g. `Account`, `Opportunity`).
`sf_record_id`	`text`	no	—	The SF record this callout concerns.
`payload`	`text`	no	—	Raw callout body (stored as text, not jsonb).
`processed_at`	`timestamp`	yes	—	NULL until handled.
`error`	`text`	yes	—	Last processing error, if any.
`received_at`	`timestamp`	no	`now()`

Indexes / constraints: UNIQUE on idempotency_key (column-level) plus ce_idempotency_key_idx; ce_sf_record_id_idx on sf_record_id to find all callouts touching one SF record.

Relationships at a glance#

There are no database-level foreign keys in this schema — all relationships are logical and enforced in application code. The recurring join keys are:

clerk_org_id — the org-scoping spine. Present on partner_sessions, partner_invitations, opportunities, partner_profiles, partner_agreements, mdf_requests, notifications, and (nullable) activity_log.
clerk_user_id / actor_clerk_user_id / submitted_by… / author… — the acting Clerk user across sessions, opportunities, notes, MDF, notifications, and the audit log.
sf_account_id — links the local partner identity (partner_sessions, partner_profiles, opportunities, partner_invitations, mdf_requests) to the Salesforce-cached partner_accounts.sf_account_id (UNIQUE).
partner_tier slug — partner_sessions.partner_tier and partner_accounts.partner_tier reference the catalog partner_tiers.slug; also matched against content_documents.visible_to_tiers for gating.
opportunity_id — opportunity_notes.opportunity_id → opportunities.id (logical).

For how these tables are queried under RBAC, see RBAC; for the inbound/outbound Salesforce flow that feeds partner_accounts and callout_events, see Salesforce; and for applying schema changes safely, see Operations.

Loading…