A national education-programs operator runs enrichment groups in schools across the country, staffed by field educators who are paid by the hour or by the unit. I led the platform that plans those programs, collects every hour reported in the field, routes it through a multi-stage approval chain and produces the payroll and budget reports. It started on .NET Core 2.2 in 2019 and runs on .NET 8 today.
Each program has sub-projects, budget sections, schools, groups of students and the educators assigned to them. Every month, every educator reports hours or delivered units, and every report has to pass checks against the allocation, the monthly limit, the school calendar and the educator's own days off. Then it goes through coordinators, inspectors and school staff before it can be paid. Rates change, people move between groups and projects close, but last year's payroll still has to come out exactly the same.
The organization also needed a clear record of who changed what, reminders for educators who hadn't reported, and the ability to hand any table to finance as Excel.
Angular with Material, in Hebrew and English with full RTL support. It has 212 components in 59 lazy-loaded route modules: projects, allocations, groups, students, institutions, work reports, approvals, documents, dynamic forms and messaging. Every list uses one generic table and every dropdown is driven by metadata.
A mobile web app for educators. They see their allocations, report hours, attendance and units for the salary month, and track each report's approval status.
474 endpoints across 16 controllers and a service layer. It handles JWT access and refresh tokens, per-entity authorisation and the report status workflow, validates against limits and calendars, and handles 105 Excel export types plus bulk Excel import.
A database-first schema project with 128 tables, 156 views, 30 stored procedures and 333 index definitions, plus audit and history tables. Schema compare keeps the Dev, QA, staging and production databases aligned.
11 SSRS reports covering budget vs actual, salaries, attendance, activity and limit exceptions. They are opened through one-time report keys issued by the API, which expire after 60 minutes.
The diagram is wider than the screen: swipe sideways to see all of it →
Everything runs on the client's own Windows servers and SQL Server. The database is the source of truth: reporting logic lives in views and stored procedures that SSRS and the API both use, and the EF Core model is scaffolded from the schema project instead of the other way round.
Payroll and budget reports are relational questions, so they live in SQL views and procedures that SSRS, Excel exports and the API share. EF Core is scaffolded from a versioned schema project. Trade-off: each schema change is a two-step change, DB script and scaffold. I accepted that for one definition of every number.
Nine roles and 105 permissions are resolved per project, group and allocation. The resolved map is cached in memory with a 5-minute sliding window, and a SQL Server change-tracking queue clears a user's entry as soon as their roles change. Trade-off: it needs Service Broker enabled on the database, but there is no stale access and no per-request lookup.
When a report is approved, the hourly cost, travel, extra expenses and budget section are copied into the report itself. Trade-off: deliberate denormalisation. A rate change can never quietly rewrite last year's payroll.
Retired projects are purged by six stored procedures, deepest children first, all inside one transaction. Cascade deletes and disabled constraints were ruled out. Trade-off: more code, but it is generated from the live foreign-key graph, so it stays complete when the schema grows.
Every .NET jump (2.2 → 3.1 → 6 → 8) went in as a focused migration branch on the running product, with client releases continuing. Trade-off: some legacy front-end tooling stays. The client never paid for a big-bang rewrite.
A selection from the commit history, in order.
The move from .NET Core 2.2 to 3.1 ended EF's silent client-side evaluation. Several queries used string comparisons that can't be translated to SQL.
Rewrote them as server-translatable expressions, relying on the database's case-insensitive collation, and migrated the build and publish setup to 3.1.
Every request needed the user's resolved access across projects, groups and allocations, built by one very wide query.
Split it into 3 narrower queries to cut the result set, and applied index recommendations from a SQL Server tuning session.
Loading a project with all 12 child collections in one query multiplied the rows: a cartesian explosion.
Split the load into 3 queries of 4 includes each and merged the results in memory.
Caching the permission map made requests fast, but a role change didn't apply until the cache expired.
Added a SQL Server change-tracking table and a Service Broker queue. The API subscribes and clears just that user's cache entry the moment their access changes.
The same SQL that EF generated ran in under a second in Management Studio but was very slow from the API.
The two sessions used different ARITHABORT settings, so they had different cached plans. The fix sets SET ARITHABORT ON on every EF command, and the app now gets the same plan.
Exporting all students or reports for a large project failed in a single request.
The export now reads in batches of up to 100,000 rows. It moved from OpenXML to ClosedXML with ID formatting that keeps leading zeros, and the database got a round of new indexes.
The “who hasn't reported” screen was slow, and the reminder action blocked the request while it sent the emails.
Moved the calculation into a stored procedure, stored a timestamp for each reminder, and sent the emails without holding the response.
A salary report failed because an employee's accounting code contained Hebrew characters where the report expected a number.
Hardened the conversion with safe casts (TRY_CONVERT) and a matching database change. The report now handles bad input without failing.
A security review found that a user could change their own role without the required permission, and that uploads weren't checked by type.
Closed the role-change path, validated uploaded file types, hid role and permission data from the configuration API, and removed an unused generic data endpoint.
Closed projects reached 48 tables up to 6 foreign-key levels deep, and some tables had several paths back to the project.
Wrote a Python tool that walks the live FK graph (237 relationships) and generates 6 level-ordered delete procedures and 74 supporting indexes. One orchestrator runs them in a single transaction, in batches of 1,000 rows, and rolls back on any error.
Choose a stage to see what changed and what it bought. Dates and figures come from the repository history.
| Area | Before | After |
|---|---|---|
| Runtime | .NET Core 2.2 (2019) | .NET 8 LTS, EF Core 8, through 3 in-place migrations |
| Permission checks | A wide DB query on every request | 3 narrower queries, then cached and cleared by change notifications |
| Project duplication | 1 query joining 12 collections | 3 queries × 4 includes |
| App vs SQL query time | Fast in SQL, slow from the app | Same plan in both: under 1 s, as measured in SQL |
| Large Excel exports | One request, failing on large projects | Batched at up to 100k rows; 105 export types |
| Report deployment | Manual, once per environment | One script, 11 reports, 4 environments |
| Monthly report cycle | Collected and checked by hand (≈5 working days, estimate) | Web and mobile reporting with validation and batch approval (≈1–2 days, estimate) |
| Retiring old projects | No safe path (≈2–3 days of manual DBA work, estimate) | Generated, transactional purge of 48 tables (<1 hour, estimate) |
Estimates are conservative engineering estimates, marked with “≈”. The rest comes from the code and commit history.
Free scoping call · 30 minutes · No commitment
Write a few lines: the process or system, what hurts today (time, errors, cost or an AI pilot that stalled) and who uses it. After a short call you get a written plan and a fixed quote within ≈3 days.
Opens a pre-filled email · reply within one business day
The model reads. The code counts.
All projects