# Database and Caching Schema

This document details the database schema, sharded counters logic, and Redis data storage required for the Duuble backend.

## Core Design Principles
- **Database**: Google Cloud Spanner using the PostgreSQL dialect.
- **Primary Keys**: UUIDv4 used universally as primary keys for global uniqueness.
  - Stored as `VARCHAR(36)` rather than the `UUID` type. The PostgreSQL/PGAdapter
    layer Spanner exposes does not advertise a stable `uuid` OID, so the
    implementation pins the wire type as `VARCHAR(36)` and uses
    `spanner.generate_uuid()` for server-side generation.
- **Top-level primary key naming**: Every top-level table names its primary
  key column after the table — `user_id`, `post_id`, `hub_id`, `asset_id`,
  `comment_id`, `notification_id`, `report_id`, `main_content_id`,
  `invitation_id`, `invite_link_id`, `join_request_id`, `discussion_id`,
  `message_id`. The schema in this document uses these names, not a generic
  `id` column. The reason is `INTERLEAVE IN PARENT`: Spanner requires the
  child table's first primary key column to share the **exact same name** as
  the parent's primary key column, so a parent named `id` would force every
  interleaved descendant to also name its first PK column `id`, which is
  unusable when several different parents must coexist in the same schema.
- **Foreign-key columns** that reference a top-level table point at that
  table's named PK (e.g. `posts.author_id` → `users.user_id`).
- **Interleaving**: Spanner's `INTERLEAVE IN PARENT` used extensively for hierarchical data (comments, votes, counters) to strictly co-locate child rows with the parent for maximum read performance and zero-join-penalty within the split. This is the operational driver behind the naming convention above.
- **Sharded Counters**: High-throughput writes for social metrics and vote aggregations are routed to randomly bucketed shards, with reads performing sum aggregations.
- **Soft Delete**: Mutable user-state tables carry a nullable `deleted_at TIMESTAMPTZ` column. See [Soft Delete Policy](#soft-delete-policy) for the full list of affected tables, why it exists, and the planned 30-day purge job that turns it into a true hard delete.
- **Stateless Auth**: Redis is the source of truth for caching, OTP challenges, active token sessions/refresh tokens, and fast moving active-user aggregates.

---

## Soft Delete Policy

Several mutable relationships in Duuble use a soft-delete pattern instead of
issuing a `DELETE` immediately. A row is marked deleted by setting
`deleted_at` to the current UTC timestamp, and every read path filters
`WHERE deleted_at IS NULL`.

### Tables with `deleted_at`

| Table              | Why soft delete |
|--------------------|-----------------|
| `users`            | Account deletion must remove the user from public surfaces immediately while keeping referenced rows (posts, comments, hub memberships) readable to other users until cleanup runs. |
| `user_follows`     | A follow/unfollow toggle that hard-deletes loses the original `created_at`, breaks counters, and re-creates row churn when a user unfollows and re-follows. Soft delete lets us **revive** the row on a re-follow instead of inserting a new one. |
| `user_blocks`      | Same revive pattern as follows so an unblock+reblock keeps the original record. |
| `hub_memberships`  | Leaving and rejoining a hub must not silently rewrite the original join date; soft delete preserves the audit trail and lets a re-join revive the row. |
| `posts`            | A deleted post is invisible to feed/profile readers, but the row stays referenced from `comments`, vote receipts, and notification rows until purge. |
| `comments`         | Same as posts — the comment disappears from the post detail page, but downstream notification rows remain valid until purge. |

Tables outside this list — counters, invitations, join requests, vote
receipts, notifications, reports, assets, hub_discussions, etc. — do **not**
soft-delete. Counters never delete (they're updated in place), invitations
already have a lifecycle column (`status`), and the rest are either
append-only or carry their own terminal status.

### Read semantics

All service-layer reads of soft-deletable tables go through a `.NotDeleted()`
extension that adds `WHERE deleted_at IS NULL`. New code MUST keep this
discipline so soft-deleted rows never leak into responses, counters, or
search results.

### Revive semantics

On a follow/unfollow/block/unblock/hub re-join, the service first tries to
revive the existing row (`UPDATE … SET deleted_at = NULL WHERE deleted_at IS
NOT NULL`) instead of inserting a new one. This avoids primary-key
collisions, preserves the original `created_at`/`joined_at`, and keeps
counter reconciliation deterministic.

### Planned hard-delete purge (30 days)

Soft-deleted rows are **temporary**. A scheduled job (to be added; not in
the current build) will hard-delete every row whose `deleted_at` is older
than 30 days from now. The 30-day window covers:

- Reversal grace period: a user who deleted their account or unfollowed by
  accident can be restored within the window by clearing `deleted_at`.
- Cross-table cleanup ordering: by the time a post row is hard-deleted, the
  derived rows that reference it (comments, vote receipts, notification
  rows pointing at that post) are already either purged or harmless.
- Compliance: 30 days is a defensible erasure window for user-initiated
  account deletion requests.

The purge job MUST batch deletes per shard/partition to avoid Spanner
transaction-size limits, and MUST be idempotent so a retry never deletes
extra rows.

Until the purge job ships, `deleted_at` rows accumulate. That is acceptable
because storage cost is low and every read path already filters them out.

---

## Data Models & Cloud Spanner Schema

### 1. Users & Social Graph

#### `users`
Top-level table holding user profiles and identities.
```sql
CREATE TABLE users (
    user_id                  VARCHAR(36)  NOT NULL DEFAULT spanner.generate_uuid(),
    username                 VARCHAR(100),
    full_name                VARCHAR(100) NOT NULL,
    profile_asset_id         VARCHAR(36),
    bio                      TEXT,
    onboarding_step          VARCHAR(50)  DEFAULT 'verified_identity',
    is_onboarding_completed  BOOLEAN      NOT NULL DEFAULT FALSE,
    accepted_terms_version   VARCHAR(50),
    accepted_privacy_version VARCHAR(50),
    accepted_at              TIMESTAMPTZ,
    created_at               TIMESTAMPTZ  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at               TIMESTAMPTZ  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at               TIMESTAMPTZ,
    PRIMARY KEY (user_id)
);

CREATE UNIQUE INDEX idx_users_username ON users(username);
```

`username` is nullable on purpose: see [Username Lifecycle](#username-lifecycle).
The unique index treats NULL values as distinct (Spanner PostgreSQL semantics),
so multiple rows in the pre-pick state coexist while two rows with the same
non-NULL username are still rejected.

### Username Lifecycle

The `users.username` column models a **set-once, never-changeable** handle:

1. **Created with `NULL`.** A user row is created on first phone identity
   verification, before any profile basics exist. The user has no username
   yet, so the column starts as `NULL`. The unique index permits multiple
   `NULL` values to coexist, so any number of accounts can sit in the
   pre-pick state.
2. **Set once during onboarding.** When the user submits the profile-basics
   step (the route `PATCH /api/v1/users/me/profile` for the initial pick,
   or the onboarding profile-basics endpoint), the service writes the
   chosen username only if the current value is still `NULL`. The unique
   index then enforces global uniqueness against every other non-NULL
   value.
3. **Immutable afterwards.** Once `username` is non-NULL, no API path may
   change it. An attempt to PATCH the profile with a different username
   returns `USERNAME_TAKEN` (the service detects that the current user
   already has a username set and refuses to overwrite it). This is
   intentional: the username is the user's external identity surface in
   URLs (`/users/by-username/{username}`), so making it mutable would
   break links and identity over time.

This contract is the reason `username` is declared `NULL`-able even though
every active product surface assumes it is set: the default value `NULL`
is the only state a user can be in before they have picked one, and the
"never changes" rule means the column never needs to switch back to NULL
once a username has been chosen.

Implementation references:

- Migration `002_username_nullable.sql` dropped the original `NOT NULL`
  constraint after this lifecycle was finalized.
- `UsersService.PatchMeProfileAsync` enforces the set-once rule by
  rejecting the write when the current row already has a non-NULL
  `username`.

#### `user_identities` (Phone)
Links verified phone identity records. Interleaved in users. In the current product, phone is the only supported authentication identity.
```sql
CREATE TABLE user_identities (
    user_id        VARCHAR(36)  NOT NULL,
    provider_name  VARCHAR(50)  NOT NULL DEFAULT 'phone', -- only 'phone'
    provider_id    VARCHAR(255) NOT NULL,                 -- E.164 phone number
    created_at     TIMESTAMPTZ  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, provider_name)
) INTERLEAVE IN PARENT users ON DELETE CASCADE;

CREATE UNIQUE INDEX idx_identities_provider ON user_identities(provider_name, provider_id);
```

#### Contact Privacy Boundary
The backend does not store a user's full address book. Local contacts access is a mobile OS permission used by the app for selecting invitees. Server-side data stores should contain only verified user phone identities and, when invite flows are modeled, phone numbers explicitly selected for a specific invite action.

#### `user_follows`
Follower graph. Interleaved under the follower (the `user_id` column **is**
the follower id; the column is named `user_id` rather than `follower_id` so it
shares a name with the parent `users.user_id`, which `INTERLEAVE IN PARENT`
requires for the first PK column).
```sql
CREATE TABLE user_follows (
    user_id       VARCHAR(36) NOT NULL,                       -- the follower
    following_id  VARCHAR(36) NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at    TIMESTAMPTZ,
    PRIMARY KEY (user_id, following_id)
) INTERLEAVE IN PARENT users ON DELETE CASCADE;

-- Required index to find followers of a specific user quickly
CREATE INDEX idx_follows_following    ON user_follows(following_id);
-- Required index to paginate the users someone is following by date
CREATE INDEX idx_follows_user_created ON user_follows(user_id, created_at DESC) INTERLEAVE IN users;
```

Application code reads the follower id via the `follower_id` alias in
[soft-deletable extensions](#soft-delete-policy); at the storage layer the
column is `user_id`.

#### `default_feed_users`
Admin-managed fallback feed sources. When a guest user opens the app, or when an authenticated user has no followed users, joined hubs, hub co-members, or interaction-based personal feed sources, the main feed is seeded from public posts authored by these users.
```sql
CREATE TABLE default_feed_users (
    user_id        VARCHAR(36) NOT NULL,
    display_order  INT8        NOT NULL,
    is_active      BOOLEAN     NOT NULL DEFAULT TRUE,
    created_at     TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at     TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id)
);

CREATE INDEX idx_default_feed_users_active_order ON default_feed_users(is_active, display_order);
```

#### `user_blocks`
Interleaved under the blocker. As with `user_follows`, the first PK column is
named `user_id` (matching the parent `users.user_id`) rather than `blocker_id`.
```sql
CREATE TABLE user_blocks (
    user_id     VARCHAR(36) NOT NULL,                          -- the blocker
    blocked_id  VARCHAR(36) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at  TIMESTAMPTZ,
    PRIMARY KEY (user_id, blocked_id)
) INTERLEAVE IN PARENT users ON DELETE CASCADE;
```

#### `user_social_counters` (Sharded Counter)
High-throughput counters for users' social stats to avoid locking contention on the `users` table.
```sql
CREATE TABLE user_social_counters (
    user_id           VARCHAR(36) NOT NULL,
    shard_id          INT8        NOT NULL, -- Random shard from 0 to N-1
    followers_count   INT8        NOT NULL DEFAULT 0,
    following_count   INT8        NOT NULL DEFAULT 0,
    posts_count       INT8        NOT NULL DEFAULT 0,
    votes_cast_count  INT8        NOT NULL DEFAULT 0,
    PRIMARY KEY (user_id, shard_id)
) INTERLEAVE IN PARENT users ON DELETE CASCADE;
```

---

### 2. Posts, Votes, and Comments

#### `post_main_contents`
Optional reusable article/content for original posts. Posts can exist without this row when they are thought-only, slider-only, or thought+slider. Reposts point to the original through `source_post_id`; API cards expose original article content as `sourcePost.article`, not as a repost-owned top-level `article`.
```sql
CREATE TABLE post_main_contents (
    main_content_id   VARCHAR(36)  NOT NULL DEFAULT spanner.generate_uuid(),
    created_by_id     VARCHAR(36)  NOT NULL,
    kind              VARCHAR(50)  NOT NULL, -- text, media, link, mixed
    text              TEXT,
    asset_id          VARCHAR(36),
    link_url          TEXT,
    link_title        VARCHAR(200),
    link_description  VARCHAR(500),
    created_at        TIMESTAMPTZ  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (main_content_id)
);
```

#### `posts`
Top-level table tracking a user's optional article, thought, and slider. At least one of `main_content_id`, `thought_text`, or `slider_text` is required by application validation. Reposts are new post rows that point to an original through `source_post_id`; repost-owned content is limited to `thought_text` and optional slider fields. Original article fallback for repost cards is resolved through the source post and returned as `sourcePost.article`.
```sql
CREATE TABLE posts (
    post_id          VARCHAR(36)  NOT NULL DEFAULT spanner.generate_uuid(),
    author_id        VARCHAR(36)  NOT NULL,
    main_content_id  VARCHAR(36),
    source_post_id   VARCHAR(36),                                -- original source post when this row is a repost; reposting a repost is not allowed
    is_repost        BOOLEAN      NOT NULL DEFAULT FALSE,
    visibility       VARCHAR(50)  NOT NULL DEFAULT 'public',     -- public, public_hub, private_locked_hub
    thought_text     VARCHAR(250),                               -- author-specific thought/take
    slider_text      VARCHAR(260),                               -- visible slider title/prompt when the post has a slider
    left_label       VARCHAR(50),
    right_label      VARCHAR(50),
    created_at       TIMESTAMPTZ  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at       TIMESTAMPTZ  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at       TIMESTAMPTZ,
    PRIMARY KEY (post_id)
);

-- Index for the global recent post feed (posts-feed-list endpoint without hub filters)
CREATE INDEX idx_posts_created_at ON posts(created_at DESC);
-- Index for a user's profile feed to load native posts they authored chronologically
CREATE INDEX idx_posts_author_created ON posts(author_id, created_at DESC);
-- Index for listing reposts of an original post
CREATE INDEX idx_posts_source_post_created ON posts(source_post_id, created_at DESC);
-- Index for public/default feed candidates
CREATE INDEX idx_posts_visibility_created ON posts(visibility, created_at DESC);
```

#### `post_hub_targets`
Maps posts to hubs selected at creation/repost time. `private_locked_hub` posts are visible only through these hub targets and cannot be reposted or shared onward.
```sql
CREATE TABLE post_hub_targets (
    post_id      VARCHAR(36) NOT NULL,
    hub_id       VARCHAR(36) NOT NULL,
    added_by_id  VARCHAR(36) NOT NULL,
    target_type  VARCHAR(50) NOT NULL,   -- created_in_hub, repost_to_hub
    created_at   TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (post_id, hub_id)
) INTERLEAVE IN PARENT posts ON DELETE CASCADE;

CREATE INDEX idx_post_hub_targets_hub_created ON post_hub_targets(hub_id, created_at DESC);
```

#### `post_repost_counters` (Sharded Counter)
Counts active reposts of an original post. Repost of repost is not allowed, so counters are attached directly to original source posts only.
```sql
CREATE TABLE post_repost_counters (
    post_id              VARCHAR(36) NOT NULL,
    shard_id             INT8        NOT NULL,
    active_repost_count  INT8        NOT NULL DEFAULT 0,
    PRIMARY KEY (post_id, shard_id)
) INTERLEAVE IN PARENT posts ON DELETE CASCADE;
```

#### `comments`
Public comments on posts with one-level reply threads. `parent_comment_id` always points at the thread's root comment (replying to a reply re-attaches to the root; added in migration `010_comment_reply_threading.sql`). Hub-scoped discussion/chat is stored separately under hub discussions.
```sql
CREATE TABLE comments (
    post_id            VARCHAR(36) NOT NULL,
    comment_id         VARCHAR(36) NOT NULL DEFAULT spanner.generate_uuid(),
    author_id          VARCHAR(36) NOT NULL,
    text               TEXT,
    asset_id           VARCHAR(36),
    parent_comment_id  VARCHAR(36),
    created_at         TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at         TIMESTAMPTZ,
    PRIMARY KEY (post_id, comment_id)
) INTERLEAVE IN PARENT posts ON DELETE CASCADE;

-- Required Index to paginate a post's comments timeline
CREATE INDEX idx_comments_post_created ON comments(post_id, created_at DESC) INTERLEAVE IN posts;
```

#### `post_vote_receipts`
User-linked vote receipt. Proves that a user has voted on a post frame, but never stores the selected vote position.
```sql
CREATE TABLE post_vote_receipts (
    post_id   VARCHAR(36) NOT NULL,
    user_id   VARCHAR(36) NOT NULL,
    voted_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (post_id, user_id)
) INTERLEAVE IN PARENT posts ON DELETE CASCADE;
```

#### `post_vote_scope_receipts`
Records which scopes a user's vote counted in at cast time, without storing the selected vote position. `scope_id` is `global` for the global post scope or the hub ID for a hub scope; `hub_id` is populated only for hub scopes. This table prevents future backfill when a user later joins a hub or when a post is later shared into a hub.
```sql
CREATE TABLE post_vote_scope_receipts (
    post_id     VARCHAR(36) NOT NULL,
    user_id     VARCHAR(36) NOT NULL,
    scope_type  VARCHAR(20) NOT NULL,   -- 'global' or 'hub'
    scope_id    VARCHAR(80) NOT NULL,   -- 'global' for the global scope, or the hub UUID string
    hub_id      VARCHAR(36),            -- populated only for hub scopes
    counted_at  TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (post_id, user_id, scope_type, scope_id)
) INTERLEAVE IN PARENT posts ON DELETE CASCADE;

CREATE INDEX idx_vote_scope_receipts_user ON post_vote_scope_receipts(user_id, counted_at DESC);
```

#### `post_vote_counters` (Sharded Counter)
Holds anonymous aggregate voting stats that power the vote/summary endpoints. The selected vote value survives only in these aggregate counters and the detailed bucket counters, never in user-linked receipt tables.
Instead of querying individual votes, we sum up shards.
Read APIs hide bucket counts until the relevant result scope has at least 3 votes to preserve anonymity; counters still store all anonymous aggregate votes.
```sql
CREATE TABLE post_vote_counters (
    post_id        VARCHAR(36) NOT NULL,
    shard_id       INT8        NOT NULL,        -- Random shard 0 to N-1
    total_votes    INT8        NOT NULL DEFAULT 0,
    left_count     INT8        NOT NULL DEFAULT 0,   -- strong_left + left
    neutral_count  INT8        NOT NULL DEFAULT 0,
    right_count    INT8        NOT NULL DEFAULT 0,   -- right + strong_right
    PRIMARY KEY (post_id, shard_id)
) INTERLEAVE IN PARENT posts ON DELETE CASCADE;
```

#### `post_hub_vote_counters` (Sharded Counter)
Powers the `/posts/{postId}/vote/hubs` endpoint.
Stores anonymous aggregate votes per post frame, grouped by hub, to avoid massive read-time JOINs between receipts and hub members. Updated at vote time for hubs where the post is already visible and the voter is already an active member.
Hub-scoped analytics hide bucket counts until that hub scope has at least 3 votes. Later hub joins or later shares into a hub do not backfill old votes.
```sql
CREATE TABLE post_hub_vote_counters (
    post_id        VARCHAR(36) NOT NULL,
    hub_id         VARCHAR(36) NOT NULL,
    shard_id       INT8        NOT NULL,
    total_votes    INT8        NOT NULL DEFAULT 0,
    left_count     INT8        NOT NULL DEFAULT 0,
    neutral_count  INT8        NOT NULL DEFAULT 0,
    right_count    INT8        NOT NULL DEFAULT 0,
    PRIMARY KEY (post_id, hub_id, shard_id)
) INTERLEAVE IN PARENT posts ON DELETE CASCADE;
```

#### `post_vote_bucket_counters` (Sharded Counter)
Provides O(1) anonymous reads for `/vote/distribution` bucket arrays. `position` is one of the five slider positions.
Read APIs return an empty distribution until the post has at least 3 votes.
```sql
CREATE TABLE post_vote_bucket_counters (
    post_id   VARCHAR(36) NOT NULL,
    position  VARCHAR(20) NOT NULL,         -- strong_left, left, neutral, right, strong_right
    shard_id  INT8        NOT NULL,         -- Random shard
    count     INT8        NOT NULL DEFAULT 0,
    PRIMARY KEY (post_id, position, shard_id)
) INTERLEAVE IN PARENT posts ON DELETE CASCADE;
```

#### `post_hub_vote_bucket_counters` (Sharded Counter)
Provides O(1) anonymous reads for `/vote/hubs` bucket distributions.
```sql
CREATE TABLE post_hub_vote_bucket_counters (
    post_id   VARCHAR(36) NOT NULL,
    hub_id    VARCHAR(36) NOT NULL,
    position  VARCHAR(20) NOT NULL,
    shard_id  INT8        NOT NULL,
    count     INT8        NOT NULL DEFAULT 0,
    PRIMARY KEY (post_id, hub_id, position, shard_id)
) INTERLEAVE IN PARENT posts ON DELETE CASCADE;
```

---

### 3. Hubs and Memberships

#### `hubs`
Top-level table tracking hub definition.
```sql
CREATE TABLE hubs (
    hub_id            VARCHAR(36)  NOT NULL DEFAULT spanner.generate_uuid(),
    name              VARCHAR(100) NOT NULL,
    description       TEXT,
    profile_asset_id  VARCHAR(36)  NOT NULL,
    custom_link       VARCHAR(60),
    created_at        TIMESTAMPTZ  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        TIMESTAMPTZ  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (hub_id)
);

CREATE UNIQUE INDEX idx_hubs_custom_link ON hubs(custom_link);
```

#### `hub_memberships`
Interleaved within hubs. Includes admin roles.
```sql
CREATE TABLE hub_memberships (
    hub_id      VARCHAR(36) NOT NULL,
    user_id     VARCHAR(36) NOT NULL,
    role        VARCHAR(50) NOT NULL DEFAULT 'member',   -- member, admin, super_admin; app displays super_admin as manager
    joined_at   TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at  TIMESTAMPTZ,
    PRIMARY KEY (hub_id, user_id)
) INTERLEAVE IN PARENT hubs ON DELETE CASCADE;

-- Required Index to get all hubs a user belongs to quickly
CREATE INDEX idx_hub_membership_user   ON hub_memberships(user_id);
-- Index to filter and paginate hub members/admins specifically
CREATE INDEX idx_hub_membership_role   ON hub_memberships(hub_id, role, joined_at DESC) INTERLEAVE IN hubs;
-- Index to paginate general hub members sorted by join date
CREATE INDEX idx_hub_membership_joined ON hub_memberships(hub_id, joined_at DESC) INTERLEAVE IN hubs;
```

#### `hub_notification_settings`
Per-user settings for an active hub membership. `content_muted` suppresses only normal hub content activity notifications (`hub_post_created`, `post_shared_to_hub`) for this user/hub. Suppressed content activity creates no in-app notification feed row, SSE event, push, app-icon badge, or hub footer badge for that user. Invite, join-request, member, role-change, and non-hub notifications are not muted here.
```sql
CREATE TABLE hub_notification_settings (
    hub_id         VARCHAR(36) NOT NULL,
    user_id        VARCHAR(36) NOT NULL,
    content_muted  BOOLEAN     NOT NULL DEFAULT FALSE,
    muted_until    TIMESTAMPTZ,
    updated_at     TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (hub_id, user_id)
) INTERLEAVE IN PARENT hubs ON DELETE CASCADE;

CREATE INDEX idx_hub_notification_settings_user ON hub_notification_settings(user_id, hub_id);
```

#### `hub_member_counters` (Sharded Counter)
Tracks total members.
```sql
CREATE TABLE hub_member_counters (
    hub_id        VARCHAR(36) NOT NULL,
    shard_id      INT8        NOT NULL,
    member_count  INT8        NOT NULL DEFAULT 0,
    PRIMARY KEY (hub_id, shard_id)
) INTERLEAVE IN PARENT hubs ON DELETE CASCADE;
```

#### `hub_invitations`
Pending phone-number invitations. These are not active memberships. The server stores only phone numbers explicitly selected for an invite action, never the user's full contact book. SMS delivery is device-side: the API returns invite URLs/share text, and the mobile app uses the phone's native SMS/share flow.
```sql
CREATE TABLE hub_invitations (
    invitation_id                         VARCHAR(36)  NOT NULL DEFAULT spanner.generate_uuid(),
    hub_id                                VARCHAR(36)  NOT NULL,
    inviter_user_id                       VARCHAR(36)  NOT NULL,
    inviter_role_at_create                VARCHAR(50)  NOT NULL,   -- member, admin, super_admin
    invitee_user_id                       VARCHAR(36),
    phone_e164                            VARCHAR(30)  NOT NULL,
    token_hash                            VARCHAR(255) NOT NULL,
    status                                VARCHAR(50)  NOT NULL DEFAULT 'invited',   -- invited, accepted_active, pending_admin_approval, declined, expired, cancelled
    requires_admin_approval_after_accept  BOOLEAN      NOT NULL,
    join_request_id                       VARCHAR(36),
    expires_at                            TIMESTAMPTZ  NOT NULL,
    accepted_at                           TIMESTAMPTZ,
    declined_at                           TIMESTAMPTZ,
    created_at                            TIMESTAMPTZ  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (invitation_id)
);

CREATE UNIQUE INDEX idx_hub_invitations_token        ON hub_invitations(token_hash);
CREATE INDEX        idx_hub_invitations_hub_status   ON hub_invitations(hub_id, status, created_at DESC);
CREATE INDEX        idx_hub_invitations_phone_status ON hub_invitations(phone_e164, status, expires_at);
```

#### `hub_invite_links`
Limited-time links generated by active hub members. Joining through a link always creates a pending admin-approval request.
```sql
CREATE TABLE hub_invite_links (
    invite_link_id  VARCHAR(36)  NOT NULL DEFAULT spanner.generate_uuid(),
    hub_id          VARCHAR(36)  NOT NULL,
    created_by_id   VARCHAR(36)  NOT NULL,
    token_hash      VARCHAR(255) NOT NULL,
    expires_at      TIMESTAMPTZ  NOT NULL,
    revoked_at      TIMESTAMPTZ,
    created_at      TIMESTAMPTZ  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (invite_link_id)
);

CREATE UNIQUE INDEX idx_hub_invite_links_token ON hub_invite_links(token_hash);
CREATE INDEX        idx_hub_invite_links_hub   ON hub_invite_links(hub_id, created_at DESC);
```

#### `hub_join_requests`
Pending admin approvals created from member invitations or invite links. These are not active memberships until approved.
```sql
CREATE TABLE hub_join_requests (
    join_request_id    VARCHAR(36) NOT NULL DEFAULT spanner.generate_uuid(),
    hub_id             VARCHAR(36) NOT NULL,
    requester_user_id  VARCHAR(36) NOT NULL,
    source             VARCHAR(50) NOT NULL,   -- 'member_invitation' or 'invite_link'
    invitation_id      VARCHAR(36),
    invite_link_id     VARCHAR(36),
    status             VARCHAR(50) NOT NULL DEFAULT 'pending_admin_approval',   -- pending_admin_approval, approved, rejected, cancelled
    decided_by_id      VARCHAR(36),
    decided_at         TIMESTAMPTZ,
    created_at         TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (join_request_id)
);

CREATE INDEX idx_hub_join_requests_hub_status ON hub_join_requests(hub_id, status, created_at DESC);
CREATE INDEX idx_hub_join_requests_requester  ON hub_join_requests(requester_user_id, status, created_at DESC);
```

#### `post_shares` (legacy)
Initial hub-only share table. It remains in the schema for compatibility, but current share/feed code uses `post_share_events` and `post_share_targets`.
```sql
CREATE TABLE post_shares (
    hub_id        VARCHAR(36) NOT NULL,
    post_id       VARCHAR(36) NOT NULL,
    shared_by_id  VARCHAR(36) NOT NULL,
    note          TEXT,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (hub_id, post_id)
) INTERLEAVE IN PARENT hubs ON DELETE CASCADE;

-- Required Index to query the feed for a hub sorted by recency
CREATE INDEX idx_post_shares_feed      ON post_shares(hub_id, created_at DESC) INTERLEAVE IN hubs;
-- Required Index for a user's profile feed to load posts they shared to hubs chronologically
CREATE INDEX idx_post_shares_shared_by ON post_shares(shared_by_id, created_at DESC);
```

#### `post_share_events`
Records a repeatable share action by a user. One share event points to the exact post or repost frame that was shared; repost shares are not collapsed to the source original. `private_locked_hub` posts cannot be inserted here because locked content cannot be shared onward.
```sql
CREATE TABLE post_share_events (
    share_id      VARCHAR(36) NOT NULL DEFAULT spanner.generate_uuid(),
    post_id       VARCHAR(36) NOT NULL,
    shared_by_id  VARCHAR(36) NOT NULL,
    note          TEXT,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (share_id)
);

CREATE INDEX idx_post_share_events_actor_created ON post_share_events(shared_by_id, created_at DESC, share_id DESC);
CREATE INDEX idx_post_share_events_post_created  ON post_share_events(post_id, created_at DESC, share_id DESC);
```

#### `post_share_targets`
Fanout targets for a share event. Feed-share creates one row with `target_type=feed`; share-to-hubs creates one row per hub with `target_type=hub`. Rows are used instead of an array so hub feeds and user profile feeds can page by indexed `hub_id/shared_by_id + created_at + share_id`.
```sql
CREATE TABLE post_share_targets (
    share_id      VARCHAR(36) NOT NULL,
    target_type   VARCHAR(20) NOT NULL, -- feed, hub
    target_id     VARCHAR(80) NOT NULL, -- user id for feed, hub id for hub
    hub_id        VARCHAR(36),
    post_id       VARCHAR(36) NOT NULL,
    shared_by_id  VARCHAR(36) NOT NULL,
    created_at    TIMESTAMPTZ NOT NULL,
    PRIMARY KEY (share_id, target_type, target_id)
);

CREATE INDEX idx_post_share_targets_hub_feed ON post_share_targets(hub_id, created_at DESC, share_id DESC);
CREATE INDEX idx_post_share_targets_feed_actor ON post_share_targets(shared_by_id, created_at DESC, share_id DESC);
```

#### `post_feed_activity_events`
Denormalized activity log for the Home feed. It supports "posts that friends interacted with" without scanning votes/comments/reposts/shares at read time. The feed reader still enforces current visibility rules before returning any post; `private_locked_hub` content can appear only when the viewer is the author or an active member of at least one selected hub for the post.
```sql
CREATE TABLE post_feed_activity_events (
    activity_id     VARCHAR(36) NOT NULL DEFAULT spanner.generate_uuid(),
    actor_user_id   VARCHAR(36) NOT NULL,
    post_id         VARCHAR(36) NOT NULL,
    source_post_id  VARCHAR(36),
    hub_id          VARCHAR(36),
    activity_type   VARCHAR(50) NOT NULL,   -- post_created, post_voted, post_commented, post_reposted, post_shared_to_feed, post_shared_to_hub
    created_at      TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (activity_id)
);

CREATE INDEX idx_post_feed_activity_actor_created ON post_feed_activity_events(actor_user_id, created_at DESC);
CREATE INDEX idx_post_feed_activity_post_created  ON post_feed_activity_events(post_id, created_at DESC);
CREATE INDEX idx_post_feed_activity_hub_created   ON post_feed_activity_events(hub_id, created_at DESC);
```

#### `hub_discussions`
Private post-bound discussion threads inside a hub. Each discussion belongs to one hub and one post that appears in that hub. Activity here updates hub last-activity ordering but does not create notification events.
```sql
CREATE TABLE hub_discussions (
    hub_id           VARCHAR(36) NOT NULL,
    discussion_id    VARCHAR(36) NOT NULL DEFAULT spanner.generate_uuid(),
    post_id          VARCHAR(36) NOT NULL,
    created_by_id    VARCHAR(36) NOT NULL,
    message_count    INT8        NOT NULL DEFAULT 0,
    last_message_at  TIMESTAMPTZ,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (hub_id, discussion_id)
) INTERLEAVE IN PARENT hubs ON DELETE CASCADE;

CREATE UNIQUE INDEX idx_hub_discussions_post          ON hub_discussions(hub_id, post_id);
CREATE INDEX        idx_hub_discussions_last_activity ON hub_discussions(hub_id, last_message_at DESC) INTERLEAVE IN hubs;
```

#### `hub_discussion_messages`
Chat-style messages inside a hub discussion with one-level reply threads. `parent_message_id` always points at the thread's root message (replying to a reply re-attaches to the root; added in migration `010_comment_reply_threading.sql`). There are no replies to replies.
```sql
CREATE TABLE hub_discussion_messages (
    hub_id             VARCHAR(36) NOT NULL,
    discussion_id      VARCHAR(36) NOT NULL,
    message_id         VARCHAR(36) NOT NULL DEFAULT spanner.generate_uuid(),
    author_id          VARCHAR(36) NOT NULL,
    text               TEXT,
    asset_id           VARCHAR(36),
    parent_message_id  VARCHAR(36),
    created_at         TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (hub_id, discussion_id, message_id)
);

CREATE INDEX idx_hub_discussion_messages_created ON hub_discussion_messages(hub_id, discussion_id, created_at DESC);
```

#### `saved_hub_discussions`
Saved content points to a hub discussion/chat item, not directly to a post. Saved items are shown only while the user remains an active member of the hub.
```sql
CREATE TABLE saved_hub_discussions (
    user_id        VARCHAR(36) NOT NULL,
    hub_id         VARCHAR(36) NOT NULL,
    discussion_id  VARCHAR(36) NOT NULL,
    saved_at       TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, hub_id, discussion_id)
) INTERLEAVE IN PARENT users ON DELETE CASCADE;

-- Required index to paginate a user's saved discussion list chronologically.
CREATE INDEX idx_saved_hub_discussions_saved_at ON saved_hub_discussions(user_id, saved_at DESC) INTERLEAVE IN users;
```

---

### 4. General Infrastructure (Uploads, Notifications, Reports)

#### `assets`
Standalone table to track signed URL uploads lifecycle.
```sql
CREATE TABLE assets (
    asset_id         VARCHAR(36) NOT NULL DEFAULT spanner.generate_uuid(),
    user_id          VARCHAR(36) NOT NULL,
    purpose          VARCHAR(50) NOT NULL,
    content_type     VARCHAR(50) NOT NULL,
    file_size_bytes  INT8        NOT NULL,
    status           VARCHAR(50) NOT NULL DEFAULT 'pending',   -- 'pending' -> 'completed'
    gcs_object_path  TEXT        NOT NULL,
    checksum_sha256  VARCHAR(64),
    created_at       TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (asset_id)
);
```

#### `notifications`
Feed of in-app notification events. The notification feed and SSE stream use this table. Hub content mute prevents muted hub content rows from being created for the muted user; push/app-icon/hub-footer delivery is also controlled by user notification settings and mute rules. Hub discussion/chat messages do not create rows here.

Current event types include `post_voted`, `post_commented`, `post_reposted`, `post_shared_to_feed`, `user_followed`, `hub_post_created`, `post_shared_to_hub`, `hub_invite_received`, `hub_join_request_created`, `hub_join_request_approved`, `hub_join_request_rejected`, `hub_member_joined`, `hub_member_removed`, and `hub_role_changed`. Saved discussions, reports, and blocks do not create user-facing notification rows.
```sql
CREATE TABLE notifications (
    user_id          VARCHAR(36) NOT NULL,
    notification_id  VARCHAR(36) NOT NULL DEFAULT spanner.generate_uuid(),
    type             VARCHAR(50) NOT NULL,
    actor_id         VARCHAR(36) NOT NULL,
    post_id          VARCHAR(36),
    source_post_id   VARCHAR(36),
    comment_id       VARCHAR(36),
    hub_id           VARCHAR(36),
    invitation_id    VARCHAR(36),
    join_request_id  VARCHAR(36),
    is_read          BOOLEAN     NOT NULL DEFAULT FALSE,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, notification_id)
) INTERLEAVE IN PARENT users ON DELETE CASCADE;

-- Required Index to query unread notifications efficiently
CREATE INDEX idx_notifications_user_unread ON notifications(user_id, is_read, created_at DESC) INTERLEAVE IN users;
```

#### `reports`
Moderation queue, entirely decoupled from primary data reads.
```sql
CREATE TABLE reports (
    report_id         VARCHAR(36) NOT NULL DEFAULT spanner.generate_uuid(),
    reporter_id       VARCHAR(36) NOT NULL,
    reason            VARCHAR(50) NOT NULL,
    details           TEXT,
    reported_user_id  VARCHAR(36),
    reported_post_id  VARCHAR(36),
    reported_hub_id   VARCHAR(36),
    status            VARCHAR(50) NOT NULL DEFAULT 'open',
    created_at        TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (report_id)
);
```

---

## Stateless Auth & Redis Storage

As defined by API constraints, we will leverage Redis to remove expensive session retrieval and aggregate queries from the critical path DB operations.

### 1. JSON Web Tokens (JWT) & Refresh Context
- **Access Tokens**: Short-lived (e.g. 15 minutes) statutory JWTs containing the `userId`. The backend cryptographically verifies these without any DB impact.
- **Refresh Tokens (Redis)**: 
  - Pattern: `SET user:{userId}:refresh_token:{jti}` -> JSON string mapped to `<device context, issuance>`.
  - Expiry tied exactly to the refresh token TTL (e.g., 30 days).
  - On `auth/logout`, explicitly run `DEL user:{userId}:refresh_token:{jti}`.
  - Doing this guarantees instantaneous global revocation without polling Spanner.

### 2. OTP Challenges
- For `POST /auth/otp/request` and `POST /auth/otp/verify`:
- **Redis Key**: `HSET otp:{challengeId}`
- Fields: `channel`, `destination`, `code_hash`, `attempts`, `purpose`.
- TTL: 5-10 minutes. 

### 3. Fast Analytics (Active Users 24h & Rate limits)
- Since `activeUsers24h` is requested frequently in UI, storing it in Spanner relies on expensive aggregation.
- Solution: Redis HyperLogLog (HLL) per hub.
  - On user activity inside a Hub: `PFADD hub:{hubId}:active_users_day:{Y-M-D} {userId}`.
  - Fast merging via `PFMERGE` and fast distinct count read via `PFCOUNT` daily.
- Rate limit buckets stored via Redis sliding window strings natively for OTP spam blocks.

---

## Backup & Disaster Recovery (Spanner)

Spanner handles disaster recovery natively without requiring external batch export jobs:

1. **Point-in-Time Recovery (PITR):** The database retains a rolling 7-day history (`version_retention_period = "7d"`). In the event of a dropped table or a bad data migration, operators can instantly query or restore the database to any exact microsecond within the past 7 days.
2. **Scheduled Managed Backups:** For longer-term compliance, Spanner triggers automated, highly-consistent byte-level snapshots in the background. The specific policy is configured to take **1 snapshot per day, retained for 30 days**, providing exactly 30 rolling daily restore points.