HIPAA. PHI discovery checklist.
Finding PHI outside the EHR: a checklist for HIPAA teams
Where electronic PHI collects outside the record system, the catalog queries that find it, and the evidence your risk analysis needs for each location.
Co-founder and CEO · Updated November 8, 2026

On this page
The scope rule, and what you need to start
Applies to the PostgreSQL 16 to 18 and SQL Server 2022 catalogs, and to the file platforms you already search. The PostgreSQL queries were run in the Sylox lab on versions 16, 17 and 18; the SQL Server queries come from vendor documentation, which Sylox has not yet run. This guide is general information, not legal advice.
Scope rule: a location belongs in the risk analysis when electronic protected health information (ePHI) is stored, received, maintained or transmitted there. A system nobody has searched goes into the analysis as not searched, with an owner and a date.
The privacy officer and the security officer own the scope. A database administrator runs the catalog queries, and file platform administrators run the content searches. Plan a day per system for the first pass.
The checklist and its evidence
Work down the list for each system in scope. Every line ends with a record you can hand to an assessor.
| Step | What to do | Evidence to keep |
|---|---|---|
| Scope | List every system that could hold ePHI, clinical or not, with an owner | A dated list of systems and owners |
| Structured data | Search the catalog for candidate columns, then check values | Each query, its result and its run date |
| Files | Search shared folders and mailboxes; list scanned images apart | Search results by folder, with the sharing setting |
| Confirmation | Decide for each finding whether it is ePHI, and say why | A note per finding, rejected ones included |
| Access | Record who and what can read each location | Role and group lists compared with approvals |
| Protection | Check stored values as well as platform settings | A sample check of the stored form |
| Records | Decide delete, move, restrict or protect in place; repeat on change | Decisions and dates, kept six years |
What the Security Rule asks of the search
Under 45 CFR 160.103, protected health information is individually identifiable health information transmitted or maintained in any form or medium, with limited exclusions. ePHI is the part held or sent in electronic media.
Section 164.308(a)(1)(ii)(A) requires a thorough assessment of the potential risks and vulnerabilities to the ePHI an entity holds. HHS guidance on risk analysis reads that as all of an organization’s ePHI, whatever its medium, source or location.
A risk analysis that stops at the EHR leaves out the copies. HHS’s audit protocol for this specification looks for a defined scope that names the systems holding ePHI. The protocol has the assessor review that scope record.
Where ePHI collects outside the EHR
ePHI spreads through ordinary work: reporting, billing, quality review and system testing. Start with the places below, in whatever order your own systems suggest.
Files exchanged with outside parties leave copies on both sides, so include the shared folders and transfer locations you use with them.
- Reporting databases and warehouse copies of clinical data
- Billing, claims and revenue-cycle exports
- Spreadsheets on shared drives, SharePoint and OneDrive
- Email attachments, scanned forms and faxes saved as images
- Test and development copies refreshed from production
Search structured data in six steps
Steps two and three carry the read-only queries. Keep every result with its date.
1. Agree the identifiers to look for
- Owner
- Privacy officer with the security officer
- Tool or path
- Your working definition of PHI
- Expected output
- A written list of column-name patterns and identifier shapes, for example patient, member, diagnosis, birth date, record number.
- How to verify
- The list is dated and signed off before the first query runs.
2. Find candidate columns in PostgreSQL
- Owner
- Database administrator
- Tool or path
- psql, any login that can connect
- Command
- SQLTested on PostgreSQL 16.15, 17.11 and 18.6, November 9, 2026, as a login with no grants beyond the PUBLIC defaults (queries 1 and 2); a role that can read the table (query 3), against synthetic data.
-- Read-only. pg_catalog lists every column whatever the table privileges. -- 1. Anchor: tables and columns the catalog holds (compare with your inventory). SELECT count(DISTINCT c.oid) AS tables, count(*) AS columns FROM pg_catalog.pg_attribute a JOIN pg_catalog.pg_class c ON c.oid = a.attrelid AND c.relkind IN ('r','p') JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE a.attnum > 0 AND NOT a.attisdropped AND n.nspname NOT IN ('pg_catalog','information_schema') AND n.nspname !~ '^pg_toast'; -- 2. Candidate columns by name. reltuples is an estimate; -1 means never analyzed. SELECT n.nspname AS schema_name, c.relname AS table_name, a.attname AS column_name, format_type(a.atttypid, a.atttypmod) AS data_type, c.reltuples::bigint AS est_rows FROM pg_catalog.pg_attribute a JOIN pg_catalog.pg_class c ON c.oid = a.attrelid AND c.relkind IN ('r','p','v','m','f') JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE a.attnum > 0 AND NOT a.attisdropped AND n.nspname NOT IN ('pg_catalog','information_schema') AND n.nspname !~ '^pg_toast' AND a.attname ~* '(patient|mrn|member_?id|diagnos|icd_?10|birth|dob|ssn|npi|insur|claim|prescri|admit|discharge)' ORDER BY 1, 2, 3; -- 3. Shape check on one candidate (synthetic table): values that look like a US SSN. SELECT count(*) AS rows_checked, count(*) FILTER (WHERE ssn ~ '^\d{3}-\d{2}-\d{4}$') AS ssn_shaped FROM billing.claims_export; - Expected output
- Query 1 returns one row of counts. Query 2 returns one row per candidate column. Query 3 returns the rows checked and how many have the shape.
- How to verify
- The table count in query 1 matches your inventory of PostgreSQL tables, or the difference is explained.
3. Find candidate columns in SQL Server
- Owner
- Database administrator
- Tool or path
- sqlcmd or SQL Server Management Studio
- Command
- SQLFrom Microsoft Learn documentation, page updated October 19, 2026; not yet tested by Sylox.
-- Read-only. Needs VIEW DEFINITION (else the catalog lists fewer objects, with no error) -- and VIEW DATABASE PERFORMANCE STATE plus VIEW SECURITY DEFINITION for the row estimates. SELECT COUNT(*) AS tables_visible FROM sys.tables; SELECT s.name AS schema_name, o.name AS object_name, c.name AS column_name, ty.name AS data_type, p.row_count AS est_rows FROM sys.columns AS c JOIN sys.objects AS o ON o.object_id = c.object_id AND o.type IN ('U','V') JOIN sys.schemas AS s ON s.schema_id = o.schema_id JOIN sys.types AS ty ON ty.user_type_id = c.user_type_id LEFT JOIN (SELECT object_id, SUM(row_count) AS row_count FROM sys.dm_db_partition_stats WHERE index_id IN (0,1) GROUP BY object_id) AS p ON p.object_id = o.object_id WHERE LOWER(c.name) LIKE '%patient%' OR LOWER(c.name) LIKE '%mrn%' OR LOWER(c.name) LIKE '%diagnos%' OR LOWER(c.name) LIKE '%birth%' OR LOWER(c.name) LIKE '%ssn%' OR LOWER(c.name) LIKE '%npi%' OR LOWER(c.name) LIKE '%insur%' OR LOWER(c.name) LIKE '%claim%' ORDER BY s.name, o.name, c.name; -- Shape check on one candidate (synthetic table): values that look like a US SSN. SELECT COUNT(*) AS rows_checked, SUM(CASE WHEN ssn LIKE '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]' THEN 1 ELSE 0 END) AS ssn_shaped FROM billing.claims_export; - Expected output
- One row per candidate column, with an estimated row count. The shape check returns two counts.
- How to verify
- Run the first query as a login with full metadata rights and compare the table count. A lower count for the review login means hidden objects.
4. Check values in each candidate column
- Owner
- Database administrator, with the privacy officer
- Tool or path
- A redacted sample from your own tooling
- Expected output
- For each candidate, a note on whether the values are health data tied to a person. Keep a five-row redacted sample apart from the result.
- How to verify
- A reviewer other than the administrator reads the notes and agrees or rejects each one.
5. Record the decision for each copy
- Owner
- Security officer, on the data owner’s advice
- Tool or path
- Your risk register
- Expected output
- One decision per copy: delete, move, restrict access or protect in place, with the date and the person who decided.
- How to verify
- Re-run the queries after the change. A deleted column is no longer listed by query 2.
Five findings, five decisions
Findings from one health system’s reporting and file stores. The provider number and the clinic phone line are rejected, with the reason.
| Value | Detected type | Label | Reason | Kept or rejected |
|---|---|---|---|---|
| 987-65-4321 | US Social Security number shape | ePHI | In billing.claims_export beside a diagnosis code | Kept |
| MRN-00481516 | Medical record number | ePHI | Copied from the EHR into a quality-review workbook | Kept |
| jane.doe@example.com | Email address | ePHI | In a referral spreadsheet with a diagnosis column | Kept |
| 1234567893 | National Provider Identifier | Not ePHI here | Names a clinician in a directory table, no patient attached | Rejected: provider identifier alone |
| 555-0142 | Phone number | Not ePHI here | Clinic switchboard line on a signage file | Rejected: an organization number |
Confirm it is PHI before you act
Individually identifiable health information relates to a person’s health, care or payment for care, and identifies the person or could reasonably be used to. Health terms with no identifier, or an identifier with no health context, need a closer look.
Information stops being PHI once it is de-identified under 164.514(b), by an expert’s determination or by removing the listed identifiers. A dataset with the names missing is not de-identified on that ground alone.
Record access and protection for each location
List who can read each location: users, roles, groups and service accounts. Section 164.312(a)(1) asks for access only by people or software programs granted access rights, and 164.308(a)(4) ties those rights to your authorization policies. Access nobody approved is a finding of its own.
Check encryption of stored ePHI under 164.312(a)(2)(iv) and of ePHI in transit under (e)(2)(ii). Both are addressable, which means you assess whether the safeguard is reasonable and appropriate, and implement it or document why not. Disk encryption does not travel with an export, so check the stored values of each copy.
Keep the record, and watch the proposed rule
Feed each decision into risk management under 164.308(a)(1)(ii)(B), and repeat the search when systems change, as part of the periodic evaluation in 164.308(a)(8). Keep the documentation for six years from creation or the date it last was in effect, whichever is later, under 164.316(b)(2)(i).
HHS proposed a Security Rule update on January 6, 2025 (90 FR 898). It would require a written technology asset inventory and a network map. As of November 9, 2026 the Federal Register lists no final rule. The current rule applies.
What this method misses
Column names find structure and nothing else. A free-text notes column named comments can hold a diagnosis, and a column named ssn can hold something else. The shape check also cannot separate a real number from a test value that fits the pattern.
The queries read tables and views. They miss PHI in file contents, scanned images, email bodies, backups and restored hosts. Read replicas keep their own catalog, so run the queries on each one.
Out of scope here: Privacy Rule uses and disclosures, breach notification, state health privacy laws, and substance use disorder records under 42 CFR Part 2.
IRIS finds sensitive data, including US health-related identifiers such as NPI, in the databases and files you connect, and records what it could not read. Capabilities vary by edition.
Where ePHI sits, copies included (164.308(a)(1)(ii)(A)): IRIS produces part of this, for the sources you connect and the data types IRIS detects.
Who can reach each ePHI store (164.308(a)(4)): IRIS produces part of this, where the source exposes permission data.
Plain or protected stored values (164.312(a)(2)(iv)): IRIS produces part of this, a check on sampled values, once activated on your install.
What each scan covered and could not read: IRIS produces this.
Who accessed ePHI, and when: IRIS produces part of this, where the source keeps an audit log.
Read the HIPAA pageRun it on one system and check the count
Pick the reporting database with the most clinical copies and run steps one to five on it. The pass test is simple: the table count matches your inventory, every candidate has a decision, and a second run shows the decided changes.
The next guide, questions auditors ask about sensitive data, lists the other questions an assessor may ask once this record exists.
Official sources
- 45 CFR 160.103: definitions, protected health information, eCFR. Opened November 8, 2026.
- 45 CFR 164.308: administrative safeguards, risk analysis and evaluation, eCFR. Opened November 8, 2026.
- 45 CFR 164.312: technical safeguards, eCFR. Opened November 8, 2026.
- 45 CFR 164.316: policies, procedures and documentation, six-year retention, eCFR. Opened November 8, 2026.
- 45 CFR 164.514: de-identification of protected health information, eCFR. Opened November 8, 2026.
- Guidance on risk analysis under the Security Rule, HHS Office for Civil Rights. Opened November 8, 2026.
- HIPAA audit protocol: Security Rule risk analysis, HHS Office for Civil Rights. Opened November 8, 2026.
- HIPAA Security Rule to strengthen the cybersecurity of ePHI, 90 FR 898, Federal Register. Opened November 8, 2026.
- pg_attribute system catalog, PostgreSQL 18, PostgreSQL documentation. Opened November 8, 2026.
- sys.columns (Transact-SQL), Microsoft Learn. Opened November 8, 2026.
Start with a free assessment of one database you choose.
Get a free assessmentPHI discovery questions
Yes, when a covered entity or business associate holds it. The definition covers any form or medium, and HHS guidance reads the risk analysis as covering all ePHI whatever its location.
Yes, if it was refreshed from production and still holds identifiable health information. Run the same catalog queries there and record whether the values differ from production.
No. A name only nominates the column. Check the values, and record rejected candidates with the reason, as the findings table above does.
Six years from creation or from the date it last was in effect, whichever is later, under 164.316(b)(2)(i). Keep each dated query output with the decision it supported.
Protect your brand. Start with your data.
A free assessment shows what sensitive data one database holds, who can reach it and what the scan could not check.


