SPC Admin Panel

The problem

The existing workflow for updating SPC (Statistical Process Control) configuration was entirely manual: a quality engineer would open SQL Developer, make direct edits to the configuration tables, then notify the rest of the team by email. There was no audit trail, no input validation, no way to propagate a tested configuration across multiple production lines without error-prone copy-paste.

This created recurring problems. Configuration drift between lines went undetected until an audit. Control limit changes couldn’t be traced — when something looked wrong in the data, there was no record of whether the limit had changed recently or who had changed it. Re-qualification after a process change meant manually cross-referencing several tables to reconstruct what the configuration had been at a specific point in time.

The ask was to replace direct database access with an application that made configuration changes auditable, validated, and reproducible.

Constraints

These shaped every decision.

Schema co-ownership. The Oracle schema was shared with two other systems — one that ingested raw measurement data from the line, and one that fed a reporting layer. Restructuring any shared tables would have required coordinated releases across three codebases and a multi-hour maintenance window. The schema was effectively read-only for structural purposes.

Deployment environment. The application had to run on Windows workstations on the factory network. No web server was available or in scope. The choice was desktop or nothing.

Regulatory traceability. Any change to a control limit, measurement plan, or line configuration had to be attributable: who changed it, when, what the previous value was. This was an audit requirement, not a feature request.

Concurrent editing. Quality engineers work across overlapping shifts. The system couldn’t use a checkout-lock model — it would have blocked too often. Multiple users needed to be able to edit simultaneously.

Architecture

Avalonia desktop, direct ODP.NET over Oracle, no ORM.

The existing schema had enough accumulated decisions — nullable compound keys, column names that had outlived their original meaning, implicit join semantics — that mapping it through an ORM would have required more workarounds than the ORM saved. All Oracle access is hand-written SQL, organized into a thin service layer:

Services/
  MeasurementPlanService.cs     // plan definitions, active revisions
  ControlLimitService.cs        // UCL/LCL per characteristic per line
  LineConfigService.cs          // production line metadata and state
  AuditService.cs               // every write passes through here

ViewModels depend on the service layer only. No Oracle calls outside of Services/.

Audit trail at the application layer, not in triggers.

The first design used AFTER UPDATE triggers on the configuration tables to write audit records. This was dropped for two reasons.

First, the shared tables already had triggers from the measurement ingestion system. Adding more compounded a lock contention problem that surfaced during load testing with realistic concurrent writes.

Second, trigger-based auditing captures what changed but not who. The Oracle connection pool used a shared service account, so USER inside a trigger was always the same value. The application layer knew who was logged in; the database did not.

The replacement: every write goes through AuditService, which wraps the operation in an explicit transaction — the target table write and the audit log write are atomic. The audit record includes the table name, row key, field name, old value, new value, timestamp, and the application-layer user identity.

public async Task WriteAsync(AuditEntry entry, OracleTransaction tx)
{
    const string sql = @"
        INSERT INTO SPC_AUDIT_LOG
            (TABLE_NAME, ROW_KEY, FIELD_NAME, OLD_VALUE, NEW_VALUE,
             CHANGED_BY, CHANGED_AT)
        VALUES
            (:tableName, :rowKey, :fieldName, :oldValue, :newValue,
             :changedBy, SYSTIMESTAMP)";

    await using var cmd = new OracleCommand(sql, tx.Connection, tx);
    cmd.Parameters.Add(":tableName", entry.TableName);
    cmd.Parameters.Add(":rowKey",    entry.RowKey);
    cmd.Parameters.Add(":fieldName", entry.FieldName);
    cmd.Parameters.Add(":oldValue",  entry.OldValue ?? (object)DBNull.Value);
    cmd.Parameters.Add(":newValue",  entry.NewValue ?? (object)DBNull.Value);
    cmd.Parameters.Add(":changedBy", entry.UserId);
    await cmd.ExecuteNonQueryAsync();
}

Optimistic concurrency without a version column.

The configuration tables have no ROWVERSION or timestamp column — adding one required a schema change that was in scope, and it became the one structural addition approved for this project.

The concurrency check is a read-before-write comparison. When a user opens a configuration form, the current MODIFIED_AT value is captured. On save, the service re-reads the current MODIFIED_AT and compares:

var current = await _limitService.GetAsync(limitId, tx);
if (current.ModifiedAt != snapshot.ModifiedAt)
    throw new ConcurrencyException(limitId, snapshot.ModifiedAt, current.ModifiedAt);

The UI handles ConcurrencyException by reloading the current record and rendering a before/after diff. The user reviews what changed and decides whether to overwrite or discard their edit. In practice, genuine conflicts are rare — most concurrent edits touch different measurement characteristics — but the failure mode has to be handled explicitly. Silent last-write-wins was not acceptable for an audited system.

Multi-line propagation.

A frequent operation is propagating a validated control limit from a reference line to a group of similar production lines. Doing this as a loop of single-row inserts was too slow for groups of 20+ lines and also created a window where some lines had the new limit and others didn’t.

The implementation uses a pre-flight validation step followed by OracleBulkCopy. Pre-flight checks compatibility (same measurement characteristic, compatible unit of measure, no frozen lines in the target set) and returns a list of rejected lines with reasons before any writes occur. If pre-flight passes, the bulk write is a single batched operation inside a transaction — either all lines get the update or none do.

Pre-flight rejections surfaced two near-misses during rollout: limit updates that would have silently applied to lines frozen for re-qualification. Explicit pre-write validation before bulk operations is worth the extra roundtrip.

What didn’t go well

The audit trail should have been the first service written. It was scoped as a cross-cutting concern and deferred while the core edit flows were being built. When it was time to add it, every write path had to be touched twice — once to make it work, once to make it auditable. Starting with AuditService and building every subsequent write through it would have been cleaner and caught gaps earlier.

Concurrency was underscoped initially. The original design assumed low concurrency based on the existing manual process. When shift-overlap coverage became a requirement partway through, optimistic concurrency was added reactively. The mechanism works, but the ModifiedAt comparison should have been in the service interface from the beginning rather than retrofitted onto specific endpoints.

Avalonia’s binding behavior differs from WPF in non-obvious ways. Several patterns that work in WPF — particularly around INotifyPropertyChanged propagation with nested ViewModels and collection change events — behave differently in Avalonia. These were mid-sprint discoveries that a short throwaway prototype would have caught early.

What worked well

The bounded-context split in the service layer paid off. When a separate reporting initiative needed access to control limit data, ControlLimitService was already a clean extraction point. The reporting layer consumed it directly without touching ViewModels or the UI.

Keeping ODP.NET calls entirely out of the ViewModel layer made the services straightforward to test in isolation using a real test schema on a shared dev instance. The testing overhead was low because the services were thin by design — query, map, return — rather than containing business logic.

The decision to write a standalone migration tool to backfill the new MODIFIED_AT column on existing rows (rather than defaulting to a sentinel value) meant that the audit history was accurate from the first day of production use, not only from the day of deployment forward. That mattered when the first audit happened six weeks later.