The Fact Check Database

Why I built this

I’ve spent most of my career at startups I helped found, usually working alone or leading a small team. I built the Fact Check Database to work in a domain I care about and hadn’t worked in professionally: information integrity. I also wanted to design for the kind of organization that does that work, a grant-funded team where the pipeline, the data warehouse and the research each have their own owner, rather than a startup where one person holds every key.

What happened, in brief

• I built a four-stage pipeline on Google Cloud alone, archiving every fetch, for under 2 cents a day per environment.
• A copyright question and two months of NLP exploration kept the production pipeline switched off from mid July to early October.
• Standing it back up exposed three early shortcuts: a main branch that no longer matched what was running, no alerts, and a stand-in identity for a fact check.
• The archive and the way each stage absorbs repeated messages held up. Bounding units of work so that one bad item didn’t fail a whole run took more work.

The system pulls content from a list of 52 feeds. On October 9, the production dataset held 1,502 distinct fact checks, built from 15,075 archived records since October 7. Everything is deployed twice, first in a development environment and then to production. Figure 1 shows a simplified topology of the final system.

How to read this report

This report tells the story of how the project went while attempting to hold the project to a professional standard; it outlines what happened, what I decided, why, what it cost and what broke.

While building the system, I tried to approach it as a real project as much as possible. However, four things about it were not like a professional project:

• One person. There was no outside reviewer, advisor or owner other than myself.
• No consumers. Nobody depended on the dataset, so I considered data collected during development to be expendable. I accepted data loss when a change called for it, which is a luxury most production systems don’t have.
• My own time. I built it alongside freelance work and a six-week move. The nine months covered in this post are calendar months, but it doesn’t mean I had the opportunity to work on it daily.
• My own money. I paid the cloud bill myself, so cost was a design constraint from the start.

The parts worth judging this project by are the architecture, the correctness of the data, the operations and the decisions. Calendar time and scale were not my focus.

If you want to just read about the architecture, the decisions and the numbers, read the formal case study. The production site is at factcheckdatabase.com.

Part 1: Building the pipeline

Designing for a hypothetical research team

I know from experience that the design always comes from context, and it would be difficult to build a system like this without a clear research objective. However, I sketched out these hypothetical stakeholders:

• Grant PI or project owner: holds the keys to the entire system.
• Pipeline engineering: operates, extends and monitors the ingestion pipeline.
• Database management and analysis: operates and monitors the data warehouse (BigQuery).
• Research: academics or data analysts who consume curated data via SQL, CSV exports, Google Sheets or a Jupyter notebook.

While the imaginary stand-ins for the “who” were largely sufficient, I didn't flesh out what the research team would be researching. This led me to push off the decision of how to identify fact checks, which ended up being a hole in my design. I didn’t realize the size of the hole until September (more on this later).

I planned the pipeline as a chain of three small services on Google Cloud:

The ingestor would fetch the feeds on a schedule, the sanitizer would decide what was safe to pass on, and the extractor would turn feed items into fact check records. I chose BigQuery because it would let the researchers in my sketch access the data in a variety of ways, with no database server to run.

I chose this split for two reasons. I wanted to be able to re-run any stage from what the stage before it had archived, without fetching anything again, so every fetch is archived in full. I also wanted each service to hold only the permissions its job needs.

I also wanted a failure in one record to stay with that record. The feeds are third-party content outside my control and I knew the system needed a way to give up on or defer a message that can’t be processed. I chose to have every hand-off between stages go through Pub/Sub, which provides configurable retry logic and deadlettering for bad messages. This design achieves the desired effect: one bad feed cannot hold up the other sources.

At this point, I estimated I could finish the pipeline in about three months.

Initial infrastructure and CI setup

I started with a new infrastructure-as-code (IaC) project and a new GCP project. This meant I could declare the desired infrastructure and use my existing free Pulumi account to handle provisioning it. One of the first things I did was set up automatic deployment of new infrastructure using GitHub Actions. For this I set up an identity pool for GitHub Actions to use to impersonate a GCP Service Account using OIDC. This allowed GitHub Actions to run IaC deployments.

I then began provisioning the core infrastructure I knew I’d need for the system in a new GCP project, starting with an archive bucket and a customer-managed encryption key (CMEK). I decided to use CMEKs with the archive and the dataset because a grant-funded project might be expected to. Public feeds can still carry personal data and disabling a key provides a way to cut off access to everything encrypted with it.

Dropping automatic deploys to move faster

For two weeks of the project, every push to the main branch ran checks and then deployed the dev infrastructure. On January 27 I took the deploy step out, because waiting on a pipeline for every change was slow while the infrastructure was still taking shape. CI kept checking every push, and deploying became something I did by hand, after a while from my own machine.

For a one person project, trading process for speed was a reasonable call. What I gave up was the guarantee that the main branch matched what was running. I committed almost everything straight to main and nothing deployed it or compared it with the deployed system. I paid for this later.

Building the pipeline components

I started the first service, the ingestor, in week five, and the sanitizer a week later. I built both to switch between filesystem and GCS adapters, so I could run locally against the filesystem or in the cloud against GCS. Decoupling the services from their side effects this early cost more than it saved: I later used FUSE to mount GCS buckets into the filesystems of Cloud Run instances, so a filesystem was present in both environments and the adapters weren't needed.

In the extractor, which I started in April, I used the strategy pattern to keep each version of the extraction logic separate and selectable. A change to how records are extracted became a new strategy instead of an edit to the old one, so every row records which version produced it. Old archives can be re-extracted either in the way they were first extracted, or in an updated way. I also moved every data shape into versioned contracts, so each record and storage path carries a version.

Two abstractions: one I'd remove, one I'd keep

Every abstraction is more code to read and maintain, so it has to earn its place. While the implementation of ports and adapters can look like the strategy pattern, this situation highlighted the difference.

The ports and adapters pattern is about abstracting side-effects in order to write code as a pure, testable core that can run in different environments. However, environments vary less than one might think and applications of the pattern are likely to have a larger scope than those of the strategy pattern. It’s the difference between abstracting a payment strategy to be able to choose between strategies at runtime versus abstracting all reads and writes to be able to choose between datastores at deploy time.

The strategy pattern is about control flow. In this case, extraction logic does change between content types and versions, so the strategy pattern was the right call. The ports and adapters pattern was a main reason the repository went through three layouts in its first four months and I was still deleting unused adapters in October.

Keeping boundaries

Every stage added is more to build, deploy and operate, and in this stretch I added two I hadn't planned for.

In mid-February I paused work for about six weeks for a move. When I started again I realized loading the extracted data into BigQuery needed its own service, which changed the chain from three stages to four:

The split had three reasons. In my stakeholder sketch the dataset has a different owner from the pipeline, and the boundary between them belongs at the loader. I decided the pipeline engineering team should not hold write access to the database team’s resources just like the database team shouldn’t be able to read the archive directly.

The hand-off between ingestion and analysis becomes a file in a shared staging area owned by the project admin team. The file contains a batch of extracted fact checks so a load can be re-run without extracting again. This is also when I added the dead-lettering I had planned: in mid-April every subscription got a dead-letter topic, and in early May I added persistence to keep dead-lettered messages for me to inspect by hand.

Adding tests

Tests pin a design in place, and for my first three months the design was still moving.

I wrote no tests in the first weeks and tested services by deploying to dev. Tests arrived when I started building the extractor in mid April. I wrote the first real ones, for the ingest step, the sanitizer and the record and path schemas. By then the stages had a shape worth pinning down. Over the next few months the tests' main value was telling me when a reorganization or a contract change had broken something, which happened often.

Part 2: Visibility, a public site and a copyright dilemma

Logs, traces and a dashboard

In April and May I spent a lot of time on OpenTelemetry tracing and was eventually able to trace a row in BigQuery backwards through the service and code level execution paths that produced it. Adding spans and annotations to the service code was one place that Effect-TS really shone. Capturing this level of detail would have taken much longer without it. See appendix D for screenshots of the trace explorer.

In early May, I revised the provisional logging that already existed and added annotations and custom metrics while redesigning the dashboard in tandem. Later, the key log events that the dashboard’s log based metrics rely on became contracts to be consumed by both the pipeline and the dashboard. The final dashboard is one page, defined in Pulumi so that dev and production get the same one. It covers the ingestion pipeline only. See appendix C for screenshots of the dashboard.

Adding a public site

Nearly five months in, I had a dashboard. But when I showed it to people I knew, they didn’t seem to understand what it was doing. I wanted non-technical people to be able to see the project too, so I decided to add a public site, with a search index mirroring what the pipeline was collecting. I found that the domain factcheckdatabase.com was available and purchased it. This is when the project got its name.

Algolia was the fastest way to a live demo. The free tier meant nothing to run or pay for, and its search components gave me a working search page quickly. At first I fed the index with a ready-built integration that read from BigQuery on a schedule. That got the search page running, but it tied the BigQuery table schemas to a SQL query I had to update by hand in the Algolia web console. When runs failed, the console reported errors incompletely, which made them frustrating to debug. In early July I replaced the integration with a second loader of my own, which reads the same batch files as the BigQuery loader. From then on the pipeline forks after the extractor, sending extracted data to BigQuery and Algolia in parallel.

The index is still coupled to the BigQuery table schema indirectly, because the batch files in staging follow that schema. What changed is that the index no longer needs BigQuery to be available in order to update, feeding it is monitored like any other stage, and the index schema acts as a buffer between changes to the BigQuery schema and the website’s view model.

Appendix A has a screenshot of the search page.

A copyright question I hadn't planned for

In late June I added rate limiting to the site, to stop automated scraping of the articles on it. Working on that made me realize I was doing something close to what I was guarding against. The feeds are public, but I had no agreement with any publisher, and my public site was showing their articles in full.

My concern was never that I held a copy. Collecting articles for analysis is something research datasets do routinely. My concern was that the site could become a place where people read the publishers’ fact checks instead of visiting the publishers, taking their readers and the traffic that comes with them.

In mid-July, 12 days after production went live, I switched off ingestion in both environments and took the public site offline. I wasn’t sure the project would come back in the same form, so the data it was collecting might never be worth anything, and I didn’t want to add to a legal question I didn’t yet understand. Whatever publishers posted in the meantime was lost.

Truncating articles as a stopgap

On July 15 I changed the extractor so that the dataset and anything public carry only a 500-character preview of each article body, with a link back to the publisher. At the time this was a stopgap. I didn’t yet know whether a preview was the answer, or whether the whole project needed to change shape.

What each datastore keeps follows its audience (Table 3):

• The archive keeps the full text, deliberately. It is private, encrypted under my own key, and nothing in it is ever published.
• The dataset, which researchers can request, keeps only a preview of each body. Summaries stay as the publisher wrote them, even though some publishers put the whole article there. A researcher querying a dataset isn’t a substitute reader.
• The public site shows previews only.

This is consistent with the Association of Research Libraries’ analysis of text and data mining under fair use, which treats analysis of full texts as generally permissible and draws the line at making the full text publicly available.

Until October 14 the search index still carried full summaries. They were also cut to previews as part of a search loader fix described in Part 3.

Could the pipeline support NLP analysis of its own?

The stopgap left a bigger question. If the project couldn’t republish other people’s work, maybe it had something else to offer. Could the dataset support analysis of its own, such as sentiment analysis or stance labeling? Data derived from the fact checks, in place of their text, would also answer the copyright concern more fully than a preview.

To find out, I switched the development pipeline back on in August and experimented with NLP techniques, both on the data it collected and on public news datasets. Dev is private, with its site behind a login. Production, the search index and the public site all stayed off.

After two months I decided NLP was beyond the scope of this project. It needs a lot of compute compared with parsing RSS feeds, and running it in production is a large undertaking of its own. Deciding against it settled the copyright question too: the preview would be the answer, and production could come back. I moved the useful parts of the NLP work into their own repository and turned back to finishing the pipeline. In the future I’ll write up the stance analysis technique I implemented, inspired by this study.

Part 3: Finishing the pipeline, solidifying operations

Standing it back up

Over a few weeks I removed the half-finished features and stood the system back up, redeploying development in late September and production in early October. Bringing a system back after months off should mean switching it on again. However, my earlier decision to deploy by hand let the infrastructure and code drift significantly.

After deciding against the NLP work, I had a production system that had been offline since July, and half-finished changes on the main branch. Nothing had kept the deployed system in step either. Getting main back to something I could deploy took about a week. Getting rid of the NLP work removed over 12,000 lines and the redeploy changed 45 resources.

Adopting a Git strategy

In October I put some of the process back. A change now reaches main only through a pull request with passing checks, and a daily workflow previews four of the five stacks and fails when the code and the deployment differ. I still deploy by hand, but CI deployments are on the roadmap.

Adding alerting

Without alerting, a pipeline stage can fail until someone goes looking for a problem. This was happening in early September when the ingestion stage failed 42 of 48 of its runs over eight days. The dashboard and traces I built in the spring would only show me a failure if I went looking. I accepted this until I started running the production pipeline in October, when I added the five alert policies in Table 4. Alerts in production trigger an email and alerts in development just open an incident.

What counts as one fact check?

A dataset was meant to be deduplicated — counting each fact check once. However, what it means to count something once depends on the research objective of building the dataset in the first place. If the research objective involves tracking changes to the headline of a fact check article, then each article might get exactly one row for each variation of the title that existed. If the research objective involves tracking what fact checks were available to the public on a particular day, then each article might get exactly one row for each day that it was available. I neglected to define a research objective for the hypothetical research team at the start and so the question of what deduplication meant had no answer.

I decided that the dataset’s purpose was simply to serve as a catalog. Each fact check should get exactly one row and fact checks with different content or titles would be considered duplicate if the article’s reference URL was the same. Changes to content would be considered an update to an existing row rather than the insertion of a new row.

Each service was identifying data differently and without cohesion: the ingestor identified fetches with the hash of the result, the extractor identified fact checks with a hash of a subset of its fields, the search index keyed on the extractor’s hash and the dataset computed its own identifier in SQL using the article url and title. This was evidenced by the dataset and search index disagreeing not only on which fact checks were present, but also on how many times they were duplicated. Without a research objective properly defined, identity had become an afterthought.

Fact check ids needed to be reimagined from the ground up. I separated three identities that the old code had blurred together: the fetch, the content it returned, and the fact check itself (Table 5).

The final identity of a fact check is the ID the extractor computes. Every four hours a scheduled MERGE takes the latest staging row for each id into the curated table, as a new row or an update to an existing one. Because the content hash is the version, an unchanged re-fetch changes nothing, and when a publisher edits an article's title or summary the curated row is updated rather than duplicated, as long as the URL stays the same. The curated table keeps only the latest version of each fact check. Staging keeps every row for seven days, and the raw feeds stay in the archive.

Data schemas were always versioned, so the old rows could have been migrated and backfilled with corrected identities. Following the ground rule about development data I set at the start, I dropped them instead of migrating them.

Identity depending on article URLs does come with a risk: a tracking tag such as ?utm_source=rss in the URL can change the ID of the article. After implementing the identity redesign I also extended the sanitizer to strip tracking parameters from every URL in a feed before extraction. A consequence of this is that the fact check’s identity is now coupled to the sanitizer’s configuration: changing it has the ability to change the IDs of fact checks when they are extracted.

The identity change went in with 15 new tests. One pins the new id function to a value that BigQuery had computed for a real row, so an accidental change to the function fails a test. Another checks that editing a title or the content does not change the id.

See appendix B for screenshots of the final deduplicated research dataset, opened in various formats and tools.

Absorbing duplicates

The January decision to pass work between stages via Pub/Sub meant that every stage had to cope with seeing the same input again (at-least-once delivery). Any message published should be expected to arrive twice.

An example of this situation happened in production in October, when sanitizer requests ran past the default 10-second acknowledgement deadline. The ingestor job enqueues messages in bursts and introducing the expanded source list caused each burst to include a larger volume of messages. The cold sanitizer service instance was not able to warm up and then handle the entire volume of concurrent requests within each request’s acknowledgement deadline.

When the deadline passed without the sanitizer completing the request, Pub/Sub attempted to redeliver messages again while the service was still processing the first delivery. By the time of the second delivery, the service was already warm and had handled at least some of the messages from the original burst, so it was able to process the redelivered messages within the deadline, draining the queue. In this case, the service was already designed to handle repeat deliveries and increasing the deadline to five minutes mitigated the redeliveries.

While processing the same message twice isn’t desirable, the system can handle at-least-once delivery without becoming incorrect; a duplicate delivery costs some compute and, at worst, an extra row in staging, but it doesn’t add new rows to the curated table. Each stage handles duplicate messages in context (Table 6).

Improving logging and final debugging

After standing the system back up, the first problem I noticed was a backlog in front of the extractor. During the NLP work, I had increased the size of the source list from 8 to 54. The batch size was still set to 100, which fit the original 8 sources but was not enough to drain the queue fed by the new source list. After fixing this, several more errors were still preventing pipeline data from reaching the BigQuery dataset. Not being able to immediately see the issue in the logs, I spent some time formalizing a logging strategy and aligning the code with it: library code logs internal execution at trace and debug, while apps consuming library code log at info and above.

After improving logging in the system, I deployed the new services at trace log level. For the next week, I began a daily routine of analyzing the log dumps with an LLM and fixing any bugs that were found. One of the bugs involved non-standard HTTP status codes being returned by Cloudflare and needed to be traced back to a record of the fetch in the archive because the relevant logs had expired.

Whenever a fix involved a change to source code, I added a test so that it could not come back. Depending on how confident I was of knowing where the bug came from, the tests were either written before the fix or after the fix was confirmed in the running pipeline. The suite went from 100 tests across 27 spec files in mid-September to 207 tests across 48 files in early October (see Figure 4). All of them cover pipeline services and shared libraries and use production-like data where possible.

Where it stands

Here is the system as it stands (Figure 5). The diagram shows the main path only, from a publisher's feed to the two places the data ends up. Cylinders are storage, rectangles are services and jobs, and the outlined groups show the five domains.

The diagram shows the main path of a fact check through the system, starting with a publisher’s feed and ending in the two final repositories for the data. In the production system each of the five domains has its own Google Cloud project and in development all five share one.

I left the failure paths off the diagram: the dead-letter buckets, retry policies and the ingestion message log that a templated Dataflow job can replay. The formal case study has the full diagram with topics, schedulers and failure channels.

A retrospective on the timeline

Figure 6 sets my three-month estimate against what happened. The initial estimate covered only the pipeline. The pipeline itself ran about a month long but most of the rest came from scope I added in these months without re-planning.

The public site, started in late May, raised the copyright question, and that led to two months of NLP exploration. This is the place where the original estimate broke down the most. However, as mentioned in the introduction, this is a personal project and calendar months don’t necessarily reflect working time. I committed code on 147 days out of the 288 days covered in Figure 6.

What remains to be done

• There is no dedicated backfill job. Archived fetches can be extracted again, but it requires running a templated Dataflow job. A dedicated backfill job that can filter and select by date range would be more precise and harder to misconfigure.
• Testing has gaps. The infrastructure stacks, the site and the emailer have no tests, nothing measures coverage, and no integration or end-to-end tests run entire stages against each other.
• Identity is made brittle by its dependency on the sanitizer. A change to the sanitizer's list of tags to strip from URLs can change identity during extraction.
• The dashboard covers ingestion only. The loaders, BigQuery and the site are visible only through logs and traces.
• Deployment is still manual. This will require decisions about whether to create new deployment branches as well as careful management of the highly privileged access to GCP that CI deployment would require.
• The sanitizer has read/write privileges to the entire archive. Dedicated archive buckets for raw and sanitized fetches would improve security posture.
• The website stack has no alerting. Nothing watches for failed load jobs or website downtime.

Conclusion

I built the Fact Check Database not only to gain experience in a domain I care about, but also to design for a kind of organization I hadn’t worked in before. Nine calendar months later, it runs in two environments for under 2 cents a day each and produces a deduplicated dataset of fact checks that a researcher can query from SQL, Google Sheets or a notebook.

Looking back, I can see a split in which decisions held up. The ones I made deliberately at the start did: archiving every fetch, versioning contracts, least-privilege, stages handing off work via messaging and building for at-least-once delivery. The costs came from decisions I deferred: what the research team would ask of the data, what counts as one fact check, and when to put guardrails back on the main branch. Each was easy to put off, and each got more expensive the longer I waited. My main take-aways from the experience are to settle the decisions later stages will depend on early, and to revisit a shortcut when the design stops moving — not after it breaks something.

Next, I plan to automate deployment and build a proper backfill job. The dataset is still available to researchers on request. Please reach out if you’re interested in any part of this project and have questions.

Appendices

Appendix A: Screenshot of the public site, October 15

Appendix B: Accessing the research dataset in various tools and formats, October 15

Appendix C: Screenshots of the pipeline dashboard, October 15

Appendix D: Tracing a failed fetch to a source using the pipeline’s execution trace explorer, October 15