Lawyer Assignment, Authorization, and Bulk Worklist Safety

Full report

On this page

Recommended control plane

The safest design for this platform is a history-first assignment model with a single database-enforced active assignment per county, paired with server-side authorization checks on every request and operation-specific bulk schemas that fail closed. In practice, that means: keep every county reassignment as a new row rather than overwriting history; enforce “only one active assignment per county” with a partial or filtered unique index; authorize access from current assignment state, not from stale UI state or long-lived county claims; and treat every bulk operation as a separate contract with its own required county-key column such as BulkAssignCountyKey or BulkLaunchCountyKey, never a generic CountyKey. OWASP explicitly recommends deny-by-default authorization, checks on every request, and ABAC/ReBAC-style policies over pure RBAC for application-level object access. NIST defines ABAC as evaluating subject, object, operation, and environment attributes against policy, which fits county assignment well because the key question is always “is this user related to the county’s current assignee right now?”

That control plane should also assume no implicit trust after login. NIST’s zero trust guidance says trust should not be granted implicitly and should focus on users, assets, and resources rather than network location. OWASP’s session guidance separately requires renewing session identifiers after privilege changes and destroying old session identifiers, which is directly relevant when a user loses county access because of reassignment. Combined, these sources support a design in which reassignment immediately changes both the authorization decision surface and the session/token state relied on to reach that surface.

Assignment data model

A strong model is to separate the thing being assigned from the party that can hold assignments. If counties may be assigned either to an individual lawyer or to a law office, use an abstraction such as AssignmentHolder instead of a polymorphic nullable foreign key pair on the assignment table. That keeps authorization simpler, avoids “exactly one of two foreign keys must be non-null” edge cases, and gives one uniform object for policy evaluation and indexing. This aligns with OWASP’s preference for attribute- and relationship-based access control, because the core authorization relationship becomes “county → current assignment → assignment holder → current user membership.”

A recommended conceptual model is:

The most important modeling choice is in CountyAssignment: do not update the current row in place when a county is reassigned. End the old row and insert a new one. OWASP’s workflow guidance recommends explicit server-side state validation and explicit state machines for workflows; history rows make the state transition observable and auditable instead of implicit.

The County table should also carry a small mutable concurrency/auth field such as AssignmentScopeVersion or CurrentAssignmentVersion. That field increments on any assign, reassign, unassign, activation, or holder-membership change that affects county access. Application code can embed that version in form pages, ETags, job envelopes, and ephemeral authorization caches so that stale work is rejected at submit, enqueue, pickup, or download time. SQL Server’s rowversion and EF Core concurrency tokens are a good reference pattern for application-visible concurrency validation even if the actual implementation differs by database.

Database enforcement and index design

The database should enforce the invariant “at most one active assignment per county” directly. PostgreSQL documents that uniqueness limited to only some rows cannot be expressed as a normal unique constraint but can be enforced by a unique partial index. SQL Server documents the same underlying capability through filtered indexes, and its CREATE INDEX documentation explicitly states that for UNIQUE filtered indexes, only the selected rows must have unique index values. That is exactly the right primitive for “one active assignment per county.”

A portable logical rule is:

-- Logical rule
active assignment = EndedAtUtc IS NULL
unique among active rows: CountyId

Typical implementations:

-- PostgreSQL
CREATE UNIQUE INDEX ux_county_assignment_active
    ON county_assignment (county_id)
    WHERE ended_at_utc IS NULL;

CREATE INDEX ix_county_assignment_holder_active
    ON county_assignment (assignment_holder_id, county_id)
    WHERE ended_at_utc IS NULL;

-- SQL Server
CREATE UNIQUE INDEX UX_CountyAssignment_Active
    ON dbo.CountyAssignment (CountyId)
    WHERE EndedAtUtc IS NULL;

CREATE INDEX IX_CountyAssignment_Holder_Active
    ON dbo.CountyAssignment (AssignmentHolderId, CountyId)
    WHERE EndedAtUtc IS NULL;

That active uniqueness index enforces the county-side invariant while deliberately not constraining AssignmentHolderId, which allows one lawyer or office to hold many counties. SQL Server’s unique-index guidance notes that multicolumn and nonclustered unique indexes enforce only the key uniqueness you define; leaving the holder off the unique active key is what preserves the one-to-many side of the relationship.

Database enforcement is necessary but not sufficient; reassignment must also be transactional. PostgreSQL’s row-level locking documentation shows that SELECT … FOR UPDATE locks retrieved rows against concurrent modification until transaction end. SQL Server’s transaction locking guide likewise emphasizes that applications must define consistent transactional sequences and that the engine provides locking and row-versioning to preserve integrity and consistency during concurrent access. The safe reassignment sequence is therefore: lock county or current assignment state, validate current condition, end old active row, insert new active row, increment county scope version, and commit as one transaction.

At the application layer, add optimistic concurrency on top of database constraints. SQL Server rowversion and EF Core concurrency tokens are good examples: the token changes automatically when a row is updated, allowing updates to fail when the record changed between read and write. For this platform, the concurrency token should be checked on county-admin edit screens, bulk preview/apply, and reassignment confirmation screens so that an admin is warned when another admin changed assignment state after the preview was generated.

Two additional index recommendations materially help safety and performance. First, index current-holder lookups because most authorization checks answer “which counties does this holder currently control?” Second, index audit and history access patterns such as (CountyId, StartedAtUtc DESC) and (SourceBatchId, RowNumber) so that investigations do not require slow table scans. OWASP also recommends that access-control decisions be testable and loggable in consistent formats, which becomes much easier when operational queries are straightforward and stable.

Authorization and reassignment rules

The right authorization design is RBAC for broad capability, ABAC/ReBAC for county scope. In other words, “admin,” “operations,” and “lawyer user” remain useful roles, but actual county access should depend on attributes and relationships: the user’s holder memberships, the county’s current active assignment, the requested operation, and sometimes the workflow state. OWASP recommends ABAC/ReBAC over pure RBAC for application development, and NIST’s ABAC definition maps cleanly onto this model.

A practical policy set looks like this:

OWASP is emphatic that permissions must be validated on every request for the specific object being accessed and that deny-by-default should be the baseline. That means every route that serves a lead form, CSV export, PDF report, job result, or static artifact must receive CountyId or an object that resolves to a county and must independently confirm that the requester still maps to the county’s current active holder. The check cannot rely on the fact that the user once opened the page successfully.

Reassignment should be an explicit workflow, not a generic edit. OWASP’s REST guidance recommends modeling workflows explicitly and rejecting invalid or out-of-order transitions. A safe reassignment workflow is: preview current state, present current holder and target holder side by side, require an explicit reassign action and reason, optionally require dual control for high-risk regions, then apply the transaction that ends the old row and creates the new row. If the county is already assigned to the target holder, the workflow should be a no-op or explicit conflict, not an implicit “update anyway.”

To prevent stale users from retaining access after reassignment, the system should separate identity from scope. Long-lived access tokens should identify the user, not carry an authoritative embedded list of county ids. The authoritative county scope must be checked server-side from current data. If the platform uses sessions, regenerate session identifiers after permission changes and invalidate old sessions, exactly as OWASP recommends for privilege changes. If it uses JWTs, keep them short-lived and denylist jti values when explicit session termination or privilege-loss events occur; OWASP’s REST guidance discusses the disconnect that can arise between JWT contents and current session state and recommends denylisting when explicit session termination occurs.

The same principle applies to non-page resources. OWASP warns that static resources are often overlooked in authorization models. For this platform, that means exports, report files, generated documents, zip bundles, and completed run artifacts must be guarded by the same policy check as the dynamic page that linked to them. A presigned or guessable URL should never be enough to retrieve a county-scoped artifact after reassignment.

Forms and queued work need an additional stale-state rule. A user who loaded a lead form before reassignment but submits after reassignment should receive a stale authorization failure, not a success. The easiest implementation is to bind CountyId, CurrentAssignmentId, and AssignmentScopeVersion into the form or job envelope and revalidate them at submit or enqueue. If any value no longer matches current state, the server rejects the action and asks the client to refresh. This is an inference from OWASP’s “validate permissions on every request” and state-machine guidance, and it is the only reliable way to stop stale tabs, queued form posts, and replayed UI actions from writing after scope changes.

Bulk worklist safety model

Bulk operations are where accidental widening is most likely, so the parser must be schema-first, operation-specific, and allowlist-driven. OWASP’s mass-assignment guidance is directly relevant here: do not bind client input directly to domain objects; allow-list bindable fields; and use DTOs containing only intended editable values. Translated to bulk worklists, that means every dangerous bulk operation should have its own DTO and its own required county-key column, with no shared generic identifier that multiple workflows might interpret differently.

A safe naming scheme is:

Under this model, a file containing CountyKey but not BulkAssignCountyKey is not “close enough” for bulk assign. It is rejected. That is the correct fail-closed behavior because OWASP recommends deny-by-default and allow-listed binding, and because a W3C CSV schema can validate that a CSV has the expected column titles and count rather than relying on loose interpretation.

The parser should also refuse to guess too much. RFC 4180 makes clear that CSV headers are optional, and the file itself does not reliably tell you whether the first row is a header unless that is specified out of band. For safety-critical admin operations, that means the platform should require a header row for CSV/TSV bulk operations and require that the selected operation determines the expected schema in advance. No sniffing. No “best effort” column mapping. No synonym resolution between CountyKey, county_key, County Code, and BulkAssignCountyKey for high-risk workflows.

Recommended parsing rules are:

• Normalize only harmless formatting differences such as BOM removal and trailing-space trim.
• Resolve county keys by exact match only against the canonical county dimension.
• Never widen by prefix, suffix, substring, county name, or fuzzy match.
• Treat duplicate rows as errors or explicit dedup warnings shown in preview, never as implicit multi-hit expansion.
• Reject unknown columns for dangerous actions unless the operation explicitly supports an extension area.
• Reject generic county identifiers in assign/reassign/launch/copy/activate flows.
• Map input to operation DTOs, not directly to domain write models.

Pasted text should have the narrowest contract of all: either one county key per line for the selected action, or the same explicit delimited schema as upload files. Free-form pasted text that “looks like a county list” should not be auto-interpreted into assign or launch actions. That recommendation follows OWASP’s repeated guidance to distrust input parameters and to reject unexpected or illegal content.

CSV, TSV, and ZIP support need separate hardening. OWASP’s file-upload guidance says to allow only required extensions, validate type rather than trusting headers, restrict size, and validate ZIPs before extraction, including target path and estimated unzip size. The OWASP testing guide further warns about archive directory traversal and zip bombs. For this platform, ZIPs should therefore be treated only as containers for a small, whitelisted set of expected filenames such as bulk-assign.csv, with no nested archives, no duplicate filenames, no relative paths, and strict entry-count and decompressed-size limits.

If the platform exports bulk templates or results as CSV/TSV for admins to open in spreadsheet software, it should also address CSV injection. OWASP documents that spreadsheet applications may interpret leading =, +, -, @, tab, CR, or LF as formulas or malicious content. That is adjacent to your core concern: a bulk-safety design should protect both against accidental widening on import and against spreadsheet-triggered abuse on export.

Admin preview and save workflow

The admin flow should be deliberately two-phase: parse and preview first, apply second. The preview is not just a UI convenience; it is the main safety boundary against accidental widening and hidden reassignment. OWASP’s REST guidance recommends server-side workflow validation and explicit state machines; a preview/apply workflow is the natural expression of that advice for assignment administration.

A recommended flow is:

Parse and classify. The admin chooses the operation type first, then uploads or pastes data. The server binds only the DTO for that operation, validates content type and size, parses rows, normalizes exact keys, and classifies each row as matched, unmatched, duplicate, already assigned to target, assigned elsewhere, or structurally invalid. No data is changed at this step. OWASP recommends validating request content types, rejecting unexpected inputs, and using secure parsers.

Preview conflicts explicitly. The preview should show per-row current holder, target holder, whether reassignment is required, and whether destructive consequences exist. Counties already assigned to a different holder should land in a visible reassignment bucket with a required reason and explicit confirmation. Counties matched by exact key but inactive, locked, or otherwise not eligible should be blocked. This is where you prevent a generic identifier from silently broadening into unrelated records: every row shows exactly one county resolution or an error.

Apply with re-validation. When the admin clicks Save or Apply, the server must re-run authorization and current-state validation against fresh database state, not trust the preview snapshot. If another admin reassigned a county after preview, that row must fail or the whole batch must fail, depending on the operation’s atomicity rule. For assignment and reassignment, the safest default is all-or-nothing apply, because partial application can hide operator mistakes and leave a mixed state that is harder to reason about. OWASP’s fail-closed and transaction-audit guidance strongly supports this posture.

Record immutable batch results. Whether a batch succeeds or fails, the parsed source hash, parser version, row outcomes, and final apply decision should remain immutable. That lets investigators prove what the admin intended to do, what the parser understood, and what the system ultimately changed. OWASP’s logging guidance explicitly calls out imports, exports, access-control failures, configuration changes, and high-risk admin actions as events that should be logged.

A subtle but important rule is that preview is advisory, not authorization. A user can be authorized at preview time and unauthorized at apply time because of role changes, session changes, or county reassignment that occurred in the interim. The apply endpoint must therefore do a fresh authorization check, just like any normal read or write operation.

Audit requirements

Assignment changes, bulk imports, launches, exports, and post-reassignment access denials should all be treated as security-relevant and business-critical audit events. OWASP’s logging guidance says application logs should record “when, where, who and what” for each event, and that authorization failures, input validation failures, administrative actions, and data import/export activity should be logged. ASVS adds that access-control decisions should be loggable and failed decisions must be logged with metadata sufficient for investigation.

For county assignment changes, the audit record should include at minimum:

• actor user id and authentication context
• UTC event timestamp and interaction/correlation id
• source channel such as UI, API, CSV, TSV, ZIP, or pasted text
• operation type such as assign, reassign, launch, activate, copy, export
• county id and canonical county key
• previous active assignment id and holder
• new assignment id and holder
• reason code and free-text note if permitted
• batch id, row number, file hash, and parser version for bulk actions
• result code such as success, denied, stale preview, stale authorization, validation failure, duplicate key failure.

Logs also need integrity protections. OWASP’s logging guidance says logging mechanisms and collected event data must be protected against unauthorized access, modification, and deletion, and recommends tamper detection, read-only storage as soon as possible, and recording/monitoring all access to logs. OWASP Top 10 2025 separately recommends audit trails with integrity controls such as append-only database tables. For this platform, the safest approach is an append-only audit store plus centralized forwarding to a SIEM or secured log pipeline.

Use UTC for all audit timestamps. ASVS explicitly recommends strongly considering logging only in UTC for global systems to support forensics, which is highly relevant for a nationwide platform that may be operated across time zones.

A final audit rule is to log denials after reassignment, not just successful reassignments. If a former holder tries to open a lead form, download a cached export, or retrieve a completed run after losing access, that denial should be logged as an access-control event tied back to the assignment change. That produces the evidence chain needed to prove revocation worked. OWASP lists authorization failures among the events that should always be logged.

Security and regression test matrix

OWASP recommends unit and integration tests for authorization logic and separately recommends automating authorization testing through an authorization matrix that can be read by humans and machines. For this platform, the matrix should be three-dimensional: feature, logical role, and data scope / relationship, because county assignment is fundamentally data-scoped access rather than just menu-level role access.

A recommended regression matrix is:

Those tests should exist at several layers. The database layer should have concurrency and uniqueness tests. Service tests should verify transaction boundaries and stale-version rejection. End-to-end tests should exercise UI preview/apply and post-reassignment access loss. And security regression tests should be matrix-driven so that every new feature or endpoint must declare its expected county-scope policy before release. OWASP’s authorization testing automation guidance specifically recommends a formal authorization matrix that is both human-readable and machine-consumable so automated integration tests can detect regressions when releases add or modify features.

The central regression principle is simple: every county-scoped capability must be proven to fail for the former assignee immediately after reassignment. If a test suite does not explicitly cover lead forms, reports, exports, generated documents, queued runs, static artifact URLs, and stale form submissions, it is not actually proving revocation safety. That recommendation follows directly from OWASP’s requirement to validate permissions on every request, secure static resources, and create unit/integration tests for authorization logic.

In short, the cleanest implementation is an append-only assignment history, database-enforced single active county assignment, ABAC/ReBAC authorization over current relationships, explicit reassignment workflows, schema-bound bulk contracts with operation-specific county columns, and tamper-evident audit plus matrix-driven regression tests. If those pieces are all present, the platform can safely support many counties per lawyer or office without ever allowing more than one active lawyer/office per county, and without silently widening bulk actions beyond exactly what the admin intended.