Non-functional spec

10.1 Authentication

Covered in §4.5. In summary: Windows Authentication, AD-group → role mapping, no MFA at the application layer, MFA only at VPN boundary, three service accounts including one with db_owner.

10.2 Authorisation

Application-layer role checks plus database-layer grants. Application and database layers agree for application roles; service accounts have broader database grants than the application uses. Field-level edit rights exist for ~80 % of editable fields; the remainder are governed by screen-level access only. [DB]

10.3 Audit logging

Covered in §4.6. The user_activity_log table covers:

• All login / logout events with timestamp and source IP
• Period-state transitions
• Supplier-master changes
• PO approvals (header level)
• Schema DDL events (auto-captured by a server-level audit trigger added 2019)

It does not cover:

• Read access to financial reports or screens
• Inventory transaction reversals (only the originating event is logged with a reverse marker, not as a separate audited action)
• Soft-deletes of master data (row is flagged inactive; deleting user not captured)
• Service-account activity at the granularity of “what triggered this broker action”

Retention: 24 months at row level. Older events are purged by a nightly job. The database does maintain transaction-log backups for 14 days, which permit forensic reconstruction but require a specialist.

10.4 Concurrency and multi-user behaviour

• Optimistic locking is the default at the row level: a row_version column on every transactional table is checked on update; a conflict raises a user-visible message and the changes are abandoned. [DB, CFG]
• Pessimistic locks are taken by the MRP run (entire planning tables for the duration of the run, ~2.5 hours), by period-close transitions (the period row plus the GL journal cache), and by inventory-revaluation. These are intentional and necessary; they constrain when other long-running batch jobs can run.
• Deadlock incidence: SQL Server log shows an average of ~3 deadlocks per day, all auto-resolved by SQL Server’s deadlock victim selection. None have produced application-visible failure in the 90-day sample. [DB]

The optimistic-locking pattern occasionally surprises users: two operators editing the same item-master row see the second submit fail with a “row has changed” message. The frequency is low (~5 events per month) but the user-facing message is jargony and not localised to the customer’s language. [INT]

10.5 Data volumes and growth

Covered in §6.6. Database is 340 GB, growing ~30 GB/year. The growth rate is dominated by inventory transactions and production events; the financial sub-model grows about an order of magnitude more slowly.

At current growth, the database will reach approximately 460 GB by 2030. Current hardware (DB01: 8 cores, 64 GB RAM, 1.2 TB SSD storage) has headroom for this trajectory. The bottleneck is not capacity but the maintenance window length — index rebuilds during weekly maintenance are already approaching the window edge for the largest tables.

10.6 Availability and uptime

The system has no formal SLA. Observed availability over the last 12 months, per the customer’s incident log [DOC]:

Year	Unplanned outages	Total downtime	Longest single outage
2024	4	19 hours	7 hours (storage failover, May 2024)
2023	6	14 hours	4 hours
2022	9	28 hours	6 hours

Trajectory is improving (incident count decreased; total downtime stable in 2023–24). The 2024 storage failover was the single highest-impact event; the secondary DB02 was successfully promoted but the failback took longer than expected due to log-shipping lag.

Planned downtime (Saturday maintenance windows, ~6/year, ~4 hours each = ~24 hours/year) is not counted in the above.

10.7 Backup and recovery

• Full backup: daily 22:00, written to a NAS share. Retention 30 days on NAS, then weekly retention to LTO tape for 12 months, then quarterly retention to tape for 7 years.
• Differential backup: every 6 hours.
• Transaction-log backup: every 15 minutes.
• Off-site copy: weekly LTO tape rotation to a bank deposit box; latest tape lag is approximately 1 week.

Documented RTO: 4 hours. Observed RTO (2024 actual): 7 hours (storage failover with DB02 promotion; failback took an additional 14 hours but during this window the system was operational on DB02). Last full DR test: 2019, tape-restore-to-running-state, took 9 hours.

Severity-1 risk: backup and DR have drifted from documented procedure. R-04 in §11. Most concretely: the integration broker’s encrypted secrets (§8.2) are not in the backup set as cleartext-recoverable items. A full DB01+APP01 loss event would require credential resets with every external counterparty.

10.8 Performance

Observed performance during the audit (Phase-2 walkthroughs and Phase-3 targeted timing):

Action	Median	90th percentile	Note
Login	1.2 s	2.0 s	Acceptable
Open customer-order list (250 open orders)	0.8 s	1.4 s	OK
Open production-order detail	0.3 s	0.7 s	Fast
Submit goods receipt (5 lines)	1.1 s	2.4 s	OK
Run “stock by location” report	4 s	11 s	Acceptable but trending up
Run “period sub-ledger reconciliation” report	38 s	75 s	Slow but used only at month-end
Run MRP (full batch)	2 h 45 min	4 h 30 min (worst observed in last 12 months)	Within batch window but trending up
Bank-statement import (200 lines)	25 s	50 s	OK

Two performance trajectories are mildly concerning: the “stock by location” report and the MRP run are both growing faster than the database. The MRP growth is the more material; it is the single longest job in the nightly window and is the constraint that holds the close cycle’s day-1 batch within envelope. [DB, perfmon counters reviewed]

10.9 Security observations

This is not a security audit; observations are limited to configuration-level findings.

• Default sa SQL login is disabled. [CFG, OK]
• role_sysadmin_legacy SQL login remains enabled with the original 2008 password. [Sev-1, R-03]
• Service account passwords stored in a KeePass file on a network share; the KeePass master password is shared among 4 IT-team-members. Acceptable for a customer of this size but does not meet the bar for any formal compliance regime.
• TLS for external transports: SFTP and HTTPS web service connections all use current TLS (1.2+); no plain-text transports observed.
• Internal traffic between thick client and database server is over SQL Server’s default TDS protocol, not TLS-encrypted. A LAN attacker could capture credentials and transactional data. The customer’s LAN is single-tenant (one business, one physical site) so the risk is constrained but not zero. [CFG]
• No formal patch policy: OS patches are applied “when there’s a reason”; on the audited hosts, the most recent OS patch was applied 11 weeks ago [CFG]. SQL Server cumulative updates are applied even less frequently — the most recent was 2022.

10.10 Compliance / regulatory

The customer operates in a CEE jurisdiction with VAT reporting, intrastat (for EU cross-border), and statistical reporting obligations. MES-Plus supports these through standard reports (§9). The customer’s accountant verifies regulatory submissions manually; the system itself is not certified for any specific regulation.

The customer is not subject to:

• PCI DSS (no card data in scope)
• HIPAA (irrelevant jurisdiction and sector)
• SOX (private company, not SEC-listed)
• GDPR — applies, but the system’s exposure to personal data is limited to customer-contact records (~600 customers, business contacts only) and employee records (only labour hours of named operators, not full HR data, which lives in payroll). The customer’s data-protection officer has not flagged MES-Plus as a notable risk.

Upcoming regulatory pressure (KSeF in Poland, eFaktúra in Slovakia, evolving CZ requirements) will require integration changes if the customer expands into new e-invoicing jurisdictions or as existing jurisdictions tighten requirements. Treated in §12.