Data Model

Note

Italic columns are additional indexes, columns followed by "?" are nullable. More complex properties are expressed in SQL code blocks below the tables.

Users

id (uuid)	name?	created_at	updated_at?	deactivated_at?	deleted_at?

Relationships

Directed relation from user to user that enables level tracking.

At creation time the reciprocal relationship is created. Since the pair of relationships has a mutual non nullable reference in other_relationship_id, their UUIDs must be generated in application code and they must be inserted in a single statement.

id (uuid)	user_id	partner_id	other_relationship_id	nickname?	notification_enabled	notification_threshold	created_at	updated_at?	deactivated_at?	deleted_at?

UNIQUE(user_id, partner_id)
WHERE deleted_at IS NULL;

UNIQUE(other_relationship_id);

FOREIGN KEY(other_relationship_id) REFERENCES relationships(id);

Groups

Undirected grouping of users for organization and discoverability purposes.

It does not translate to a relationship among users, but users can see other members and ask for their approval to establish a relationship. A periodic cleanup job deletes empty groups without pending direct invites.

id (uuid)	name	updated_by_user_id?	created_at	updated_at?

Memberships

Membership of a user in a group.

Membership is active if joined_at is NOT NULL and left_at is NULL. A user can grant a group permission to see the relationship levels they record toward other members of the same group (share_relationships).

id	user_id	group_id	share_relationships	joined_at?	left_at?

UNIQUE(user_id, group_id)
WHERE left_at IS NULL;

Entries

Fuckumeter data points.

Since relationships are directed, an entry always represents the level registered by a user towards another user. A multicolumn index is useful for time range queries.

id	relationship_id	level	created_at

INDEX(relationship_id, created_at);

Devices

Device revocation triggers deletion of the device row and relative refresh token.

Refresh tokens are used for JWT authentication. When the backend validates a refresh token a new refresh token is issued and sent with the response.

id (uuid)	user_id	name	notification_enabled	fcm_token?	refresh_token_hash	created_at	last_accessed_at?

UNIQUE(fcm_token);

Invites

Anonymous invite codes are the main way to expand the community.

They are generally single-use and can be shared as a string or QR code. A periodic cleanup job deletes older, expired invites.

id	created_by_user_id	consumed_by_user_id	code_hash	type	metadata (json)	created_at	expires_at	consumed_at?	revoked_at?

UNIQUE(code_hash);

The type of invite can be:

Type	Expiry	Metadata
INVITE_USER	7 days	group_id?
LINK_DEVICE	15 min	device_name
JOIN_GROUP*	30 days	group_id
RECOVERY	24 h	recovery_request_id

* Thought to be shared freely: can be used multiple times and does not establish a relationship. The group invite ignores consumed_at and its lifecycle is bounded only by expires_at/revoked_at. Users in a relationship can invite the partner without a code: in that case a membership entry is created with joined_at set to NULL until the other accepts.

Recovery requests

For partner-assisted recovery.

It is linked to the relationship initiator -> target. All target devices get notified and there is a 24h wait period before the recovery code is generated. Both initiator and target can revoke the request. Blocks user/relationship deletion if not revoked or consumed.

id	relationship_id	revoked_by_id?	created_at	consumed_at?	revoked_at?

UNIQUE(relationship_id)
WHERE consumed_at IS NULL
  AND revoked_at IS NULL;