How to Build an RCM Data Foundation for AI: The 5-Table Model

TLDR: An RCM data foundation for AI is five validated master tables (claims, remittances, denials, appeals, and prior authorizations) joined on one encounter key and owned by a named person. Build them in that order, test each one against four validation rules before any AI runs on it, and pilot on a single payer. Without the foundation, AI denial tools inherit every gap in your current data and report results nobody can defend to the board.

Best For: VP or SVP Revenue Cycle and Directors of RCM or Payer Operations at US provider, laboratory, and diagnostics enterprises (1,000 to 15,000 employees) who have accepted that AI needs a data foundation and now have to specify what that foundation contains.

An RCM data foundation for AI is a set of validated, encounter-keyed master tables that records what was authorized, billed, denied, appealed, and paid for every claim, so that AI can prioritize, match, and draft against facts your own team has reconciled. The point is not storage. Most revenue cycle shops already have the data somewhere, usually in four or five places: the practice management system, the clearinghouse, a denials spreadsheet, an authorization portal export. The point is that none of those sources agree with each other, and AI applied to disagreeing sources produces confident answers that are wrong. Revenue cycle leaders who want AI to reduce denials have to decide, table by table, what "true" means before they let a model touch the worklist.

What an RCM data foundation for AI is, and what it is not

An RCM data foundation for AI is the smallest set of reconciled tables that lets a model answer four questions about any encounter: was it authorized, what was billed, what was denied and why, and what was finally paid. It is narrower than a data warehouse and deeper than a vendor dashboard. A warehouse collects everything; a dashboard shows whatever the vendor loaded. A foundation is the handful of tables you have personally verified against remittances.

The distinction matters because the leak is real and the usual tools are not finding it. In a January 2026 MGMA poll, 48% of practice leaders named denials and appeals as their single biggest revenue cycle leak, ahead of front-end eligibility (23%) and coding (13%). The 2025 State of Claims survey of 250 revenue cycle decision makers found 41% of providers now run denial rates of 10% or higher, and the top cited cause of denials was missing or inaccurate data, at 50%. The second cause was authorizations at 35%, and the third was incomplete registration data at 32%. Read those three together and the message is uncomfortable: most denials start as a data problem on the provider side, long before a payer makes a decision.

How the foundation differs from a warehouse and a dashboard

Why industry thinking shifted from tools to tables

Five years ago the conversation was about denial management software. The 2024 to 2026 data moved it. Gartner's February 2025 prediction that organizations will abandon 60% of AI projects unsupported by AI-ready data through 2026, and its finding that 63% of organizations are unsure they have the right data management practices for AI, landed in healthcare finance at the same time as denial volumes rose. An HFMA poll published in 2025 found 63% of organizations already using AI or automation somewhere in the revenue cycle, yet only 15% reported a positive return, and the top barrier at 51% was IT infrastructure that could not get data to the tool. The sequence most teams followed was tool first, data second. The ones with a return did it the other way around.

The 5 master tables every RCM data foundation for AI needs

An RCM data foundation for AI contains five master tables: claims, remittances, denials, appeals, and prior authorizations. Each has a defined grain (what one row means), a shared encounter key, and a source system of record. Together they let a model trace a single service from authorization through final payment without a human stitching spreadsheets together. The order below is also the build order, which the next section explains.

1. The claims table

One row per claim line, keyed to the encounter, the patient account, the payer, and the rendering provider. Fields that matter for AI: dates of service, submission date, procedure and diagnosis codes, billed amount, place of service, and the claim status history with timestamps. The claims table is the spine. Every other table joins to it. If the clearinghouse and the billing system disagree on submission date, the claims table records which one you trust and why.

2. The remittances table

One row per adjudicated claim line from the 835 remittance advice, keyed back to the claim line. Fields: paid amount, allowed amount, adjustment group and reason codes, remark codes, remittance date, and check or EFT reference. The remittances table is the only table a payer has already validated for you, because it is the payer's own statement of what happened. That makes it the reconciliation anchor for the other four.

3. The denials table

One row per denial event, not per claim. A claim denied twice has two rows. Fields: denial date, payer, denial category (eligibility, authorization, medical necessity, coding, timely filing, request for information), the raw reason and remark codes, the dollar exposure at the time of denial, the appeal deadline implied by the payer's contract, and the current disposition. The category field is where most organizations fail. Hospital benchmarking data covering more than 2,300 hospitals, reported in April 2026, showed request-for-information initial denials up more than 10% in 2025, clinical initial denials up more than 12%, and final denials up more than 14%. Those are three different problems with three different fixes. A denials table that lumps them into "denied" cannot support prioritization.

4. The appeals table

One row per appeal action, keyed to the denial event. Fields: appeal level, submission date, the documents attached, the clinical reviewer who signed, the payer's response date and outcome, and the amount recovered. The appeals table is the one most shops do not have at all; the appeal lives in a letter template and an email thread. The data on why it matters is strong. KFF's analysis of 2025 data across 14 insurers found that 67% of Medicare Advantage prior authorization denials were overturned on appeal, as were 47% in Medicaid managed care and 43% in ACA Marketplace plans, yet denials were rarely appealed in the first place. The AHA's October 2024 update put the gap more sharply: 83.2% of appealed denials were overturned in 2022, but only 9.9% of denials were appealed. Without an appeals table, nobody can tell you which 9.9% you chose or what happened to the rest.

5. The prior authorizations table

One row per authorization request, keyed to the encounter it was meant to cover. Fields: payer, service requested, request date, submission channel (portal, fax, phone, electronic), documents submitted, decision date, decision, authorization number, approved units, and validity window. The prior authorizations table exists so that the denials table can answer the question "was this denied because the authorization was missing, expired, mismatched, or never requested?" The AMA's 2025 physician survey of 1,000 physicians found practices spend an average of 13 hours of physician and staff time per week on prior authorization, 40% employ staff who do nothing else, and 32% report requests are often or always denied. That labor leaves almost no structured record behind. The table is how you capture it.

What "validated" means: the four tests

A validated RCM table is one that passes four tests before any AI runs on it: it is reconciled to the remittance, keyed to one encounter identifier, dated at every status change, and owned by a named person with a refresh cadence. Fail any one and the model is working from a guess. Most organizations that describe their data as "clean" have passed the first test for one payer and none of the others.

Reconciled: the totals tie to the 835

Reconciled means the sum of paid amounts in your remittances table equals the deposits in the bank for the same period, and the sum of denied amounts in your denials table equals the adjustment amounts on the 835s. The test is simple and almost nobody runs it monthly. The HHS Office of Inspector General found in its Medicare Advantage audit that 18% of payment denials met Medicare coverage and billing rules and should have been paid, most of them caused by manual or system processing errors. Payers make processing errors; so do providers. Reconciliation is how you find out whose error it was.

Keyed: one encounter identifier joins all five tables

Keyed means a single encounter identifier appears on every row of every table and joins cleanly, with no fuzzy matching on patient name and date of service. The practical test is to pick 50 denials at random and ask the team to show the matching authorization, claim line, remittance, and appeal for each one without opening a second system. Where the join breaks, the AI will break in the same place, and it will not tell you.

Dated: every status change has a timestamp

Dated means every status transition carries the date it happened, not the date someone updated the spreadsheet. Appeal deadlines, timely filing windows, and payer response clocks all depend on this. Premier's analysis of 2023 claims across 280 hospitals found that roughly 15% of claims were initially denied, 69% of contested denials were eventually paid, and a typical denied claim went through about three rounds of review at 45 to 60 days each. A model asked to prioritize by deadline cannot do it on a table where "denied" has no date.

Owned: a named person and a refresh cadence

Owned means one person's name is on the table, that person can explain every field, and the table refreshes on a schedule the rest of the organization knows. Ownership is the test that separates a foundation from a project. Warehouses are owned by IT and refreshed when IT gets to it. Vendor dashboards are owned by the vendor. A foundation is owned inside revenue cycle, which is why it survives a system migration and a vendor change.

For a multi-entity provider or diagnostics company whose denial write-offs are already quantified as a material share of net revenue, with Medicare Advantage denials rising faster than the rest of the book and a board pre-read on the calendar, a generic approach is not enough. The answer is a dedicated build that does three things: reconciles one payer's five tables end to end before touching a second payer, assigns a revenue cycle owner to each table with a published refresh cadence, and holds every AI output to a comparison against the reconciled baseline rather than against the vendor's own report. Anything less produces a number the CFO cannot sign.

The build order, and why claims come first

The build order for an RCM data foundation for AI is claims, then remittances, then denials, then appeals, then prior authorizations, one payer at a time. Claims come first because every other table joins to them. Remittances come second because they are the only payer-validated source and they let you reconcile everything that follows. Denials, appeals, and authorizations come last because they are the tables where your own process is messiest and reconciliation is what exposes the mess.

Why one payer, not all payers

Starting with one payer is not timidity. Payer behavior differs enough that a model trained on the blended book learns nothing useful about any single payer. The AHA's 2024 data showed care denials rising an average of 55.7% for Medicare Advantage claims between 2022 and 2023 against 20.2% for commercial claims. Premier found Medicare Advantage plans required prior authorization on 30.5% of claims. Pick the payer where the write-offs concentrate, build the five tables for that payer alone, and reconcile them. You will have a defensible number in weeks rather than a warehouse in quarters. The earlier post on why AI denial tools fail on a fragmented RCM workflow explains what goes wrong when teams skip this step and point a tool at the whole book.

What each table lets AI do

Once the claims and remittances tables pass the four tests, AI can do documentation matching: checking that what was billed matches what was documented and authorized before submission. Once the denials table passes, AI can prioritize the worklist by exposure and deadline rather than by arrival order. Once the appeals table passes, AI can draft payer-specific, patient-specific appeals from prior winning appeals rather than from a template. Once the prior authorizations table passes, AI can flag the orders most likely to be denied for authorization reasons before service is delivered. The three-column map of what AI can and cannot fix in claim denials covers each of those uses in detail; the foundation is what makes them real instead of demo-ware.

How long one payer takes

A realistic timeline for one payer is six to ten weeks to a reconciled claims and remittances pair, another four to six weeks for denials and appeals, and two to four weeks for authorizations, assuming a data analyst inside revenue cycle and read access to the billing system, clearinghouse, and authorization portal. The pace is set by reconciliation, not by data movement. Teams that have already read the argument for AI as the reason to modernize legacy RCM systems will recognize the sequence: the foundation is the modernization, built one layer at a time.

What a clean foundation still cannot do

A validated RCM data foundation for AI does not fix missing clinical documentation, does not change payer policy, and does not make a denial appealable when the clinical record does not support medical necessity. AI with clean data can tell you which denials to work and draft the letter. It cannot invent the physician's note, and it cannot know a payer's unpublished clinical criteria. Revenue cycle leaders who promise otherwise to their board will spend the next year explaining the gap.

The evidence on where the limits sit is public. The OIG found that 13% of Medicare Advantage prior authorization denials met Medicare coverage rules, with payers citing insufficient documentation even where the record supported the service, and using clinical criteria not found in Medicare rules. No table fixes that; only an appeal does, and the appeal needs a human clinician's signature. 2025 payer audit benchmark data across 4,500 facilities found coding errors were the top hospital audit reason at 25%, and missing information the top professional audit reason at 10%. Those start upstream of any table, in documentation and coding. The foundation makes the pattern visible; a documentation program fixes it.

There is also a limit on expectations. The 2025 State of Claims survey found 60% of physicians in the AMA survey worry AI will increase denials on the payer side, and on the provider side only 14% of organizations were using AI for claims at all, with 90% of denials still reworked with at least some human review. McKinsey's 2021 estimate that about 28% of administrative spending is addressable through known interventions, including automated prior authorization, is an industry-level ceiling, not a provider-level forecast. A foundation lets you measure your share of it. Treating the ceiling as the target is how AI programs lose credibility with a CFO.

The hardest questions revenue cycle leaders ask

Revenue cycle leaders push back on the five-table model in predictable ways, and the pushback is usually right about the effort involved and wrong about the alternative. Three objections come up in almost every conversation.

"Our vendor already has all this data"

The vendor has your 837s and 835s. The vendor does not have your authorization portal history, your appeal letters, or your clinical reviewer's notes, and the vendor's denial categories are the vendor's, not yours. And you cannot audit what the vendor loaded. The 2025 State of Claims survey found only 56% of providers believe their current claims technology is sufficient, down from 77% in 2022. Trust in vendor-held data is falling for a reason. Build the five tables, then compare the vendor's numbers to yours. The gap is informative either way.

"IT says this is a data warehouse project"

A warehouse is a reasonable long-term asset and a terrible place to start. The HFMA poll's 51% citing IT infrastructure as the top barrier to AI in revenue cycle is a description of warehouse projects that never reached the worklist. Five tables for one payer, owned by revenue cycle, is a quarter's work with an analyst. If IT later wants to load those tables into the warehouse, the validation is already done. The earlier piece on why your data foundation is the last moat once AI models become a commodity makes the broader case: the tables are the asset, the model is replaceable.

"We cannot afford to pause the worklist to build tables"

Nobody pauses the worklist. The build runs alongside current operations, and the first thing it produces is a reconciled baseline of what the current process actually recovers, by payer and by denial category. That baseline is the number the board will ask for whether or not AI ever ships. The approach described in how to establish an AI ROI baseline before deployment applies directly: measure the current flow first, so that any later claim of improvement has something to be compared against.

This analysis was developed using methodologies and operating experience from Assembly.

Frequently Asked Questions

What is an RCM data foundation for AI?

An RCM data foundation for AI is five validated master tables (claims, remittances, denials, appeals, and prior authorizations) joined on one encounter identifier and owned inside revenue cycle. Each table has a defined grain and a source of record, and each is reconciled to the 835 remittance before any AI model prioritizes, matches, or drafts against it.

What are the five master tables in an RCM data foundation?

The five master tables are claims, remittances, denials, appeals, and prior authorizations. Claims hold one row per claim line and act as the spine. Remittances record what the payer actually paid. Denials record each denial event with category and deadline. Appeals record each appeal action and outcome. Prior authorizations record each request, decision, and validity window.

What does "validated" mean for revenue cycle data?

Validated revenue cycle data passes four tests: reconciled, so totals tie to the 835 remittance and bank deposits; keyed, so one encounter identifier joins every table; dated, so every status change carries the date it happened; and owned, so a named person inside revenue cycle maintains it on a published cadence. Failing any test leaves AI working from a guess.

How is an RCM data foundation different from a data warehouse?

An RCM data foundation is narrower, faster, and owned by revenue cycle rather than IT. A warehouse collects every system and field and takes quarters. A foundation is five reconciled tables for one payer, built in weeks. An HFMA poll in 2025 found 51% of organizations named IT infrastructure as the top barrier to AI in the revenue cycle.

Why should the foundation be built one payer at a time?

One payer at a time produces a reconciled number in weeks and exposes payer-specific behavior that a blended view hides. The AHA reported care denials rose 55.7% for Medicare Advantage claims between 2022 and 2023 against 20.2% for commercial claims. A model trained on the blended book learns little about either.

Which table should be built first?

The claims table is built first because every other table joins to it. Remittances come second because the 835 is the only payer-validated source and anchors reconciliation. Denials, appeals, and prior authorizations follow, because those are the tables where provider-side process is messiest and reconciliation against claims and remittances is what exposes the gaps.

Why does the denials table need one row per denial event instead of per claim?

A claim denied twice has two different stories, and AI needs both. Hospital benchmarking data reported in April 2026 showed request-for-information denials, clinical denials, and final denials each rising at different rates in 2025. Event-level rows with a denial category are what let a model separate those problems and prioritize accordingly.

Why do most providers not have an appeals table?

Most appeals live in letter templates and email threads rather than structured records. The size of that gap is measurable: the AHA found 83.2% of appealed denials were overturned in 2022, yet only 9.9% of denials were appealed. Without an appeals table, nobody can say which denials were chosen for appeal or what happened to the rest.

What is the main cause of claim denials according to recent data?

Missing or inaccurate data is the most cited cause of claim denials, at 50%. The 2025 State of Claims survey of 250 revenue cycle decision makers ranked authorizations second at 35% and incomplete registration data third at 32%. All three originate on the provider side, before a payer decision, which is why the foundation comes before the AI.

How long does it take to build an RCM data foundation for one payer?

One payer typically takes three to five months end to end with a revenue cycle analyst and read access to the billing system, clearinghouse, and portal: six to ten weeks for a reconciled claims and remittances pair, four to six weeks for denials and appeals, and two to four weeks for prior authorizations. Reconciliation, not data movement, sets the pace.

Who should own the RCM data foundation?

Revenue cycle should own the foundation, with one named owner per table. Ownership is the fourth validation test and the one that separates a foundation from a project. IT-owned warehouses refresh when IT gets to them; vendor dashboards are owned by the vendor. A revenue cycle owner can explain every field and survives a system migration or vendor change.

What can AI do once the foundation is validated?

Validated tables make four AI uses possible, in order: documentation matching before submission once claims and remittances pass, worklist prioritization by exposure and deadline once denials pass, payer-specific appeal drafting from prior winning appeals once appeals pass, and pre-service authorization risk flags once prior authorizations pass. Each use depends on the tables beneath it being reconciled.

What can AI not fix even with clean RCM data?

AI cannot create missing clinical documentation, change payer policy, or make an unsupported denial appealable. The HHS Office of Inspector General found 13% of Medicare Advantage prior authorization denials met Medicare coverage rules, often with payers citing insufficient documentation or unpublished criteria. Fixing that takes a clinician-signed appeal.

How do you know when a table is ready for AI?

A table is ready when it passes all four validation tests for the chosen payer. A practical check is to pull 50 denials at random and have the team show the matching authorization, claim line, remittance, and appeal for each without opening a second system. Where the join breaks for a human, it will break for the model, silently.

Should the vendor's denial data replace building your own tables?

Vendor data cannot replace your own tables because you cannot audit what the vendor loaded. Vendors hold 837s and 835s but not authorization portal history, appeal letters, or clinical reviewer notes, and their denial categories are theirs. The 2025 State of Claims survey found only 56% of providers believe their claims technology is sufficient, down from 77% in 2022.

What happens to AI projects that skip the data foundation?

AI projects without AI-ready data are mostly abandoned. Gartner predicted in February 2025 that organizations would abandon 60% of such projects through 2026, and found 63% unsure they had the right data practices for AI. In revenue cycle, the symptom is a denial tool that reports improvement nobody can reconcile to the bank.