Medication Tracker

Data model

Back to project README

This page lists every table and view in apps/meds/schema.sql, what one row means, and what each column holds. It is for anyone who reads the app’s data files or changes its schema. It changes in the same commit as the schema, so what you read here matches the code.

The user guide shows the screens for each table, and the architecture page explains how the code uses them.

How the app stores data

Privatium keeps every record as one line of plain text in an append-only log under the node’s data directory, at data/meds/log/<device>.jsonl. The tables below are rebuilt from that log on every start, so deleting the SQLite cache loses nothing. Saving a change appends a new line that reuses the row’s id. The latest line for an id wins. Nothing is ever edited in place.

The log is not encrypted at rest. Anyone who can read the files can read every row. Privatium’s security page explains what the design protects and what it leaves to the person who runs the node.

Rules that shape the model

1. Every table has an id column that holds a ULID. A ULID is a 26-character identifier that sorts by the time it was made.
2. No table declares UNIQUE, because Privatium refuses it.
3. No table declares a foreign key. The code that saves a record checks that the records it points to exist.
4. No column relies on a default value. Privatium does not apply a column default when it saves, so the code supplies every value.
5. A change replaces the whole row. A save that leaves out a column clears it, so every save sends every column of the record.
6. Changes that belong together are written in one batch, so they land together.
7. Removing a record writes a marker that hides it. The original line stays in the log.
8. Dates are calendar dates in YYYY-MM-DD form, with no time zone. Money and quantities are exact decimal text, never floating-point numbers.

Tables at a glance

erDiagram
    person ||--o{ person_medication : tracks
    person_medication ||--|{ person_medication_product : "stands for"
    medication ||--o{ person_medication_product : "is a product of"
    medication ||--o{ medication_alias : "is also called"
    pharmacy |o--o{ person_medication : "is the current pharmacy of"
    prescriber |o--o{ person_medication : prescribes
    plan |o--o{ person : "is the default payer of"
    plan |o--o{ fill : pays
    person_medication ||--o{ fill : receives
    medication |o--o{ fill : "was dispensed as"
    pharmacy ||--o{ fill : dispenses
    person_medication ||--o{ prior_authorization : "is covered by"

The diagram shows the same facts as this list:

• A person tracks any number of medications. Each is a person_medication row with the name the person prefers.
• A tracked medication stands for one or more catalog products, through person_medication_product. A medication that comes in two carton sizes is one tracked medication with two products. One product belongs to at most one tracked medication of a person, so its fills are never counted twice.
• A tracked medication has any number of fills and prior authorizations. A fill may name the product that was dispensed.
• A catalog product has any number of other names.
• A pharmacy has any number of fills. Every fill names one pharmacy.
• A person_medication row may name a pharmacy and a prescriber.
• A prior authorization is an insurer’s approval to cover a medication for a set period.
• A plan can be the default payer of several people and the payer of several fills.
• The profile table stands alone.

Tables

The last column of each table marks the fields that hold personal or health information. Every table also has an id column, which holds a ULID and no such information.

profile

Grain: one row per node. At most one row ever exists. The table holds the reminder settings. It has no row until someone saves the settings, and saving them again amends that row. With no row, every default is in force. Saving stores every number on the form, and a box left empty saves its default, so the row holds the numbers you saw. An empty column still means the default.

The six reminder day counts must be zero or more, and the form limits them to 365. The view v_reminder_default holds the defaults. The view v_reminder_setting returns the counts in force, with the defaults filled in.

plan

Grain: one row per insurance plan or other payer. Its id links people and fills. Plan names hold no member numbers. NULL settings inherit the household defaults.

The app refuses removal while a person or fill points to the plan.

person

Grain: one row per household member whose medications the household tracks.

medication

Grain: one row per product. A product is a drug at one strength and form, or one supply item. The same product under another spelling is a row of medication_alias, never a second row here.

A medication needs a generic name or a brand name.

RxNorm is the drug list of the United States National Library of Medicine. It gives every product a number, the RxCUI. Two medications never share an RxCUI and a package, and the forms check that. Two packages of one product share the RxCUI. The architecture page explains how the catalog gets entries from the drug references.

The short name follows one pattern unless a person types another: the brand name, the generic name in brackets, the strength, the release form when the product has one, then the package. Examples are Lipitor (Atorvastatin) 20 mg, Metformin 500 mg 24 HR XR and Examplol (Exampline) 10 mcg/mL 2 Pack. A medication with no brand name leaves out the brackets. A release form such as 24 HR XR, 12 HR XR or DR makes another product, because an extended-release tablet is not interchangeable with the plain one. A pack of tablets taken in a set order has no strength of its own; its name holds the count instead, as in Yasmin (Drospirenone / Ethinyl estradiol) Pack of 28. A vitamin labeled in international units shows units, as in Cholecalciferol 1000 units.

One product in two packages is two medications. A carton of 2 is another thing to refill than a carton of 6, and a pharmacy bills them apart.

The strength is text because the app never calculates with it. One field holds a single strength such as 10 mg, a concentration such as 100 units/mL, or the strengths of a product with several drugs such as 875-125 mg.

A catalog entry holds no health information until a person is linked to it.

medication_alias

Grain: one row per medication per other name.

pharmacy

Grain: one row per pharmacy or supplier.

prescriber

Grain: one row per prescriber.

person_medication

Grain: one row per medication a person tracks, taken now or in the past. This is what the Medications page lists. Its products are the rows of person_medication_product.

The forms refuse a second tracked medication of the same person with the same preferred name, compared without regard to case, spacing or punctuation.

person_medication_product

Grain: one row per tracked medication per catalog product. Every tracked medication has at least one row here; the forms refuse to take the last product off. One product belongs to at most one tracked medication of a person, which the forms check, because the schema cannot declare it.

A tracked medication is a specialty or controlled medication when any of its products carries the mark.

fill

Grain: one row per fill of one tracked medication. A fill is one pickup or one delivery. The person is the one of the tracked medication.

prior_authorization

Grain: one row per approval window for one tracked medication. An insurer approves the drug for one member, whatever carton it comes in.

Only the expiration date drives the reminders, so an approval with no first day works the same.

Status values

The refill rules depend on the status, so the five values are fixed in the schema.

A new fill can change the status. When a fill is added to a medication that is not_started or not_taking, the status becomes taking_regularly if filled_on plus days_supply (30 days when it is missing) is today or later. The rule is refill.status_after_fill, applied in fills.add_all. On the paste review, the person picks the status for each medication instead. It starts at taking_regularly unless the medication is taking_as_needed or on_hold.

Choices

Five columns hold a choice as plain text: route, form, package_type, medication_type and when_to_take. A line in the log reads "route":"Oral", which keeps the log readable without a lookup. No table holds the choices.

Views

In v_medication, the full name joins every part that is filled in: the generic name, the brand name in brackets, the strength, the route, the form and the package.

Every view except v_spending_by_year leaves out the quantity and the amount paid. Those columns need SQLite’s decimal extension, which many SQLite tools lack. Without them, any SQLite tool can run the other views against the cache file.

How the dates are worked out

The app works out two dates for each tracked medication, over the fills of all of its products. They are estimates from recorded fills and rules, rather than a guarantee that a claim will be paid.

1. The last fill is the one with the latest date. Ties use the greater record id.
2. Every fill adds supply. It starts on its date or when previous supply ends, whichever is later. Gaps earn no credit. lasts_until is when this physical supply runs out.
3. The payer’s count includes fills under the last fill’s plan and fills with no plan. If the last fill has no plan, every payer counts. Other plans add physical supply but do not enter this payer’s count.
4. The allowance is the last fill’s days supply times the effective early fill percent, rounded down. At 25 percent, 30 days allows 7 days early, and 90 allows 22.
5. The rolling frame is measured on the proposed next fill date, including its oldest boundary day. Older fills leave the count the following day. earliest_fill_on is the earliest date when the payer’s remaining supply is within the allowance.
6. Frame 0 counts the last fill only, including when several fills share its date. Frame 3650 is a special value that counts every fill, even beyond ten years. For other frames, eligibility can also begin when the last counted fill leaves.
7. A controlled medication counts every fill across payers, without a frame limit. Its allowance is controlled_early_days, zero by default. A zero-percent payer for an ordinary medication waits for all physical supply to run out. A tracked medication is controlled, or specialty, when any of its products is marked so.
8. The backup supply is the last fill’s days supply times backup_percent, rounded down, but at least backup_min_days, or specialty_backup_min_days for a specialty medication. next_fill_on is lasts_until minus the backup, or earliest_fill_on when that is later. The app never suggests a date before the payer allows it, and it never urges a fill while much more than the backup is left. The backup counts back from all recorded supply, so it stays the same size from one refill to the next.

Plan settings override household settings. Empty overrides use 25 percent and 180 days until you change the household defaults. Controlled medications use only their household allowance. A missing days supply counts as 1 day; a recorded zero stays zero.

There is no January 1 reset. The rolling frame handles the history. Changing a person’s plan affects new fills; the latest recorded fill determines the current payer’s count.

These fills are invented, under one plan with a 25 percent allowance and a 180-day frame.

Both fills on August 25 count. Physical supply lasts until December 28, 2026. The last fill provides 90 days, so its allowance is 22 days. The payer allows a refill from December 6, 2026. The backup is 13 days, 15 percent of 90, so the next fill date is December 15.

For a gap followed by early fills, take 30-day fills on January 1, February 10 and March 5, 2026. Supply from January runs out before February 10, so the gap contributes nothing. Physical supply lasts until April 11. The payer allows a refill from April 4. The backup is 7 days, the minimum, so the next fill date is April 4 as well.

v_active_medication also exposes the marks is_specialty and is_controlled as 1 or 0, the count of products, plan_name, allowance, and both day counts. Dates stay calendar dates, and day counts are whole numbers. days_until_next_fill may be negative while days_until_runs_out is still positive.

How the refill status is worked out

Overdue uses physical supply. Due and due soon use the next fill date. Checks run in the order shown, with household reminder settings applied.

After the next fill date passes, a row with supply left reads “Fill now. Runs out in N days”. On the exhaustion date it reads “Fill now. Runs out today”. Overdue begins the next day and counts days from exhaustion, rather than from the next fill date.

Today is date('now', 'localtime'), the node computer’s local calendar date. SQLite’s plain date('now') uses Coordinated Universal Time (UTC).

Several devices

Privatium merges changes from several devices row by row, and the later change wins the whole row. Two devices that edit the same record before they sync keep one of the two edits.

The starter catalog

apps/meds/lib/starter_catalog.lua holds a starter catalog and nothing else. The app loads it by itself the first time a page needs the catalog and finds it empty. The catalog has 2,852 medications and 619 other names for them. It holds the 200 drugs most prescribed in the United States, and the drugs of a second list of common medications, at every strength that RxTerms lists. It also holds entries written by hand, such as continuous glucose monitors, and syringes and needles in many sizes. How to build the starter catalog names the sources and their licenses. It holds no person, no fill and no other record about anyone.

lib/starter.lua loads it only when the catalog holds no medication. The records carry fixed ids, so a second device that loads it repeats the same records instead of doubling them. The file must never hold a real name, a date of birth, or a record of what a person takes.

The catalog names products. It is not medical advice, and it says nothing about doses. Some brand names in it are no longer sold. They stay because older labels and statements still use them. Check the label in your hand before you rely on an entry.

Conclusion

You now know each table and view, which fields are sensitive, how a tracked medication relates to its catalog products, and how the two refill dates are worked out. Change schema.sql and this page together.

Additional resources

• Using the app
• The app folder
• Architecture
• How the app works, the same date rules in plain words.
• Privatium’s app contract, the normative definition of an app and its data.
• Privatium’s data dictionary, which defines the column types.
• Privatium’s security page
• Privatium’s backup and restore guide, which names the data directory on each platform.

Back to project README

Copyright © 2026 Gabriel Mongefranco