This document is the browser-friendly companion to LANEAWARD_SETUP_LOG.md. The original log remains the chronological source of truth. This reference reorganizes that history into the current architecture, workflows, roles, operating rules, and design decisions so a new maintainer can understand the project without decoding the full timeline first.
LaneAward started in this log as a broader server and reporting effort, then narrowed into a workforce and production-floor system, and finally consolidated around two active frontends backed by one shared SQLite database: the contributor-facing Time On Tasks app and the desktop-first Operations Console. The current design intentionally keeps contributor workflow simple while allowing administration, tester onboarding, and later reporting to grow from the same shared data foundation.
The most important architectural decision preserved across the recent changes is the shared database model. Customer and order search, contributor task setup, user management, and future reporting all depend on the same SQLite file and shared API. That keeps the separate interfaces harmonized without duplicating business rules or reference data.
timeontasks.laneaward.com; Operations Console at console.laneaward.com. Both are backed by one shared Python API and one shared SQLite database. Legacy /workforce/ redirects to root.staging.timeontasks.laneaward.com / staging.console.laneaward.com for development and testing; production at timeontasks.laneaward.com / console.laneaward.com for live operations. Both run on the same AWS server with isolated services, web roots, and databases.Owner can exist; all other roles can have multiple accounts.window.confirm() summary before finalizing so contributors can catch accidental taps.APKGDTL.SHPCHG. Cells and the panel turn red when carrier cost exceeds collected freight. See the Shipping Cost research findings in the Data section for the fulfillment-lag caveat.server.py at 4,627 lines). Both PWA frontends combined total under 7,000 lines of vanilla JavaScript.
The log begins with a broad LaneAward server build on the Ubuntu VM: Nginx on port 8081,
an initial SQLite database named engagement.db, migration scaffolding, and early customer,
order, invoice, and reporting prototypes.
On 2026-03-11 the project pivoted into a dedicated workforce tracking module under workforce_app. This introduced the contributor time-and-materials direction, role hierarchy, task states, and the first SQLite-backed workforce schema.
By 2026-03-24 the project consolidated into the current two-frontend model: the the Time On Tasks contributor app and the Operations Console, both backed by the same API and SQLite database.
Historical note: the old broad reporting prototype and several demo-only design phases remain important as context, but they are no longer the primary runtime model. The current system should be understood as a workforce platform with shared reference data and role-aware interfaces, not as a general CRM or quoting app.
| Frontend split | Time On Tasks at timeontasks.laneaward.com; Operations Console at console.laneaward.com. Staging mirrors at staging.timeontasks.laneaward.com and staging.console.laneaward.com. Legacy /workforce/ redirects to root. |
|---|---|
| Backend | Python HTTP service in workforce_app/backend/server.py. |
| Shared database | workforce_app/data/workforce.db locally. Each server environment has its own isolated database — staging and production do not share a file. |
| Reference data source | ProfitMaker DBF-family snapshots promoted into pm_database, then imported into SQLite. |
| App Launchpad | https://ai.laneaward.com/ — public landing page linking to both production apps, with a Staging tab for developer access. |
| Shared route rule | Canonical API base is /api/; the backend still normalizes legacy /api/workforce/... paths during transition. |
| Environment parity rule | Development, staging, and production should stay aligned on Python version, SQLite version, schema level, refreshed database content, backend code, frontend code, and service configuration so report and workflow behavior stays consistent. |
Mac workspace in /Users/donaldscott/Project-Code/laneaward/repo. Source of truth for app code, docs, scripts, and local SQLite refreshes.
Validation runtime at staging.timeontasks.laneaward.com and staging.console.laneaward.com. Isolated web roots, database, and backend service. All changes are validated here before being promoted to production.
Live runtime at timeontasks.laneaward.com and console.laneaward.com. Same AWS server as staging, fully isolated. Promoted from staging via scripts/deploy_to_production.sh. App Launchpad at ai.laneaward.com.
Audience: manufacturing contributors first, with tightly scoped shop-floor workflow. This is the tablet and mobile-first app used to sign in by PIN, set up workload entries, manage queued and paused tasks, and track active session time.
↻ Setup × 25 · 3m 12s per run · 80m total.window.confirm() before executing to prevent accidental taps on touch screens.Primary source: /Users/donaldscott/Project-Code/laneaward/repo/timeontasks
Audience: administrative and supervisory roles. This is the desktop-first shell used for user management, staging preparation, task-template assignment, and eventually reporting.
View Tasks opens the reusable task-list modal for Team Lead, Foreman, and Manufacturing Contributor accounts.Primary source: /Users/donaldscott/Project-Code/laneaward/repo/ops_console
The contributor app and the console intentionally use different frontend codebases while sharing one backend, one schema, and one SQLite file. This was a deliberate design choice so contributor workflow could stay clean and mobile-first while admin and reporting tools could evolve separately without forking business logic.
The role model evolved from the original OWNER / DEVELOPER / MANAGER / TEAM_LEAD / CONTRIBUTOR
hierarchy and now includes Senior Management and Foreman. The current console rules are the most relevant
operational version for handoff.
| Owner | Highest business authority. Only one owner account may exist. Sees active roles only, and intentionally does not see inactive users or the deactivate button in the console. The backend can still expose broad task visibility to this role, but the intended long-term surface for that visibility is the read-only reports experience rather than the contributor tablet workflow. Console-specific: the Reports tab is the default first tab on sign-in; the Tax column is hidden from the Order Profitability table (the "Tax Collected" metric panel is still visible); the panel label reads "Tax Collected" rather than "Taxes Collected". |
|---|---|
| Developer | Same top-level system authority as Owner for management purposes. Sees inactive users and can see every console document tab, including Topology and Runbook. |
| Senior Management | Can create and deactivate all non-owner roles. Does not see Owner. Can use the console correction workflow on eligible accounts and sees User Guide, Reports, and Reference, but not Topology or Runbook. |
| Manager | Can create and deactivate Team Lead, Foreman, and Manufacturing Contributor users. Does not see Owner, Developer, or Senior Management. Can correct tracked time on eligible accounts and sees User Guide, Reports, and Reference. |
| Team Lead | Can create and deactivate Manufacturing Contributor users only. Does not see higher roles. Can correct tracked time on eligible contributor-style accounts within that scope, but sees only the User Guide tab in the console and does not see compensation data there. |
| Foreman | Shop-floor foreman role modeled after an industry quote-shop foreman. This is still a production contributor role, so it can create work, log time, and use contributor task templates in Time On Tasks. In the Operations Console it inherits Team Lead-level permissions, sees only the User Guide tab, and does not see compensation data there. |
| Manufacturing Contributor | Direct production-floor user. Uses Time On Tasks for workload setup, session timing, and task progression. |
Pay Type or Amount in the Accounts tab or selected-account workspace.User Guide.Reports is limited to Owner, Developer, Senior Management, and Manager.Topology and Runbook are limited to Owner and Developer.Reference is limited to Owner, Developer, Senior Management, and Manager.
The Reports tab now hosts two read-only report drafts behind one Report selector.
Contributor Task Activity summarizes contributor and team-lead workload without disturbing the
shared SQLite data. Order Profitability combines invoice-backed orders and labor-backed
orders inside the selected period, then joins invoice-linked order data so the console can show invoice
number, taxes collected, ship charge (collected), shipping paid, tracked labor cost, current total cost,
retail price, and margin percentage in one place. The Tax column is hidden for the Owner role.
The order profitability draft currently uses these order-level fields from the shared SQLite data:
invoice_number, invoice_date, and billed_total as the current retail
price field. That retail value is imported from the ProfitMaker invoice amount when available, with a
fallback to the order line-total summary when the invoice amount comes through as zero.
Orders can show a Retail Price and Margin % even when no formal AR invoice has been posted —
this is expected behavior. The billed_total field is sourced from the ProfitMaker
order record (ORDER.DBF), which carries the order value from the time the order is created, before an
invoice is formally posted in the accounting system. Margin is calculated as
(Retail Price − Labor Cost) ÷ Retail Price using that order-level value. If the billed amount
is revised before final invoicing, the margin will update on the next reference import. The same report also
uses taxes_collected, shipping_collected (labeled "Ship Charge"), and a reserved
admin_cost column that is not populated yet. Shipping Paid is now a live
computed column sourced from APKGDTL.SHPCHG per order and stored as
shipping_paid in sales_order at import time. Rows and the metric panel turn red
when shipping paid exceeds shipping collected. See the Shipping Cost research findings below for the
fulfillment-lag caveat. Total cost currently reflects tracked hourly labor plus admin cost when it
exists. Salary workers contribute to labor cost using their annual salary ÷ 2,080 as an effective hourly rate
(Task #9 — complete). Changing Period or either
Date Range calendar now refreshes the report data automatically, while the
Refresh button remains as a manual retry control.
Metric cards in both reports use the value itself as the hover/tap trigger for drill-down detail — there is no separate note button. On desktop, hovering the value opens the detail popover; on touch, a tap opens it. The grid fits all nine Order Profitability panels on one row at typical laptop widths (100 px minimum per panel).
Time On Tasks should be treated as an operational contributor tool, not a universal workforce dashboard. Manufacturing Contributors, Foremen, and Team Leads are the intended daily users. Owner and Senior Management may still benefit from broad task visibility in the shared backend, but that visibility is being preserved primarily so the future reports view can show the full picture of open work without re-implementing task logic somewhere else.
When a contributor forgets to start, pause, or stop on time, the correction path is intentionally human-first: the contributor reports it to a Team Lead, Foreman, or Manager, and leadership applies the fix in the Operations Console with a required reason and an audit trail.
This project deliberately uses one shared SQLite database for all active interfaces. That decision supports a harmonious relationship between contributor workflow, user management, and future reporting.
The import path is not trying to clone the full source system. Its current high-value purpose is to keep contributor-facing search useful by importing current customers, order numbers, and ranked order descriptions.
customer_account rows.sales_order rows.LNITM.DBF so hover/detail text uses real product descriptions.asidta_file_* snapshot is auto-detected if no folder is passed.Copy of ... are ignored.Key scripts: refresh_workforce_reference_snapshot.sh, refresh_workforce_reference_data.sh, import_profitmaker_reference.py
A full investigation was conducted into the discrepancy between Shipping Collected and Shipping Paid in the Order Profitability report. Here is what was found and what decisions were made.
The report initially sourced shipping paid from FFSHPLOG.PLPOST, which captures only USPS postage.
97% of records in that table are $0.00 — it misses UPS, FedEx, and all other carriers entirely.
For Jan 1–Apr 14 2026, this produced a shipping paid total of only $352.97 against
$8,412.87 collected — a 23× understatement.
Shipping paid is now sourced from APKGDTL.SHPCHG, which records actual carrier costs
(UPS, FedEx, Ground, Air, etc.) per package, linked to orders via PKORDNO.
Voided records (VOID = 'Y') are excluded. This is the most complete per-order shipping cost
record available in ProfitMaker.
Even with the correct source, shipping collected and shipping paid will rarely match within a narrow date window. This is a known business reality, not a data error:
897 of 1,403 orders placed Jan–Apr 2026 have no corresponding APKGDTL record, yet collectively
show $7,013 in freight collected. All other ProfitMaker tables were checked
(FFSHIPTO, FFSHIPHT, FFSHPLOG, COSTORD) — none contain
carrier cost data for these orders. Likely causes:
Action item for discussion: Determine whether carrier invoices (UPS, FedEx, freight carriers) are reconciled in any external system (accounting software, spreadsheet). If so, that data source could be integrated to give a complete shipping cost picture. Until then, the report reflects the most accurate data available inside ProfitMaker and should be interpreted over longer date ranges (quarterly, annual) where the fulfillment lag averages out.
A user_task_template table was added so contributor users can have reusable task descriptions
associated with them. These are created and managed from the console and then appear as fuzzy-search options
inside Workload Setup in Time On Tasks.
A default standard set was seeded on 2026-03-24, including Order Intake,
Artwork Preparation, Assembly, Packaging, and
Shipping, with room for more contributor-specific templates over time.
The Time On Tasks workload setup fields accept customer codes and order numbers that are not yet present in the local SQLite database. This is by design, so contributors are never blocked when an order is new, freshly entered in ProfitMaker, or simply not yet reflected in the latest snapshot. The following describes exactly what happens at each stage and what is safe to rely on.
Customer and order search queries hit /api/customers/search and /api/orders/search
using LIKE-based fuzzy matching against imported ProfitMaker rows. The search requires at least
two characters and returns up to ten ranked matches. If no suggestions appear, the contributor can type the
value directly and proceed — a missing suggestion does not block task creation.
When a contributor adds a task with a customer code or order number that does not yet exist in SQLite,
the backend's ensure_customer_account() and ensure_sales_order() functions
(in server.py) silently create lightweight stub records:
display_name = "Customer {code}", data_source = "LaneAward".description = "LaneAward task queue for order {number}", lifecycle_status = "READY_TO_START", data_source = "LaneAward".
When the ProfitMaker reference import runs (import_profitmaker_reference.py), all customer and
order inserts use ON CONFLICT(source_customer_code / source_order_number) DO UPDATE SET ....
If the manually entered code or order number now appears in the ProfitMaker snapshot, the existing stub row
is enriched in place: real display name, segment, lifecycle status, invoice number, billing totals, shipping
data, and fulfillment mode all fill in. The database primary key (id) does not change.
Because all order_task and work_session rows reference order_id and
customer_id as foreign keys, they automatically reflect the enriched data the moment the sync
completes. No explicit merge, re-link, or data-migration step is required.
The prune_reference_orders function only removes orders that carry reference_only = 1,
match the ProfitMaker data source, are absent from the current import window, and have no attached tasks,
sessions, or material usage. A stub order that had any work logged against it will never be pruned — the
presence of a task or session record is a hard guard.
At task-creation time the server prevents assigning an already-known order to the wrong customer. However,
if both the customer code and order number are new (both create stubs), and the typed combination does not
match what ProfitMaker later says, the sync upsert will silently correct the customer_id on
the order row to the ProfitMaker value. The tasks and sessions already attached to that order will then
appear under the corrected customer name. This is the correct outcome in most cases, but it means typed
customer-and-order pairs that are genuinely wrong will self-correct at sync rather than producing an error.
If accurate attribution before the first sync matters, confirm the customer code against the ProfitMaker
source before typing it in.
The ProfitMaker reference import uses a rolling date window to keep the local SQLite database focused on current and near-term orders. As of 2026-04-24 this window spans 36 months (extended from the original 12 months) and the prune logic was hardened to also respect lifecycle status. Both changes were made together because either one alone leaves a gap.
The 12-month window was sufficient when all orders completed within a year, but LaneAward serves customers with long-cycle manufacturing orders, standing credits, and unfulfilled inventory that can span multiple years. Two failure modes were identified:
A reference-only order is eligible for pruning only when all three of the following are true:
order_date falls outside the 36-month rolling window.lifecycle_status is SHIPPED — the only terminal state the ProfitMaker import produces. Orders in OPEN or AWAITING_SHIPMENT status are never pruned regardless of age.The 36-month window acts as the baseline safety net and covers the vast majority of real-world order cycles without depending on upstream data quality. The lifecycle status condition provides the additional layer of protection for genuinely long-cycle or anomalous orders that outlive the window.
The lifecycle status gate introduces a dependency on ProfitMaker data accuracy that the automated import cannot resolve on its own. If an order is genuinely fulfilled but was never properly closed in ProfitMaker, it will remain in the LaneAward database indefinitely — the prune guard will protect it forever. Over time, without a periodic human review, this creates an unbounded accumulation of stale open-status rows.
The required human step: on a periodic basis (recommended quarterly), someone with ProfitMaker access should run a review of orders that are marked open or active in ProfitMaker but have had no invoice activity, shipment activity, or production work for an extended period. Orders confirmed as genuinely complete should be formally closed in ProfitMaker. The next reference import will then see the terminal status and allow normal pruning to proceed on the next cycle when the order also falls outside the 36-month window.
This step cannot be skipped or delegated to the import script. LaneAward reads ProfitMaker status — it does not write back to it. The authoritative close action must happen in ProfitMaker first.
app_user, work_session, order_task, and activity tables were not modified should appear on every reference-only sync. Its absence indicates the sync touched contributor data, which warrants investigation.
The project is maintained under local Git at
/Users/donaldscott/Project-Code/laneaward/repo/. Git is a tool that takes
named snapshots of the project files. Each snapshot — called a commit —
preserves the exact state of every tracked file at the moment it was taken. Older
snapshots remain available indefinitely, which makes it possible to view what the project
looked like at any past point, see exactly what changed between any two points, and
return the working files to any prior snapshot in seconds.
Every commit is preserved. The full project state at any past commit can be inspected with git log and git show.
If a change breaks something, git checkout main restores all working files to the most recent main commit. No manual undo.
Parallel work streams. git checkout -b feature/x creates a new branch for experimental edits without disturbing main. Switching branches is instant.
The project intentionally uses Option A — 100% local Git. No GitHub,
GitLab, or any remote server is configured. All snapshots, history, and branches
live inside the .git folder of the repo on the development Mac.
Nothing about the source code or its history leaves the machine through Git.
Off-machine history backup is provided through the same channel that already backs up
source code: the PWA BDR weekly source snapshot. As long as the
snapshot captures the .git folder, history is preserved off-machine.
If a private remote (e.g. private GitHub repository) is ever desired for additional
backup or collaboration, it can be added with a single git remote add
command without changing any other behavior.
Git does not modify working files unless explicitly told to (commit,
checkout, reset). git status and
git diff are read-only. Committed snapshots cannot be lost through
ordinary operations — only the deliberately destructive commands
git reset --hard and git push --force can discard work,
and neither is used in this project's workflow.
The .gitignore file at the repo root defines what Git
never tracks. Sensitive artifacts (SSH keys,
.env files, large binary data such as the pm_database/
DBF tree, OS metadata like .DS_Store) are excluded so they cannot be
accidentally committed into history.
The everyday loop is four commands:
git status # see what changed
git diff # review the actual edits
git add <file> # stage the change
git commit -m "..." # take a named snapshotEditing files between commits is unchanged from working without Git. Only deliberate save points create commits.
For changes intended to be validated on staging before reaching production:
git checkout -b staging/some-change
... edit, commit ...
... deploy that branch to staging, test ...
git checkout main && git merge staging/some-change
... deploy main to production ...If staging surfaces a problem, git checkout main instantly restores the production-equivalent files on the Mac. Nothing about the live environments is affected by branch operations.
Deploy scripts (scripts/deploy_*) read from the working tree
— the actual files in /Users/donaldscott/Project-Code/laneaward/repo/ —
not from Git directly. The currently checked-out branch determines what the deploy
scripts see. Before any deploy, confirm the branch is the intended one with
git branch --show-current.
Each environment has dedicated web roots on the AWS server. Files are promoted via rsync from the Mac repo. Production is deployed using scripts/deploy_to_production.sh or the individual app deploy scripts.
The VM service is laneaward-workforce-api.service. Nginx fronts the static apps and proxies API requests to the Python service.
Staged databases are promoted with the VM cutover script, which validates, backs up, swaps, restarts, and verifies the service.
customer_account = 3245 and sales_order = 4912 rows imported from ProfitMaker./workforce/ is no longer the main contributor route and now redirects to root.502 Bad Gateway responses observed during backend restarts are restart-window artifacts, not persistent failures.The AWS server is protected by two independent layers of access control. Both layers must be understood before making any changes to server access or network configuration.
A single security group (sg-0cc9719fa0e029c40, named launch-wizard-1)
is attached to the instance. It is the only group attached — the default VPC security group is not.
SSH (port 22) is restricted to three specific IP addresses. HTTP (port 80) and HTTPS (port 443)
are open to the public. All other ports are closed by default.
A Twingate connector (eggplant-okapi) runs on the Ubuntu server as a systemd
service. It dials outbound to Twingate's relay — no inbound port is required. Administrators
connect through the Twingate client on their device, which tunnels SSH traffic through the
relay to the connector regardless of the administrator's current IP address.
| COX Fiber — office | 98.175.1.150/32 |
|---|---|
| COX Cable failover — office | 72.215.199.214/32 |
| Home lab (dynamic — pending removal) | 72.208.129.218/32 |
The home lab IP is a dynamic residential address and should be removed from the security group once Twingate is confirmed as the primary home access path. Twingate eliminates the need for static IP-based home access entirely.
| Remote Network | Lane Award PWA Server (AWS) |
|---|---|
| Connector | eggplant-okapi — Online |
| Resource | SSH — Lane Award PWA Server |
| Resource address | 172.31.7.224 (private IP) |
The resource uses the server's private IP, not the public IP. This is required because the connector runs on the same machine — routing through the public IP would loop externally and fail.
| COX Fiber (primary) | Static IP 98.175.1.150 · Subnet 255.255.255.252 · Gateway 98.175.1.149 |
|---|---|
| COX Cable (failover) | Static IP 72.215.199.214 · Subnet 255.255.255.240 · Gateway 72.215.199.209 |
| ISP | COX Communications, Phoenix AZ · 1G Fiber primary, 512 Mb Cable failover on site |
SSH port 22 is not exposed to the public internet. Only the three authorized IPs can
reach port 22 directly, and all three require a valid .pem key to
authenticate — an IP address alone is not enough. Twingate adds a second access path that
requires authentication through Twingate's identity layer before any session can be
established. Automated scanning attacks and brute-force attempts against port 22 are
blocked entirely by the security group before they reach the operating system.
The Twingate connector requires no inbound security group rules of its own — it dials outbound, so the attack surface is not increased by running it. Outbound traffic from the server is unrestricted (required for Twingate relay connections, package updates, and API calls).
Security group changes are made via the AWS CLI using IAM user donald
(AmazonEC2FullAccess policy, region us-east-2).
Root account access keys are not used. The CLI is configured on the development Mac.
AWS CloudShell in the browser console is the fallback when local CLI access is unavailable.
See the Production Runbook Process 10 for step-by-step procedures for SSH access and
security group updates.
datalist were resolved by replacing them with custom lookup menus.window.confirm() so a mistouch on the shop floor does not silently end a session. Repeat tasks extend this with a calculated-total summary in the confirm text.sounds/ directory: Start/Resume = Contact, Pause = Meteor, Stop = Nebula, Cancel = Chaos. The Android path is necessary because the Web Audio API gesture-context expires across await fetch() boundaries on Android Chrome; installed-PWA elevated autoplay permissions allow new Audio().play() to fire reliably after async API calls. Cancel received a sound cue for the first time on this release. All gain values were doubled from the original values to improve audibility on shop-floor tablets.overflow-y: scroll on the body element permanently reserves the scrollbar gutter, preventing the page from shifting left when the native scrollbar appears or disappears. The scrollbar track is styled to match the page background so the reserved space is invisible. This fix applies to both Time On Tasks and Operations Console.001.version.json file fetched on every load and on tab-focus return. Two update types are supported: a soft reload (network-first cache refresh, no data loss) and a hard reinstall (service worker unregister + full cache clear, required for breaking changes). The reinstall flow will show a modal with step-by-step instructions for re-adding the PWA icon to the home screen on Samsung tablets — implementation pending tablet verification. An orange dot badge on the Time On Tasks hamburger icon surfaces the update notice without requiring the menu to be opened. The version number is maintained in version.json alongside a reinstall boolean flag; bumping that file is the only operator action required to trigger an update for all users.customer-group-a / customer-group-b CSS classes. This gives readers a horizontal guide rail across wide rows. The original implementation shaded by customer group (all rows for the same customer shared one shade), but this was replaced with standard index-based alternation because the table is already sorted by Customer# — repeated customer orders appear together naturally and are easy to read without special grouping logic. No backend or schema changes were required.▾ caret indicator was added to the right of the user name in the selected-account tab to make it visually clear that the tab has a dropdown menu with additional sections (Profile, Teams, Time Corrections, Order Number Corrections).text-align: center on <button> elements. Fixed using display: flex; justify-content: center on the button and an inner <span> as the text container. Applies to report metric cards and any future value-trigger buttons.applyBootstrap() which runs on every save action. This is the correct pattern for any future role-specific default navigation.overflow-y: scroll on the body element permanently reserves the scrollbar gutter so the page does not shift horizontally when switching between tabs with different content heights. The scrollbar track is styled to match the app background. Same fix applied to Time On Tasks simultaneously.
The project intentionally treats this as a web app rather than a static mock. A web-app manifest, Home Screen
icons, Apple metadata, and iPad standalone refinements were added so tablet install behavior and coarse-pointer
layouts remain workable. Full offline support is not implemented, but a service worker update check system was
added in April 2026: both apps register a network-first service worker and poll a static version.json
file to detect when a new build is available. See the PWA update check system entry in Console Decisions for
the full design detail.
Both Time On Tasks and the Operations Console are protected by a 6-digit PIN authentication system. The core security layer (Argon2id hashing, lockout, HTTPS) was implemented 2026-04-30. A login performance fix (HMAC-SHA256 lookup token) was added 2026-05-01. This section documents the full security implementation, the rationale behind each design decision, and the protections now active in production.
secrets.randbelow(), not randomThe applications serve a small, known workforce (maximum 10 active users) on a private manufacturing network. Exposed data is operational — task timers, order status, and team assignments. No financial transactions, no PII beyond employee names, and no external customer access. The threat model is proportionate: the primary risk is unauthorized internal access, not external attack.
This context informed the decision to implement strong hashing and brute-force protection without requiring multi-factor authentication or certificate-based identity — both of which would create operational friction on the shop floor that outweighs the marginal security benefit at this scale.
Four hashing algorithms were evaluated: Argon2id, bcrypt, PBKDF2+SHA-256, and scrypt. Argon2id was selected because it is the current recommendation of OWASP, NIST, and the 2015 Password Hashing Competition. Its key advantage over bcrypt and PBKDF2 is memory-hardness: each verification requires a configurable amount of RAM, making large-scale parallel cracking — including GPU and ASIC attacks — significantly more expensive than algorithms that require only CPU time.
Parameters were determined by running a benchmark on the production server (AWS Graviton ARM64) against a 300ms user-experience budget. Results:
| time_cost (t) | 3 iterations |
|---|---|
| memory_cost (m) | 65,536 KB (64 MB per hash) |
| parallelism (p) | 1 thread |
| hash_len | 32 bytes (256-bit output) |
| salt_len | 16 bytes (128-bit, unique per hash) |
| Measured latency | 229 ms on production server |
| Budget headroom | ~70 ms remaining before 300ms threshold |
These parameters meet OWASP's "interactive login" recommendation and provide meaningful resistance against brute-force attacks: at this cost, an attacker can attempt approximately 4–5 hashes per second on comparable hardware — requiring hours per user to exhaust the full 1,000,000 PIN space offline.
Existing plaintext PINs were not force-migrated. Instead, the backend uses a dual-path approach: when a user logs in, the stored value is inspected. If it is a 6-digit plaintext PIN, it is verified using the original constant-time comparison, then immediately replaced with an Argon2id hash before the response is returned. If it is already a hash, Argon2id verification is used directly. This means every user's PIN is automatically and silently upgraded on their first login after deployment — no reinstall, no reset, no user action required.
After deploying Argon2id hashing, a performance regression was identified: the anonymous PIN login path
(authenticate_active_user_by_pin()) loaded all active users and ran a full Argon2id verify
on each one sequentially until finding a match. With 13 active users and 229 ms per verify, the worst case
was approximately 3 seconds. This was confirmed by live timing (2.97 s for a user near the end of the list).
The fix adds a HMAC-SHA256 lookup token stored alongside the Argon2id hash in a new
pin_token column. The token is computed as
HMAC-SHA256(PIN_PEPPER, normalized_pin) using a fixed server-side pepper constant
(PIN_PEPPER = "laneaward-pin-pepper-v1"). Login now works in three steps:
WHERE pin_token = ? AND is_active = 1 via a partial index — under 1 msTotal login time is now approximately 230 ms regardless of how many active users exist. Measured in production: 258 ms.
Transition: Existing users have pin_token = '' after deploy. On their first
login, the fast path finds no match, and a slow fallback scans only un-migrated users
(WHERE pin_token = ''). The fallback writes the token immediately, so the user takes the fast
path on every subsequent login. The fallback pool shrinks with each login until it is empty.
PIN uniqueness check: allocate_unique_pin() was updated to query
WHERE pin_token = ? instead of the raw hash column, restoring correct collision detection
after hashing made the old WHERE pin_code = ? check meaningless.
Important: PIN_PEPPER must never be changed after first deploy. Changing it
invalidates all stored tokens and forces every user back to the slow fallback path until they log in again.
It is a named constant with an explicit comment to this effect in server.py.
As part of the same session, the AWS security group (sg-0cc9719fa0e029c40) was reviewed
and port 80 (HTTP) was removed. The server now accepts inbound traffic on port 443 (HTTPS, open to
public) and port 22 (SSH, restricted to three named IPs only). Port 80 was not in use — all traffic
is served over SSL — and the open rule represented an unnecessary exposure.
2026-04-30 — Argon2id hashing: Deployed to staging first and validated end-to-end: PIN login via Time On Tasks, PIN login via the Operations Console, hash verification in the SQLite database confirming Argon2id storage, and a second login confirming the verify path. Both applications were tested on tablet hardware (Samsung) and desktop. All active user accounts were updated through normal login or manual PIN reset within the same session.
2026-05-01 — HMAC lookup token performance fix: Deployed to staging and validated with a
timed curl test (256 ms, down from 2.97 s). Token population was confirmed in the staging
SQLite database for all five test accounts, with HMAC values independently verified against
PIN_PEPPER. Production deployment followed immediately; measured login time 258 ms.
The following items are approved in direction but not yet implemented. They represent the next logical development phase before the system is considered production-ready for full go-live.
Implemented 2026-04-19. Salary workers now contribute to labor cost in the Order Profitability report
using the U.S. BLS standard: Annual Salary ÷ 2,080 = effective hourly rate, applied
against tracked session minutes. No schema changes were required — the existing pay_type
and pay_rate fields on app_user were sufficient. The
user_pay_rate_history table was not needed for this phase.
The "Pending" placeholders have been removed from the console UI. Base wage only —
burden rate (taxes, benefits, ~25–35%) is documented as a future discussion item.
Implemented 2026-05-01. After Argon2id hashing was added, the anonymous login path degraded to
~3 seconds because it scanned all active users sequentially. Fixed by adding a
pin_token column (HMAC-SHA256 of the PIN against a server-side pepper) that allows
the backend to identify the correct user with a single indexed SQL lookup before running one
Argon2id verify. Login time is now ~230 ms regardless of user count, measured at 258 ms in
production. The pin_token column and partial index are applied automatically via
ensure_schema_upgrades() on service startup — no manual migration required.
Phase 1 hardening is complete and validated. WAL mode enabled; busy_timeout = 10000;
synchronous = FULL; multi-step writes wrapped in BEGIN IMMEDIATE transactions;
contention returns retryable HTTP 503. The backend surfaces a clean 503 on lock contention rather than
an unhandled exception.
Phase 2 (client-side retry/backoff, offline-safe write handling, idempotent write keys) was evaluated and deferred. A concurrent stress test at 2× the expected user load passed cleanly with significant headroom — Phase 1 alone is sufficient at current scale. Phase 2 should be reconsidered only if load grows significantly beyond current projections.
Selected approach before go-live: add a user_pay_rate_history table where each rate change
is an INSERT (effective date + amount + pay type), and a denormalized cache column on app_user
holds the current rate for fast reads. Reports join to the history table to find the rate in effect at
the time each work session started. This enables accurate annual and semi-annual labor cost reports even
after pay rate changes.
Four nullable columns were added to order_task as part of the repeat multiplier feature
(2026-04-14). All four use ADD COLUMN with no NOT NULL constraint so the migration
runs without a table rebuild:
repeat_action TEXT — label set by the contributor (e.g. "setup")repeat_quantity INTEGER — number of iterations (≥ 2); NULL = not a repeat taskrepeat_iteration_minutes REAL — actual duration of one timed run, written at Stoprepeat_total_minutes REAL — round(repeat_iteration_minutes × repeat_quantity, 2); can be manually overridden by a manager in the Operations Console Time Corrections modal
The schema migration must be run via ALTER TABLE on both the staging and production databases
before deploying any backend changes that reference these columns.
Do not split the contributor app, console, and reporting logic onto separate databases unless there is a compelling operational reason. The project is cleaner because user records, task templates, and reference search all live in one place.
Update both the chronological log and the browser-facing docs whenever routes, roles, scripts, or workflows change. The handoff burden grows quickly if changes land only in chat or in code comments.
Use the VM cutover and status scripts, refresh reference data before testing, and validate the actual staged app behavior on Safari and tablet-class devices before treating a change as ready.
Recommended next steps before go-live: complete the Pay Rate History table (Option B) and validate the Task Repeat Multiplier end-to-end on staging. This reference page is intended to be the first-stop overview for anyone inheriting the project.