Modelling Traffic Fines in Postgres

If you model a traffic fine as a row with a description and an amount, you have given up the two things a fines system exists to prove: what the offence was, and what the penalty was worth on the day it was issued. Offences belong in a reference table, so the description on a fine is a foreign key rather than free text. The fee belongs on an effective-dated version row, so raising a penalty inserts a row instead of overwriting one.

Update, September 2026: The schema has since been changed to answer the question this piece raises: offence fees are effective-dated in their own table, with a Postgres exclusion constraint making overlapping validity periods impossible, and each fine records what its offences cost on the day it was issued. bcryptjs is gone. What follows describes the schema as it stood.

FineTrack — Express and Sequelize on Postgres behind an Angular frontend — got the first half right and the second half wrong.

An offence is a type, not an event#

The first instinct is a fine with a description and an amount on it. If the amount lives on the fine, two officers can issue different amounts for the same offence. If the description lives there, "no seatbelt", "No Seatbelt" and "not wearing seatbelt" all end up in the database and no report can group them.

So offences are a seeded reference table. Backend/src/seeders/20240609115810-add-offences.js inserts eleven — running a red light at 2000.00, drink-driving at 5000.00 — each with an offenceType of Driver or Pedestrian, a fee, a demerit score and an enabled flag. Fines reach them through a join table keyed on (fineId, offenceId), since one stop can produce several.

That turns the report everyone actually wants — how many of each offence, by station, this month — into a group-by rather than a text match. It costs an afternoon now and a data migration later, like the e-commerce site I built with a JSON file standing in for a product database.

How do you change a fine amount without rewriting history?#

The original write-up raised this and then walked away from it: referencing an offence by id fixes the naming problem and does nothing about time.

Offences has one fee column. When the penalty for drink-driving goes up, somebody sets fee = 25000 on that row, and every fine ever issued for it now joins to the new number. Postgres will tell you, with total confidence, that a fine written in 2024 was for 25,000 rupees. The enabled boolean is the schema's only temporal control, it has no dates on it, and add_fine_record never filters on it anyway.

FineTrack half-saves itself by accident: add_fine_record sums the fees into FineRecords.totalFine, so the amount charged is snapshotted. The breakdown is not. Join a 2024 fine to its offences to explain that total to a magistrate and you get today's fees, which will not add up to the total beside them.

The answer is effective-dated reference data — a slowly changing dimension, type 2. Split the offence into a stable identity and a version carrying everything amendable:

CREATE EXTENSION IF NOT EXISTS btree_gist;

-- Identity: stable for the life of the offence.
CREATE TABLE offence (
    offence_id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    offence_type text   NOT NULL CHECK (offence_type IN ('Driver', 'Pedestrian')),
    code         text   NOT NULL UNIQUE
);

-- Version: everything amendable, and when it was in force.
CREATE TABLE offence_version (
    offence_version_id bigint        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    offence_id         bigint        NOT NULL REFERENCES offence,
    description        text          NOT NULL,
    fee                numeric(10,2) NOT NULL CHECK (fee >= 0),
    demerit_points     integer       NOT NULL CHECK (demerit_points >= 0),
    instrument         text,  -- the gazette or act that made this true
    valid              daterange     NOT NULL,
    EXCLUDE USING gist (offence_id WITH =, valid WITH &&)
);

The exclusion constraint is the whole point. WITH = on offence_id and WITH && on the range together say that two rows for the same offence may not have overlapping validity, and Postgres enforces that in the index — concurrency-safe in a way an application check never is. btree_gist is required because GiST cannot compare a plain bigint for equality on its own.

Amending a penalty becomes a close and an insert in one transaction:

BEGIN;

UPDATE offence_version
   SET valid = daterange(lower(valid), DATE '2026-01-01', '[)')
 WHERE offence_id = 3
   AND upper_inf(valid);

INSERT INTO offence_version
       (offence_id, description, fee, demerit_points, instrument, valid)
VALUES (3, 'Driving under the influence of alcohol', 25000.00, 80,
        'Gazette 2025/47', daterange(DATE '2026-01-01', NULL, '[)'));

COMMIT;

Close the open row before inserting, or declare the constraint DEFERRABLE INITIALLY DEFERRED. Half-open bounds, [), are the ones you want: a fee ending 31 December and one starting 1 January do not overlap. Reading is a containment test, and the fine records the number and its source:

SELECT ov.offence_version_id, ov.description, ov.fee, ov.demerit_points
  FROM offence_version ov
 WHERE ov.offence_id = $1
   AND ov.valid @> $2::date;  -- the date the fine was issued

CREATE TABLE fine_line (
    fine_id            bigint        NOT NULL REFERENCES fine ON DELETE CASCADE,
    offence_version_id bigint        NOT NULL REFERENCES offence_version,
    fee_charged        numeric(10,2) NOT NULL,
    points_charged     integer       NOT NULL,
    PRIMARY KEY (fine_id, offence_version_id)
);

Copying fee_charged onto the line looks redundant next to the version reference. It is not: the version says what the rule was, the copy says what was applied, and when they disagree you want to see it rather than have a join resolve it silently.

Postgres 18 gives you tidier syntax — PRIMARY KEY (offence_id, valid WITHOUT OVERLAPS), which the documentation says behaves like the EXCLUDE clause above, plus temporal foreign keys through PERIOD. You still need btree_gist for the scalar column. One limit either way: this records when a rule was in force, not when the database learned of it.

What a DECIMAL(4,2) column did to the demerit points#

Demerit points live in DECIMAL(4,2) on Offences.score, FineRecords.totalScore and Citizens.earnedScore — two digits before the point, so the ceiling is 99.99. The seeded offences score 45, 50, 70, 80, 90; two on one stop is 125, which Postgres rejects with numeric field overflow. The application's answer:

totalScore = Math.min(totalScore, 99);  // Ensure totalScore does not exceed 99
const averageScore = totalScore / offences.length;

A column type chose a business rule. Worse, the code then divides by the offence count and writes the mean into a column named totalScore — and earnedScore is a running average too, so a citizen collecting several small offences can watch their score fall. The code bent around the type until the name and the meaning came apart. Demerit points are unbounded integers: use integer, cap them in a CHECK.

Three identity documents, and only two made it into the schema#

Sri Lankan traffic enforcement has three identity documents in play: the National Identity Card, the driving licence, and the vehicle registration. Different authorities, no shared key. A fine has to attach to all three, because the person, the driver and the vehicle are three different things.

The schema mostly avoids the one-users-table-with-nullable-columns shortcut. NICs is the person record, keyed on a 12-character idNumber. Citizens is the account, keyed on that same NIC, its username and password nullable because a citizen exists long before they register. DrLicences is keyed on licenceNumber with a unique NIC reference.

The vehicle is where it falls over. There is no vehicle table — only IfDrivers, a fineId and a vehicle CHAR(10): a plate as a bare string, no foreign key, no owner, no association in models/index.ts, nothing writing to it. I claimed three identity documents; the schema has two and a text field. That is what I would fix first, because "the registered owner disputes that they were driving" is not an edge case here, it is Tuesday.

The NIC also deserves more than CHAR(12). Sri Lanka has issued the 12-digit format for new cards since 2016, but the older nine-digit-plus-letter numbers ending in V or X stay valid for their holders' lifetimes, and in a blank-padded CHAR(12) one picks up trailing spaces. varchar with a CHECK accepting both shapes, plus validation that names what was wrong — the same argument as parsing matrices out of text files.

Two hashing libraries is an artefact, not a decision#

package.json lists both bcrypt at ^5.1.1 and bcryptjs at ^2.4.3. Not a decision — a native build failing on one machine, bcryptjs going in as the pure-JS fallback, the original never removed.

I previously wrote that this made password verification depend on import order. It does not, and I should correct it: every call site imports bcrypt, bcryptjs nowhere, and @types/bcrypt is present while @types/bcryptjs is not. Dead weight, not a live hazard — but a trap for whoever adds the next auth path.

Both are alive in 2026. bcrypt is on 6.0.0 and builds through node-gyp-build, so prebuilt binaries where they exist and compilation only as a fallback — the failure that started this is rarer now. bcryptjs is on 3.0.3, zero dependencies, ships its own types.

The recommendation has moved, though. OWASP now puts Argon2id first for new systems — 19 MiB of memory, two iterations, one degree of parallelism as the floor — and treats bcrypt as the legacy option at a work factor of 10 or more with a 72-byte password limit. FineTrack hashes at cost 10, the minimum rather than a choice, and a migration hides in the schema: the password columns are varchar(60), sized to a bcrypt hash, while an Argon2id hash runs closer to 95 characters. Column width is a cryptographic decision you make by accident. Nor is the hashing the weak link — the JWT secret is the literal "finetrack2024" in three controllers and the middleware, the same mistake as an auth middleware that validates nothing.

Is Sequelize still the right choice for Postgres and TypeScript?#

The project runs Sequelize 6 with InferAttributes and InferCreationAttributes, still the reasonable conservative choice — a lower bar than it sounds. Sequelize 6 is the stable line, 6.37.8 as of March 2026. Version 7, the TypeScript-first rewrite that moves the package to @sequelize/core and splits dialects into separate packages, has been in alpha since 2022 and sits at 7.0.0-alpha.48. Four years of alpha is not a criticism of the people doing the work, but it is a fact to plan around: start on Sequelize today and you start on 6, inheriting an undated migration.

The alternatives went the other way. Drizzle publishes 0.45.2 as latest with 1.0 in release candidate, keeps the schema in TypeScript with no generation step, and sits close enough to SQL that a daterange column with an exclusion constraint is not a fight. Prisma 7 shipped in late 2025, replacing the Rust query engine with TypeScript and WASM, and Prisma 8 is in release candidate. Starting fresh I would use Drizzle, because this post argues throughout for schema features that live in the database.

A fine's location is evidence, not two floats#

FineRecords stores locationName, nullable, and locationLink, a required STRING(512) — in practice a maps URL, taken straight from req.body and written as given. geoip-lite is in the dependency list, which prompted the original point that IP geolocation tells you where the request came from, not where the car was. That holds, and the package turns out never to be imported anywhere. The link is worse than either: a URL is not a coordinate, so you cannot index it, ask which fines fell inside a station's jurisdiction, or compute a distance. What I would use:

ALTER TABLE fine
    ADD COLUMN occurred_at      timestamptz NOT NULL,
    ADD COLUMN recorded_at      timestamptz NOT NULL DEFAULT now(),
    ADD COLUMN position         geography(Point, 4326),
    ADD COLUMN accuracy_metres  numeric(6,1),
    ADD COLUMN position_source  text CHECK (position_source IN ('gps', 'network', 'manual')),
    ADD COLUMN device_id        text;

geography(Point, 4326) over a plain point because PostGIS gives distances in metres on a spheroid, a GiST index that answers "inside this polygon" directly, and an SRID so nobody has to guess what the numbers mean. The built-in point is two floats with no coordinate system attached.

accuracy_metres matters more than people expect. Geolocation APIs report accuracy in metres at 95% confidence, and a fix good to 2,000 metres is a different fact from one good to 5: the first cannot establish jurisdiction and the second can. Dropping it turns an honest measurement into false precision.

The rest is chain of custody. A fine's location decides which station owns the case and whether the officer had jurisdiction, which makes it evidence — and evidence has to say who recorded it, on what device, when and by what method. Timestamps count too: fineDate and fineTime are separate columns, and fineTime comes from the server's toTimeString(), so a server off Asia/Colombo disagrees with itself about the same event.

What holds up#

The seeder-driven reference data. Reporting, validation and the officer's dropdown all got easier because offences and licence categories were closed sets from day one rather than strings that accumulated variants. Separating the identity documents held up for the two that got built — it looked like over-modelling until a fine needed a driver who was not the registered owner.

What does not hold up is the treatment of time. A mutable fee column, an enabled flag with no dates, and a location captured as a link are the same error at three scales: recording the current state of the world and hoping nobody asks what it was in June. In a domain whose entire output is contestable records, that is the one thing I could not afford to get wrong, and it is the one thing I did.

Common questions#

Should a fine store the amount, or join the offence table for it?#

Both, because they answer different questions. Store the amount charged on the fine line — what the citizen was told and what a court will ask about — and store which version of the offence produced it, so you can show the rule that justified the number. Joining alone is wrong because reference data changes underneath you; storing alone loses the provenance. The pair makes a historical record defensible.

How do I stop two versions of the same reference row overlapping in time?#

Give the version table a daterange column and add EXCLUDE USING gist (parent_id WITH =, valid WITH &&), after CREATE EXTENSION btree_gist, which GiST needs to compare a scalar like a bigint for equality. Postgres then enforces non-overlap in the index, concurrency-safe in a way a trigger or an application check is not. On Postgres 18 and later, PRIMARY KEY (parent_id, valid WITHOUT OVERLAPS) does the same thing and still expects btree_gist.

Is bcryptjs or bcrypt the right choice in 2026, and what about Argon2?#

Both are maintained — bcrypt at 6.0.0 with prebuilt binaries through node-gyp-build, bcryptjs at 3.0.3 with zero dependencies and its own TypeScript types, making the DefinitelyTyped package obsolete. Pick bcryptjs for portability, bcrypt for native speed when you control the build. For a new system, though, OWASP recommends Argon2id first, at a minimum of 19 MiB memory, two iterations and one degree of parallelism. Check your column width if you switch: varchar(60) will not hold an Argon2id hash.

Is Sequelize v7 ready to use?#

Not as a stable release. Sequelize 6 is still the published latest at 6.37.8, while v7 — the rewrite that renames the package to @sequelize/core and splits out the dialects — has been in alpha since 2022 and is at 7.0.0-alpha.48. The maintainers say the alphas are stable enough for non-production use and have not committed to a release date. Stay on a working Sequelize 6 codebase; if you are starting fresh, Drizzle or Prisma give you a version number you can plan around.

Why is IP geolocation the wrong way to record where an offence happened?#

Because it answers a different question. An IP lookup tells you roughly where the network connection originated — often the mobile carrier's gateway, which can be a different city — not where the vehicle was. A fine's location decides jurisdiction and which court hears a dispute, so it has to come from the device's GPS and carry its reported accuracy in metres. Use geography(Point, 4326) with PostGIS so you can index it and measure real distances, and record who captured it and when, because evidence needs a chain of custody.