Mapping Users to Wearable Provider Accounts and Devices

The failure this prevents is concrete. Two accounts in your product both connected the same Fitbit login — a user who signed up twice, or a coach who set up an athlete. You merge them. With a collapsed schema you now hold two full copies of the same samples under one person, and every daily step and calorie total for their entire history doubles. Nobody reports it as a merge bug. They report that the app thinks they walked 34,000 steps.

The three entities

The user is your product's person. It has no provider fields on it at all.

The provider account is one grant from one provider: an OAuth token pair and the provider's own identifier for whoever authorized it, or — for on-device stores — a permission grant held by one installation of your app on one phone. This is the row a webhook is addressed to — making those deliveries safe to process twice begins with resolving one correctly. Note the direction: a Fitbit or Strava callback names the provider's user id, not yours, so the lookup you actually perform in production is (provider, external_user_id) to a set of your users. It is a set. If you built it as a single foreign key you will drop deliveries the first time two of your users share a login.

The data source is what produced a given sample inside that account: an app bundle id, a package name, a watch, a chest strap, a scale. This is the level your dedupe and source-priority logic keys on, so its stability is a correctness property, not a nicety.

create table app_user (
  id           uuid primary key,
  created_at   timestamptz not null default now()
);

-- One grant. NOT one per user - two users can share one provider login.
create table provider_account (
  id                uuid primary key,
  provider          text not null,        -- fitbit | oura | strava | apple_health | health_connect
  external_user_id  text,                 -- provider's id; NULL for on-device grants
  install_id        uuid,                 -- on-device: which install holds the permission; NULL for cloud
  access_token_enc  bytea,
  refresh_token_enc bytea,
  access_expires_at timestamptz,
  scopes_granted    text[] not null default '{}',
  webhook_sub_id    text                  -- provider-side subscription to tear down on unlink
);

create unique index provider_account_cloud_key
  on provider_account (provider, external_user_id) where external_user_id is not null;
create unique index provider_account_device_key
  on provider_account (provider, install_id)       where install_id is not null;

-- The link. This is the table the whole design exists for.
create table connection (
  id                    uuid primary key,
  user_id               uuid not null references app_user(id),
  provider_account_id   uuid not null references provider_account(id),
  state                 text not null,    -- see the state table below
  state_reason          text,
  connected_at          timestamptz not null,
  state_changed_at      timestamptz not null,
  last_call_ok_at       timestamptz,      -- we successfully talked to the provider
  last_sample_at        timestamptz,      -- a sample actually landed
  history_floor         date,             -- earliest date THIS grant may read
  unique (user_id, provider_account_id)
);

-- Provenance. Samples point here, not at a provider string.
create table data_source (
  id                  uuid primary key,
  provider_account_id uuid not null references provider_account(id),
  source_key          text not null,      -- HKSource.bundleIdentifier, dataOrigin.packageName, provider device id
  display_label       text,               -- 'Charge 3', 'Your phone' - display only, never a join key
  first_seen_at       timestamptz not null,
  last_seen_at        timestamptz not null,
  unique (provider_account_id, source_key)
);

Samples then carry connection_id and data_source_id alongside user_id. Denormalising user_id onto the sample is fine for query planning; deriving it at read time from the sample's provider string is not, because that is exactly the join that breaks when a user has two accounts with the same provider.

The states a connection can be in

Four states is the honest minimum, and you will need a fifth whether you plan for it or not.

Two of these get collapsed constantly and should not be. expired is a token lifecycle event your code fixes silently; revoked_upstream is a human decision only another human can undo. A single 401 does not distinguish them — see why a 401 is ambiguous and what refresh failures actually mean. Our rule: a 401 on a resource call moves you to expired; only a failed refresh with the provider's explicit invalid-grant response moves you to revoked_upstream. Anything else and you will show a "reconnect your tracker" banner to users whose connection was fine, which trains them to ignore the banner that matters.

The other collapse is scope granularity. On Health Connect, permission is per data type, and Google's own sync guidance recommends separate change tokens per type precisely so that one revoked permission does not take down the rest — the docs say to get separate tokens "to avoid having an Exception in case one of the permissions is revoked". A single state column on the connection is therefore a lie on Android: heart rate can be revoked while steps still flows. Keep scopes_granted current and derive per-metric availability from it.

Lazy discovery, and the platform that never tells you

Revocation is discovered on the next call. That is fine until you notice when the next call happens.

The Apple row is the one that breaks the standard design. There is no 401 to wait for, so there is no lazy discovery — a revoked HealthKit read grant and a user who took no steps produce byte-identical results forever. The only positive signal Apple exposes is getEarliestAuthorizedSampleDate(for:), and Apple is explicit about its limits: limited authorization is the only authorization state your app can positively identify, and full access and denied access both return no entry. Apple's instruction for data before that date is equally explicit — treat it as unknown, not as an absence of data. So write that earliest date into connection.history_floor when you get it, clamp your queries to it, and accept that unverifiable is a permanent state for this provider rather than a bug in your state machine.

Lazy discovery has a second edge that is easy to miss even on providers that do return errors. If you only call the API when you have work to do, a user who revokes and then stops opening your app is never re-checked, so your UI says "Connected" indefinitely. A cheap periodic liveness probe per connection fixes it, but size the cadence carefully: Fitbit's rate limit is reported to be per consented user rather than per application, which would mean your probe spends the user's own quota — we could not reach Fitbit's primary documentation to confirm that, so verify it before you tune anything. Google documents rate-limit categories for Health Connect and publishes no numbers at all, and states only that background limiting is stricter than foreground. Design the probe to degrade rather than to hit a figure you guessed.

What "connected" should mean on screen

Not "we hold a token". A green dot driven by token presence is the single most common cause of a support ticket that reads "the app says my ring is connected but there's no sleep data".

Derive the displayed state from state and last_sample_at, with a staleness threshold that is per provider and per metric. This is where a generic freshness alarm falls apart: a phone step counter that has produced nothing in 24 hours is broken; a ring that has produced nothing in 24 hours is charging; a smart scale that has produced nothing in nine days is a person who weighs themselves on Sundays. Three UI states carry it:

• Connected — active and fresher than the threshold for that provider.
• Connected, no recent data — active or unverifiable, but stale. Say what you last saw and when, and name the likely cause for that specific device rather than showing a generic error.
• Reconnect needed — revoked_upstream or disconnected_by_user. This is the only state that should ever interrupt the user.

Do not show "no recent data" as an error state on unverifiable connections. On iOS it is the expected condition when the user granted write but not read, and telling them to reconnect sends them into a permission screen where nothing looks wrong.

Merges are where the model holds or collapses

Account merge is the test case. Run it against your schema on paper before you ship.

With a connection table, merging user B into user A is update connection set user_id = A where user_id = B. Samples do not move, because they were never attributed to the user in the first place — they belong to a connection, and the connection moved. Three outcomes follow, and each one is decided by the unique constraint:

1. Both users connected the same provider account. The unique (user_id, provider_account_id) constraint collides. Correct handling is to keep one connection and drop the other, keeping whichever has the earlier connected_at and the earlier history_floor. The samples were already stored once against a single provider_account, so nothing doubles. In a collapsed schema this is the case that doubles the user's step and calorie history permanently.
2. Both connected the same provider with different accounts. Legitimate — one human really can hold two Fitbit accounts, and after a merge that is now one person with two of them. This is not a duplicate; it is two honest measurements of the same reality, which is a different problem with a different fix. The merge must seed a per-user, per-metric source priority so your aggregation picks a source per interval instead of summing. Summing is how you get the 34,000-step day.
3. Disjoint providers. Nothing to resolve. Recompute the merged user's daily rollups over the union of dates touched, because the rollups are per user and both sets are now in scope.

The reverse direction matters too. Do not assume a provider's external_user_id is stable across a disconnect and reconnect — we found no provider documentation guaranteeing it, in either direction. Design so that a reconnect returning a different external id creates a second provider_account row and a second connection rather than overwriting the first, then reconcile explicitly. That reconciliation is the same code path as case 2 above, which is a good reason to build it once and well.

Devices are not stable identifiers, and Android just proved it

Your data_source.source_key looks like a natural key. It is less stable than it looks, and Google shipped a change that demonstrates it: from the June 2026 Health Connect update, steps counted on-device are attributed to a per-device, per-app Synthetic Package Name rather than the package name android, which historical data keeps forever. So one physical phone legitimately occupies two source_key values in one user's history, split at a date boundary, and neither value can be enumerated from your server. Resolve source identity on the device and ship up a resolved label. What that split then does to a priority table — and why it needs an alias concept rather than a mapping table — is worked through in resolving which source wrote a sample, and integrating Health Connect has the platform-side detail.

Apple's version of the same problem is a trust hierarchy. Apple documents that sourceRevision is assigned by HealthKit at save time, not by the writer, which makes it trustworthy; HKDevice is optional on the object and populated by the writing app, and we found no Apple guarantee that any given sample carries it. So key data_source on HKSource.bundleIdentifier — the stable machine key — and treat HKDevice fields as display strings only.

A device that changes owners should become a new source, and your schema already does this correctly — data_source is scoped under provider_account, so a watch that moves to a different account cannot merge with its old history on a serial-number match. Resist the temptation to add that match. Merging sources across provider accounts because the model and manufacturer strings agree is how you import a stranger's resting heart rate into someone's baseline.

The household case, which no schema fixes by itself

One provider account, one scale, four bodies. The provider account is a container for measurements, not a person, and your connection row says nothing about whose body produced any given reading. A second adult's weight entering the first adult's trend line does not look like an error — it looks like a nine-kilogram overnight change, and it will move any calorie target or body-composition chart derived from it.

Handle it at the sample level, not the connection level. If the provider supplies a per-measurement subject or profile hint, carry it and give it its own column; do not flatten it into the connection. Where there is no such hint, our position is that body-composition samples from a shared-capable source should never be auto-accepted into a user's trend: gate them behind an explicit confirmation, or behind a rate-of-change bound. A bound like "reject and ask about a weight change above roughly two kilograms in a day" is engineering judgement offered as an illustration, not a clinical threshold — have someone with a physiology background set the real numbers before you ship them.

Unlink must revoke upstream, not just delete your row

Deleting the connection row is the smallest part of unlinking. If you delete your row and leave the grant live, the provider keeps pushing to your webhook, your ingest path recreates a user you believed was gone, and the user's revocation was cosmetic. The unlink path has to, in order: mark the connection disconnected_by_user so all reads and ingest stop immediately, delete the provider-side webhook subscription using the id you stored on provider_account, call the provider's revocation endpoint, and only then retire your rows.

Order matters because the middle two steps can fail. If revocation fails, you need a connection in a terminal local state with a retryable upstream obligation attached — which means the state machine has to survive the row, and a hard DELETE cannot express that.

Whether to keep the data is a separate question from whether to keep syncing, and the states answer it differently. disconnected_by_user means stop, not erase; erasure is its own request with its own consequences across rollups, queues, caches and logs, covered in data deletion and export and health data retention and deletion. revoked_upstream is weaker still — the user withdrew permission at the provider, which is not by itself an instruction to you about data you already hold.

One health-specific reason not to hard-delete on unlink: relinking may not restore what you dropped. Google documents that deleting your app revokes all Health Connect permissions including the history permission, and that on reinstall and re-grant "the same default restrictions apply" — the app can read up to 30 days prior to the new grant date, with the docs' own worked example of a May 15 re-grant yielding an April 15 floor. Anything older than that window is unreachable without PERMISSION_READ_HEALTH_DATA_HISTORY. So a disconnect-reconnect cycle can permanently narrow what you are allowed to see, and data you deleted on unlink may be data you can never fetch again. Write the new floor into connection.history_floor on every re-grant and let backfill read it rather than assuming a fixed window.

What we'd actually build

Four tables as sketched: app_user, provider_account, connection, data_source. Samples reference the connection and the source, never a provider name string.

The connection state machine lives in one module with one entry point, and every transition writes state_reason. Nothing else in the codebase is allowed to write connection.state — not the webhook handler, not the sync worker. This sounds like ceremony until the first time you have to answer "why did this user get a reconnect prompt on Tuesday", and the answer is a row instead of an archaeology session.

external_user_id to connections is a one-to-many lookup everywhere in the code, including the places where you are certain it is one-to-one.

UI state derives from (state, last_sample_at) against a per-provider, per-metric threshold table, and that table is configuration, not constants in a component.

Unlink is a durable multi-step job, not a request handler.

The honest limits

This model does not tell you whose body a measurement came from. It tells you which grant and which device it came through, which is the most any provider we researched gives you, and the gap between those two things is exactly the household problem above. Treat multi-person attribution as unsolved and make it explicit in the schema rather than assuming one account equals one body.

Several things here are genuinely undocumented and we will not pretend otherwise. No provider documents whether its user identifier survives a disconnect-reconnect cycle. Apple documents that HKQueryAnchor conforms to NSSecureCoding and can be persisted, and documents nothing about whether an anchor stays valid across an app reinstall or a device restore — so a reconnect on iOS may replay the entire store as new, which is survivable only if your ingest is idempotent on sample uuid. Whether a sample's uuid is itself stable across a device restore is likewise unverified. Google publishes no numeric rate limits for Health Connect. Do not build a probe cadence or a reconnect heuristic on any of those.

And the buy-versus-build point, plainly: if your product proposition is "connect any wearable", this table is most of your integration work, and buying it is often the right answer. A health data aggregator gives you one identifier, one webhook shape, and someone else's on-call rotation for the connection state machine across a dozen providers — see what a health data aggregator is for the model. What it does not remove is this page. You still hold a many-to-many between your users and the aggregator's users, you still need per-source provenance because dedupe and priority are still yours, and the on-device HealthKit and Health Connect grants still live in your app on the user's phone regardless of who brokers the cloud ones. The aggregator collapses the token problem, not the identity problem. Build it yourself when you have one or two providers, when a provider's data model is central enough to your product that a normalized layer costs you more than it saves, or when the per-user pricing crosses your margin — and even then, build these four tables first.

If your problem is a single connection that never established in the first place rather than the model behind them all, start at OAuth redirect URI mismatch; nothing on this page matters until the grant itself lands.