Multi-Site Inventory Tracking App

Overview

The ask was straightforward on paper and painful in practice: build a single place where an operations team can quickly answer “what is deployed, where, how, and who owns it?” across many sites.

We evaluated the usual options. The problem wasn’t a lack of tools — it was that none of them matched the required checkboxes without forcing the team into a rigid schema, a heavy process, or a fragmented set of spreadsheets and links.

So we built a lightweight, login-protected portal that combines structured tracking (sites, inventory) with flexible records (for everything that evolves). It includes an admin Settings area for access control, SSL/HTTPS, and operational actions that keep the hosting layer maintainable.

Requirements

• Hit a real-world operational checklist (not a “generic tracker”)
• Multiple sites with nested sub-sites
• Inventory tracked per site plus “what’s deployed here” context
• Track channels and lineups in a way that’s fast to update
• Track files in use (what’s referenced/active per site)
• Track site contacts / ownership (who to call, who approves)
• Flexible per-site records for evolving fields without constant schema migrations
• Admin controls for access, SSL/HTTPS, and operational hosting actions
• Optional Active Directory sign-in (LDAP), while keeping authorization local

What it tracks

The core goal is completeness: if you’re trying to troubleshoot, plan a change, or hand off operations, this captures the “full picture” per site — not just a list of items.

• Sites & sub-sites: a hierarchy that mirrors real-world structure
• Site inventory: categorized items with model/version/quantity and notes
• Channels: channel identifiers and operational attributes
• Lineups: what is scheduled/assigned/deployed at a site
• Files in use: references to active files/assets and where they’re used
• Site contacts: ownership, escalation contacts, and operational notes
• Flexible records: custom record types that evolve as needs change

Data model

The schema stays intentionally small so it remains easy to operate and extend. Structured tables cover the repeatable entities, and JSON-backed records cover the “always changing” details.

• Users: login identities with password hashes and last-login tracking
• Sites: hierarchical site tree via a nullable ParentSiteID
• Inventory: rows attached to a site (category/model/version/quantity + notes)
• SiteRecords: flexible per-site records with FieldsJson validated via ISJSON

Key mechanics:

• Site hierarchy is modeled via a self-referencing FK (Sites.ParentSiteID)
• Site-scoped rows reference Sites.SiteID (cascading delete prevents orphans)
• Indexes support common lookups (by parent site, by site, by category/type)

API design

The backend follows a minimal JSON API approach designed for a small internal UI: predictable endpoints, session auth, and admin-only operations where appropriate.

• login / logout / me: session-based auth and session introspection
• sites: manage facilities and sub-sites
• inventory: manage inventory; supports filtering (site/category)
• records + recordTypes: flexible per-site records with hierarchical site support
• headers: admin-managed UI column/label configuration
• settings + dbTest: operational settings stored on-disk with a DB connectivity test path
• sslUpload + networkingApply: support for SSL/HTTPS and networking workflows
• users + changePassword: user management and password rotation
• stats: dashboard rollups

Implementation notes:

• Requests are routed by an explicit action parameter (e.g., ?action=inventory)
• Database access uses PDO (ODBC/SQL Server), with UI-configured settings and env var fallback
• Protected routes require an authenticated session; admin operations are gated by role/access checks

Browser UI
   |
   | HTTPS (recommended)
   v
Apache HTTP Server
   |
   | Static UI + PHP JSON API (session auth)
   v
MS SQL Server

Operations & hosting

This portal is designed to be easy to run: simple hosting, predictable settings, and admin actions for the operational “sharp edges” (networking, SSL, and access management). It can coexist with other stacks already running in the environment.

Operational priorities:

• Clear separation of logs, runtime config, and service management
• Admin Settings for networking + SSL/cert workflows (upload/store/apply patterns)
• Integrated Apache-side apply actions (admin-only) to keep changes controlled and repeatable
• Access control that supports internal users and admin-only actions

Integrations

Integrations in this setup are intentionally minimal — focused on fast linking and workflow alignment — but the architecture supports expanding into deeper, automated sync when needed.

• Google Drive: quick links to site docs, as-builts, and shared artifacts
• Confluence: documentation pages linked from site records
• Jira: ticket links for installs, maintenance, and incident follow-up
• AppSheet: lightweight task/workflow references (where applicable)

Security notes

This design assumes a production posture where authentication and access are treated seriously:

• Store only password hashes (never plaintext) and require changing any bootstrap credentials
• Prefer HTTPS (certificate + HSTS) for any login-protected UI
• Use least-privilege DB credentials (reader/writer roles only as needed)
• Keep API responses generic on auth failures to reduce account enumeration
• Encrypt sensitive record payloads at rest using an application-level key (AES-256-GCM envelope)

Outcomes

Delivered a practical operational portal that hits the checklist: channels, lineups, site inventory, files-in-use references, contacts/ownership, and flexible records — all organized by a site/sub-site hierarchy.

The end result is faster troubleshooting, cleaner handoffs, and less “tribal knowledge” required to answer what’s deployed, where, and how.