When I started rewriting the clients section of Melororium (a team CRM for agencies), I expected a few days of work. It took three weeks. Not because the features were complex individually, but because the design decisions compounded — each one constrained the next.
This post documents eight decisions from that sprint: the problem each one solved, the alternatives I considered, and what the implementation looks like in practice. It is written for developers building similar systems.
The stack: Next.js App Router, Zustand for client state, Server Actions for mutations, PostgreSQL for persistence.
1. Client health score: client-side vs server-side
The requirement: A 0–100 score per client that reflects current relationship health, recalculating when relevant data changes.
I initially planned a server-side job: run every night, write scores to a health_score column, surface them in the UI.
I killed this after thinking through the latency. If a PM logs a contact call at 2pm, the score updates tomorrow morning. That breaks the core use case.
The solution: compute client-side from the Zustand store on every render.
The algorithm is additive:
base = 50
// Work signals
if (client.activeTasks > 0) score += 25
else score -= 15
if (client.activeProjects > 0) score += 10
// Contact recency
const daysSinceContact = diffInDays(today, client.lastContactDate)
if (!client.lastContactDate) score -= 10
else if (daysSinceContact <= 14) score += 15
else if (daysSinceContact <= 30) score += 5
else if (daysSinceContact <= 60) score -= 10
else score -= 25
// Follow-up
if (client.followUpDate) score += 5
return clamp(score, 0, 100)
This runs inside a useMemo with [clients, tasks, projects] as dependencies. Zero latency.
The tradeoff I accepted: no cross-session consistency. Two users compute the same score from the same data — fine. When one changes data, their view updates and the other's will on next render — also fine.
What I would add next: Invoice payment status as a negative signal.
2. Health buckets: three states not two
Most health score implementations produce a number and render it as a gradient. I wanted actionable buckets.
function getHealthBucket(client, score) {
const hasNoActiveTasks = client.activeTasks === 0
const neverContacted = !client.lastContactDate
const staleContact = diffInDays(today, client.lastContactDate) > 60
if (hasNoActiveTasks && (neverContacted || staleContact)) {
return 'dormant'
}
if (score < 50) {
return 'at-risk'
}
return 'healthy'
}
Dormant is not just "very low score." It specifically identifies: no active work AND (never contacted OR contact older than 60 days). These clients may have churned silently — they are not unhappy, they are just gone.
The reasons array builds in parallel with score calculation:
const reasons = []
if (!client.lastContactDate) reasons.push('Never contacted')
else if (daysSinceContact > 60) reasons.push(${daysSinceContact}d since contact)
if (client.activeTasks === 0) reasons.push('No active work')
if (overdueFollowUp) reasons.push('Follow-up overdue')
Renders as: "No active work · 47d since contact." The PM reading this row knows what to do.
3. Briefing status: three-state tracking
Requirement: Track whether a recipient received, opened, and responded to a briefing form.
Three states: Awaiting, Viewed, Responded. The middle state (Viewed) requires a tracking endpoint.
async function markBriefViewed(recipientId) {
await db
.update(briefingRecipients)
.set({ viewedAt: new Date() })
.where(eq(briefingRecipients.id, recipientId))
}
This fires on form page load. Known limitation: if the recipient forwards the link and a colleague opens it, the original recipient's record shows Viewed. Fixing this requires email-specific tokens — a larger implementation. For V1, Viewed means "the link was opened from somewhere."
4. Booking system: timezone handling
Requirement: Agency in Kyiv configures availability. Client in London books a slot. No timezone confusion.
Store all availability in the configured timezone. Convert to local timezone at display time in the visitor's browser.
function computeAvailability(date, config) {
const slots = []
const windowStart = setTimeInTz(date, config.startTime, config.timezone)
const windowEnd = setTimeInTz(date, config.endTime, config.timezone)
let cursor = windowStart
while (cursor < windowEnd) {
const slotEnd = addMinutes(cursor, config.duration)
if (slotEnd > windowEnd) break
slots.push({
startUtc: cursor.toISOString(),
endUtc: slotEnd.toISOString(),
})
cursor = slotEnd
}
return slots
}
Slots return as UTC ISO strings. The booking page component converts to the visitor's local timezone:
function formatSlotLocal(utcString) {
return new Intl.DateTimeFormat(navigator.language, {
hour: '2-digit',
minute: '2-digit',
timeZoneName: 'short',
}).format(new Date(utcString))
}
I limited to 16 timezone options rather than all 500+ IANA zones. A curated 16 is better UX than an exhaustive dropdown nobody can navigate.
The validation I almost missed: the slot window must accommodate at least one meeting. If duration is 60 minutes and window is 45 minutes, the system blocks you from publishing rather than showing an empty calendar.
5. CSV export: injection attack prevention
Client records sometimes start with formula characters: =, +, -, @. When a CSV opens in Excel, cells with these prefixes execute as formulas.
function sanitizeCsvCell(value) {
if (!value) return ''
const dangerous = ['=', '+', '-', '@', '\t', '\r']
if (dangerous.some(char => value.startsWith(char))) {
return '${value} // prefix with single quote
}
return value
}
A single quote prefix tells Excel to treat the cell as text, not a formula. The quote is invisible in the spreadsheet. The original value "=SUM(A1)" becomes '=SUM(A1) in the CSV and displays as =SUM(A1) in the sheet, inert.
This is not hypothetical. Melororium is multi-user. An agency might import contacts from an external source with crafted values. The person who exports the client list is not always the person who provided the data.
6. Document system: folder-based file management
Flat lists fail at scale. After 30 files, finding "the contract from March" requires metadata tags or memory. Hierarchical folders provide spatial memory.
interface ClientDocument {
id: string
clientId: string
folderId: string | null // null = root
name: string
size: number
mimeType: string
url: string
}
The UI limits navigation to two levels. Deeper nesting creates complexity that exceeds the benefit for typical agency file volumes.
The drag-and-drop zone uses a depth counter to handle nested drag events:
const [dragDepth, setDragDepth] = useState(0)
const isDragging = dragDepth > 0
const handleDragEnter = () => setDragDepth(d => d + 1)
const handleDragLeave = () => setDragDepth(d => d - 1)
Without this, dragleave fires when the cursor moves from the panel to a child element (a file row), falsely resetting drag state. The counter tracks depth and only clears when count reaches zero.
File size limit: 500MB. The cost is roughly $0.01/month per 500MB file. The constraint is upload reliability on slow connections. We mitigate with multipart uploads.
7. Recurring invoice detection in the invoice list
Invoices can be recurring. The invoice form stores recurring data in the notes field as a prefix: [Recurring: day 15 at 09:00] Original notes...
This was a schema constraint. The recurring metadata should be a separate column. It is not, because the invoice schema was locked before the recurring feature was designed, and migration was out of scope.
The solution: parse the prefix at render time.
const RECURRING_PREFIX = /^[Recurring: day (\d+) at (\d{2}:\d{2})]/
function extractRecurringInfo(notes) {
const match = notes?.match(RECURRING_PREFIX)
if (!match) return { isRecurring: false, cleanNotes: notes ?? '' }
return {
isRecurring: true,
day: parseInt(match[1]),
time: match[2],
cleanNotes: notes.replace(RECURRING_PREFIX, '').trim()
}
}
O(n) per invoice, paginated at 20-50 items — negligible cost. The cleanNotes field strips the prefix before displaying to the user. I document this as technical debt with a clear migration path.
8. Invoice aging: segment bar visualization
Aging data computed during the invoice list query:
interface AgingBuckets {
le30: number // 0-30 days overdue
d3160: number // 31-60 days
gt60: number // 60+ days
oldestDays: number // max days overdue
}
The bar renders three segments proportional to count. Edge case: if one bucket has zero invoices, its segment collapses to zero width:
// flex: count gives proportional widths
// flex: 0 with no minimum collapses to zero (not 1px line)
The oldestDays field drives a secondary indicator: if any invoice exceeds 90 days overdue, the entire stat card gets a red border regardless of bucket distribution.
9. Activity feed aggregation across event types
Requirement: A chronological feed per client showing all significant events, grouped by day.
Rather than a dedicated events table, I derive the feed from existing data on render.
function buildActivityFeed(client, tasks, invoices, documents, briefingResponses, bookings) {
const events = []
tasks.forEach(task => {
if (task.clientId === client.id) {
if (task.completedAt) {
events.push({ type: 'task_completed', date: task.completedAt, data: task })
}
events.push({ type: 'task_created', date: task.createdAt, data: task })
}
})
invoices.filter(inv => inv.clientId === client.id).forEach(inv => {
if (inv.paidAt) events.push({ type: 'invoice_paid', date: inv.paidAt, data: inv })
events.push({ type: 'invoice_sent', date: inv.sentAt || inv.createdAt, data: inv })
})
// ... similar for documents, briefings, bookings
return events.sort((a, b) => new Date(b.date).getTime() - new Date(a.date).getTime())
}
Memoized with useMemo and a dependency array. Re-computation only when source data changes.
Day grouping uses string comparison to avoid timezone bugs:
function groupByDay(events) {
return events.reduce((groups, event) => {
const day = event.date.split('T')[0] // '2026-07-31'
if (!groups[day]) groups[day] = []
groups[day].push(event)
return groups
}, {})
}
function getDayLabel(dateStr, today, yesterday) {
if (dateStr === today) return 'Today'
if (dateStr === yesterday) return 'Yesterday'
return new Intl.DateTimeFormat('en', { day: 'numeric', month: 'short', year: 'numeric' }).format(new Date(dateStr))
}
The path to a dedicated events table is non-breaking: add the table, write events on actions, fall back to derived feed for backfill.
10. State management: Zustand over context
The clients section reads the same data across many components simultaneously. A contact log update should ripple to the health score, the activity feed, and the billing section without prop drilling or a parent that re-renders everything.
React Context re-renders every consumer when any part of context changes. For 30+ clients with multiple computed values, that is expensive.
Zustand solves this with granular subscriptions via selectors:
// Billing tab only re-renders when clients change
const recurringClients = useClientStore(
state => state.clients.filter(c => c.recurring && c.monthlyPayment > 0)
)
// Health section subscribes to all three sources
const { clients, tasks, projects } = useClientStore(
state => ({ clients: state.clients, tasks: state.tasks, projects: state.projects })
)
Zustand does a shallow equality check on the selected value. If the selector returns the same reference, the component does not re-render.
Initial data loads server-side via Server Actions and hydrates the store. Mutations go through optimistic updates that update the store before the server confirms.
11. Bulk operations with optimistic updates
Select 8 clients, click "Create invoices." The user expects immediate feedback.
async function bulkCreateInvoices(clientIds) {
// Optimistic: mark all as pending immediately
clientIds.forEach(id => {
updateClientStore(id, { pendingInvoice: true })
})
// Parallel server requests
const results = await Promise.allSettled(
clientIds.map(id => createInvoiceAction(id))
)
// Resolve each independently
results.forEach((result, index) => {
const clientId = clientIds[index]
if (result.status === 'fulfilled') {
updateClientStore(clientId, { pendingInvoice: false, lastInvoiceId: result.value.id })
} else {
updateClientStore(clientId, { pendingInvoice: false, invoiceError: result.reason.message })
}
})
}
Promise.allSettled instead of Promise.all: one failed invoice creation does not reject the entire batch. The user sees 7 invoices created and 1 with an error — they retry the single failed one.
The pending state in the UI is a loading spinner per client row, not a global overlay. The user can continue scrolling while invoices create in the background.
12. CSV import validation and limits
The 2,000 row limit is not arbitrary. Above that threshold, the import runs long enough that users navigate away before it completes.
Validation runs before any write. Parse the entire CSV, validate every row, collect all errors, then either proceed or return an error report. Never partial-import.
function validateImportCSV(rows) {
const valid = []
const errors = []
rows.forEach((row, index) => {
const rowNumber = index + 2 // account for header row
const [name, email, monthlyPayment] = row
if (!name?.trim()) {
errors.push({ row: rowNumber, field: 'name', message: 'Name is required' })
return
}
if (email && !isValidEmail(email)) {
errors.push({ row: rowNumber, field: 'email', message: 'Invalid email format' })
return
}
const payment = parseFloat(monthlyPayment)
if (monthlyPayment && (isNaN(payment) || payment < 0)) {
errors.push({ row: rowNumber, field: 'monthlyPayment', message: 'Payment must be a positive number' })
return
}
valid.push({ name: name.trim(), email: email?.trim(), monthlyPayment: payment || 0 })
})
return { valid, errors }
}
Error report shows row numbers, field names, and human-readable messages. Not "Row 47 failed." Specifically: "Row 47, email: Invalid email format."
What the sprint taught me about product design
One pattern in retrospect across all twelve decisions: the right call was almost always "derive from existing data before adding a new data model." The health score derives from tasks, contacts, and projects. The activity feed derives from six existing collections. The recurring invoice metadata embeds in the notes field rather than a new table. Each one carries technical debt, and each one let us ship in three weeks instead of six. The refactors are on the roadmap with clear migration paths. The debt is intentional and bounded.
Three things I will carry into the next sprint:
Every feature should answer one specific question. The health score answers "which clients need attention?" The briefing tracker answers "who responded?" When I could not state the question a feature answered, the feature needed more scoping.
The "Why" column is cheaper than it looks and more valuable than it appears. Adding reason strings to scoring functions takes about 20 lines per algorithm. The payoff is a UI where every actionable item explains itself.
Two-state tracking hides the most actionable state. Sent/not-responded loses the middle. Healthy/unhealthy loses the dormant. The design question is always "what is the actionable difference between the two extremes?" — that difference is almost always a third state worth surfacing.
The full source for the features above lives inside Melororium — a team workspace for agencies at https://melororium.com. Agency plan is $59/month flat for up to 10 people. 14-day free demo, no credit card required.
If you have questions on the implementations above, I am in the comments.
Top comments (0)