A production-style PostgreSQL schema for a residential-complex maintenance service desk: SCD2 worker registry, trigger-based data validation, range partitioning, window-function analytics and EXPLAIN-verified query plans — with a seeded synthetic dataset of ~112k rows.
Residents of apartments file maintenance requests classified by problem type; requests are assigned to workers and move through a status lifecycle whose every transition is journalled. The schema goes well beyond CRUD.
| Conceptual model | Logical model |
|---|---|
 |
 |
What you're looking at: two levels of the same database blueprint. Left — the conceptual map (what entities exist and how they relate: residents live in apartments, file requests, workers execute them). Right — the logical model with exact columns, types and keys, ready to become SQL.
Imagine the office that manages an apartment complex. Residents call: "my faucet leaks", "the elevator is stuck". Someone must log the request, classify it, send the right worker and track every status change. This project is the database under such an office — the structured memory that never loses a request.
Three things make it more than a spreadsheet:
- History that never lies (SCD2). When a worker changes brigade or rate, the old record isn't overwritten — it's closed with a timestamp and a new version is stapled on top. You can ask "what did we know about worker 17 last March?" and get the truth as of then.
- A bouncer at the door (validation trigger). Incoming resident records pass a checkpoint: broken phone numbers and emails don't crash the load and don't sneak into the data — they're quarantined into a log for review. The synthetic dataset deliberately contains ~8% bad rows to prove it works.
- Filing cabinets by year (partitioning). The 85k-row status journal is physically split by year, so a query about 2025 never even opens the 2023 drawer — the
EXPLAINdemos show the database skipping whole partitions.
flowchart LR
R[resident] -->|files| Q[request]
Q --> T[problem type<br/>+ SLA hours]
Q --> W[worker<br/>SCD2 versions]
Q --> H[status history<br/>partitioned by year]
S[staging + validation trigger] -->|clean rows| R
S -->|bad rows| L[validation_log]
| Feature | Implementation |
|---|---|
| 6 business tables + staging + audit | problem_types, apartments, residents, workers, requests, request_status_history + residents_staging, validation_log |
| SCD Type 2 worker registry | load_workers_scd2(jsonb) procedure: change detection via IS DISTINCT FROM over 6 attributes, closes old versions, inserts new, reports counters via RAISE NOTICE; partial unique index guarantees one current version per worker |
| Validation trigger | process_resident_staging(): regex phone/email checks, FK existence; dirty rows are quarantined into validation_log instead of failing the load |
| Range partitioning | request_status_history partitioned by year (2023–2026 + DEFAULT); demo shows partition pruning in EXPLAIN |
| Analytics | 2 multi-CTE reports with window functions: SLA by problem type (avg reaction/resolution hours, DENSE_RANK) and worker efficiency ranking |
| Performance demos | 3 × EXPLAIN (ANALYZE, BUFFERS): main report plan, partition pruning, forced index scan via enable_seqscan |
| Constraints | ~10 CHECKs (status/priority domains, floor ranges, temporal sanity), 11 indexes |
Generated by scripts/generate_data.py (Faker ru_RU, seed 42 — fully reproducible): 400 apartments, 900 residents, 25,000 requests, 85,836 status-history rows, 2×60 worker snapshots (14 records differ between snapshots — a ready-made SCD2 test fixture). ~8% of phones/emails are deliberately malformed so the validation trigger has something to reject. All personal data is fake.
createdb service_desk
psql -d service_desk -f sql/solution.sql # schema, triggers, procedures + inline demo scenario
psql -d service_desk -f sql/load.sql # loads data/ CSVs + both SCD2 snapshots, resyncs sequencesload.sql loads CSVs in FK-safe order with NULL '' handling, runs both worker snapshots through the SCD2 procedure, verifies the one-current-version invariant and prints sanity counts. Regenerate the dataset with pip install faker && python scripts/generate_data.py.
sql/solution.sql # DDL + triggers + SCD2 procedure + analytics + EXPLAIN demos
sql/load.sql # \copy loading script (FK-safe, sequence resync)
scripts/generate_data.py # seeded synthetic data generator
data/ # 5 CSVs + 2 JSON worker snapshots (~112k rows)
docs/img/ # ER diagrams (conceptual + logical)
Keywords: PostgreSQL, database design, SQL, SCD2, slowly changing dimensions, partitioning, triggers, PL/pgSQL, data engineering, ER diagram, query optimization
Ключевые слова: PostgreSQL, проектирование баз данных, SQL, SCD2, медленно меняющиеся измерения, партиционирование, триггеры, инженерия данных, ER-диаграмма, оптимизация запросов