Bergaz Operations System

Since 2007 the business had been running a desktop system on a single computer: Suttek.exe, Paradox files on Delphi/BDE. Everything else was scattered across paper and spreadsheets. The problems fed one another — access from a single point, no multi-user support, no mobile access, the corruption risk of file-based storage (during the migration one table's record count did not match across two separate reads), and receipts being copied into the accounting program by hand. Above all of that sat a quieter problem: every existing screen had been designed for the operator — dense forms, many tabs, keyboard shortcuts. The manager could not get information out of those screens; the answer to the question was sitting in the system, but the only way to reach it was to ask someone else.

Four roles, four different needs. Operator: enters a large number of repetitive milk intake records during the day, high risk of duplicates, speed matters more than anything. Accountant: runs advance settlement, month close, deduction calculation and receipt generation — and accepts no error down to the last kuruş. Quality officer: has to see a sample value falling outside the reference range before finishing the record, and the reference range differs by milk type. Manager: not technology-inclined, looks from a phone, wants few screens and large numbers — and wants the chance of touching something by accident to be zero.

Sole developer, four repositories. Drawing the system boundaries, four data models and their migrations, the Paradox ETL pipeline and its verification, the money and deduction calculation engines, two separate auth layers, the operator panels, the management PWA and deployment.

Four independent Next.js 16 App Router applications, four separate Neon Postgres databases. No foreign keys, cross-DB queries or synchronisation mechanisms between them. The write path is the same in every application: Server Action → Zod validation → db.transaction(). The read path is Server Component → query layer. On the accounting side advance settlement runs FIFO with SELECT ... FOR UPDATE inside a single transaction; milk entry is protected by an advisory lock on the (producer, date) pair; receipt numbers come from a Postgres SEQUENCE. On the analysis side deduction parameters are copied onto the row at the moment of recording (deduction snapshot), a period is locked with COMPLETED → LOCKED, and a locked period closes to both sample and production records; the daily aggregate table is updated on write, so region and producer analyses never rescan the entire history. The management application carries four connections: read and write to its own auth database, read-only to the other three. Mirrors of the other schemas are copied column by column with index and relation definitions dropped; users and audit_log are never mirrored.

1. 01

   Four separate applications, four separate databases — not one monolith

   The earlier design had two systems sharing a common schema; that was removed. Each application is independent in its own migration, backup and PITR cycle; when one breaks the others are unaffected, and their development speeds can diverge — the analysis side could stay on Auth.js and bcrypt while accounting moved to argon2id. The boundary is not arbitrary, it follows the business boundary: money on one side, quality and production on the other.

   Trade-offThe same person exists as two separate records in two systems; a new producer or village definition is entered by hand in both places and matching is manual, by name and village. A report spanning both systems cannot be produced with a single query — you export from each application separately and combine the results outside. Users log into each system with a separate account; there is no single sign-on.

2. 02

   decimal.js and ROUND_HALF_UP for money; JS Number forbidden

   Numeric columns coming from Postgres are read as strings and are never converted to Number; all arithmetic runs at full precision through decimal.js. Rounding happens only when writing to the database and when printing to the screen, with HALF_UP — not banker's rounding, because the kuruş on a receipt has to match a hand calculation exactly. Rounding accumulation is a separate concern: if settlement multiplies price × kg for every row, the last allocation can end up one kuruş short, so the final row is calculated from the remaining amount instead.

   Trade-offEvery arithmetic expression needs a Decimal wrapper; you cannot write `a + b`, and the calculation code is noticeably longer and more tiring to read. Worse, the rule is not enforced by the compiler — a single line converting a string to Number runs silently, and you only notice the error when the kuruş no longer matches.

3. 03

   The management application is read-only — no data entry, editing or deletion

   What the manager needed was not another entry screen but a readable window onto existing data; adding write capability would only have introduced the risk of touching the wrong thing. Where read-only is guaranteed is the real decision: the plan called for a separate read-only role on the Neon side, but since the connections in practice still run under the owner role, the guarantee fell to a query shield inside the application. The shield requires a query to begin with `select` or `with`, and if a writing keyword appears the query is rejected before it reaches the network — writable CTEs included.

   Trade-offThe guarantee sits inside the application rather than inside the database; the moment a new query path is added that does not pass through the shield, the protection disappears, and nothing reminds you of it. Keyword-based rejection can also produce false positives: a legitimate column or CTE whose name contains a forbidden word blocks the query, and at that point you have to rewrite it in a form the shield understands.

4. 04

   19 years of Paradox data migrated by ETL; Turkish character verification as a separate step

   The Paradox reader returns the byte stream as a mojibake mixture of latin1 and cp437; plain cp857 or cp1254 decoding fails. The decoder works in two stages: first a deterministic map from observed corruptions to Turkish letters, then a scoring contest between cp1254 and cp857 for the remaining high bytes. The import alone was not treated as sufficient — per-year total comparison, count comparison and a separate verification run were added, and rows that could not be resolved were dropped into a manual review list.

   Trade-offThe first stage is an observation-based map: when a corruption not seen before appears in the source it does not generalise on its own and has to be added by hand. The second stage is probabilistic; in short fields where both encodings look plausible it can fall on the wrong side. Because of this the migration was never "run-and-forget" — every run required a verification pass and a human decision. The reader itself also requires x86_64 Python, which added a Rosetta layer to the development environment on Apple Silicon.

5. 05

   Neon pooled/unpooled split — pooled at runtime, unpooled for migrations

   At runtime the application uses the pooled connection; this is what prevents the connection explosion caused by every serverless function instance on Vercel opening its own connection. Migrations, on the other hand, need a direct connection: pgbouncer's transaction mode breaks multi-statement migrations. So two separate connection strings are kept, and the migration path refuses to run if the direct connection is not defined.

   Trade-offTwo connection strings are carried instead of one, and both have to be set correctly in every environment (local, preview, production). A mix-up surfaces at runtime rather than at compile time — and usually in the middle of a migration, which is the most expensive place for it to happen.

Keeping all four applications on Next.js 16 App Router was deliberate: the same write pattern (Server Action → Zod → transaction) repeats across four repositories, so the mental model does not change when moving from one to another. Neon stays in the same serverless model as the deployment while still offering real transactions through its WebSocket driver — the SELECT ... FOR UPDATE in FIFO settlement and the multi-statement writes made that mandatory. Drizzle ORM stays close to SQL: partial unique indexes, enums and FK behaviour are visible in the migration file, and in a system built around data integrity it mattered that constraints do not disappear behind an ORM abstraction. decimal.js keeps float rounding out of money calculations from the start. Zod works as a single schema for both the form and the Server Action input. On the management side Serwist makes the application installable on a phone and keeps the last seen data in cache. The only reason the ETL is in Python is Paradox: the only working reader lives there.

Two separate auth stacks sit side by side on purpose. On the analysis and stock side, Auth.js credentials with bcrypt and JWT sessions; authorisation is checked at three points — optimistic route protection in the proxy layer, full role validation at the layout level, and an ownership check inside the Server Action, so entry staff can only edit and delete their own records. On the accounting and management side, auth I wrote myself: argon2id with OWASP 2024 parameters, four roles in accounting (admin / accountant / operator / viewer), and a long-lived session plus a six-digit PIN in management. In the audit log, fields such as national ID number, IBAN and insurance number are never written raw, only a "changed" flag is kept; every other INSERT, UPDATE and DELETE is logged as JSONB with its old and new values, and old entries are pruned by a batched command. Rate limiting is active on login and on all write actions, deleted users cannot log in, and dynamic route parameters are validated as UUIDs. All four are closed systems: fully disallowed in robots.txt and noindex in the response header. Every query leaving the management application also passes through the query shield.

None of these four applications are public, so there is no stored Lighthouse measurement and I am not writing a score that does not exist; the measure here is query behaviour, not page speed. On the analysis side the daily aggregate table is updated on write, so region and producer analyses do not scan the whole history; slow queries are logged above a configurable threshold, and the repository ships a command for taking an EXPLAIN ANALYZE profile of admin queries. On the accounting side no materialized view was built, but the decision was left open: it will be revisited if p95 exceeds two seconds once all historical data is live. The management application was designed for phones only — a 390px design width, no breakpoints, 17px body text and 34–40px KPI figures, a minimum touch target of 48×48px; zooming was deliberately left enabled. The last seen data is kept in the service worker cache and the screen shows a "updated N minutes ago" stamp.

Active development

2026-02 → 2026-09 · 347 commits across four repositories

TypeScript source files

802 — accounting 364 · management 203 · analysis 160 · stock 75

Schema

49 tables across four separate databases · 30 Drizzle migrations

Schema mirror

The management application carries 24 tables from the other three databases as read-only mirrors

Test files

9 — all in the management application (query shield, withholding, pricing, deviation)

Write integrity

On the analysis side all 17 write actions run in a single transaction — no partial records

Accounting guarantee

A locked period is closed to writes; the deduction calculation is frozen with the parameters in effect at recording time

Read-only guarantee

A query leaving management must begin with `select` or `with`; a writing query never reaches the network

Read-only today sits in the application layer as a query shield, when its right place is the database: had a separate read-only role been created on Neon, the guarantee would live outside the application and no new query path could bypass it. I had already learned that lesson once inside this same system — when I moved the duplicate sample check out of form validation and into a partial unique index, the rule held no matter which path you arrived from. The same pattern showed up in Görev Kahramanı on the concurrency side: protecting reward writes with a compare-and-set update in the application layer means every new write path has to re-establish the same protection. The recurring result is this: as long as the guarantee lives in the application, every new path has to revalidate it, and sooner or later someone forgets. The second point is testing: only one of the four repositories has automated tests. Money calculations — withholding, pricing, deviation — are pure functions and the easiest thing to test; I started there in management but did not do the same in accounting. Today I would reverse the order and write the tests in the repository where most of the money moves.