Designing a Vanilla PHP Lead Management System for LongTermCapabilities

Full report

On this page

Executive summary

LongTermCapabilities is publicly reachable, and its current public site presents a very specific operating posture: it is described as “static-first,” uses local assets and local search, and publicly states that it does not configure external analytics or a third-party form processor. Most importantly for this project, the public contact experience currently does not submit to the website or store inquiry data; instead, it validates fields in the browser and opens the visitor’s email application with a structured draft to consulting@longtermcapabilities.com. The same public pages emphasize a “public-safe qualification only” boundary and instruct visitors not to send credentials, private source code, customer records, PHI, financial account data, classified/CUI/export-controlled material, or confidential production architecture through public forms or ordinary email.

That current posture has a major architectural consequence: the new admin lead system should be designed as a separate internal application with its own authentication, audit trail, and MySQL database, while preserving the public site’s trust model unless and until LongTermCapabilities explicitly decides to change it. In other words, the safest initial rollout is not “turn the current form into a database-backed submission form on day one.” The safest rollout is: keep the current public email-builder boundary, let staff manually enter or import leads from email/phone/public-safe inquiries into the admin system, and only later add an optional server-side capture endpoint for public-safe fields only if the business wants that workflow and is prepared to update the privacy and trust disclosures accordingly.

For implementation, a rigorous and maintainable v1 can be built using vanilla PHP + MySQL + vanilla JavaScript with a front-controller PHP application, PDO prepared statements, PHP sessions with strict cookie settings, CSRF tokens, HTML output encoding, and MySQL foreign keys and composite indexes. MySQL’s documentation supports utf8mb4, InnoDB foreign keys, composite indexes with leftmost-prefix behavior, generated-column indexing for JSON-derived values, and InnoDB FULLTEXT indexes on CHAR/VARCHAR/TEXT. PHP’s core manuals support password hashing and verification, cryptographically secure random bytes, prepared statements, sessions, and file-upload handling without external frameworks. OWASP guidance strongly supports prepared statements, output encoding, CSRF tokens, controlled file uploads, audit logging, login throttling, and session timeout rules.

My recommendation is a two-track design. Track one is the internal lead console: lead intake, deduplication, status/workflow, assignment, notes, activity log, imports/exports, saved filters, and notifications. Track two is controlled public integration: first manual intake from inbound email and phone; later, if desired, a public-safe POST /inquiry endpoint that stores only the same categories already represented on the existing contact form and preserves the site’s boundary against sensitive submissions. This keeps implementation aligned with the site’s current language about least authority, environment separation, logging, exportable artifacts, and non-lock-in handoff.

A realistic estimate for a production-ready v1 by one experienced developer is about 8 to 12 developer-weeks, assuming no framework, a modest number of admin users, and a conventional VPS deployment. A prudent roadmap is: core auth/RBAC and schema; lead CRUD and workflow; imports/exports and dedupe; notifications and attachments; then hardening, migration, restore drills, accessibility review, and UAT. That estimate is my implementation judgment rather than a vendor quote.

Site audit and integration points

What is publicly observable today

The public route index says the site is generated from “canonical route and content data” and includes buyer paths, services, evidence, resources, policies, and multiple machine-readable surfaces such as Services.Json, Capabilities.Json, Evidence.Json, Resources.Json, Insights.Json, Trust.Json, Llms.Txt, Sitemap.Xml, Feed.Xml, and Security.Txt. The homepage, sitemap, privacy notice, accessibility statement, and trust center all reinforce the same operating model: static-first public pages, local search, no configured external analytics, no hosted search, no third-party form processor, and no public chatbot/live transport.

The publicly visible contact page contains structured inquiry fields for three inquiry types—enterprise, government/prime, and partner/subcontract—and captures name, work email, organization, role, preferred reply type, service interest, system/workflow, blockers, decision dates, budget range, public solicitation URL, and a public-safe summary. It also includes a honeypot-style Website field, a public-safe confirmation checkbox, and buttons that explicitly “Copy complete inquiry,” “Copy email address,” and “Open structured email.” The page expressly states: “The form builds an email in your browser. It does not send or store the inquiry.”

This means the public website already has a strong Lead Capture Data Model hiding in plain sight: inquiry type, service interest, organization, contact identity, public-safe problem summary, timing, and budget. That is enough to design the admin system around the current public semantics rather than inventing a completely different taxonomy. It also means the current trust documents are not incidental marketing copy; they are operational boundaries that should shape the lead system.

Likely integration points

What was not available

No public admin interface, login route, or source repository was available from the public route index or crawled pages, so this report assumes no access to the site’s hosting control panel, codebase, or hidden admin routes and limits the audit to publicly visible structure and machine-readable surfaces.

Integration strategy alternatives

Product design and user experience

Proposed feature set

The admin system should behave like a lightweight opportunity console, not a generic mass-market CRM. LongTermCapabilities appears to be a high-consideration, low-volume, expert-led consulting practice with bounded offers, public-safe first contact, and a strong evidence/trust posture. The design should therefore optimize for clarity, traceability, and controlled handoff, not social-sales automation.

User roles and permissions matrix

The trust center explicitly mentions least authority and role separation; the lead system should mirror that with role-based access control rather than a binary admin/non-admin split.

A practical implementation detail: treat “delete” as a soft-delete/archive capability in most cases, with true hard-delete reserved for super admins and only after an explicit secondary confirmation.

Lead lifecycle

The workflow should be explicit and auditable. OWASP’s business-logic guidance stresses enforcing workflows as explicit state machines rather than “letting the UI gate the order of steps,” which fits this use case well.

flowchart TD
    A[Inbound inquiry or manual intake] --> B[Normalize and dedupe check]
    B -->|Exact or high-confidence match| C[Merge or append to existing lead]
    B -->|No strong match| D[Create new lead]
    D --> E[New]
    C --> E
    E --> F[Triage]
    F -->|Not relevant or unsafe| G[Disqualified or Spam]
    F -->|Potential fit| H[Qualified]
    H --> I[Assign owner]
    I --> J[Discovery]
    J --> K[Proposal]
    K -->|Accepted| L[Won]
    K -->|Declined or no decision| M[Lost]
    K -->|Paused| N[Dormant]
    N -->|Reactivated| F
    G --> O[Retain per policy then purge or archive]
    L --> P[Handoff to delivery records]

UI and wireframe descriptions

Dashboard

The dashboard should answer four questions immediately: what is new, what is aging, who owns what, and what needs a decision this week.

+----------------------------------------------------------------------------------+
| Logo | Leads | Imports | Exports | Reports | Admin | Search [..................] |
+----------------------------------------------------------------------------------+
| New today: 4 | Unassigned: 2 | Qualified: 6 | Proposal: 3 | Aging triage: 5      |
+----------------------------------------------------------------------------------+
| My queue                          | Team queue                   | Notifications   |
| - New enterprise lead             | - Unassigned govt inquiry    | - Lead reassigned|
| - Proposal due in 3 days          | - Duplicate review pending   | - Import finished|
| - Dormant lead reactivated        | - Aging > 7 days             | - Export ready   |
+----------------------------------------------------------------------------------+
| Recent activity timeline                                                          |
| 09:42 Lead #241 created from email                                                |
| 09:58 Lead #198 moved to Proposal                                                 |
| 10:05 Import job #12 completed: 34 inserted, 6 merged, 2 flagged                 |
+----------------------------------------------------------------------------------+

Lead list

The lead list is the main “workbench.” It should be server-rendered HTML first, then enhanced with fetch-based filtering, inline status changes, selection checkboxes, and bulk actions.

Filters: [Stage v] [Owner v] [Inquiry type v] [Service v] [Source v] [Date range]
Search: [organization / name / email / summary.................................]
Bulk: [Assign] [Move stage] [Tag] [Export] [Archive]

[ ] #241  New         Acme Health      Jane Doe      Enterprise  AI Readiness   Unassigned
[ ] #198  Proposal    State Agency     John Smith    Government  Modernization  Mike
[ ] #177  Dormant     Prime Partner    Alice Brown   Partner     Workshop       Mike

Key UX behaviors:

• Keyboard-first filtering and row navigation.
• Saved views in the left rail or as chips above the table.
• Visual stage coloring, but not color-only status communication.
• Bulk actions disabled until rows are selected.
• Unread-note and overdue-next-action badges.

Lead detail

The lead detail page should combine a summary card, state machine actions, and a timeline.

Lead #241 | Acme Health | New -> [Triage] [Qualify] [Disqualify] [Assign]
--------------------------------------------------------------------------------
Primary contact: Jane Doe <jane@acme.example>   Phone: 312...
Organization: Acme Health                        Source: Email
Inquiry type: Enterprise                         Service interest: AI Readiness
Budget: $20k-$50k                               Desired decision date: 2026-08-15
Summary: "Need evidence before production launch..."

Next action due: [2026-07-28]   Owner: [Unassigned v]   Tags: [Healthcare] [AI]
--------------------------------------------------------------------------------
Timeline
- Created from manual intake
- Duplicate review: no strong matches
- Note added by Mike
- Stage changed from New to Triage
--------------------------------------------------------------------------------
Notes | Files | Assignment history | Audit trail

Import wizard

The import UX should be explicit because imports are risky:

1. Upload CSV/JSON file.
2. Preview detected columns.
3. Map columns to internal fields.
4. Choose dedupe policy: merge / update blanks only / create new / review uncertain.
5. Dry-run summary.
6. Commit import.
7. Download result report.

This workflow aligns with the site’s current emphasis on human review, named authority, and visible operating boundaries.

Data model and MySQL schema

Design basis

For this application, MySQL should be treated as the system of record for lead and admin data, using utf8mb4, InnoDB, foreign keys, composite indexes, and selective FULLTEXT indexes. MySQL recommends utf8mb4 for interoperability, InnoDB supports foreign keys and referential actions, multiple-column indexes can be used through their leftmost prefixes, and FULLTEXT indexes are supported on InnoDB CHAR/VARCHAR/TEXT columns. Generated columns can also be indexed, which is useful if later you choose to derive searchable values from JSON metadata.

Core entity model

erDiagram
    USERS ||--o{ USER_ROLES : has
    ROLES ||--o{ USER_ROLES : assigns
    ROLES ||--o{ ROLE_PERMISSIONS : grants
    PERMISSIONS ||--o{ ROLE_PERMISSIONS : maps

    ACCOUNTS ||--o{ CONTACTS : has
    ACCOUNTS ||--o{ LEADS : relates_to
    CONTACTS ||--o{ LEADS : primary_for
    USERS ||--o{ LEADS : owns
    LEAD_SOURCES ||--o{ LEADS : sourced_from
    PIPELINE_STAGES ||--o{ LEADS : is_in

    LEADS ||--o{ LEAD_ASSIGNMENTS : history
    LEADS ||--o{ LEAD_NOTES : contains
    LEADS ||--o{ LEAD_ACTIVITIES : records
    LEADS ||--o{ LEAD_FILES : attaches
    LEADS ||--o{ LEAD_TAG_MAP : tagged_with
    LEAD_TAGS ||--o{ LEAD_TAG_MAP : maps
    LEADS ||--o{ LEAD_MERGES : survivor
    LEADS ||--o{ LEAD_MERGES : merged

    USERS ||--o{ AUDIT_LOG : actor
    USERS ||--o{ NOTIFICATION_QUEUE : receives
    IMPORT_JOBS ||--o{ IMPORT_ROWS : contains

Reference DDL

The DDL below is a practical starting point for a production-oriented v1. It is intentionally normalized around organizations, contacts, leads, roles, activities, imports, exports, and security records.

CREATE DATABASE IF NOT EXISTS ltc_leads
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

USE ltc_leads;

CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(254) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    display_name VARCHAR(150) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    must_change_password TINYINT(1) NOT NULL DEFAULT 0,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_users_email (email),
    KEY idx_users_active (is_active)
) ENGINE=InnoDB;

CREATE TABLE roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_key VARCHAR(50) NOT NULL,
    label VARCHAR(100) NOT NULL,
    UNIQUE KEY uq_roles_role_key (role_key)
) ENGINE=InnoDB;

CREATE TABLE permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    permission_key VARCHAR(100) NOT NULL,
    label VARCHAR(120) NOT NULL,
    UNIQUE KEY uq_permissions_permission_key (permission_key)
) ENGINE=InnoDB;

CREATE TABLE role_permissions (
    role_id BIGINT UNSIGNED NOT NULL,
    permission_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (role_id, permission_id),
    CONSTRAINT fk_role_permissions_role
        FOREIGN KEY (role_id) REFERENCES roles(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_role_permissions_permission
        FOREIGN KEY (permission_id) REFERENCES permissions(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE user_roles (
    user_id BIGINT UNSIGNED NOT NULL,
    role_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (user_id, role_id),
    CONSTRAINT fk_user_roles_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_user_roles_role
        FOREIGN KEY (role_id) REFERENCES roles(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE accounts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    organization_name VARCHAR(255) NOT NULL,
    normalized_name VARCHAR(255) NOT NULL,
    website_url VARCHAR(255) NULL,
    email_domain VARCHAR(190) NULL,
    account_type ENUM('enterprise','government','partner','unknown') NOT NULL DEFAULT 'unknown',
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_accounts_normalized_name (normalized_name),
    KEY idx_accounts_email_domain (email_domain),
    KEY idx_accounts_type_name (account_type, normalized_name)
) ENGINE=InnoDB;

CREATE TABLE contacts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id BIGINT UNSIGNED NULL,
    first_name VARCHAR(120) NULL,
    last_name VARCHAR(120) NULL,
    full_name VARCHAR(255) NOT NULL,
    work_email VARCHAR(254) NULL,
    normalized_email VARCHAR(254) NULL,
    email_domain VARCHAR(190) NULL,
    phone_raw VARCHAR(50) NULL,
    normalized_phone VARCHAR(20) NULL,
    role_title VARCHAR(150) NULL,
    preferred_reply ENUM('email','phone','either','unknown') NOT NULL DEFAULT 'unknown',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_contacts_account
        FOREIGN KEY (account_id) REFERENCES accounts(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    KEY idx_contacts_account (account_id),
    KEY idx_contacts_name (last_name, first_name),
    KEY idx_contacts_email (normalized_email),
    KEY idx_contacts_phone (normalized_phone),
    KEY idx_contacts_domain (email_domain)
) ENGINE=InnoDB;

CREATE TABLE lead_sources (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    source_key VARCHAR(50) NOT NULL,
    label VARCHAR(100) NOT NULL,
    UNIQUE KEY uq_lead_sources_key (source_key)
) ENGINE=InnoDB;

CREATE TABLE pipeline_stages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    stage_key VARCHAR(50) NOT NULL,
    label VARCHAR(100) NOT NULL,
    sort_order INT NOT NULL,
    is_terminal TINYINT(1) NOT NULL DEFAULT 0,
    is_won TINYINT(1) NOT NULL DEFAULT 0,
    is_lost TINYINT(1) NOT NULL DEFAULT 0,
    UNIQUE KEY uq_pipeline_stages_key (stage_key),
    KEY idx_pipeline_stages_order (sort_order)
) ENGINE=InnoDB;

CREATE TABLE leads (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_reference CHAR(12) NOT NULL,
    account_id BIGINT UNSIGNED NULL,
    primary_contact_id BIGINT UNSIGNED NULL,
    source_id BIGINT UNSIGNED NOT NULL,
    stage_id BIGINT UNSIGNED NOT NULL,
    owner_user_id BIGINT UNSIGNED NULL,
    inquiry_type ENUM('enterprise','government','partner','manual','unknown') NOT NULL DEFAULT 'unknown',
    service_interest VARCHAR(120) NULL,
    title VARCHAR(255) NOT NULL,
    summary TEXT NOT NULL,
    budget_min DECIMAL(12,2) NULL,
    budget_max DECIMAL(12,2) NULL,
    desired_decision_date DATE NULL,
    priority ENUM('low','normal','high','urgent') NOT NULL DEFAULT 'normal',
    next_action_due DATE NULL,
    close_reason VARCHAR(150) NULL,
    intake_hash CHAR(64) NULL,
    is_sensitive TINYINT(1) NOT NULL DEFAULT 0,
    metadata_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    first_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_activity_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    closed_at DATETIME NULL,
    CONSTRAINT fk_leads_account
        FOREIGN KEY (account_id) REFERENCES accounts(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_leads_primary_contact
        FOREIGN KEY (primary_contact_id) REFERENCES contacts(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_leads_source
        FOREIGN KEY (source_id) REFERENCES lead_sources(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_leads_stage
        FOREIGN KEY (stage_id) REFERENCES pipeline_stages(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_leads_owner
        FOREIGN KEY (owner_user_id) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT chk_leads_budget_range
        CHECK (budget_max IS NULL OR budget_min IS NULL OR budget_max >= budget_min),
    UNIQUE KEY uq_leads_public_reference (public_reference),
    KEY idx_leads_stage_owner_created (stage_id, owner_user_id, created_at),
    KEY idx_leads_source_created (source_id, created_at),
    KEY idx_leads_decision_date (desired_decision_date),
    KEY idx_leads_next_action (next_action_due),
    KEY idx_leads_account_contact (account_id, primary_contact_id),
    KEY idx_leads_hash (intake_hash),
    FULLTEXT KEY ftx_leads_title_summary (title, summary)
) ENGINE=InnoDB;

CREATE TABLE lead_assignments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id BIGINT UNSIGNED NOT NULL,
    assigned_user_id BIGINT UNSIGNED NOT NULL,
    assigned_by_user_id BIGINT UNSIGNED NOT NULL,
    assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    unassigned_at DATETIME NULL,
    CONSTRAINT fk_lead_assignments_lead
        FOREIGN KEY (lead_id) REFERENCES leads(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_lead_assignments_assigned_user
        FOREIGN KEY (assigned_user_id) REFERENCES users(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_lead_assignments_by_user
        FOREIGN KEY (assigned_by_user_id) REFERENCES users(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    KEY idx_lead_assignments_open (lead_id, unassigned_at),
    KEY idx_lead_assignments_user (assigned_user_id, unassigned_at)
) ENGINE=InnoDB;

CREATE TABLE lead_notes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id BIGINT UNSIGNED NOT NULL,
    author_user_id BIGINT UNSIGNED NOT NULL,
    note_text MEDIUMTEXT NOT NULL,
    is_pinned TINYINT(1) NOT NULL DEFAULT 0,
    is_private TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_lead_notes_lead
        FOREIGN KEY (lead_id) REFERENCES leads(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_lead_notes_author
        FOREIGN KEY (author_user_id) REFERENCES users(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    KEY idx_lead_notes_lead_created (lead_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE lead_activities (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id BIGINT UNSIGNED NOT NULL,
    actor_user_id BIGINT UNSIGNED NULL,
    activity_type VARCHAR(50) NOT NULL,
    message VARCHAR(255) NOT NULL,
    old_values_json JSON NULL,
    new_values_json JSON NULL,
    ip_address VARBINARY(16) NULL,
    user_agent VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_lead_activities_lead
        FOREIGN KEY (lead_id) REFERENCES leads(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_lead_activities_actor
        FOREIGN KEY (actor_user_id) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    KEY idx_lead_activities_lead_created (lead_id, created_at),
    KEY idx_lead_activities_type_created (activity_type, created_at)
) ENGINE=InnoDB;

CREATE TABLE lead_tags (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tag_key VARCHAR(50) NOT NULL,
    label VARCHAR(100) NOT NULL,
    UNIQUE KEY uq_lead_tags_key (tag_key)
) ENGINE=InnoDB;

CREATE TABLE lead_tag_map (
    lead_id BIGINT UNSIGNED NOT NULL,
    tag_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (lead_id, tag_id),
    CONSTRAINT fk_lead_tag_map_lead
        FOREIGN KEY (lead_id) REFERENCES leads(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_lead_tag_map_tag
        FOREIGN KEY (tag_id) REFERENCES lead_tags(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE lead_files (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id BIGINT UNSIGNED NOT NULL,
    uploaded_by_user_id BIGINT UNSIGNED NOT NULL,
    original_filename VARCHAR(255) NOT NULL,
    stored_filename VARCHAR(255) NOT NULL,
    mime_type VARCHAR(120) NOT NULL,
    byte_size BIGINT UNSIGNED NOT NULL,
    sha256_hash CHAR(64) NOT NULL,
    storage_path VARCHAR(500) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_lead_files_lead
        FOREIGN KEY (lead_id) REFERENCES leads(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_lead_files_user
        FOREIGN KEY (uploaded_by_user_id) REFERENCES users(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    KEY idx_lead_files_lead_created (lead_id, created_at),
    KEY idx_lead_files_hash (sha256_hash)
) ENGINE=InnoDB;

CREATE TABLE lead_merges (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    survivor_lead_id BIGINT UNSIGNED NOT NULL,
    merged_lead_id BIGINT UNSIGNED NOT NULL,
    merged_by_user_id BIGINT UNSIGNED NOT NULL,
    merge_reason VARCHAR(255) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_lead_merges_survivor
        FOREIGN KEY (survivor_lead_id) REFERENCES leads(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_lead_merges_merged
        FOREIGN KEY (merged_lead_id) REFERENCES leads(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_lead_merges_user
        FOREIGN KEY (merged_by_user_id) REFERENCES users(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    UNIQUE KEY uq_lead_merges_merged (merged_lead_id)
) ENGINE=InnoDB;

CREATE TABLE notification_queue (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    recipient_user_id BIGINT UNSIGNED NOT NULL,
    lead_id BIGINT UNSIGNED NULL,
    channel ENUM('in_app','email') NOT NULL DEFAULT 'in_app',
    subject VARCHAR(150) NOT NULL,
    body_text TEXT NOT NULL,
    status ENUM('queued','sent','failed','read') NOT NULL DEFAULT 'queued',
    available_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sent_at DATETIME NULL,
    read_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_notification_queue_user
        FOREIGN KEY (recipient_user_id) REFERENCES users(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_notification_queue_lead
        FOREIGN KEY (lead_id) REFERENCES leads(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    KEY idx_notification_queue_delivery (recipient_user_id, status, available_at)
) ENGINE=InnoDB;

CREATE TABLE import_jobs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    created_by_user_id BIGINT UNSIGNED NOT NULL,
    source_filename VARCHAR(255) NOT NULL,
    source_format ENUM('csv','json') NOT NULL,
    status ENUM('uploaded','mapped','validated','processing','completed','failed','cancelled') NOT NULL DEFAULT 'uploaded',
    mode ENUM('dry_run','commit') NOT NULL DEFAULT 'dry_run',
    summary_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME NULL,
    CONSTRAINT fk_import_jobs_user
        FOREIGN KEY (created_by_user_id) REFERENCES users(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    KEY idx_import_jobs_user_created (created_by_user_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE import_rows (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    import_job_id BIGINT UNSIGNED NOT NULL,
    source_row_number INT NOT NULL,
    raw_payload_json JSON NOT NULL,
    normalized_payload_json JSON NULL,
    match_score DECIMAL(5,2) NULL,
    matched_lead_id BIGINT UNSIGNED NULL,
    action_taken ENUM('none','inserted','updated','merged','skipped','flagged') NOT NULL DEFAULT 'none',
    message VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_import_rows_job
        FOREIGN KEY (import_job_id) REFERENCES import_jobs(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_import_rows_matched_lead
        FOREIGN KEY (matched_lead_id) REFERENCES leads(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    KEY idx_import_rows_job_row (import_job_id, source_row_number),
    KEY idx_import_rows_action (action_taken)
) ENGINE=InnoDB;

CREATE TABLE export_jobs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    created_by_user_id BIGINT UNSIGNED NOT NULL,
    format ENUM('csv','json') NOT NULL,
    filter_json JSON NOT NULL,
    row_count INT UNSIGNED NULL,
    file_path VARCHAR(500) NULL,
    status ENUM('queued','processing','completed','failed','expired') NOT NULL DEFAULT 'queued',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME NULL,
    expires_at DATETIME NULL,
    CONSTRAINT fk_export_jobs_user
        FOREIGN KEY (created_by_user_id) REFERENCES users(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    KEY idx_export_jobs_user_created (created_by_user_id, created_at)
) ENGINE=InnoDB;

CREATE TABLE saved_views (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    view_name VARCHAR(120) NOT NULL,
    filter_json JSON NOT NULL,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_saved_views_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    UNIQUE KEY uq_saved_views_user_name (user_id, view_name)
) ENGINE=InnoDB;

CREATE TABLE auth_throttle (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    throttle_key VARCHAR(191) NOT NULL,
    scope ENUM('login_account','login_ip','password_reset','api_route') NOT NULL,
    attempt_count INT NOT NULL DEFAULT 0,
    blocked_until DATETIME NULL,
    last_attempt_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_auth_throttle_key_scope (throttle_key, scope),
    KEY idx_auth_throttle_blocked_until (blocked_until)
) ENGINE=InnoDB;

CREATE TABLE password_reset_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    token_hash CHAR(64) NOT NULL,
    expires_at DATETIME NOT NULL,
    used_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_password_reset_tokens_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    UNIQUE KEY uq_password_reset_tokens_hash (token_hash),
    KEY idx_password_reset_tokens_user (user_id, expires_at)
) ENGINE=InnoDB;

CREATE TABLE audit_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    actor_user_id BIGINT UNSIGNED NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id BIGINT UNSIGNED NULL,
    action_type VARCHAR(50) NOT NULL,
    event_message VARCHAR(255) NOT NULL,
    details_json JSON NULL,
    ip_address VARBINARY(16) NULL,
    user_agent VARCHAR(255) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_audit_log_actor
        FOREIGN KEY (actor_user_id) REFERENCES users(id)
        ON DELETE SET NULL ON UPDATE CASCADE,
    KEY idx_audit_log_entity (entity_type, entity_id, created_at),
    KEY idx_audit_log_action (action_type, created_at)
) ENGINE=InnoDB;

Seed data

INSERT INTO lead_sources (source_key, label) VALUES
('web_form', 'Website form'),
('manual_email', 'Manual from email'),
('phone_call', 'Phone call'),
('manual_entry', 'Manual entry'),
('csv_import', 'CSV import'),
('json_import', 'JSON import');

INSERT INTO pipeline_stages (stage_key, label, sort_order, is_terminal, is_won, is_lost) VALUES
('new', 'New', 10, 0, 0, 0),
('triaged', 'Triaged', 20, 0, 0, 0),
('qualified', 'Qualified', 30, 0, 0, 0),
('disqualified', 'Disqualified', 40, 1, 0, 1),
('assigned', 'Assigned', 50, 0, 0, 0),
('discovery', 'Discovery', 60, 0, 0, 0),
('proposal', 'Proposal', 70, 0, 0, 0),
('won', 'Won', 80, 1, 1, 0),
('lost', 'Lost', 90, 1, 0, 1),
('dormant', 'Dormant', 100, 0, 0, 0),
('spam', 'Spam', 110, 1, 0, 1);

Sample queries

Dashboard counts by stage and owner

SELECT
    ps.label AS stage,
    u.display_name AS owner,
    COUNT(*) AS lead_count
FROM leads l
JOIN pipeline_stages ps ON ps.id = l.stage_id
LEFT JOIN users u ON u.id = l.owner_user_id
GROUP BY ps.label, u.display_name
ORDER BY ps.sort_order, owner;

Lead list search with filters

SELECT
    l.id,
    l.public_reference,
    ps.label AS stage,
    a.organization_name,
    c.full_name,
    l.service_interest,
    l.inquiry_type,
    u.display_name AS owner,
    l.next_action_due,
    l.created_at
FROM leads l
LEFT JOIN accounts a ON a.id = l.account_id
LEFT JOIN contacts c ON c.id = l.primary_contact_id
LEFT JOIN users u ON u.id = l.owner_user_id
JOIN pipeline_stages ps ON ps.id = l.stage_id
WHERE
    (:stage_id IS NULL OR l.stage_id = :stage_id)
    AND (:owner_user_id IS NULL OR l.owner_user_id = :owner_user_id)
    AND (:service_interest IS NULL OR l.service_interest = :service_interest)
    AND (
        :q IS NULL
        OR a.organization_name LIKE CONCAT('%', :q, '%')
        OR c.full_name LIKE CONCAT('%', :q, '%')
        OR MATCH(l.title, l.summary) AGAINST (:q IN NATURAL LANGUAGE MODE)
    )
ORDER BY l.created_at DESC
LIMIT :limit OFFSET :offset;

Unassigned and aging triage queue

SELECT
    l.id,
    l.public_reference,
    a.organization_name,
    c.full_name,
    DATEDIFF(CURDATE(), DATE(l.created_at)) AS age_days
FROM leads l
LEFT JOIN accounts a ON a.id = l.account_id
LEFT JOIN contacts c ON c.id = l.primary_contact_id
JOIN pipeline_stages ps ON ps.id = l.stage_id
WHERE ps.stage_key IN ('new','triaged')
  AND l.owner_user_id IS NULL
ORDER BY age_days DESC, l.created_at ASC;

Duplicate-candidate prefilter for a new intake

SELECT
    l.id,
    l.public_reference,
    a.organization_name,
    c.full_name,
    c.normalized_email,
    c.normalized_phone,
    a.normalized_name
FROM leads l
LEFT JOIN accounts a ON a.id = l.account_id
LEFT JOIN contacts c ON c.id = l.primary_contact_id
WHERE
    c.normalized_email = :normalized_email
    OR c.normalized_phone = :normalized_phone
    OR (
        a.normalized_name = :normalized_org
        AND c.last_name = :last_name
    )
ORDER BY l.updated_at DESC
LIMIT 20;

Export-ready reporting by service interest and close outcome

SELECT
    COALESCE(service_interest, 'Unspecified') AS service_interest,
    SUM(CASE WHEN ps.stage_key = 'won'  THEN 1 ELSE 0 END) AS won_count,
    SUM(CASE WHEN ps.stage_key = 'lost' THEN 1 ELSE 0 END) AS lost_count,
    COUNT(*) AS total_count
FROM leads l
JOIN pipeline_stages ps ON ps.id = l.stage_id
GROUP BY COALESCE(service_interest, 'Unspecified')
ORDER BY total_count DESC;

Vanilla PHP backend

Recommended architecture

A clean vanilla-PHP structure should use a front controller and a small internal MVC-ish organization, but without framework abstractions that add indirection without value. The goals are deterministic routing, explicit dependencies, and easy handoff.

/app
  /Config
    app.php
    database.php
    permissions.php
  /Core
    Router.php
    Request.php
    Response.php
    Auth.php
    Csrf.php
    Validator.php
    View.php
    Db.php
    Logger.php
  /Controllers
    AuthController.php
    DashboardController.php
    LeadController.php
    ImportController.php
    ExportController.php
    UserController.php
    FileController.php
  /Repositories
    LeadRepository.php
    ContactRepository.php
    AccountRepository.php
    UserRepository.php
  /Services
    LeadService.php
    DedupeService.php
    NotificationService.php
    ImportService.php
    ExportService.php
  /Views
    /layouts
    /auth
    /leads
    /imports
    /admin
/public
  index.php
  assets/
  uploads-temp/   # temp only, not permanent storage
/storage
  /logs
  /exports
  /private-files  # outside public webroot in production
/bootstrap
  init.php
/scripts
  cron_notifications.php
  cron_cleanup.php
  cron_backups.sh

Use spl_autoload_register() for local class loading, not Composer, if the “no third-party libraries” requirement is strict.

Routing, controllers, and data access

Use one PHP entry point under /public/index.php that boots configuration, starts the session, resolves the request, checks authentication/authorization, then dispatches to a controller. For data access, use PDO prepared statements rather than string-concatenated SQL; both PHP’s PDO manual and OWASP recommend prepared statements/parameterized queries because they separate SQL code from user data. Multi-table write actions such as “create account + contact + lead + activity log” should run inside explicit database transactions.

Suggested route map:

GET   /login
POST  /login
POST  /logout

GET   /dashboard
GET   /leads
GET   /leads/create
POST  /leads
GET   /leads/{id}
POST  /leads/{id}/update
POST  /leads/{id}/stage
POST  /leads/{id}/assign
POST  /leads/{id}/notes
POST  /leads/{id}/files
POST  /leads/{id}/merge

GET   /imports
POST  /imports/upload
POST  /imports/{id}/map
POST  /imports/{id}/run

GET   /exports
POST  /exports
GET   /exports/{id}/download

GET   /admin/users
POST  /admin/users
POST  /admin/users/{id}/roles

GET   /api/notifications
POST  /api/leads/bulk
POST  /api/saved-views

Authentication and session management

For passwords, use password_hash() and password_verify(). PHP’s password API is built precisely for strong one-way password hashing, and password_verify() is documented as timing-attack safe. Use password_needs_rehash() to upgrade hashes when PHP’s defaults or your target algorithm changes. OWASP’s password-storage guidance recommends strong slow hashing algorithms and explicitly rejects plaintext and fast hashes for password storage.

For sessions, use native PHP sessions with secure cookie parameters set before session_start(). PHP documents session_set_cookie_params() and session cookie flags such as secure, httponly, and samesite. The PHP session security guidance recommends session.use_strict_mode, session.cookie_httponly, secure cookies over HTTPS, and SameSite=Lax or Strict to mitigate fixation and CSRF risks; OWASP additionally recommends idle and absolute timeouts, server-side enforcement, and active invalidation on logout/expiration. Regenerate the session ID on successful login and privilege changes.

A good baseline policy for this system:

• Idle timeout: 20 minutes.
• Absolute timeout: 8 hours.
• Reauthentication required for password change, export of large datasets, user/role administration, and lead merge.
• Logout performs $_SESSION = [], cookie invalidation, and session_destroy(). PHP notes that session destruction and cookie invalidation are separate concerns.

CSRF, XSS, and SQL injection protections

For CSRF, because this app will use cookie-based sessions, every state-changing request should carry a server-generated CSRF token. OWASP recommends CSRF tokens on state-changing requests when framework protection is absent, and notes that SameSite is defense-in-depth rather than a substitute for proper token validation. For fetch/AJAX, send the token in a custom header such as X-CSRF-Token.

For XSS, the rule should be simple: never store or render user-authored HTML in v1. Restrict notes and summaries to text, and HTML-encode on output with htmlspecialchars(). OWASP’s XSS guidance emphasizes output encoding by context, and PHP’s own form-handling guidance explicitly recommends htmlspecialchars() to prevent HTML/JavaScript injection in rendered pages.

For SQL injection, use prepared statements everywhere user data touches SQL. For dynamic clause pieces that cannot be bound—such as sort column, sort direction, or filter field names—use strict allow-lists. OWASP explicitly recommends prepared statements and allow-list input validation and strongly discourages trying to “escape everything” as the primary defense.

File uploads

The current public site should not accept confidential evidence on public forms, because the trust and privacy pages explicitly prohibit that. But the internal admin system may legitimately need private attachments after qualification. When you add internal attachments, follow an allow-list policy for extensions and MIME types, validate type server-side using Fileinfo, rename every file to an application-generated storage name, limit size, and store permanent files outside the webroot. OWASP’s file-upload guidance recommends allow-listing extensions, not trusting the client Content-Type, renaming files, and storing them outside the webroot or on a separate host; PHP provides move_uploaded_file() and the Fileinfo extension for safe handling and type inspection.

Recommended v1 attachment policy:

• Public site: no file uploads.
• Admin app: PDF, DOCX, TXT only unless a later business case requires images.
• Max file size: 10 MB in v1.
• Store under a non-public root such as /srv/ltc-admin/private-files/.
• Download only through an authenticated PHP controller that checks authorization and logs access.

Rate limiting and abuse controls

Login throttling should be both account-based and IP-aware, but the canonical counter should be tied to the account rather than only the source IP. OWASP’s authentication guidance recommends login throttling, explicit lockout design, and logging of failures and lockouts; NIST guidance also ties password strength to rate limiting against online guessing. For this app, an exponential delay strategy is often safer than long hard lockouts because it reduces denial-of-service risk against legitimate users.

Minimal v1 rules:

Backup and recovery strategy

Use logical backups first, then augment if scale grows. MySQL documents mysqldump as a logical backup utility that can output SQL, CSV, or XML, and MySQL Shell’s dump utilities can export schemas/instances to local files. Cron remains the simplest scheduler for a VPS; crontab files are explicitly intended to run commands at set times. OWASP recommends keeping logs and sensitive data in controlled locations, and its cryptographic-storage guidance notes that encryption at rest may be applied at the filesystem or application layer depending on threat model.

A good self-hosted backup plan:

• Nightly mysqldump of the application schema.
• Hourly incremental database-safe exports only if business volume later justifies them.
• Nightly tarball of /storage/private-files.
• Backup encryption/compression via OS utilities after dump creation.
• Weekly restore drill to a staging database and file area.
• Separate retention buckets: daily for 14 days, weekly for 8 weeks, monthly for 12 months.

Vanilla JavaScript frontend and data exchange

Frontend pattern

Use server-rendered HTML as the baseline, then enhance with small vanilla-JS modules for filtering, modals, inline edits, notifications, and import previews. The Fetch API is now the standard browser interface for requesting resources and returns a Promise for a Response; use it with same-origin requests and credentials for authenticated admin calls.

Example request pattern:

async function postJson(url, payload, csrfToken) {
  const response = await fetch(url, {
    method: 'POST',
    credentials: 'same-origin',
    headers: {
      'Content-Type': 'application/json',
      'X-CSRF-Token': csrfToken
    },
    body: JSON.stringify(payload)
  });

  const data = await response.json();
  if (!response.ok) {
    throw new Error(data.message || 'Request failed');
  }
  return data;
}

Form validation

Use native HTML constraints for the first pass and the Constraint Validation API for richer messages. MDN documents checkValidity() and reportValidity() on elements and forms, and explicitly warns that client-side validation does not replace server-side validation because requests can be modified or handcrafted. That maps well to this application: client-side checks for UX, server-side checks for trust.

Suggested approach:

• HTML attributes: required, type="email", maxlength, pattern.
• JS uses reportValidity() before fetch submits.
• Server performs the same semantic checks again.
• Error summary anchors focus to the first invalid field.

Real-time updates

For v1, polling is the best fit. MDN notes that WebSockets enable bidirectional communication without polling, but implementing a robust WebSocket server in pure no-library PHP is substantially more complex than implementing short polling or long polling. In a strict “no third-party libraries” environment, reliability beats novelty.

Polling endpoints should be narrow and cheap:

• /api/notifications?since=cursor
• /api/leads/{id}/timeline?since=cursor
• /api/dashboard/widgets

Accessibility

LongTermCapabilities already targets WCAG 2.2 AA-oriented implementation on the public site and emphasizes semantic structure, keyboard access, programmatic labels, instructions, error association, reduced motion, and searchable HTML equivalents for PDF content. The admin app should follow the same pattern. For dynamic updates, use ARIA live regions or role="status" elements so screen readers announce notification changes without stealing focus. MDN documents live regions precisely for this use case.

v1 accessibility checklist:

• Semantic labels on every form control.
• Error summary with field links.
• Keyboard-navigable data table actions.
• No modal without focus trapping and focus restoration.
• Contrast-safe stage badges.
• Live region for “saved,” “import completed,” and “new assignment” messages.
• No status conveyed by color alone.

Import and export formats

Use CSV and JSON only in v1. PHP ships with fgetcsv(), fputcsv(), json_encode(), and json_decode(), which are enough to ship a reliable importer/exporter without external libraries. PHP’s JSON decoder expects UTF-8 input, and recent PHP documentation warns to pass the CSV escape parameter explicitly rather than relying on defaults.

Recommended CSV columns:

inquiry_type,organization_name,contact_name,work_email,phone,preferred_reply,
service_interest,title,summary,budget_min,budget_max,desired_decision_date,
source_label,external_reference,next_action_due,tags

Recommended JSON export shape:

{
  "exported_at": "2026-07-24T15:00:00Z",
  "filters": { "stage": "qualified" },
  "rows": [
    {
      "lead_id": 241,
      "public_reference": "LD26A84QXH9M",
      "stage": "Qualified",
      "organization_name": "Acme Health",
      "contact": {
        "full_name": "Jane Doe",
        "work_email": "jane@acme.example"
      },
      "service_interest": "AI Production Readiness Sprint",
      "summary": "Need evidence before production launch"
    }
  ]
}

Deduplication algorithm

Because this site appears to pursue relatively few, higher-value inquiries, the dedupe policy should be conservative. False merges are more damaging than false duplicates.

Recommended matching model:

Decision thresholds:

• >= 90: auto-merge or append to existing lead.
• 70–89: flag for human review.
• < 70: create new lead.

Normalization rules:

• Lowercase and trim email.
• Phone to digits-only, keep last 10–15 digits.
• Organization name normalized by removing punctuation and common suffixes (inc, llc, corp, ltd, company).
• Compute intake_hash = SHA-256(normalized_email + normalized_phone + normalized_org + service_interest + date_bucket).

Sample PHP scripts

CSV import skeleton

This importer uses built-in CSV handling, explicit CSV control characters, PDO prepared statements, and a transaction per row or per chunk.

<?php
declare(strict_types=1);

function normalizeEmail(?string $email): ?string {
    if ($email === null) return null;
    $email = strtolower(trim($email));
    return $email !== '' ? $email : null;
}

function normalizePhone(?string $phone): ?string {
    if ($phone === null) return null;
    $digits = preg_replace('/\D+/', '', $phone);
    return $digits !== '' ? $digits : null;
}

function normalizeOrg(string $org): string {
    $org = strtolower(trim($org));
    $org = preg_replace('/[^\p{L}\p{N}\s]+/u', ' ', $org);
    $org = preg_replace('/\b(inc|llc|corp|ltd|company|co)\b/u', ' ', $org);
    $org = preg_replace('/\s+/u', ' ', $org);
    return trim($org);
}

function findExistingLead(PDO $pdo, ?string $email, ?string $phone, string $org, ?string $lastName): ?array {
    $sql = "
        SELECT l.id, l.public_reference
        FROM leads l
        LEFT JOIN contacts c ON c.id = l.primary_contact_id
        LEFT JOIN accounts a ON a.id = l.account_id
        WHERE (:email IS NOT NULL AND c.normalized_email = :email)
           OR (:phone IS NOT NULL AND c.normalized_phone = :phone)
           OR (a.normalized_name = :org AND c.last_name = :last_name)
        ORDER BY l.updated_at DESC
        LIMIT 1
    ";
    $stmt = $pdo->prepare($sql);
    $stmt->execute([
        ':email' => $email,
        ':phone' => $phone,
        ':org' => $org,
        ':last_name' => $lastName
    ]);
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row ?: null;
}

$pdo = new PDO(
    'mysql:host=127.0.0.1;dbname=ltc_leads;charset=utf8mb4',
    'app_user',
    'app_password',
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]
);

$fh = fopen(__DIR__ . '/import.csv', 'rb');
if (!$fh) {
    throw new RuntimeException('Unable to open import file.');
}

$header = fgetcsv($fh, null, ',', '"', '\\');
if (!$header) {
    throw new RuntimeException('Missing CSV header row.');
}

$map = array_flip($header);

while (($row = fgetcsv($fh, null, ',', '"', '\\')) !== false) {
    $orgName = trim($row[$map['organization_name']] ?? '');
    $contactName = trim($row[$map['contact_name']] ?? '');
    $email = normalizeEmail($row[$map['work_email']] ?? null);
    $phone = normalizePhone($row[$map['phone']] ?? null);
    $summary = trim($row[$map['summary']] ?? '');

    if ($orgName === '' || $contactName === '' || $summary === '') {
        continue; // or record row error
    }

    $normalizedOrg = normalizeOrg($orgName);
    $nameParts = preg_split('/\s+/', $contactName);
    $lastName = $nameParts ? end($nameParts) : null;

    $existing = findExistingLead($pdo, $email, $phone, $normalizedOrg, $lastName ?: null);

    $pdo->beginTransaction();
    try {
        if ($existing) {
            $update = $pdo->prepare("
                UPDATE leads
                SET summary = CONCAT(summary, '\n\n--- IMPORT APPEND ---\n', :summary),
                    last_activity_at = NOW(),
                    updated_at = NOW()
                WHERE id = :lead_id
            ");
            $update->execute([
                ':summary' => $summary,
                ':lead_id' => $existing['id']
            ]);
        } else {
            $accountStmt = $pdo->prepare("
                INSERT INTO accounts (organization_name, normalized_name)
                VALUES (:organization_name, :normalized_name)
            ");
            $accountStmt->execute([
                ':organization_name' => $orgName,
                ':normalized_name' => $normalizedOrg,
            ]);
            $accountId = (int)$pdo->lastInsertId();

            $contactStmt = $pdo->prepare("
                INSERT INTO contacts (account_id, full_name, last_name, work_email, normalized_email, normalized_phone)
                VALUES (:account_id, :full_name, :last_name, :work_email, :normalized_email, :normalized_phone)
            ");
            $contactStmt->execute([
                ':account_id' => $accountId,
                ':full_name' => $contactName,
                ':last_name' => $lastName,
                ':work_email' => $row[$map['work_email']] ?? null,
                ':normalized_email' => $email,
                ':normalized_phone' => $phone,
            ]);
            $contactId = (int)$pdo->lastInsertId();

            $leadStmt = $pdo->prepare("
                INSERT INTO leads (
                    public_reference, account_id, primary_contact_id, source_id, stage_id,
                    inquiry_type, service_interest, title, summary
                )
                VALUES (
                    :public_reference, :account_id, :primary_contact_id, :source_id, :stage_id,
                    :inquiry_type, :service_interest, :title, :summary
                )
            ");
            $leadStmt->execute([
                ':public_reference' => bin2hex(random_bytes(6)),
                ':account_id' => $accountId,
                ':primary_contact_id' => $contactId,
                ':source_id' => 5, // csv_import
                ':stage_id' => 1,  // new
                ':inquiry_type' => $row[$map['inquiry_type']] ?? 'unknown',
                ':service_interest' => $row[$map['service_interest']] ?? null,
                ':title' => $row[$map['title']] ?? 'Imported lead',
                ':summary' => $summary,
            ]);
        }

        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
        throw $e;
    }
}

fclose($fh);

JSON export endpoint skeleton

<?php
declare(strict_types=1);

require __DIR__ . '/../bootstrap/init.php';

if (!Auth::check() || !Auth::can('lead.export')) {
    http_response_code(403);
    exit('Forbidden');
}

header('Content-Type: application/json; charset=utf-8');
header('Content-Disposition: attachment; filename="leads-export.json"');

$stmt = $pdo->prepare("
    SELECT
        l.id,
        l.public_reference,
        ps.label AS stage,
        a.organization_name,
        c.full_name,
        c.work_email,
        l.service_interest,
        l.summary,
        l.created_at
    FROM leads l
    JOIN pipeline_stages ps ON ps.id = l.stage_id
    LEFT JOIN accounts a ON a.id = l.account_id
    LEFT JOIN contacts c ON c.id = l.primary_contact_id
    ORDER BY l.created_at DESC
");
$stmt->execute();

$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo json_encode([
    'exported_at' => gmdate('c'),
    'rows' => $rows,
], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT);

These scripts rely entirely on built-in PHP database, CSV, JSON, and CSPRNG features.

Deployment migration and assurance

Hosting and deployment options

Because the public site consistently presents itself as static-first and tightly bounded, the cleanest deployment is to keep the public site and the admin app logically separate, even if they live on the same VPS. That reduces the chance that an admin-only stateful app accidentally changes the public site’s privacy posture or operational simplicity.

Suggested stack:

• Linux VPS
• Nginx or Apache as reverse proxy / web server
• PHP-FPM
• MySQL 8
• Local filesystem storage for private files and exports
• Cron for recurring tasks
• Optional local MTA such as Postfix for internal email notifications and backup job mailouts. Postfix provides a mature self-hosted mail-transfer architecture with standard configuration paths.

Migration plan

Phase zero

Stand up the admin app with authentication, RBAC, schema, dashboard shell, and manual lead entry. Do not change the public site. Staff copy public-safe inquiries from email or phone into the admin console.

Phase one

Add lead workflow, notes, activity log, assignment history, saved views, imports/exports, and dashboard reporting. Create a canonical mapping table for current public service names so imported/manual leads use the same taxonomy as the site’s services and contact-page selections.

Phase two

Optionally add a same-origin submission endpoint to /contact/ that stores only currently public-safe fields. Before that launch, revise the privacy and trust language because the public pages currently say the inquiry is not sent or stored by the site.

Phase three

Add deeper reporting, optional local-email notification digests, and, if needed, controlled private-file intake after qualification.

Security, privacy, and compliance checklist

The most important compliance observation is not a statute; it is a representation issue. Today’s public pages promise that the contact form does not submit to the site and does not store inquiry data. If LongTermCapabilities changes that behavior, those public statements must be updated before launch. The trust and privacy pages also draw a line around public-safe qualification only, so any web capture should keep that boundary unless the business is prepared for a materially different intake and control model.

Recommended launch checklist:

Estimated development effort

This estimate assumes one experienced full-stack PHP developer, one production environment, a modest user count, vanilla PHP/JS only, and no custom WebSocket server in v1.

Phased roadmap and testing plan

Testing should be built around the system’s real risks:

Final recommendation

Build the admin lead system now as a separate, internal, stateful application using vanilla PHP, vanilla JavaScript, and MySQL. Preserve the current public email-builder intake in v1. Model the schema directly on the public site’s current inquiry structure. Use MySQL as the system of record, PDO prepared statements, secure PHP sessions, CSRF tokens, HTML output encoding, internal audit logs, and cron-driven backups. Then, only after the business explicitly chooses to change the public privacy/trust boundary, add limited server-side web capture for the same public-safe fields already present on /contact/. That path is the best technical fit for LongTermCapabilities as it exists today.