Integration techniques: sync, unified schemas, rules and caching

Part 1

One shape, one mapping per source

Study first

• One internal model per concept, with the mapping from each provider in code and the provider's record kept whole in a raw field. A field only one provider has stays in raw until a second provider needs it; then it joins the shape.
• A changed shape is a migration. The convention here: the create statement stays as the day it was written, the columns a later day needs are appended as idempotent statements, and a step that must run once guards itself on what it finds (do $$ ... if exists (select 1 from information_schema.columns ...) ... $$).
• GitHub's list response and its webhook payload differ: labels come as names or as objects, the state in upper or lower case, and only the delivery carries html_url. fromIssue reads both, and a test holds the 2 results equal.

Build

1. src/lib/unified.ts: the Ticket and Contact types; fromIssue, fromRequest (a request's team is already set, so no rule routes it), fromDocument for the vendor of an invoice, the From of an email, the writer of a letter with the fields a form carries, and nothing for a job posting; fromLogin for the people on tickets; upsertTicket and upsertContact, which run the rules and record what changed in applied. Invoices are the exception: saveDocument runs their rules.
2. A document contact's key is its email, lower-cased, when it has one, so a sender seen twice is one row; on conflict the upsert fills the fields the row lacks from the new sighting and keeps the rest. The id carries the source, so a login and a sender never merge.
3. The invoice schema gains the vendor's email and phone, nullable, so a sender can be reached; the extraction eval compares only the fields each case names, so it does not move.
4. The migration in src/lib/schema.ts: 4 columns appended to Day 27's table, and the block that re-keys its rows under their source and drops the issue number, once.
5. Tests: the 2 issue shapes map equal; a request maps with its team; a document of each kind maps, or does not; a contact seen twice; a ticket stored with the team the rules gave it; a version older than the row's is dropped.

Done when

• find_ticket and find_contact read the one shape only, and the same issue arriving listed or delivered makes one row.

Part 2

Two paths, and a nightly read

Study first

• The paths here: a pull every 15 minutes and on demand, with since as its cursor (Day 27); a webhook for push; a nightly full pull for reconciliation. GitHub has no change stream for issues. Each path leaves the copy some distance behind the system; measure that distance rather than assume it.
• Reconciliation catches what the other paths missed, and sees what they cannot: a deletion. What it finds is drift; log every row it had to fix.
• Backfill: the first pull is the backfill, paced by the page size under the rate limit, 100 a page and 2 pages here. Its lag is the age of old issues, which says nothing about the path, so it is not logged as one.
• Effectively once: rows are keyed by source and record key, and an update is taken only when its updated_at is at least the stored one's, so a replayed delivery is harmless and an out-of-order one is dropped.

Build

1. src/inngest/sync.ts: sync every 15 minutes and on demand, 3 steps: the issues changed since the latest updated_at stored, then every request and every contact. Requests and the documents' senders are WorkBot's own rows, so their steps map them whole into the shape each run rather than sync them. reconcile runs nightly: the same pull with full: true, which reads open and closed issues by name rather than trusting a default.
2. POST /api/webhooks/github (src/app/api/webhooks/github/): the body is checked against X-Hub-Signature-256 with the secret, in constant time; only issues events for the repository in GITHUB_REPO pass; the payload is parsed by the contract in types.ts; a deleted issue's row is dropped, any other action's row is replaced through the same mapping; the delivery is logged with its lag. Wrong signature 401; another event or repository 204; a body that is not JSON or not an issue 400.
3. pullTickets(mcp, { full: true }) in src/lib/tickets.ts: every issue, a missed log for each row the copy had older than the system, a gone for each row the system no longer lists, which is dropped.
4. /admin/tickets: Sync now, Reconcile now, and 4 cards: the copy's size, the median lag by webhook with the pull's beside it, what reconciliation found in the last day, the page cache.
5. Tests: the sync's steps with and without a ticketing system; the reconciliation on a fake whose issue changed behind the copy's back and whose other issue vanished; the webhook's answers to a good delivery, a deletion, names and objects for labels, a wrong signature, another event or repository, a malformed body. Live: a copy of a real issue, read through the server and signed, replaces its row; the reconciliation reads the real repository.

Done when

• On this run the dashboard reported a median lag of 2 s by webhook and 80 s by pull, for a change made 75 s before the pull, and the reconciliation logged 1 missed issue and 0 gone across 62.

Part 3

Rules before the model

Study first

• Deterministic rules where the criterion is explicit, before the model sees the data: the form of a phone number, a company from a domain, a team from a title's words, a threshold an invoice crosses. The model is for what needs judgment.
• Rules as data: an ordered table of conditions and actions, evaluated in sequence, each rule seeing the record as the ones before it left it, testable without a model.
• Audit: for each change a rule makes, the rule's id, the field, the old value and the new, stored with the record. A rule that fires and changes nothing writes nothing.

Build

1. content/rules.json: 6 rules. A phone of 7 digits or more becomes E.164, North America assumed for 10; a contact without a company gets one from its email's domain; a ticket without a team gets finance, support or operations by the words of its title, 3 rules in that order; an invoice over $5,000 is flagged for the finance lead.
2. src/lib/rules.ts: the conditions (matches a pattern, missing, over a number), the actions (set fields, normalize a phone, companyFromEmail, flag), the parse at load that refuses a duplicate id or a team not in content/customer.json, and applyRules(on, record), which returns the record and the list of changes.
3. The ticket and contact upserts apply their rules; saveDocument applies the invoice rules, and a flag joins the review list the inbox of Day 13 shows. find_contact normalizes a phone in the question before matching it, so a number typed with spaces or brackets finds the contact stored as E.164.
4. Tests: each rule on its own; the order of the routing rules; a routed ticket left alone; the audit's entries; the table's ids unique and its teams real.

Done when

• The chat finds a contact by a phone typed differently from the document it came from, and a ticket's row carries its team and the rule that set it.

Part 4

A cache that says its age

Study first

• Freshness per field: a contact's email changes rarely, a deal's stage hourly; the age a cache allows belongs to the field, not to the cache.
• Three strategies that are not one: stale-while-revalidate serves the cached value while a fetch replaces it; write-through updates the cache alongside the source; invalidation drops or refreshes an entry when the source's own events say it changed.
• Measure it: hit rate, the age served, and the cost of a wrong cached answer, which is what the age should be set from.

Build

1. src/lib/cache.ts: cached(key, maxAgeMs, fetch) keyed by tenant and key, with hit, stale and miss as outcomes; a stale read returns the cached value and starts the replacement without waiting for it; every read is logged with its outcome and age.
2. read_url keeps a page an hour and answers As of 14:02: ..., so the model, and the person reading its answer, know how old the page is.
3. The dashboard's fourth card: reads, hit rate, the median age served.
4. A test: a miss, a hit while fresh, a stale read that starts the replacement, a hit of the new value once the replacement is done; the summary's numbers.

Done when

• A page read twice is a hit the second time and the model's answer says when it was fetched. The card shows the demo's 2 reads and 1 hit; a day of traffic gives the rate the age should be set from.

Part 5

Measure, then write it down

Build

1. docs/data-layer.md: the shape, the paths with their measurements, the rule table, the cache's numbers.
2. Record D17 in brain/architecture/decisions.md, then update the backlog, the README, the architecture document's data section and the agent notes' conventions.

Done when

• Each measurement in the document names its run, and the document separates what is built from what is not: deals and a second external provider, for want of a CRM and an account; a public address for deliveries; an alert for a copy that stays stale; ages per field.