'2026-27' is a better database key than a date range

We sell school and university discounts. A school gets an allocation: a code its students can redeem, capped at some number of redemptions, and the cap resets each year.

Each year, but not each calendar year. A sixth form's intake arrives in September. The cohort that used the allocation in the spring has left by the autumn, and resetting their cap on 1 January means resetting it in the middle of application season, which is the worst possible moment for the people relying on it.

So the reset is an academic year boundary. That sounds like a date problem and it mostly is not.

The obvious implementation

Compute the current academic year's start and end, then count redemptions between them:

const { start, end } = currentAcademicYearBounds();
const used = await db
  .select({ count: count() })
  .from(redemptions)
  .where(and(
    eq(redemptions.code_id, codeId),
    gte(redemptions.created_at, start),
    lt(redemptions.created_at, end),
  ));

This works. It is also a range scan on a timestamp on every redemption attempt, it recomputes the boundary logic in every query that needs it, and it has a subtler problem I will get to.

Store the period on the row

Each redemption row carries the period it belongs to, as a short string:

export function getAcademicYearPeriod(date: Date = new Date()): string {
  const year = date.getUTCFullYear();
  const month = date.getUTCMonth() + 1; // getUTCMonth is 0-indexed

  const startYear = month >= ACADEMIC_YEAR_START_MONTH ? year : year - 1;
  const endShortYear = String((startYear + 1) % 100).padStart(2, '0');

  return `${startYear}-${endShortYear}`;
}

2026-09-01 gives '2026-27'. 2027-08-31 gives '2026-27' too, because a date before the start month still belongs to the year that opened.

Counting a school's usage becomes an indexed equality match on (code_id, period). No range, no boundary arithmetic at query time, and the same string appears in the admin UI without formatting.

ACADEMIC_YEAR_START_MONTH is a named constant set to 9, which makes the whole policy one line to change and one line to find. It is also 1-indexed on purpose, because getUTCMonth being 0-indexed is the single most reliable source of off-by-one in JavaScript date code, and the + 1 next to the comment is cheaper than the bug.

The reason that is actually the good one

Speed is the boring justification. The real one is that a stored period is immutable and a computed one is not.

Suppose next year we decide the academic year should start in August rather than September, or we add an institution type whose year runs differently. With computed ranges, changing that constant retroactively reclassifies every historical redemption. Rows that were counted against 2025-26 silently become 2026-27. A school that hit its cap last year now has capacity, or does not, depending on which way the boundary moved. Your historical reporting changes without a single row being written.

With the period stored at redemption time, history is a fact. Rows say what year they were counted in because that is what was true when they were counted. A policy change applies to the future, which is what a policy change should do.

This is the same reasoning behind storing the price paid on an order row rather than joining to the current price list. Anything that is part of a decision you made should be written down at the moment you make it, not recomputed later from inputs that are free to move.

Keep the range function anyway, for reporting

export function getAcademicYearBounds(period: string): { start: Date; end: Date } {
  const match = /^(\d{4})-(\d{2})$/.exec(period);
  if (!match) {
    throw new Error(`Malformed academic year period: "${period}"`);
  }

  const startYear = Number(match[1]);
  const monthIndex = ACADEMIC_YEAR_START_MONTH - 1;

  return {
    start: new Date(Date.UTC(startYear, monthIndex, 1)),
    end: new Date(Date.UTC(startYear + 1, monthIndex, 1)),
  };
}

Inclusive start, exclusive end, which is the only convention that composes without off-by-one errors when periods are adjacent.

Note that this one throws on a malformed period, where most of our small utilities return null. The difference is intent: getAcademicYearPeriod produces periods and is total, so a malformed string reaching getAcademicYearBounds means something upstream wrote garbage into a column. That should be loud. A silent null there produces an admin report covering the wrong window, which is the kind of wrong that gets believed.

Everything is UTC. A redemption at 23:30 on 31 August in London and one at 00:30 on 1 September in Sydney are already different days to each other; picking one timezone and using it consistently is the only version of this that is explainable to a human.

And a label function, because formatting is not the caller's job

export function describeAcademicYear(period: string): string {
  return `${period.replace('-', '/')} academic year`;
}

Eleven characters of logic, and it exists so that '2026-27' is never rendered by a template that decides its own punctuation. The day someone wants "2026/2027" it is one edit rather than a grep.

The general shape

When a business rule has a period, ask whether the period is a derived view of a timestamp or a fact about the row. If a row's membership in a period is part of a decision (a cap, an allocation, a quota, a billing cycle), store it:

• equality beats range on both readability and index use
• history stops moving when policy changes
• the stored value is usually already the thing you want to display
• and the boundary logic lives in exactly one function, which is where the timezone and off-by-one hazards can be dealt with once

Our school and student pricing is at https://cogniprep.app/pricing. The allocation behind it resets on 1 September, and every redemption before then is stamped with the year it was actually counted in, permanently.