> ## Documentation index
> Fetch the complete index at: /llms.txt
> Use it to discover every page before exploring further.

# Keeping in sync

> Two ways to follow the portfolio as the nightly fetch moves it: read it again through the API each morning, or take the file feed retrieve-IQ delivers to you.

## What moves overnight

Each night retrieve-IQ fetches bills from the units on the schedule set for your firm: every parcel is read again at least weekly, and one whose last fetch failed is tried again the next night. The checks run on what was read and the deadlines follow. A new bill arrives as `NEEDS_REVIEW`. A bill whose payment, dates or interest the unit has changed is brought up to date in place, under the same `key`, with its checks run again, whatever was decided about it. A bill the unit re-issues with other charges (on a page that lists none, another total) is the bill's next `revision`, under a key of its own and waiting for review, and the revision it replaces reads `SUPERSEDED` and counts nowhere; a rejected bill stays rejected. A unit that did not answer leaves its bills as they were. Appeals and owners change only when a person or your system changes them; a parcel's current assessment follows what the unit publishes, and the fetch fills in a blank street line from the unit's record.

## Reading the portfolio again

There is no "changed since" filter in the API yet, so a system that mirrors the portfolio reads it in full each morning and compares. Read it in two steps, so that each request stays inside the [limits](/docs.md#limits). First the parcels, 100 a page in parcel-number order, each with its six newest bills and their headline figures (needs `PARCEL_READ` and `BILL_READ`); a portfolio of ten thousand parcels is a hundred requests:

```graphql
query ($after: String) {
  parcels(first: 100, after: $after, orderBy: { field: PARCEL_NUMBER, direction: ASC }) {
    pageInfo { hasNextPage endCursor }
    edges {
      node {
        id
        parcelNumber
        status
        legalEntity { id name }
        jurisdiction { id name state unitCode }
        currentAssessment { assessmentYear totalValue }
        bills {
          key
          status
          withheld
          totalBilled
          amountOutstanding { known installmentsWithUnknownPayment }
        }
      }
    }
  }
}
```

Then, for each parcel whose bills moved, the installments:

```graphql
query ($id: ID!) {
  parcel(id: $id) {
    bills {
      key
      status
      withheld
      installments { id installmentLabel dueDate amountDue amountPaid paymentKnown amountOutstanding status }
    }
  }
}
```

A page of the first query costs 865 of the 5,000 a request may spend, and the second 842; the tests hold both there with each release.

- Key parcels on `id` and bills on `key`, which is stable across requests. A bill's payment and dates can change under the same key: compare `status`, `withheld`, `totalBilled` and `amountOutstanding` with what you hold, and read the installments of the bills that differ. A re-issue is a new key, the bill's next `revision`, and the one it replaced reads `SUPERSEDED`.
- Read in the morning, Pacific time: the fetch runs overnight and the checks follow it.
- A parcel is never dropped from the list: one that was sold or is exempt says so in its `status`.
- A bill with `withheld: true` shows `null` for `totalBilled` and `amountOutstanding` until a person resolves the check that holds it; its installments still carry their figures.

## The file feed

For a system that would rather load files than call an API, retrieve-IQ can deliver the whole portfolio each night as a set of files: to an SFTP server (a user on it whose authorised keys include the public key retrieve-IQ gives you, the path to write under, and the server's host key fingerprint, which is pinned), to an S3 bucket or an S3-compatible store (the bucket, a prefix, and credentials that may write under it), or to a folder on a host retrieve-IQ runs. The feed is set up for your firm on request. Each night's files land in a folder named for the date, `YYYY-MM-DD` in UTC, under the path you gave; `manifest.json` is written last, so a folder with a manifest is complete and one without is still being written or was abandoned. Once the feed runs, [feedRuns](/docs/queries.md#feedruns) shows each client's nights and where the files went, read under that client's engagement.

A delivery is four files, every client of the firm together:

- `bills.csv`: one row per installment of every bill, with the client named on each row.
- `line_items.csv`: one row per charge on a bill, joined to `bills.csv` by `tax_bill_id`.
- `feed.xlsx`: the same two tables as the sheets "Bills" and "Line items", money as numbers.
- `manifest.json`: written last; the night's id, the schema version (`3`), each file's row count, byte size and SHA-256 so a partial upload is caught before it is loaded, each client's row count, the night's fetch summary per client, and the readings withheld since the feed last ran: pages fetched that could not be read as a bill, each named by parcel and unit with the year, the bill type and the reason, so a bill that is missing is missing on purpose. A bill recorded with a check standing is in the file, not here.

The CSVs are RFC 4180: UTF-8 without a byte order mark, CRLF records, a field quoted only when it holds a comma, a quote or a line break. Money is dollars with two decimals as text (`71486.32`); an unknown figure is an empty cell, never `0`. Booleans are `true` and `false`. Every bill retrieve-IQ holds is in the file, a rejected one and one a failed check holds back included, with their amounts empty and `figures_withheld` saying so, so a column summed never adds a figure that failed a check. The files are the whole portfolio every night, not the changes, and `fetched_at` moves whenever an account's bills are read again, so two nights' files rarely match byte for byte: compare rows on the other columns to see whether a figure moved.

Within each client, rows are ordered by state, county, unit, parcel number, assessment year, bill type, bill number, revision and installment, so one unit's rows are contiguous; the clients follow one another. A parcel billed by more than one unit is more than one row per installment, each naming its unit and account; in California it is one.

A client whose fetch failed that night is left out of the files rather than delivered as empty, and its night in `feedRuns` says so (`SWEEP_FAILED`); the other clients' rows are delivered. When the files could not be produced or delivered, no client gets them, and every client's night names the stage that failed.

## bills.csv

Sum amounts per row. Count properties, and sum `assessed_value`, by distinct `parcel_id`.

| Columns | Meaning |
| --- | --- |
| `client_tenant_id`, `client_name` | The client the row belongs to; every client of the firm is in the one file. |
| `parcel_id` | The parcel, one property; stable across nights. The feed's ids are retrieve-IQ's own and differ in form from the API's, so match a row to the API on `parcel_number` and `jurisdiction_code`, not on id. |
| `county`, `state` | The billing unit's county, as registered (`Los Angeles County`), and its state, two letters. |
| `jurisdiction`, `jurisdiction_level`, `jurisdiction_code`, `jurisdiction_id` | The unit that sent the bill: its name, its level (`county`, `city`, `township`, `school_district` and so on), its stable code to key a per-unit table on, and retrieve-IQ's own id for it. |
| `tax_account_id`, `account_number` | The billing relationship the bill belongs to, and the unit's own number for the property under it (the APN in California). |
| `fiscal_year_label` | The year label the unit's calendar gives the bill, where one is recorded; empty otherwise, never guessed. |
| `parcel_number`, `situs_line1`, `legal_entity` | The parcel number as the assessing unit knows it, the property's street line when known, and the owner of record as registered. |
| `assessment_year`, `payable_year` | The year of the roll the bill is for, and the year it is payable in. |
| `bill_type`, `bill_number`, `revision` | `SECURED`, `UNSECURED`, `SUPPLEMENTAL`, `ESCAPE` or `REDEMPTION`; the unit's own number, empty where it publishes none; `0` for the bill as the unit first issued it, and the next number for a re-issue with other charges or a person's correction. Only the newest revision of each bill is written. |
| `installment_no`, `installment_label` | Which installment of the bill the row is (1, 2, ...) and what the unit calls it. |
| `amount_due`, `amount_paid`, `payment_known` | The amount on the installment; what the unit reports paid, empty when it publishes no figure; `true` when `amount_paid` is a figure. Zero paid and unknown are different facts. All three are empty on a row whose figures are withheld (below). |
| `penalty`, `penalty_basis` | The penalty the unit states and whether it is `included_in_amount_due` or `added_to_amount_due`; both empty when none is stated. |
| `amount_outstanding` | `amount_due`, plus the penalty when it is added, minus `amount_paid`; empty when a side of that sum is unknown. |
| `due_date`, `delinquent_after` | When the installment is payable and the last day before it costs more. |
| `review_status` | The bill's state in review, in lower case: `needs_review`, `validated`, `disputed` or `rejected`; `ingested` is reserved. A rejected bill's rows are in the file with their figures withheld. |
| `validation_warnings` | The codes of the checks still standing against the bill, separated by `;`, on the row of the bill's first installment and empty on the others. Warnings and errors are listed alike; a check a person resolved, or one a later fetch found passing, is not listed. |
| `assessed_value` | The newest assessment held for the parcel and the assessment year. The parcel's figure, repeated on every row of the parcel: never sum it across rows. |
| `source_url`, `fetched_at`, `as_of` | Where the bill was read from, when the account was last fetched successfully (which moves whenever its bills are read again), and the date the unit's page stated. |
| `tax_bill_id` | The row's id: the join key to `line_items.csv`, which joins to the row of the bill's first installment. |
| `figures_withheld`, `withheld_reason` | `true` on every row of a bill that a failed check holds back or a reviewer rejected, whose `amount_due`, `amount_paid`, `payment_known`, `penalty` and `amount_outstanding` are then empty, so a column summed never adds a figure that failed a check; `withheld_reason` says why (`rejected by a reviewer`, or `a check failed:` and the checks). `false` and empty otherwise. |

## line_items.csv

One row per charge on a bill. The charges describe the whole bill and sum to its installments together, not to the row they join.

| Columns | Meaning |
| --- | --- |
| `tax_bill_id`, `tax_account_id`, `jurisdiction_code` | The bill row the charge belongs to (the row of the bill's first installment), with its account and unit repeated so the file joins without the other. |
| `parcel_number`, `assessment_year`, `bill_type`, `bill_number` | The bill's key, repeated for a reader that has only this file. |
| `ordinal` | The charge's position on the bill. |
| `description`, `amount` | The charge as printed; the amount is signed, so a refund is negative. |
| `charge_type` | `ad_valorem`, `voter_approved_debt`, `direct_charge`, `special_assessment`, `penalty_and_cost` or `fee`; empty when the bill's own sectioning does not say, never guessed. |
| `rate`, `code` | The rate and the unit's code for the charge, when printed. |
| `levying_agency`, `agency_phone` | Who levies the charge and the phone number printed beside it. |
| `figures_withheld`, `withheld_reason` | As on the bill's rows: when the bill's figures are withheld, `amount` is empty, so the charges never add up to what the bill holds back. |