Day 28

Integration techniques: sync, unified schemas, rules and caching

You ship
A unified, rule-applied, cached data layer under the integrations, with reconciliation and an audit trail.

What you'll build today

After Day 27, WorkBot reads a synced copy of one ticketing system and files tickets through its own tools. It is not yet a data layer. A second source would bring its own shape, the copy can fall behind without anyone noticing, and a phone number reaches the model in whatever form a document used.

Today you build the layer under the integrations. You map every source onto one shape and keep the provider's original record. You keep the copy fresh by 3 paths, and each one measures how far behind it leaves the copy. You put anything with a right answer into ordered rules that run before the model sees a record, and you cache with an age that the answer states.

By the end of the day, tickets and contacts share one shape, and rules have cleaned them with an audit trail. A dashboard shows each path's lag and the cache's hit rate. WorkBot still lacks a customer relationship management (CRM) system and a public address for webhooks, and the last part lists what that leaves unbuilt.

By the end of the day you can

  • Keep integrated data correct and fresh with sync, streaming and caching strategies chosen per source
  • Map several providers onto one unified schema without losing provider-specific fields
  • Apply deterministic business rules to integrated data before the agent reasons over it

Topics

Pull, Push + Stream

  • Polling with cursors and incremental sync, webhooks for push, and change streams where the source offers them
  • Reconciliation: periodic full syncs to catch missed events, and drift detection
  • Backfills: pacing the first sync of a large account under rate limits

Unified Schemas

  • One internal model per concept (contact, deal, ticket, invoice), with provider mappings in code
  • The escape hatch: a raw field with the provider payload, and typed extensions for provider-only fields
  • Versioning the unified schema, and migrations when a provider changes
  • Where the model helps: it proposes a mapping from a new provider's schema, and an engineer reviews and commits it

Rule Application

  • Deterministic rules run before the model sees data: normalization, deduplication, enrichment, validation and routing
  • Rules as data: an ordered table of conditions and actions, evaluated in sequence and testable without a model
  • Rules versus prompts: anything with a right answer is a rule
  • Audit: store every rule that fired on a record with the record

Caching Integrated Data

  • Freshness per field. A contact's email changes rarely, and a deal stage changes hourly
  • Stale-while-revalidate for the agent's reads, and write-through for the agent's writes
  • Invalidation from webhooks. Cache keys include the user and the tenant, the unit by which WorkBot keeps one customer's data and settings apart
  • Measurement: hit rate, staleness, and the cost of a wrong cached answer

Streaming Integration into the Agent

  • Feeding events to a running agent, such as a new ticket that arrives mid-conversation, through the patterns of Day 18 and Day 33
  • Ordering and effectively-once processing: idempotent handlers keyed by event id

Build

Features shipped today

  • WorkBot: The unified schema for contacts and tickets, the rule table, and per-tenant caching, with cached sources that state their freshness. Deals join the schema once a customer relationship management (CRM) system is connected

Build the data layer under yesterday's integrations:

  1. A unified schema for contacts, deals and tickets, with mappings from both connected providers and a raw field
  2. Sync: incremental sync with cursors for one source, webhooks for the other, and nightly reconciliation. Log every missed event that reconciliation finds
  3. Rules: a rule table, evaluated in order, with an audit trail per record. It dedupes by email domain, normalizes phone numbers, flags deals over a threshold and routes tickets by keyword
  4. Caching: per-field freshness, stale-while-revalidate reads and webhook invalidation, with a dashboard of hit rate and median staleness
  5. Agent: it reads only from the unified, rule-applied layer. Show one question it answers correctly only because a rule cleaned the data

Deals need a CRM, and a second provider needs its account. The steps below follow the reference run, which had neither. They map tickets and contacts from 4 sources, and run a pull and a webhook on the same source. They flag invoices over a threshold in place of deals, and keep one cache age per source. The last part lists what they do not build.

Step by step

Map sources to one shape

Yesterday's copy holds GitHub issues in GitHub's shape. Before a second source arrives, you give each concept one shape, so that tools and rules are written once for every source.

Study first

  • Keep one internal model per concept. The mapping from each provider lives in code, and the provider's record stays whole in a raw field. A field that only one provider has stays in raw until a second provider needs it, and then it joins the shape.
  • A changed shape is a migration. WorkBot's convention is that a create statement stays as it was on the day it was written, and the columns a later day needs are appended as idempotent statements. A step that must run once guards itself on what it finds, in a do block such as 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 comes in upper or lower case, and only the delivery carries html_url. So fromIssue reads both, and a test holds the 2 results equal.

Build

  1. Write src/lib/unified.ts with the Ticket and Contact types and a mapping per source. fromIssue maps a GitHub issue, and fromLogin maps the people on tickets. fromRequest maps a request, whose team is already set, so no rule routes it.
  2. In the same file, fromDocument maps a document to a contact. It takes the vendor of an invoice, the From of an email, and the writer of a letter with the fields its form carries. A job posting maps to nothing.
  3. Add upsertTicket and upsertContact, which run the rules and record what changed in applied. Invoices are the exception, because saveDocument runs their rules.
  4. Key a document contact by 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.
  5. Add the vendor's email and phone to the invoice schema, both nullable, so WorkBot can reach a sender. The extraction eval (Day 17) compares only the fields each case names, so its score does not move.
  6. Write the migration in src/lib/schema.ts, which appends 4 columns to Day 27's table. A block then re-keys the table's rows under their source and drops the issue number, once.
  7. Write tests that map the 2 issue shapes equal, map a request with its team, and map a document of each kind, or not. Test that a contact seen twice is one row, and that a ticket is stored with the team the rules gave it. Test that a version older than the row's is dropped.

Done when

  • find_ticket and find_contact read the one shape only.
  • The same issue, listed or delivered, makes one row.
One shape
Four sources, one mapping each, and the rules in order. Two tables keep the provider's record beside the shape, and every refresh of the copy is logged with its lag.
Technical notes
  • A ticket's id is source#key, such as github#164 or workbot#<uuid>. Day 27 keyed tickets by issue number under the repository's name, and the block in the schema rewrites those rows once.
  • GitHub's own API omits an empty labels or assignees, where a webhook sends []. Its read of an issue gives label names, where a delivery gives objects, so the delivery schema takes both.
  • The ticket upsert accepts a version only when its updated_at is not older than the row's. So an older delivery loses to a newer pull, and the same version twice changes nothing.

Keep the copy fresh

A copy is only useful when you know how old it is. So you add a webhook and a nightly read beside the pull, and each path logs how far behind it left the copy.

Study first

  • WorkBot has 3 paths. A pull runs every 15 minutes and on demand, with since as its cursor (Day 27), a webhook pushes changes, and a nightly full pull reconciles the copy. GitHub has no change stream for issues. Each path leaves the copy some distance behind the system, so measure that distance rather than assume it.
  • Reconciliation catches what the other paths missed, and it also sees what they cannot, a deletion. What it finds is drift, so log every row it had to fix.
  • The first pull is the backfill, paced by the page size under the rate limit, which in the reference run was 100 a page and 2 pages. Its lag is the age of old issues, which says nothing about the path, so do not log it as a lag.
  • Each change should land once, even when it arrives twice, which is called effectively-once processing. 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.
  • GitHub's webhook best practices cover checking the event and its action, redelivering what a server missed, and spotting a repeat by its delivery header. The issues event reference lists the actions a delivery can carry, deleted among them.

Build

  1. Write src/inngest/sync.ts, whose sync function runs every 15 minutes and on demand, in 3 steps. The first pulls the issues changed since the latest updated_at stored, the second maps every request, and the third maps every contact.
  2. Requests and the documents' senders are WorkBot's own rows, so their steps map them whole into the shape on each run rather than sync them. reconcile runs nightly, as the same pull with full: true, which asks for open and closed issues by name rather than trusting a default.
  3. Write POST /api/webhooks/github in src/app/api/webhooks/github/. Check the body against X-Hub-Signature-256 with the secret, in constant time. Pass only issues events for the repository in GITHUB_REPO, and parse the payload with the contract in types.ts.
  4. In the same route, drop a deleted issue's row, and for any other action replace the row through the same mapping. Log the delivery with its lag. Answer a wrong signature with 401, another event or repository with 204, and a body that is not JSON or not an issue with 400.
  5. Add pullTickets(mcp, { full: true }) to src/lib/tickets.ts, which pulls every issue. It logs missed for each row where the copy was older than the system, and it logs gone for each row the system no longer lists, and drops that row.
  6. Add to /admin/tickets a Sync now button, a Reconcile now button, and 4 cards. The cards show the copy's size, the median lag by webhook with the pull's beside it, what reconciliation found in the last day, and the page cache.
  7. Write tests for the sync's steps with and without a ticketing system. Test the reconciliation on a fake where one issue changed behind the copy's back and another vanished.
  8. Test the webhook's answers to a good delivery, a deletion, labels as names and as objects, and a wrong signature. Test another event or repository, and a malformed body. In the live tests, a copy of a real issue, read through the server and signed, replaces its row, and the reconciliation reads the real repository.

Done when

  • The dashboard reports the median lag by webhook and by pull, and what the reconciliation found. In the reference run, it reported a median lag of 2 s by webhook and 80 s by pull, for a change made 75 s before the pull. The reconciliation logged 1 missed issue and 0 gone, across 62.
The paths measured
The dashboard in the reference run after one change per path. The copy was 2 s behind by webhook, and 80 s behind by the pull that followed a change. The nightly read had to fix 1 row. The tickets carry their team and the rule that set it.
Technical notes
  • Store lags in seconds. In the reference run, the first run logged the backfill, whose issues were months old, and a lag in milliseconds overflowed a 32-bit integer. The backfill is no longer logged as a pull at all.
  • since is inclusive at GitHub, so a pull returns the newest row again and logs its lag. In steady state, the pull's interval bounds its lag, and in the reference run the one change was made 75 s before the pull.
  • A real delivery needs a public address, which the reference run does not have. gh webhook forward or a tunnel gives a development machine one. The reference run tests the receiver from the signature check on, with a copy of a real issue, signed as GitHub would sign it.
  • A token that stays expired fails the pull, which Inngest retries. The copy's age shows on the page, but nothing alerts yet.

Apply rules before the model

With one shape and a fresh copy, you can clean the data before the model reads it. Anything with a right answer, such as the form of a phone number, belongs in a rule rather than in a prompt.

Study first

  • Deterministic rules run before the model sees the data, wherever the criterion is explicit. Examples are the form of a phone number, a company from an email domain, a team from a title's words, and a threshold an invoice crosses. The model is for what needs judgment.
  • Rules work best as data, in an ordered table of conditions and actions evaluated in sequence. Each rule sees the record as the rules before it left it, and you can test the table without a model. Martin Fowler's refactoring to an adaptive model moves logic into JSON rules that a small engine runs, and counts what that costs. His note on rules engines warns that rules which trigger one another grow hard to follow, and advises a small rule set with thorough tests.
  • Every change a rule makes leaves an audit entry with the record: the rule's id, the field, the old value and the new one. A rule that fires and changes nothing writes nothing.

Build

  1. Write content/rules.json with 6 rules. A phone of 7 digits or more becomes E.164, the international format with a plus sign and a country code, and a 10-digit number is taken as North American. A contact without a company gets one from its email's domain.
  2. In the same file, 3 rules in order give a ticket without a team finance, support or operations, by the words of its title. The sixth rule flags an invoice over $5,000 for the finance lead.
  3. Write src/lib/rules.ts with the conditions (matches a pattern, missing, over a number) and the actions (set fields, normalize a phone, companyFromEmail, flag). Its parse at load refuses a duplicate id, or a team that is not in content/customer.json (Day 26). applyRules(on, record) returns the record and the list of changes.
  4. Make the ticket and contact upserts apply their rules. Make saveDocument apply the invoice rules, so a flag joins the review list in the inbox of Day 18.
  5. Make find_contact normalize a phone in the question before matching it. Then a number typed with spaces or brackets finds the contact stored as E.164.
  6. Write tests for each rule on its own and for the order of the routing rules. Check that a routed ticket is left alone, check the audit's entries, and check that the table's ids are unique and its teams real.

Done when

  • The chat finds a contact by a phone typed differently from the document it came from.
  • A ticket's row carries its team and the rule that set it.
A rule, not a prompt
The employee chat finds a vendor by a phone typed with brackets and spaces. The rule stored the phone as E.164, and the tool normalizes the question the same way. The ticket's team comes from its row.
The contacts
The contacts after the 30 documents of the reference run. The phones are in one form, companies come from domains where the record gave none, and the rules that fired sit beside each row.
Technical notes
  • The eval set does not cover this, because CI runs with no documents and no ticketing system. The evidence is the lookup above and the unit tests on the rules.
  • The routing reads the title, because a body says many things. In the reference run, the keyword rules first fired on incidental words in long issue bodies. The keywords restate the customer's filing rules in a second form, and Day 29 builds one configuration that both read.
  • A company from a domain is a label, not a name, so brightline-retail reads as Brightline Retail but cascadiamillwork reads as Cascadiamillwork. The audit says the company was derived. A later record that names the company does not override it, since the upsert keeps what the row already has.

Cache with a stated age

Some reads go to sources WorkBot does not copy, such as public web pages. A cache saves those calls, and stating the age in the answer tells the model and the person reading how fresh the answer is.

Study first

  • Freshness belongs to the field. A contact's email changes rarely and a deal's stage changes hourly, so the age a cache allows is set per field, not per cache.
  • Three strategies do different jobs. 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 the cache by its hit rate, the age it serves, and the cost of a wrong cached answer, and set the age from that cost.

Build

  1. Write src/lib/cache.ts. cached(key, maxAgeMs, fetch) is keyed by tenant and key, where a tenant is how WorkBot keeps one customer's data and settings apart. A read comes out as a hit, stale or a miss.
  2. Make a stale read return the cached value and start the replacement without waiting for it. Log every read with its outcome and age.
  3. Make read_url keep a page for an hour and answer As of 14:02: ..., so the model, and the person reading its answer, know how old the page is.
  4. Add the dashboard's fourth card, with the reads, the hit rate and the median age served.
  5. Write a test that covers a miss, a hit while fresh, and a stale read that starts the replacement. It then covers a hit of the new value once the replacement is done, and the summary's numbers.

Done when

  • A page read twice is a hit the second time, and the model's answer says when the page was fetched.
  • The card shows the reads and the hits. In the reference run, it showed the demo's 2 reads and 1 hit, and a day of traffic gives the rate to set the age from.
Technical notes
  • WorkBot has one age per source today. A page lasts an hour, and the tickets copy is 15 minutes behind by pull and seconds behind by webhook. A CRM would want the same table keyed by field, which the reference run does not build.
  • Invalidation is by replacement, because the webhook replaces a ticket's row, so the copy follows the system's own events. A ticket WorkBot files reaches the copy the same way, by the webhook or the next pull, rather than by a write-through.
  • Rows are keyed by (tenant, key), and a read uses the current tenant, so one tenant never receives another's value. For the same reason, the terminal runs its turns under a tenant of its own.

Write up the data layer

The data layer now has numbers behind it, and the next person to change an integration needs them in one place. So you finish by writing the layer down, with its measurements and its gaps.

Study first

  • A good write-up names the run behind each measurement, so a reader can tell a measured number from an estimate. It also keeps what is built apart from what is not, so nobody mistakes a gap for a feature. Google's technical writing lesson on documents makes the same split, as a stated scope and non-scope, and puts the key points first.

Build

  1. Write docs/data-layer.md, with the shape, the paths and their measurements, the rule table and the cache's numbers.
  2. Record the decision in brain/architecture/decisions.md, D17 in the reference run. Then update the backlog, the README, the data section of the architecture document and the conventions in AGENTS.md.

Done when

  • Each measurement in the document names its run.
  • The document separates what is built from what is not. In the reference run, 4 things are not built. Deals and a second external provider wait for a CRM and an account, and deliveries wait for a public address. Nothing alerts on a copy that stays stale, and cache ages are per source, not per field.

Resources