Terminal
Terminal/Skill
Guides decisions on copying data across tables or documents, covering who keeps each copy in sync and how drift gets detected and repaired.
By Trigger.dev
Works with
npx skills add triggerdotdev/staff-engineering-skills --skill staff-engineering-skills-denormalizationEvery copy of data is a consistency obligation. Before duplicating a field to avoid a join, ask: who updates this copy, what happens when the update fails, and how do you detect drift?
The first question when copying data: is this a snapshot or a reference?
If you're copying a reference, you've taken on a consistency obligation. If you can't answer all four questions in the checklist below, don't denormalize.
For every denormalized field, document:
If you can't fill this out, use a join instead.
Stop and assess if you're about to write any of these:
Copying a field from one table into another at write time -- order.customerName = customer.name. What happens when the customer changes their name?
Embedding related objects in a document -- { user: { org: { plan: { name: "Pro" } } } }. Each nesting level is a copy that needs an update path.
Adding a column to avoid a join -- storing planName on the user table so you don't have to join through organization. Who updates it when the org changes plans?
Building a cache blob from multiple tables -- buildFullProfile(userId) that joins user + org + plan + settings into one cached object. What invalidates every field in that blob?
A background job that "syncs" data between tables -- if this exists, you already have the trap. Is it idempotent? Does it handle partial failures?
ON UPDATE CASCADE or database triggers -- these hide write amplification inside the database where application code can't see it.
// Prefer this. Single source of truth, always consistent.
async function getUserProfile(userId: string) {
return db.user.findUnique({
where: { id: userId },
include: {
organization: {
include: { plan: true },
},
},
});
}
Joins are not inherently slow. A join on indexed foreign keys is fast. Measure before assuming you need to denormalize.
Denormalize only when ALL of these are true:
// The database owns the denormalization logic. Refresh is atomic.
await db.$executeRaw`
CREATE MATERIALIZED VIEW user_profiles AS
SELECT u.id, u.name, o.name as org_name, p.name as plan_name
FROM users u
JOIN organizations o ON u.org_id = o.id
JOIN plans p ON o.plan_id = p.id
`;
// Refresh on schedule or after writes
await db.$executeRaw`REFRESH MATERIALIZED VIEW CONCURRENTLY user_profiles`;
Stale between refreshes. But the logic is declarative, refresh is atomic, and you can't "forget" to update a copy.
// Update copies when the source changes
async function handleOrgPlanChanged(event: OrgPlanChangedEvent) {
const plan = await db.plan.findUnique({ where: { id: event.newPlanId } });
// Batch update to avoid cardinality trap
let cursor: string | undefined;
do {
const users = await db.user.findMany({
where: { orgId: event.orgId }, take: 100,
cursor: cursor ? { id: cursor } : undefined,
orderBy: { id: "asc" },
});
for (const user of users) {
await db.user.update({
where: { id: user.id },
data: { cachedPlanName: plan.name },
});
}
cursor = users.length === 100 ? users[users.length - 1].id : undefined;
} while (cursor);
}
// REPAIR: nightly job that detects and fixes drift
async function repairDenormalizedPlanNames() {
const drifted = await db.$queryRaw`
SELECT u.id, u.cached_plan_name, p.name as actual
FROM users u
JOIN organizations o ON u.org_id = o.id
JOIN plans p ON o.plan_id = p.id
WHERE u.cached_plan_name != p.name
`;
for (const row of drifted) {
await db.user.update({
where: { id: row.id },
data: { cachedPlanName: row.actual },
});
logger.warn("Repaired drifted plan name", { userId: row.id });
}
}
More infrastructure. But failures are recoverable, drift is detectable, and the system self-heals.
// Dangerous: copies org and plan data into every user record.
// Who updates orgName and planName when they change?
return db.user.create({
data: {
name: data.name, email: data.email, orgId: data.orgId,
orgName: org.name, // copy -- consistency obligation
planName: plan.name, // copy -- consistency obligation
planFeatures: plan.features, // copy -- consistency obligation
},
});
// Dangerous: updates N tables with no failure handling.
// What if updateMany times out? What if it partially succeeds?
async function updateOrgName(orgId: string, newName: string) {
await db.organization.update({ where: { id: orgId }, data: { name: newName } });
await db.user.updateMany({ where: { orgId }, data: { orgName: newName } });
await db.invoice.updateMany({ where: { orgId }, data: { orgName: newName } });
await db.auditLog.updateMany({ where: { orgId }, data: { orgName: newName } });
}