# Versioning reference data with effective_from dates

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

Author: Flavio Copes | Published: 2026-07-15 | Canonical: https://flaviocopes.com/versioned-pricing-data/

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](https://stackplan.dev)'s provider knowledge base. Railway raises a plan price, I insert a new tier row. Old reports stay explainable.

## The problem with UPDATE

```sql
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:

```ts
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`:

```ts
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`:

```sql
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:

```ts
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.