Reviews
Part of What a filing history can tell you about an entity
How to model filing history data so a name change never breaks a key
Filing history data modelling: one row per document, keys that survive a name change, verbatim office labels, and the validation checks worth automating.
If you store filing histories in a spreadsheet or database, avoid the first mistake most people make: a history is a log, not a set of attributes.
Model it as rows you append and never edit. Key each row on the entity and the office's own document reference, and most later problems do not arise.
This page is about the fields, the keys, and the traps in storing filing-history data across more than one register.
What to take away
- One row per document per entity per register. Never collapse a history into columns on the entity record.
- Key on jurisdiction plus entity identifier plus the office's document reference. Names and dates are not keys.
- Store the office's own type label verbatim, and map it to your own vocabulary in a separate column you control.
The fields a row actually has
| Field | Source | Notes |
|---|---|---|
| Jurisdiction | You | Which register. A US state, or a national registrar |
| Entity identifier | The register | The office's file number, as issued, with leading zeros and prefixes kept |
| Document reference | The register | The office's own reference for the filing, where one is shown |
| Filing date | The index | The date the office accepted the document |
| Effective date | The document | Blank unless you opened the image and it states one |
| Office type label | The index | Verbatim, including casing and abbreviations |
| Your type code | You | Your mapping of the label to a small controlled list |
| Page count | The index | Where shown; useful for spotting a placeholder |
| Image available | You | Yes, no, or paid |
| Image stored | You | Path or reference to your saved copy |
| Captured on | You | The date you took the row from the register |
The two columns marked "you" for type are the important pair. Offices label the same event a dozen ways across states. Older rows on one register may carry a different label than newer rows for the same event.
Office label vs your type code
Office type label
- Source
- The index
- Casing
- Verbatim
- Purpose
- See what office said
- Changes
- Varies by state and era
Your type code
- Source
- You
- Casing
- Controlled list
- Purpose
- Query across registers
- Changes
- Version controlled
Keep the original so you can see what the office said. Keep your mapping so you can query across registers.
When a register offers a programmatic interface, its own documentation defines each field. The Companies House developer overview shows a register describing its company and filing resources that way.
Read it even if your register offers nothing similar. It names the fields a register believes it holds.
Keys, and why names are not one of them
The natural key for a row is jurisdiction, entity identifier, and document reference together. Where a register shows no document reference, fall back to jurisdiction, identifier, filing date, and office type label, and accept that two same-typed filings on one day will collide; flag them rather than merge them.
Choosing the natural key
Does the register show a document reference?
jurisdiction + entity identifier + document reference
jurisdiction + identifier + filing date + office type label
Entity names must not be part of the key. They change, and a name change is one row you store. The filing date alone will not work either: offices routinely accept several documents from one entity on one day.
The entity number lookup pillar explains why the identifier is the only stable handle, and why it is stable only within one register.
Append, never update
A history grows. Your table should grow with it: a re-capture adds rows that were not there before and touches nothing that was. Two consequences follow.
Append-only capture cycle
- Re-capture the register
- Add rows not seen before
- Leave existing rows untouched
- Record disappearances as new rows
- Audit trail explains the difference
First, you need the captured-on column, because a row's absence from a capture is information: it says the office had not indexed the document by that date.
Second, office corrections happen. They appear as a new row contradicting an old one, or an old row no longer on the register.
Do not silently fix these: record the disappearance as its own row with your own type, so the audit trail explains why your table and the register differ.
Mapping type labels
Controlled type list
- Formation
- Periodic report
- Amendment
- Name change
- Agent change
- Address change
- Structuralmerger, conversion, domestication
- Administrative action, reinstatement, dissolution, other
Map each office label to one of these. Keep the mapping table under version control. Review it when a register introduces a label you have not seen.
Where an office label is ambiguous, "statement of change" is the usual example, map it to "other" until the document is opened, and let the opened document set the final code. Guessing at index time is how agent changes get counted as address changes for years.
Dates in three columns
Filing date from the index, effective date from the document, captured date from your own process. Never merge them, never infer one from another, or store an effective date you did not read.
Three dates, three sources
Filing date
- Source
- The index
- Blank means
- Not indexed
- Never
- Infer
Effective date
- Source
- The document
- Blank means
- Unknown
- Never
- Copy filing date
Captured date
- Source
- Your process
- Blank means
- Not captured
- Never
- Back-fill
A blank effective date means unknown, while a copied filing date in that column means false precision that will mislead later queries. The three-date problem is detailed in the filing history pillar.
Across registers
A company on two registers has two histories, two identifiers, and two vocabularies. Store them as two sets of rows with two jurisdictions. If you need a view of the company as a whole, build it as a query over both sets ordered by date, with the jurisdiction visible on every row.
Two registers, two histories
Register A
- Identifier
- Its own
- Vocabulary
- Its own labels
- Rows
- Separate set
- Combined view
- Query over both
Register B
- Identifier
- Its own
- Vocabulary
- Its own labels
- Rows
- Separate set
- Combined view
- Query over both
Do not build a merged table; you will lose which office said what, and the two offices do not agree on what a history contains. The differences are described in filing history by country, and they are large enough that a merged row would have half its columns blank on one side.
Collecting the rows in the first place
Most registers were built for a person with a question, not for a machine with a list, and their terms of use say so. Read those terms before you automate anything: rate limits, bulk-extraction conditions, and rules about records of individuals are all normal, and they differ by office.
Where a jurisdiction publishes an open-data catalog, the federal catalog's own guidance on APIs is a fair picture of what a well-served interface looks like and how far that is from screen-scraping a search page. When there is no interface, ask the office what it supplies rather than assuming a scraper is the answer.
Validation checks worth automating
- Every row has a jurisdiction, an identifier, a filing date, and an office label. Reject rows missing any.
- Identifiers match the register's format for that jurisdiction, as a string, without stripping zeros.
- No row's effective date is populated without an image-stored reference to back it.
- For each entity, the earliest row's type maps to formation or qualification. If not, the history may be truncated at the register's system date; flag it.
- Periodic-report spacing per entity is computed, not stored, and reviewed against the cadence the office states for that entity type before anyone calls a gap a lapse.
- The current registration status is stored with its own captured-on date and never back-filled onto older rows.
What the table is for
Kept this way, the data answers what a filing history is good for: when the entity was formed here, whether it has been maintained, where the breaks are, and what structural events occurred and when.
It cannot answer money, litigation, licenses, or people questions, because the register never held them. A schema leaving no room for those columns is accurate.
Common questions
Should I store the name at all, then?
Yes, as an attribute with its own captured-on date, never as a key. You need it to talk to humans and to recognize the record. You just cannot join on it.
How often should I re-capture a history?
As often as your decisions need it, and no more. A history that supports an annual review is re-captured annually. One that supports an active credit exposure is re-captured when the exposure changes. Re-capturing daily mostly buys you rate-limit problems.
What do I do when two registers disagree about the same event?
Nothing. Store both, with their jurisdictions, and let the disagreement stand. It is usually not an error: two offices recorded two filings made at two times for two different purposes.
Is it worth storing the images or just the references?
Store the images for any row you relied on. References break when a register re-platforms, and the image is the only part of the record that says what the company actually filed.
