Overview
The sfdc_accounts table stores Salesforce account data. It tracks customer account information including ARR, enterprise status, and regional data.
Fields
Primary key. Salesforce account ID.
Account type classification in Salesforce.
Geographic region or territory for the account.
Annual Recurring Revenue for the account. Stored as a number with 2 decimal places.
Whether this is an enterprise-level account. Default: false.
URL to the account’s logo image.
Link to the Salesforce account record.
Timestamp with timezone when the record was created. Default: now().
Timestamp with timezone when the record was last updated. Default: now().
Relationships
Has many
- Opportunities: Multiple opportunities can be associated with an account via
sfdc_opportunities.accountId
- Feedback: Multiple feedback items can reference an account via
feedback.accountId
Indexes
B-tree index on name for efficient account name searches.
B-tree index on arr for sorting and filtering accounts by revenue.
sfdc_accounts_is_enterprise_idx
B-tree index on isEnterprise for filtering enterprise accounts.
Example queries
Query all enterprise accounts
import { db } from './db';
import { sfdcAccounts } from './schema';
import { eq } from 'drizzle-orm';
const enterpriseAccounts = await db.query.sfdcAccounts.findMany({
where: eq(sfdcAccounts.isEnterprise, true),
orderBy: (sfdcAccounts, { desc }) => [desc(sfdcAccounts.arr)],
});
Get account with opportunities and feedback
import { db } from './db';
import { sfdcAccounts } from './schema';
import { eq } from 'drizzle-orm';
const account = await db.query.sfdcAccounts.findFirst({
where: eq(sfdcAccounts.id, 'sfdc-account-id'),
with: {
opportunities: true,
feedback: {
with: {
request: true,
},
},
},
});
Find high-ARR accounts
import { db } from './db';
import { sfdcAccounts } from './schema';
import { gte } from 'drizzle-orm';
const highValueAccounts = await db.query.sfdcAccounts.findMany({
where: gte(sfdcAccounts.arr, 100000),
orderBy: (sfdcAccounts, { desc }) => [desc(sfdcAccounts.arr)],
});
Get accounts by region
import { db } from './db';
import { sfdcAccounts } from './schema';
import { eq } from 'drizzle-orm';
const regionalAccounts = await db.query.sfdcAccounts.findMany({
where: eq(sfdcAccounts.regionName, 'North America'),
});
Search accounts by name
import { db } from './db';
import { sfdcAccounts } from './schema';
import { ilike } from 'drizzle-orm';
const searchResults = await db.query.sfdcAccounts.findMany({
where: ilike(sfdcAccounts.name, '%acme%'),
});
Create or update account (upsert)
import { db } from './db';
import { sfdcAccounts } from './schema';
const account = await db
.insert(sfdcAccounts)
.values({
id: 'sfdc-account-id',
name: 'Acme Corporation',
type: 'Customer',
regionName: 'North America',
arr: 250000,
isEnterprise: true,
website: 'https://acme.com',
link: 'https://salesforce.com/account/...',
})
.onConflictDoUpdate({
target: sfdcAccounts.id,
set: {
name: 'Acme Corporation',
arr: 250000,
updatedAt: new Date().toISOString(),
},
})
.returning();
Get account statistics
import { db } from './db';
import { sfdcAccounts } from './schema';
import { sql } from 'drizzle-orm';
const stats = await db
.select({
totalAccounts: sql<number>`count(*)`,
totalArr: sql<number>`sum(${sfdcAccounts.arr})`,
avgArr: sql<number>`avg(${sfdcAccounts.arr})`,
enterpriseCount: sql<number>`count(*) filter (where ${sfdcAccounts.isEnterprise} = true)`,
})
.from(sfdcAccounts);
