Database Design & Modeling
Somewhere sensible to keep your data
A structure that matches how the business actually works, so the questions you need answered later are still possible to ask.
Selected work demonstrating this service.
- Designed, implemented, and administered 6 ETL/ELT pipelines, utilizing Google BigQuery, MSSQL, PostgreSQL, Shell scripting, PL/pgSQL, and Transact‑SQL, integrating data for efficient Python API processing.
- Architected, created, and managed 100 PostgreSQL, MS SQL, and Google BigQuery data warehouse databases with primarily GIS and time‑series data, optimizing performance and scalability.
- Architected a layered maritime platform separating a Next.js PWA frontend, a Go (Huma/Fiber) API, and a PostgreSQL function layer, keeping all business logic in the database.
- Designed a JSON passthrough architecture where PostgreSQL functions return complete JSON forwarded verbatim by the Go API, eliminating intermediate unmarshalling and decoupling the frontend from schema changes.
- Designed a PostgreSQL function‑first data layer — 1,275 stored functions across 34 schemas — so every read and write goes through a function the database can grant, rather than through a table.
- Adopted UUID v7 time‑ordered identifiers (PostgreSQL 18) as entity keys to reduce B‑tree index fragmentation and speed up queries.
- Implemented a catalogue‑driven deep‑merge for stored JSON preferences, preventing missing‑key crashes as the schema evolves.
- Modeled the maritime domain into 348 normalized tables across 34 PostgreSQL schemas — professionals, companies, ships, jobs, reviews and the rest — with SMALLINT lookups and UUID v7 keys.
- Built the payments and entitlements layer — Stripe alongside Apple and Google in‑app purchase — gating the directory, search and export through an 11‑table access model checked on the server.
- Moved slow work off the request path onto a River job queue — 15 worker modules, 8 scheduled tasks and 20 pg_cron jobs — so a request returns while the work behind it carries on.
- Built first‑party product analytics in PostgreSQL — 47 functions over partitioned event tables that prune themselves — pseudonymised behind a rotating salt and gated on the visitor's consent.
- Built the loyalty and reputation system — 67 functions over a 31‑table ledger, with leagues, badges and a redemption shop — taking a row lock on the balance to close the double‑spend window.
- Built the hiring marketplace and the seafarer career workspace — 151 stored functions across 48 tables — covering vacancies, applications, certificates, rank progression and verified sea time.
- Built a database‑driven CV, references, portfolio and cover‑letter generator in Python — 41 modules, 10,580 lines — rendering six output formats from one 23‑table SQLite source assembled by a 19‑step idempotent pipeline.