Keep the agent's copy of your AMS fresh without hammering the API

Every agent we build reads more than it writes. Renewal Radar ranks 1,200 policies at 120 days. The COI Agent matches a holder request to an account and a policy in under a second, forty times a morning. The Service-Request Agent needs the contact list for every account in the book to decide who wrote in. None of that can run as a live call per record against Applied Epic, HawkSoft, or EZLynx. You will hit rate limits, the agency will notice the AMS getting slower, and one API outage will take your agent down with it.

So the agent gets a read model: a queryable copy of the slice of the AMS it needs, kept close to current. The one-time snapshot is easy, and we cover pulling one in profiling your AMS data. Keeping it fresh for eighteen months without a full reload every night is the part that breaks.

This tutorial is the sync layer: what to copy, how to detect change when the API will not tell you, how to handle records that disappear, and how to prove the copy still matches. Postgres and Python in the examples. The shape holds on any store.

Copy the slice, not the AMS

The first instinct is to mirror everything. Resist it. Every table you copy is a table you own forever, with its own drift, its own PII, and its own retention question.

Write down the fields each agent actually reads, then copy those and the keys needed to join them. For our four workflows that comes to five entities:

• Accounts — id, name, DBA, producer, account manager, status.
• Policies — id, account id, line of business, carrier, policy number, effective and expiration dates, status, premium.
• Contacts — id, account id, name, email, phone, role.
• Activities — id, account id, policy id, code, date, author, subject. Read-only here; writes go through the write-back layer.
• Documents — metadata only. Id, account id, policy id, filename, type, date. The file itself stays where it is and gets fetched on demand.

Certificate holders belong on that list if the AMS holds them as records rather than free text. Check before you assume; on several books we have profiled, the holder list is a merge field in a document template and nothing more.

Keep the AMS identifier as the primary key of your copy and never mint your own. When an engineer three months from now is looking at a bad match, the first thing they will do is paste your id into the AMS search box, and it needs to resolve.

Give every row a provenance column

Four columns on every table, set by the sync and never by application code:

source_system    text        not null,   -- 'epic' | 'hawksoft' | 'ezlynx'
source_updated_at timestamptz,           -- the AMS's own modified stamp, if it has one
synced_at        timestamptz not null,   -- when we last read this row
row_hash         text        not null    -- hash of the business fields

row_hash is what makes change detection cheap later. Hash the field values, not the whole payload, and exclude anything the AMS touches on read (last-viewed stamps exist in the wild):

import hashlib, json

BUSINESS_FIELDS = ["account_id", "policy_number", "carrier", "line_of_business",
                   "effective_date", "expiration_date", "status", "premium"]

def row_hash(record: dict) -> str:
    payload = {k: normalize(record.get(k)) for k in BUSINESS_FIELDS}
    blob = json.dumps(payload, sort_keys=True, separators=(",", ":"))
    return hashlib.sha256(blob.encode()).hexdigest()

normalize does the boring work: strip whitespace, upper-case policy numbers, render dates as ISO, round premium to cents, map empty string to null. Skip it and you will get a change event every night because the AMS returned " " instead of "".

Pull incrementally, with a watermark you can replay

The three platforms differ in what they give you, and one of them will change on you. Write the sync so the strategy is a property of the connector, not of the pipeline.

Modified-since query. Best case: the API accepts a filter on a last-modified field. Keep a watermark per entity per system, and always re-read with an overlap window:

WINDOW = timedelta(minutes=15)

def pull(entity, client, state):
    since = state.watermark(entity) - WINDOW
    high = since
    for page in client.list(entity, modified_since=since):
        for rec in page:
            upsert(entity, rec)
            high = max(high, rec["modified_at"])
    state.set_watermark(entity, high)

The overlap covers clock skew and records committed out of order. Upserts are idempotent, so re-reading costs nothing but bandwidth. Only advance the watermark after the page is committed, or a crash mid-run will skip records permanently.

Sequence or change feed. If the platform exposes a monotonic change id, use it instead of a timestamp. It is exact, and it survives a server clock correction.

Full-scan diff. If there is no modified filter, you are reduced to reading the entity and comparing row_hash. This is fine at commercial-lines scale: 4,000 accounts and 1,200 policies is a small job. Run it nightly, off hours, paced. Do not run it against a book you have not sized first.

Pace every mode. One sleep between pages, a token bucket for the daily cap, and a retry with exponential backoff and jitter on 429 and 5xx. The agency's account managers are on the same API quota as you are, and they are working the download queue while your job runs.

Emit change events, do not just overwrite

The upsert compares the incoming hash to the stored one and writes an event when they differ:

create table sync_event (
  id           bigserial primary key,
  entity       text not null,
  source_id    text not null,
  change       text not null,        -- 'insert' | 'update' | 'delete'
  before       jsonb,
  after        jsonb,
  observed_at  timestamptz not null default now()
);

This table is the difference between a copy and a pipeline. Renewal Radar does not need to rescan the book at 2am; it subscribes to policies whose expiration date or status changed. The COI Agent invalidates its cached endorsement answer for a policy the moment a policy row changes. The reconciliation job in the IVANS tutorial reads the before value straight out of this table instead of guessing at it.

Keep events for 90 days. They are also your answer when someone asks why the agent drafted what it drafted on a Tuesday: the record it read is in the row.

Handle the records that disappear

This is where most sync layers quietly rot. A policy gets deleted as an entry error. An account merges into its parent. A contact leaves the business. A modified-since pull will never mention any of them, so your copy keeps a policy the AMS no longer has, and Renewal Radar drafts a remarket packet for it.

Two rules:

1. Never hard-delete. Add deleted_at and filter it in every agent query. An agent that reads a row which vanished mid-run should degrade, not crash.
2. Reconcile against an id list on a schedule. Once a week, pull only the identifiers for each entity, full set, no payloads. That call is cheap on all three platforms. Anything present in your copy and absent from the list gets deleted_at set and a delete event. Anything in the list and missing from your copy gets queued for a targeted fetch.

Put a floor under it. If the id list comes back more than 10 percent smaller than your copy, do not apply the deletes. Alert instead. A truncated response from a paginated endpoint looks exactly like a mass deletion, and you do not want to find out which one it was by reading the agent's output.

Prove it still matches

Freshness is a claim, so measure it. Three checks, run after every sync and exported as metrics next to your agent traces:

• Lag. Oldest synced_at per entity. Alert when policies exceed 60 minutes or accounts exceed 24 hours. Pick the thresholds from the workflow: a COI Agent matching live requests needs tighter policy lag than a renewal ranking that runs once a day.
• Drift sample. Re-read 50 random records straight from the API and compare hashes to the copy. Any mismatch is a connector bug, and it will be a field the AMS updates through a path your filter does not see.
• Counts by status. In-force policy count per line of business, per day. A step change is either a real book event or a broken sync, and both are worth a look before the agent runs.

Publish lag on the same internal page as the agent's queue. When an account manager says the agent used an old expiration date, the first question is answerable in one glance instead of one afternoon.

What this does not do

A read model does not make the AMS the second source of truth. It is a cache, and every write still goes back through the management system's own API with an approval behind it. Nothing in this pipeline edits a policy.

It does not give you real time. Between syncs your copy is stale by design, and for anything where a minute matters — a CSR asking the agent about an endorsement change made ten minutes ago — read through to the API for that one record and accept the latency.

It does not remove the API dependency, it reshapes it. If the platform is down for a day, the agent keeps drafting from a copy that is a day old, which is usually better than stopping and sometimes worse. Decide per workflow which one you want, write the rule down, and make the agent state the age of the data on every draft a person approves.

And it does not fix data that was wrong in the AMS to begin with. A faithfully synced copy of a policy with no expiration date is still a policy with no expiration date. That is the profile's job, and it comes first.