Versioning reference data with effective_from dates

By Flavio Copes

Version reference data in SQLite with effective_from dates instead of overwriting rows. Keep pricing history and drive changelog notifications.

Cloud provider prices change. If you store pricing in a database and update rows in place, you lose history.

A report generated last month might show $12/mo. Today the same tier costs $18. You can’t explain the old number. You can’t reproduce it. Auditors (and users) will ask questions.

The fix: never overwrite pricing. Append a new row with an effective_from date. Query the latest row that was active at a given moment.

I built this into StackPlan’s provider knowledge base. Railway raises a plan price, I insert a new tier row. Old reports stay explainable.

The problem with UPDATE

UPDATE plan_tiers SET base_price_cents = 2000 WHERE slug = 'pro'

Simple. Wrong for reference data.

That row had $15/mo yesterday. Now it says $20/mo. Every historical calculation that read this row looks wrong in hindsight. You can’t answer “what did we quote on March 3?”

The append-only pattern

Each pricing edit creates a new row. The old row gets a retired_at timestamp. Nothing is deleted.

Schema sketch:

export const planTiers = sqliteTable('plan_tiers', {
  id: text('id').primaryKey(),
  serviceId: text('service_id').notNull(),
  slug: text('slug').notNull(),
  basePriceCents: integer('base_price_cents').notNull(),
  effectiveFrom: integer('effective_from', { mode: 'timestamp_ms' }).notNull(),
  retiredAt: integer('retired_at', { mode: 'timestamp_ms' }),
})

When Railway’s Pro tier goes from $15 to $20:

1. Set retired_at = now on the current active row
2. Insert a new row with the new price and effective_from = now

Both rows exist. The old one is retired. The new one is active.

Do both writes in a D1 batch so you never end up with a tier retired and no replacement.

Querying the current price

For live recommendations, load tiers where retired_at IS NULL:

const tiers = await db
  .select()
  .from(planTiers)
  .where(isNull(planTiers.retiredAt))

That’s the current snapshot. One active row per tier slug per service.

To reconstruct what a price was at a past date, filter by effective_from:

SELECT *
FROM plan_tiers
WHERE service_id = ?
  AND slug = 'pro'
  AND effective_from <= ?
  AND (retired_at IS NULL OR retired_at > ?)
ORDER BY effective_from DESC
LIMIT 1

Pass the report’s created_at as both ? values. You get the tier version that was in effect when the report was generated.

Changelog notifications

Pricing edits should not hit users silently. StackPlan drafts a changelog row on every tier change:

export const kbChanges = sqliteTable('kb_changes', {
  id: text('id').primaryKey(),
  type: text('type').notNull(), // 'pricing_change', 'improved_limits', ...
  serviceId: text('service_id'),
  tierId: text('tier_id'),
  title: text('title').notNull(),
  published: integer('published', { mode: 'boolean' }).default(false),
  publishedAt: integer('published_at', { mode: 'timestamp_ms' }),
})

Auto-draft on save. Human reviews in admin. Publish when ready.

Published entries show on a public /changelog page and in a “what’s new” panel for users whose saved stacks reference the affected service.

The flow:

1. Admin (or an automated agent) proposes a price change
2. Code retires the old tier and inserts the new one
3. Code inserts a kb_changes row with published: false
4. Admin publishes → users see “Railway Pro went from $15 to $20/mo”

Match notifications to users by the service_ids stored on their reports. Don’t blast everyone for a change that doesn’t affect them.

Metadata-only edits

Not every edit needs a new pricing version. Renaming a tier from “Pro” to “Pro Plan” is metadata. Update the active row in place.

Version only when pricing fields change — base price, included quotas, overage rates, hard limits. Compare old and new. If dollars or limits moved, retire and insert. If only the display name moved, patch the row.

Why this beats a history table

Some teams add a separate plan_tiers_history table. That works too.

I prefer keeping all versions in one table with effective_from / retired_at. One query shape. One index. The “current” view is just WHERE retired_at IS NULL.

Either way, the rule is the same: append, don’t overwrite.

Your future self — and every user who saved a quote — will thank you.

Related posts about database: