EmailInsightAI

SECTION 1What the system does

EmailInsightAI sits between a company's shared mailboxes — info@, sales@, support@ and each employee's own address — and the people who have to act on what arrives in them. It never becomes the mail client. Mail stays in Gmail or Microsoft 365; the application keeps a synchronised copy, enriches it, and gives a team a working surface over the top.

1.1The problem it addresses

A shared inbox taking a few hundred mails a day has four failure modes, and the product is organised around them:

• Nobody knows what is in there. Every mail is read by the AI and labelled with a sentiment, a category, a recommended next action and a lead signal — all filterable from one toolbar.
• Nobody owns anything. Mail is routed to a department automatically, from the category the classifier already assigned, and can be handed to one person by override.
• Senders wait for an acknowledgement. Auto Reply answers defined categories from admin-approved templates, behind a confidence floor and per-recipient daily caps.
• Things fall through. Follow-up Reminder raises a task for any mail owed an answer, chases it on a schedule, and closes itself when a reply is detected.

1.2Who uses it

Three roles, distinguished by columns on the user row. There is no roles table — users.user_level, users.auth_id and users.is_department_head are the whole access model.

The login screen enforces this directly: AuthController::login() rejects any user whose user_level is 0 with “Only Admin Or Department Head Have Access” before an OTP is ever issued.

1.3Technology

1.4System architecture

SECTION 2How mail moves through the system

One mail's journey has five stages. Stages 1–3 happen inside app:process-email-queue and cost one Gemini call. Stages 4 and 5 are separate scheduled passes that read the messages table afterwards — a design decision explained below, and the reason the system can enrich mail that arrived before a feature existed.

2.1Why the later stages are decoupled

Auto-reply and follow-up detection could have run inline, immediately after each insert. They deliberately do not, for two reasons stated in the code:

• Different failure modes should not be entangled. A Gemini outage must cost an acknowledgement, never an inbox sync.
• A mail would otherwise get exactly one chance. Anything already sitting in messages — every mail that arrived before the feature shipped, or during an outage — would be permanently invisible. Working off the table instead means a later pass still picks it up.

Idempotency is therefore structural rather than flag-based. A message is a candidate for auto-reply only when it has no auto_reply_logs row, and the service claims that row before doing any work; a message is a candidate for a follow-up only when it has no follow_ups row, enforced by a unique key. In both cases the row means this mail has been considered, whatever the outcome — so nothing is evaluated twice, and a task somebody dismissed is not raised again on the next pass.

2.2What the classifier is asked

One prompt, one call, nine fields. The prompt is assembled at runtime from the account's own vocabularies, so a category or sentiment an admin added this morning is in the prompt this afternoon without a deploy.

Two instructions in the prompt matter operationally. The model is told to judge only the newest message — quoted history and On <date>, <person> wrote: blocks are stripped by a regular expression before the call and ignored by instruction after it. And it is told to always return all nine fields with best-guess defaults, because a partial response would leave columns null and silently drop the mail out of every filter.

Failure handling

SECTION 3Multi-tenancy

Each client company gets its own MySQL database. There is no client_id column anywhere in the application tables — separation is physical. A small master database holds the directory of clients and the work queue that decides whose mail is fetched next.

3.1The two connections

3.2The work queue

Two commands consume this queue. app:process-email-queue fetches and classifies mail; app:process-auto-reply-queue uses the same table to decide whose mail is answered next. The scheduled auto-reply job is the queue-driven one precisely so that mailboxes are not treated as equal — a paying client raised up the order is served first, and a mailbox can be taken out of rotation by deleting its row rather than by editing code.

3.3Onboarding a new client

1. Create the tenant database and run the migrations against it.
2. Insert a row into master.clients with name, db_name, company_name, domain_name, mail_type, status = 'active' and, for Microsoft mail, the tenantId.
3. For Microsoft mail, add the matching master.outlook_creds row (tenantId, clientId, clientSecret).
4. Register the admin user in the tenant database with auth_id = 0 and user_level = 1.
5. Add employee mailboxes — individually via Add Email, or in bulk via the Excel upload.
6. Insert one master.cron_queue row per mailbox that should be synced.

SECTION 4Accounts, access and mailbox connection

4.1The columns that define a user

4.2Signing in

Login is two-factor by mail, and the OTP is only issued after the role check passes.

1. POST /login validates the credentials with Auth::validate() — note that this does not start a session.
2. A user with user_level = 0 is refused here.
3. A six-digit OTP is written to user_otps with a five-minute expiry (one row per user, upserted) and mailed to the address.
4. The browser is redirected to /otp-verify/{user}.
5. POST /otp-verify checks the code and expiry, then logs the session in. POST /resend-otp issues a fresh code.

Password recovery

4.3Connecting a mailbox

Google Workspace — account_type = 0

The application holds a service-account key at storage/app/email/service-account.json and uses domain-wide delegation. For each mailbox it mints a JWT with sub set to that address, signs it RS256, exchanges it at oauth2.googleapis.com/token for a one-hour access token, and calls the Gmail API as that user. No mailbox password is ever stored.

Both scopes must be authorised against the service account's client ID in the Google Workspace admin console. A mailbox outside the delegated domain will fail the connection test with a 401 no matter what is configured locally.

Microsoft 365 — account_type = 1

Client-credentials flow against Microsoft Graph. Credentials come from config/auto_reply.php (OUTLOOK_TENANT_ID, OUTLOOK_CLIENT_ID, OUTLOOK_CLIENT_SECRET) or, per tenant, from master.outlook_creds. The issued token is cached at storage/app/email/outlook_account.json.

SECTION 5Using the app, screen by screen

Navigation is a single left sidebar shared by every screen. Entries marked admin only are hidden when auth_id != 0.

5.1Dashboard

Everything is scoped to the signed-in user's mailbox and follows the same counting rule as the mail list, so the dashboard and the filter chips never disagree. Mail the user sent themselves is counted everywhere except the two reply-tracking figures, the turnaround histogram, “Mail received” and “Top senders” — you never owe yourself a reply.

On load the dashboard also runs a live connection check for the signed-in mailbox and sets is_app_pwd; a failing check surfaces the app-password prompt rather than silently showing an empty dashboard.

5.2Email Inbox and the filter toolbar

Every filter is the same route — GET /mails/{type}/{user_id?} — so each is bookmarkable and shareable. The {type} segment is resolved against five vocabularies in a fixed order, and the first that claims it wins:

1. Sentiments — claimed the parameter first historically, so tried first.
2. Next actions — prefix action_.
3. Lead types — prefix lead_, plus the three priority bands and leads.
4. Email categories — prefix cat_.
5. Assignment — prefix assigned_, tried last so it can never shadow a slug that was bookmarkable before the feature shipped.

Anything unclaimed falls through to the fixed filters. The four vocabulary controllers each reserve the other prefixes, so an admin cannot create a category slug that shadows an assignment listing.

5.3Reading and replying

The draft is sanitised before storage — the HTML it produces is stripped to a safe subset rather than trusted, because it is displayed inside the composer.

5.4Email Summary

Two different things share the word “summary”, and it is worth keeping them apart:

The thread summary's five sections are fixed: Customer requirement, Previous discussion, Current status, Pending actions, Next step. The status pill is one of Open Waiting on us Waiting on customer Resolved No action needed, defaulting to Open if the model returns anything else.

Unlike the per-mail summary, the thread summary asks Gemini for JSON, not HTML, validates it against a fixed schema and renders it through Blade — so it cannot inject anything into the page. Routes are keyed on the numeric messages.id rather than the provider id, because Microsoft conversation ids contain /, + and =, which do not survive a URL path segment.

5.5Employees, departments and groups

5.6Bulk import and export

5.7Settings

SECTION 6The AI features in detail

Four of these features are admin-editable vocabularies: rows in a table that are injected into the Gemini prompt, written back to messages, rendered as chips, and turned into filters. Adding “Angry” or “Raise a credit note” is an admin action, not a deploy. All four share the same shape.

6.1Sentiments

Ships with Positive, Neutral, Negative, Angry and Complaint. The first three carry the legacy numeric codes already in the database (1, 3, 0) and the original filter slugs, so seeding them changes nothing about existing rows. New moods get codes from 20 upward. The chosen word lands in messages.is_spam_value and its number in messages.is_spam — a column name that predates the feature and has nothing to do with spam.

6.2Next actions

Eight shipped actions, most urgent first:

The windows come from config/follow_up.php and can be overridden per account on the follow-up settings screen. An action an admin adds gets default_due_hours (48) until a window is set for it.

6.3Lead detection and priority

A lead type carries two extra columns the other vocabularies do not have:

• min_score — the floor below which a mail of this type is not counted as a lead at all. A vague product mention scoring 30 against a floor of 50 is stored but not surfaced.
• priority — the band the lead is placed in: High, Medium or Low.

Priority bands are computed at read time from the type, not stored on the message — so re-banding a lead type immediately re-bands every existing mail of that type, with no migration and no re-classification. The three band slugs are reserved in LeadType::RESERVED_FILTER_SLUGS so no lead type can ever claim one.

6.4Email categories

Categories carry the most weight of any vocabulary, because three separate features read them: auto-reply eligibility, department routing, and the category chips. Eleven ship by default, ordered specific-first with the catch-alls last, and every one maps back through ai_aliases to the words the original prompt emitted.

6.5Auto Reply

The pipeline is AI names a category → configuration decides everything else. The model never decides whether to send. A category the business has not defined, activated and given an approved template to is never answered, however certain the model is.

The four screens

Template variables

Templates are not Blade. They are a plain substitution over a fixed name list, and every value is HTML-escaped before it lands in the body — an admin writing a template can never execute anything. An unknown or empty placeholder collapses to nothing rather than leaving {{product_name}} visible in a customer's inbox.

Blocked recipients

These local parts never receive an automated reply, because answering them creates loops: noreply, no-reply, no_reply, donotreply, do-not-reply, mailer-daemon, postmaster, bounce, bounces, notification, notifications, automated, auto-reply, autoreply.

6.6Mail Routing — automatic assignment

The routing table is the category table: email_categories.assign_department_id. A rule is a category with a team against it, which is why there is no separate rules resource. The whole table is edited in one submit, because routing decisions are read and made together.

No prompt changed to ship this feature, and none should. The classifier already answers the only language question involved — what kind of mail is this — and which team owns a pricing enquiry is not a language question but this business's org chart. So the category is read, not re-asked. That is what makes assignment free: no extra Gemini call, no field that could come back malformed, and it works on mail classified months ago.

Assign and unassign are POSTs carrying the mail in the request body rather than the path, because they key on messages.email_id — a provider id that, for Microsoft mail, contains characters that do not survive a URL path segment.

6.7Follow-up Reminder

Detection costs no AI call. The classifier already said what each mail's reader should do next, and that recommendation is the detection; the settings only decide how long it may go unactioned. A mail with no usable next action is still caught through action_required = 1 — a queue that silently excludes half the mailbox would be worse than none.

What is skipped outright

• Mail the mailbox sent — that is the answer, not the question.
• Promotions, and anything the provider labelled spam.
• Mail already replied to, using the same signal the “Replied Mail” listing uses.
• Anything whose next action has a null window (no_action out of the box).

Screens

Reminders are sent as one digest per mailbox per run, never one mail per task — four separate reminders in a minute is what teaches somebody to filter them. The hourly cadence is deliberate: the per-task schedule is decided by remind_at, not by how often the command runs, so a faster tick would only produce more, smaller mails.

SECTION 7Cron and scheduling

7.1How the tick works

There is exactly one operating-system-level scheduled task, and it does not know what any of the jobs are. Windows Task Scheduler runs a wrapper every minute; the wrapper runs artisan schedule:run; Laravel decides what is due right now. One minute is correct and not wasteful — most ticks do nothing at all.

tools\run-scheduler-hidden.vbs   ← Task Scheduler runs this (no console flash)
  └─ tools\run-scheduler.bat
       └─ cd /d C:\xampp8_2\htdocs\EmailInsightAI
          C:\xampp8_2\php\php.exe artisan schedule:run >> storage\logs\scheduler.log 2>&1

The .vbs wrapper exists for one reason: without it a black console window flashes on screen every minute, all day. Task Scheduler can only hide that by running the task as another user, which needs a stored password — the wrapper avoids that. The 0 argument means hidden and the False means do not wait; the tick is fire-and-forget.

7.2The schedule

“Guarded” is withoutOverlapping(): a run that is still going when the next is due is skipped rather than doubled. Every job also writes a before and after line to the Laravel log, so a stalled command is visible as a start with no matching finish.

7.3Installing the scheduled tasks

Two Windows Task Scheduler entries are required on a fresh machine.

7.4Logs

SECTION 8Command reference

All commands run from the project root. On this machine PHP is at C:\xampp8_2\php\php.exe.

8.1Scheduled commands

app:process-email-queue

The mail sync. Takes no options — batch size (5 mailboxes), page size and the 40-insert cap per mailbox are compiled in.

php artisan app:process-email-queue

app:process-auto-reply-queue

app:process-auto-replies — the sweep alternative

Same decisions, different mailbox selection: walks every active client rather than taking queue rows. Swap the schedule to this if priority ordering is not wanted.

app:retry-auto-replies

app:process-follow-ups

Runs in two halves in a fixed order: raise first, then close. Closing first would be wasted on mail that has no task yet, and this order means a mail that arrived and was answered between two runs gets a task and loses it in the same pass.

app:send-follow-up-reminders

app:send-daily-email-summary

Sends the daily digest through PHPMailer. No options.

8.2Maintenance and recovery commands

mail:assign-existing

Applies the current category-to-team mapping to mail that is already stored and has no owner. Never touches mail that already has an owner — a rule-assigned mail because somebody may be working it, a manually assigned one because a person overruled the rule and that has to stand. The only way a mail comes back into scope is for somebody to unassign it.

mail:backfill-gmail

Restores mail present in Gmail but missing from messages. Pages through the Gmail list API — which the live cron does not do — and inserts whatever is not already stored, oldest first, because the listings order by id DESC and therefore rely on a higher id meaning a newer mail.

AI columns are left null deliberately; next_action and the lead columns are filled from the same inferFrom() helpers the migrations used, so restored mail is honestly labelled inferred rather than pretending to be ai.

mail:relink-message-refs

Run this immediately after a backfill that followed a truncation. Truncating messages resets the auto-increment, so every stored numeric message_id elsewhere points at either nothing or — once ids climb back through the same range — a completely unrelated mail. The second case is the dangerous one: a follow-up would silently attach itself to somebody else's mail.

Repair is possible because three tables also store the provider's own id, which survives a truncation:

php artisan mail:relink-message-refs --dry-run
php artisan mail:relink-message-refs --prune-summaries

mail_summaries cannot be re-linked — its summary_key is an opaque md5 of the set of mail summarised, so there is nothing to match on. It is a regenerable digest cache, so --prune-summaries drops the stale rows and lets the app rebuild them on demand.

8.3Useful ad-hoc invocations

# What would tonight's follow-up sweep raise? Change nothing.
php artisan app:process-follow-ups --owner=1 --dry-run

# Test one freshly approved template against one mailbox, safely.
php artisan app:process-auto-replies --user=7 --limit=5 --dry-run

# Silence the backlog before switching auto-reply on for a busy mailbox.
php artisan app:process-auto-replies --user=7 --days=90 --mark-seen

# Apply a routing change you just made on the Mail Routing screen.
php artisan mail:assign-existing --account=1 --dry-run
php artisan mail:assign-existing --account=1

# Run the whole schedule once, right now, without waiting for the tick.
php artisan schedule:run

SECTION 9Database and ER diagrams

Two databases. The master database is small, shared and operational; the tenant database is where an installation's real data lives, and every application table below is one of those.

9.1Identity and organisation

%%{init:{'theme':'base','themeVariables':{'primaryColor':'#ffffff','primaryTextColor':'#101617','primaryBorderColor':'#0d6e69','lineColor':'#55635f','tertiaryColor':'#f0f4f3','fontFamily':'IBM Plex Mono, monospace','fontSize':'12px'}}}%%
erDiagram
    users ||..o{ users : "auth_id = admin id"
    departments ||..o{ users : "group_by"
    users ||--o{ departments : "user_id owns the team"
    users ||--o{ user_otps : "login code"
    users ||--o{ domain : "own domain"
    users ||--o{ impmail : "important senders"
    inbox_groups ||..o{ impmail : "group_id"

    users {
        bigint id PK
        varchar email UK
        varchar password
        varchar login_password
        bigint auth_id "0 = account admin"
        varchar user_level "1 signs in, 2 mailbox only"
        varchar account_type "0 Gmail, 1 Microsoft"
        varchar is_department_head
        varchar group_by "departments.id"
        varchar is_service_account
        varchar app_email_pass "encrypted"
        varchar is_login "connection health"
        varchar fname
        varchar lname
        varchar contact
    }
    departments {
        bigint id PK
        bigint user_id FK "owning account"
        varchar department_name UK
        varchar department_lable UK
        varchar is_deletable
    }
    user_otps {
        bigint id PK
        bigint user_id FK
        varchar otp "6 digits"
        timestamp expires_at "5 minutes"
    }
    domain {
        bigint id PK
        bigint user_id FK
        varchar domain_name
    }
    inbox_groups {
        bigint id PK
        varchar group_name
        varchar auth_id
    }
    impmail {
        bigint id PK
        bigint user_id FK
        longtext sender_email
        longtext sender_name
        tinyint important_mail
        varchar group_id "inbox_groups.id"
    }

9.2Mail core

%%{init:{'theme':'base','themeVariables':{'primaryColor':'#ffffff','primaryTextColor':'#101617','primaryBorderColor':'#0d6e69','lineColor':'#55635f','tertiaryColor':'#f0f4f3','fontFamily':'IBM Plex Mono, monospace','fontSize':'12px'}}}%%
erDiagram
    users ||--o{ messages : "mailbox owns mail"
    users ||--o{ sent_mails : "outgoing"
    sent_mails ||..o{ messages : "sent_mail_id links reply to mail"
    users ||--o{ user_emails_id_data : "seen id cache"
    users ||--o{ promotion_mails : "promotion id cache"
    users ||..o{ freshcounts : "admin_id / emp_id"
    departments ||..o{ messages : "assigned_department_id"
    users ||..o{ messages : "assigned_user_id"

    messages {
        bigint id PK
        bigint user_id FK "the mailbox"
        longtext email_id "provider message id"
        varchar threadId "provider thread id"
        longtext message_id_uniqe "RFC Message-ID header"
        longtext sender_name
        longtext sender_email
        longtext subject
        longtext clean_subject "Re:/Fwd: stripped"
        varchar sent_date
        varchar sent_date_s
        longtext message "plain text"
        longtext message_with_html
        longtext message_with_regx "quoted history removed"
        varchar mimeType
        varchar attachment_status
        varchar label_ids
        varchar labelname
        varchar message_status "0 unread, 1 read"
        varchar admin_message_status
        varchar is_sent_mail
        varchar sent_mail_id "-1 = unreplied"
        varchar track_reply_email
        varchar root_email_id
        varchar is_promotion
        varchar is_spam "sentiment legacy_code"
        varchar is_spam_value "sentiment code"
        varchar action_required "1 = needs a reply"
        varchar is_action_required_value
        varchar category "category legacy_code"
        varchar is_category_value "category code"
        longtext gemini_return_json "raw AI response"
        longtext mail_summary
        varchar next_action
        varchar next_action_source "ai | inferred"
        tinyint is_potential_lead
        varchar lead_type
        tinyint lead_score "0-100"
        varchar lead_reason
        varchar lead_source "ai | inferred"
        bigint assigned_department_id
        bigint assigned_user_id
        varchar assignment_source "rule | manual"
        timestamp assigned_at
        bigint assigned_by
        longtext reply_draft
        timestamp reply_draft_generated_at
        varchar send_py
    }
    sent_mails {
        bigint id PK
        bigint user_id FK
        longtext sent_mail_id
        longtext subject
        longtext clean_subject
        varchar matched_subject
        longtext receiver_email
        longtext receiver_name
        longtext sent_htmlMessage
        varchar sent_date_s
    }
    user_emails_id_data {
        bigint id PK
        bigint user_id FK
        longtext email_ids
        tinyint is_sent_mail
    }
    promotion_mails {
        bigint id PK
        bigint user_id FK
        longtext email_ids
        bigint is_promotion_map_id
    }
    freshcounts {
        bigint id PK
        bigint admin_id
        bigint emp_id
        int current_count
        int previous_count
    }

9.3The AI vocabularies

%%{init:{'theme':'base','themeVariables':{'primaryColor':'#ffffff','primaryTextColor':'#101617','primaryBorderColor':'#0d6e69','lineColor':'#55635f','tertiaryColor':'#f0f4f3','fontFamily':'IBM Plex Mono, monospace','fontSize':'12px'}}}%%
erDiagram
    users ||--o{ sentiments : "account vocabulary"
    users ||--o{ next_actions : "account vocabulary"
    users ||--o{ lead_types : "account vocabulary"
    users ||--o{ email_categories : "account vocabulary"
    users ||--o{ prompts : "custom prompt"
    sentiments ||..o{ messages : "is_spam_value = code"
    next_actions ||..o{ messages : "next_action = code"
    lead_types ||..o{ messages : "lead_type = code"
    email_categories ||..o{ messages : "is_category_value = code"
    departments ||..o{ email_categories : "assign_department_id = the routing table"

    sentiments {
        bigint id PK
        bigint user_id FK
        varchar code UK
        varchar name
        varchar description "goes into the prompt"
        varchar ai_aliases
        varchar filter_slug UK
        varchar colour
        int legacy_code UK "written to messages.is_spam"
        tinyint is_active
        int sort_order "broad first, specific last"
    }
    next_actions {
        bigint id PK
        bigint user_id FK
        varchar code UK
        varchar name
        varchar description
        varchar ai_aliases
        varchar filter_slug UK "action_ prefix"
        varchar colour
        tinyint is_active
        int sort_order "most urgent first"
    }
    lead_types {
        bigint id PK
        bigint user_id FK
        varchar code UK
        varchar name
        varchar description
        varchar ai_aliases
        varchar filter_slug UK "lead_ prefix"
        varchar colour
        tinyint min_score "score floor"
        varchar priority "high | medium | low"
        tinyint is_active
        int sort_order
    }
    email_categories {
        bigint id PK
        bigint user_id FK
        varchar code
        varchar name
        varchar description
        varchar ai_aliases
        int legacy_code
        varchar filter_slug "cat_ prefix"
        varchar colour
        tinyint is_lead_signal
        tinyint auto_reply_enabled
        bigint template_id FK
        bigint assign_department_id "the routing rule"
        decimal confidence_threshold "overrides account default"
        tinyint is_active
        int sort_order
    }
    prompts {
        bigint id PK
        bigint user_id FK
        text ai_prompt
        varchar keywords
        tinyint status
    }

9.4Auto reply

%%{init:{'theme':'base','themeVariables':{'primaryColor':'#ffffff','primaryTextColor':'#101617','primaryBorderColor':'#0d6e69','lineColor':'#55635f','tertiaryColor':'#f0f4f3','fontFamily':'IBM Plex Mono, monospace','fontSize':'12px'}}}%%
erDiagram
    users ||--|| auto_reply_settings : "one row per account"
    users ||--o{ email_templates : "account owns"
    email_categories ||--o| email_templates : "template_id"
    email_templates ||--o{ auto_reply_logs : "template used"
    email_categories ||--o{ auto_reply_logs : "category decided"
    users ||--o{ auto_reply_logs : "mailbox"
    messages ||--o| auto_reply_logs : "one log row per mail, ever"

    auto_reply_settings {
        bigint id PK
        bigint user_id UK "the account"
        tinyint enabled
        decimal confidence_threshold "default 0.800"
        tinyint reply_once_per_thread
        tinyint manual_review_below_threshold
        tinyint reply_to_internal_senders
        int daily_limit_per_recipient "default 3"
        varchar company_name
        varchar default_product_name
        varchar signature_name
    }
    email_templates {
        bigint id PK
        bigint user_id FK
        varchar name
        bigint category_id FK
        varchar subject
        longtext body "double-brace placeholders"
        enum status "draft | approved | inactive"
        bigint approved_by
        timestamp approved_at
    }
    auto_reply_logs {
        bigint id PK
        bigint user_id FK "mailbox"
        bigint owner_id "account"
        bigint message_id "numeric, resettable"
        varchar message_ref "provider id, durable"
        varchar thread_id
        varchar internet_message_id
        bigint category_id FK
        varchar category_code
        decimal ai_confidence
        longtext ai_raw_response
        bigint template_id FK
        varchar recipient_email
        varchar recipient_name
        varchar subject
        longtext body "exactly what was sent"
        enum status "PENDING SENT FAILED SKIPPED MANUAL_REVIEW ALREADY_SENT"
        timestamp sent_at
        varchar provider_message_id
        text error_reason
        int attempts
    }

9.5Follow-ups and summaries

%%{init:{'theme':'base','themeVariables':{'primaryColor':'#ffffff','primaryTextColor':'#101617','primaryBorderColor':'#0d6e69','lineColor':'#55635f','tertiaryColor':'#f0f4f3','fontFamily':'IBM Plex Mono, monospace','fontSize':'12px'}}}%%
erDiagram
    users ||--|| follow_up_settings : "one row per account"
    users ||--o{ follow_ups : "mailbox"
    messages ||--o| follow_ups : "one task per mail, ever"
    next_actions ||..o{ follow_ups : "trigger and due window"
    users ||--o{ thread_summaries : "per conversation"
    users ||--o{ mail_summaries : "per digest key"
    messages ||..o{ thread_summaries : "thread_key = threadId"

    follow_up_settings {
        bigint id PK
        bigint user_id UK
        tinyint enabled "opt-in"
        int default_due_hours "48"
        longtext due_windows "per next_action override"
        int reminder_lead_hours "2"
        int repeat_every_hours "24"
        int max_reminders "4"
        int escalate_after_hours "48"
        tinyint email_reminders
        tinyint notify_in_app
        varchar notify_email "one desk instead of the owner"
    }
    follow_ups {
        bigint id PK
        bigint user_id FK "mailbox"
        bigint owner_id "account"
        bigint message_id
        varchar message_ref "durable provider id"
        varchar thread_key
        varchar sender_email
        varchar sender_name
        varchar subject
        timestamp mail_sent_at
        varchar trigger_type "next_action | action_required | manual"
        varchar next_action
        varchar sentiment
        varchar reason
        varchar priority "high | normal | low"
        timestamp due_at
        timestamp remind_at "nulled when budget spent"
        timestamp snoozed_until
        varchar status "open | done | dismissed"
        varchar completion_source "reply | manual"
        timestamp completed_at
        bigint completed_by
        int reminder_count
        timestamp last_reminded_at
        tinyint escalated
        text note
    }
    thread_summaries {
        bigint id PK
        bigint user_id FK
        varchar thread_key "provider thread id"
        longtext summary_json "validated, five sections"
        int message_count "staleness check"
        bigint last_message_id "staleness check"
        varchar model
        timestamp generated_at
    }
    mail_summaries {
        bigint id PK
        bigint user_id FK
        varchar summary_key "md5 of the mail set"
        longtext summary_html
        int message_count
        bigint last_message_id
    }

9.6The master database

%%{init:{'theme':'base','themeVariables':{'primaryColor':'#ffffff','primaryTextColor':'#101617','primaryBorderColor':'#0d6e69','lineColor':'#55635f','tertiaryColor':'#f0f4f3','fontFamily':'IBM Plex Mono, monospace','fontSize':'12px'}}}%%
erDiagram
    clients ||--o{ cron_queue : "one row per mailbox to sync"
    clients ||--o{ cron_mail_send : "digest dispatch queue"
    clients ||..o| outlook_creds : "tenantId"

    clients {
        int id PK
        varchar name
        varchar db_name "the tenant database"
        varchar company_name
        varchar domain_name
        varchar company_code
        varchar company_mail
        varchar mail_type
        varchar tenantId "Microsoft tenant"
        tinyint priority
        enum status "active | inactive"
        datetime expired_at
    }
    cron_queue {
        int id PK
        int client_id FK
        int employee_id "users.id in the tenant DB"
        enum status "pending processing done failed"
        tinyint priority "higher runs first"
        int progress_count
        timestamp created_at "today only"
    }
    cron_mail_send {
        int id PK
        int client_id FK
        enum mail_send "done pending processing failed"
        tinyint priority
    }
    outlook_creds {
        int id PK
        varchar tenantId
        varchar clientId
        varchar clientSecret
    }
    portal_users {
        int id PK
        varchar first_name
        varchar last_name
        varchar email
        varchar password
        enum Status "Active | inactive"
    }

SECTION 10Table dictionary

The ER diagrams above list the columns. This section says what each table is for, and what will surprise you about it.

10.1Identity and organisation

10.2Mail core

What will surprise you about messages

10.3Vocabularies, auto reply, follow-ups

10.4Generated content

10.5Framework tables

migrations, sessions, cache, cache_locks, jobs, job_batches and failed_jobs are standard Laravel. Note that the application does not use the Laravel queue for its own work — jobs is empty in normal operation, because the scheduling model is the master cron_queue table instead.

You may also find messages_status_backup_20260901: a one-off snapshot taken before a bulk status change. It is not read by any code and is safe to drop once no longer needed.

SECTION 11Configuration reference

The division is deliberate and worth stating plainly: infrastructure lives in files, business rules live in the database. Credentials, endpoints and hard safety limits are in .env and config/*.php. Which category replies, with which template, at what confidence, chased for how long — all of that is admin-editable data, because the business rule is that configuration decides, not the AI.

11.1Environment variables

Feature switches

11.2Config files worth reading

SECTION 12Backup and recovery

12.1The nightly dump

tools\backup-databases.ps1, launched by tools\run-db-backup.bat at 00:01. Per run it:

1. Reads database credentials from the project .env — a single source of truth, so a password change does not silently break backups.
2. Asks the server for its database list, minus the virtual ones (information_schema, performance_schema, sys) which cannot be restored.
3. mysqldumps each database into its own self-contained .sql.
4. Verifies each dump finished, by checking for mysqldump's “Dump completed” trailer — a truncated dump would otherwise look like a perfectly fine file.
5. Zips the night into one archive and deletes the loose .sql files.
6. Deletes archives older than the retention window.

12.2Restoring

# 1. Take a fresh dump first — a bad restore over live data is unrecoverable.
powershell -NoProfile -ExecutionPolicy Bypass -File tools\backup-databases.ps1

# 2. Unzip the chosen night from C:\xampp8_2\db-backups

# 3. Restore one database
C:\xampp8_2\mysql\bin\mysql.exe -u root -p EmailInsight < EmailInsight.sql

12.3Recovering from a lost messages table

This is the specific disaster the tooling was built for, and the order of operations matters.

1. Do not wait for the cron. It only looks forward, so it will re-sync roughly one day and stop. Everything older stays missing permanently.
2. Restore from the most recent nightly dump if one covers the loss.
3. For anything the dump does not cover, page it back out of the provider:

   php artisan mail:backfill-gmail --email=ami@mukesoft.com --user=1 \
     --after=2025/01/01 --before=2026/09/07 --dry-run

   then re-run without --dry-run. Mail is inserted oldest-first so the id DESC listings stay in the right order.
4. Immediately repair the references, because ids have been reassigned:

   php artisan mail:relink-message-refs --dry-run
   php artisan mail:relink-message-refs --prune-summaries

5. Re-apply routing to the restored mail once something has classified it:

   php artisan mail:assign-existing --dry-run

SECTION 13Troubleshooting

SECTION 14Glossary