Multi-Site Inventory Tracking Portal

Chapter 1: Overview

The ask looked straightforward on paper and was 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.

Chapter 2: 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

Chapter 3: 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

Chapter 4: 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)

Chapter 5: 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

Chapter 6: 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

Chapter 7: 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)

Chapter 8: 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
• If sensitive record payloads must be stored, encrypt at rest using an application-level key (e.g., an AES-256-GCM envelope)

Impact

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.