# Liz Felton Investigations — starter database

A working schema for the practice suite: **EXODUS · CONDUIT · HALO**, plus the
Casebook, Field capture, OSINT desk, Vault, FAFO ledger and Triple Lens.

It ships in two dialects that stay in step with each other, and with seed data
generated from the prototype's own records so the database and the interface
can never quietly drift apart.

| File | What it is |
| --- | --- |
| `liz-investigations.sql` | PostgreSQL 14+ schema, in a `lfi` schema. SHA-256 custody chain via `pgcrypto`. |
| `liz-investigations.sqlite.sql` | SQLite 3.37+ schema. Same rules, portable, no extensions. |
| `seed.postgres.sql` | Seed records with real `NULL`s, for PostgreSQL. |
| `seed.sqlite.sql` | The same records, for SQLite. |
| `seed/*.csv` | The same records as CSV, for spreadsheets and import tools. |
| `make-seeds.js` | Regenerates every seed file from `assets/data.js`. `node db/make-seeds.js`. |

## Load it

PostgreSQL:

```bash
createdb liz_investigations
psql liz_investigations -f db/liz-investigations.sql
psql liz_investigations -f db/seed.postgres.sql
psql liz_investigations -c 'SELECT * FROM lfi.custody_chain_check WHERE NOT link_ok;'
# zero rows means the custody chain verifies end to end
```

SQLite:

```bash
sqlite3 lfi.db < db/liz-investigations.sqlite.sql
sqlite3 lfi.db < db/seed.sqlite.sql
sqlite3 lfi.db 'SELECT count(*) FROM custody_chain_check WHERE link_ok = 0;'   # expect 0
```

Both were loaded and checked before this package shipped.

## The four rules the schema actually enforces

### 1. NULL is preserved. Everywhere. On purpose.

This is the rule the rest of the design hangs from.

* `NULL` means **nobody has read that field yet**.
* `0`, `false` and `'[]'` mean **somebody read it, and this is the answer**.

Those are different facts and the database never lets them collapse into each
other. The Triple Lens document lives in `objects.lenses` (`jsonb` in
PostgreSQL, JSON-in-`TEXT` in SQLite), and the hot fields are surfaced as
**generated columns that return `NULL` when the JSON path is absent**:

```sql
-- PostgreSQL
creator_share numeric GENERATED ALWAYS AS
  (NULLIF(lenses #>> '{power,creator_share}', '')::numeric) STORED

-- SQLite
creator_share REAL GENERATED ALWAYS AS
  (json_extract(lenses, '$.power.creator_share')) STORED
```

There is no `COALESCE(..., 0)` anywhere in this file, and adding one would be a
bug. In the seeded data, matter `M-2026-014` has `creator_share IS NULL`
(nobody read the fee agreement) while `M-2026-021` has `creator_share = 0`
(somebody read it, and the protected person owes nothing). A report that
averaged those together would be lying about one of them. `power_completeness`
therefore counts how many components were *read* alongside the average, and
`unread_fields` names every gap rather than filling it.

CSV cannot tell an empty string from a `NULL`, so the CSVs are for humans and
spreadsheets. **Use `seed.postgres.sql` / `seed.sqlite.sql` for real loads** —
they carry genuine `NULL`s. If you must import the CSVs, run
`NULLIF(col, '')` on every nullable column afterwards.

### 2. Custody is append-only and hash-chained

`custody_events` is a chain: each row stores `payload`, the previous row's
`hash` as `prev_hash`, and its own `hash`. The genesis `prev_hash` is 64
zeros.

* **PostgreSQL** computes the link for you on insert —
  `hash = sha256(prev_hash || '|' || payload)` — and refuses any `UPDATE` or
  `DELETE` with a trigger. A correction is a new event, never an edit.
* **SQLite** has no built-in SHA-256, so the application supplies the hash and
  a `BEFORE INSERT` trigger refuses any row whose `prev_hash` does not match
  the last event. `UPDATE` and `DELETE` are refused outright.

Verify at any time:

```sql
SELECT * FROM lfi.custody_chain_check WHERE NOT link_ok;   -- PostgreSQL
SELECT * FROM custody_chain_check WHERE link_ok = 0;       -- SQLite
```

An empty result means the chain holds. Any row is the first event whose hash or
link no longer recomputes — and everything after it is suspect.

> **The seeded hashes are a demonstration digest.** The prototype and
> `make-seeds.js` use a 64-bit FNV-1a expanded to 64 hex characters so that the
> browser page and the exported rows agree without a crypto dependency. FNV-1a
> is a hash function, not a cryptographic one — it is not collision-resistant
> and it proves nothing to a court. **Production must use SHA-256.** The
> PostgreSQL trigger already does; loading `seed.postgres.sql` recomputes every
> link in SHA-256 as it inserts. This is an open item for the owner.

### 3. Location capability requires consent — as a hard block

`location_capability` cannot receive a row unless it names either a
`consent_record` whose `status = 'current'`, or a court order reference. The
trigger raises and cites the statute:

```
HALO refused: no current consent record and no court order.
§ 934.425(2), Fla. Stat.; third-degree felony under § 934.425(5)(a).
```

Section 934.425(2), Florida Statutes, provides that a person may not knowingly
install a tracking device or application on another person's property without
that person's consent, or use one to determine the location or movement of
another person or their property without consent
([§ 934.425, Fla. Stat.](https://www.leg.state.fl.us/statutes/index.cfm?App_mode=Display_Statute&Search_String=&URL=0900-0999/0934/Sections/0934.425.html)).
Consent is presumed revoked under § 934.425(3) on the filing of a dissolution
petition or an injunction under §§ 741.30, 741.315, 784.046 or 784.0485, and
the statutory exceptions in § 934.425(4) are narrow. A refusal is written to
`refusals` with the lawful alternative — a refusal is a record, not a dead end.

Recording works the same way. `recordings.recording_enabled` is a generated
column that is true only on an explicit `all_parties_consented = TRUE`; a
`NULL` enables nothing. Section 934.03(2)(d), Florida Statutes, makes
interception lawful where **all** parties have given prior consent
([§ 934.03, Fla. Stat.](https://www.leg.state.fl.us/statutes/index.cfm?App_mode=Display_Statute&URL=0900-0999/0934/Sections/0934.03.html)),
and a violation is a third-degree felony under § 934.03(4)(a).

### 4. A room never carries a POWER lens

Check-ins and person records are *rooms* — places where a person is, not assets
to be valued. A `CHECK` constraint refuses any row where `is_room` is true and
`lenses` contains a `power` key, and person records are forced to be rooms.
The database will not let the interface make that mistake.

## Other things worth knowing

* **`verified_resources` ships entirely empty on purpose.** Domestic-violence
  centre, sheriff non-emergency line, clerk's injunction desk, legal aid intake
  and forensics referral all have `value IS NULL`, and a `CHECK` constraint
  refuses a value without a named verifier and a verification date. No phone
  number appears in this project unless a human put it there. The same applies
  to `escalation_steps.contact_value` and to every licence number in
  `practice` and `licences`.
* **`duty_clock.due_on` is `NULL` for the limitation period.** Limitation
  periods are for Florida counsel to set, not for software to compute.
* **`records_requests` cannot reach `approved` without a stated permissible
  use** — a `CHECK` constraint. The permissible uses come from the DPPA,
  [18 U.S.C. § 2721(b)](https://www.law.cornell.edu/uscode/text/18/2721); the
  pretexting prohibition that shapes the GLBA rows is
  [15 U.S.C. § 6821](https://www.law.cornell.edu/uscode/text/15/6821), which
  expressly contemplates a State-licensed private investigator at § 6821(g);
  and an investigator is not a consumer reporting agency under
  [15 U.S.C. § 1681a](https://uscode.house.gov/view.xhtml?req=(title:15%20section:1681a%20edition:prelim)).
* **`access_log.reason` has a minimum length.** Opening a sealed item without
  writing down why is not a supported operation.
* **`claims.sources` defaults to `'[]'`, not `NULL`.** An empty array means the
  sources were checked and there are none; `NULL` would mean nobody looked.
* **FAFO has no table for contacting, publishing about, or locating the other
  party.** The absence of the column is the control. FAFO is a documented
  record — evidence, elements, duties and lawful routes. It is not a
  retaliation, doxxing or harassment tool, and the schema gives it nowhere to
  become one.
* **Licensing fields follow Chapter 493, Florida Statutes.** A Class "CC"
  applicant must complete a minimum of 40 hours of professional training and
  pass an examination
  ([§ 493.6203(6)(b), Fla. Stat.](https://www.flsenate.gov/laws/statutes/2025/493.6203)),
  and licences renew biennially except Class A, B, AB, K, R and branch agency
  licences, which renew every three years
  ([§ 493.6113(1), Fla. Stat.](https://www.flsenate.gov/Laws/Statutes/2023/493.6113)).
  Class "C" and "CC" licensees must own or be employed by a Class "A" agency
  ([§ 493.6201(4), Fla. Stat.](https://www.flsenate.gov/Laws/Statutes/2023/493.6201)).
  Licence requirements and forms:
  [FDACS private investigation licences](https://www.fdacs.gov/Business-Services/Private-Investigation-Licenses).

## Open items for the owner

1. Replace the FNV-1a demonstration digest with SHA-256 in the client, so the
   browser and the PostgreSQL trigger produce identical hashes.
2. Fill `verified_resources` and `escalation_steps.contact_value` with numbers
   verified by a named person on a named date.
3. Enter the real licence class, number and agency affiliation in `practice`
   and `licences`.
4. Have Florida counsel set the limitation-period row in `duty_clock`.
5. Confirm the EXODUS · CONDUIT · HALO naming, which is currently an
   interpretation awaiting confirmation.

Nothing in this package is legal advice. Every statute is cited so a reader can
check the text; the linked sources are also collected on the site's legal
reference page.