BestBuds Public and Heimdall Schema Hardening

This page is the working plan for making the BestBuds Postgres schema safe after the Heimdall migration.

The current direction is correct: Heimdall owns identity, principals, memberships, sessions, refresh tokens, PATs, service accounts, signing keys, roles, permissions, and audit. The old public schema remains the product data surface for chat, friends, groups, campaigns, onboarding, stickers, moderation, and app-facing compatibility views.

The problem is the boundary between them. The public schema was made compatible by wide views over Heimdall. That kept old app queries working, but it also made several hot paths depend on duplicate-producing joins, JSON expression lookups, broad OR predicates, and aggregation through views. That is acceptable for a migration bridge. It is not the shape to scale.

Current Verdict

Do not optimize the current compatibility layer in place as the long-term model.

Use the current compatibility views only as a temporary bridge while moving runtime paths to app-scoped, one-row-per-principal projections and direct product tables.

The core issue is not "Postgres is slow" or "Heimdall is slow." The core issue is that the public compatibility layer hides identity resolution, app scoping, legacy alias matching, and user presentation inside broad views that are joined from many product queries.

Evidence Snapshot

Observed live on July 3, 2026 against Supabase Postgres 15.8:

These counts are small. Any query that already shows fanout, row inflation, or write-race symptoms at this size should be treated as structurally unsafe.

Source Anchors

The important files are:

Design Goal

The target state is simple:

1. Heimdall remains the canonical auth database.
2. Public tables remain product facts.
3. Compatibility is explicit, app-scoped, indexed, and bounded.
4. Hot product queries do not join through broad identity views.
5. Legacy identity matching is a data migration problem, not a per-request query problem.
6. Every write path with a uniqueness or single-use invariant is enforced by the database, not only by application order.

Boundary Model

Treat the database as three layers.

The current users_view mixes all three layers. It returns presentation data, identity aliases, membership state, profile extension data, and embeddings in one view. That makes it convenient, but also makes every downstream join pay for identity resolution.

Required Invariants

These invariants should become explicit migration gates.

Replace users_view as a Hot Path

The current users_view is useful for compatibility, but it is too broad for hot paths.

Current shape:

CREATE VIEW public.users_view AS
SELECT ...
FROM heimdall.principal p
LEFT JOIN heimdall.identity i ON p.identity_id = i.id
LEFT JOIN LATERAL (
  SELECT pa.*
  FROM heimdall.provider_account pa
  WHERE pa.identity_id = p.identity_id
  ORDER BY pa.created_at DESC
  LIMIT 1
) pa ON TRUE
JOIN heimdall.membership m ON m.principal_id = p.id
LEFT JOIN public.user_profiles up ON p.id = up.id
LEFT JOIN public.embeddings e ON p.id = e.target_id AND e.target_type LIKE '%User%'
WHERE p.kind IN ('USER', 'AGENT', 'ACTOR')
  AND p.status = 'ACTIVE'
  AND m.status IN ('ACTIVE', 'INVITED');

Problems:

• no m.app_id = BestBuds app id filter;
• one row per matching membership, not one row per principal;
• lateral provider account lookup on each principal row;
• JSON merge on every read;
• embedding join via LIKE '%User%', which is not a precise target type;
• downstream joins inherit the duplicate rows.

The replacement should be a table or materialized projection:

CREATE TABLE public.bestbuds_user_projection (
    app_id uuid NOT NULL,
    principal_id uuid NOT NULL REFERENCES heimdall.principal(id) ON DELETE CASCADE,
    data_user_id uuid NOT NULL REFERENCES public.user_profiles(id) ON DELETE CASCADE,
    membership_id uuid NOT NULL REFERENCES heimdall.membership(id) ON DELETE CASCADE,
    status text NOT NULL,
    display_name text NOT NULL,
    social_id text,
    provider text NOT NULL,
    email text,
    profile_picture text,
    data jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL,
    updated_at timestamptz NOT NULL,
    PRIMARY KEY (app_id, principal_id),
    UNIQUE (app_id, data_user_id)
);

CREATE INDEX bestbuds_user_projection_email_idx
    ON public.bestbuds_user_projection (app_id, lower(email))
    WHERE email IS NOT NULL;

CREATE INDEX bestbuds_user_projection_social_idx
    ON public.bestbuds_user_projection (app_id, social_id)
    WHERE social_id IS NOT NULL;

This can start as a maintained table populated during login/profile updates. If a view is preferred at first, it still needs DISTINCT ON (p.id) or an equivalent app-scoped selection rule:

CREATE VIEW public.bestbuds_users_v2 AS
SELECT DISTINCT ON (p.id)
    p.id,
    m.id AS membership_id,
    m.app_id,
    m.status,
    ...
FROM heimdall.principal p
JOIN heimdall.membership m ON m.principal_id = p.id
WHERE m.app_id = '38d1cee3-004d-441f-8f66-ad5060859af1'::uuid
  AND p.kind IN ('USER', 'AGENT', 'ACTOR')
  AND p.status = 'ACTIVE'
  AND m.status IN ('ACTIVE', 'INVITED')
ORDER BY p.id, m.updated_at DESC NULLS LAST, m.created_at DESC NULLS LAST;

The view version is only an intermediate step. The table version gives better control over indexes, update timing, legacy aliases, and profile shape.

Replace Per-Request Legacy Matching

resolveBestBudsDataUserId currently tries to identify the product data user by matching:

• uv.id = auth principal id;
• uv.social_id = auth social id;
• uv.data->>'email' = auth email;
• then tie-breaking by whether the candidate appears in friends, messages, and user profiles.

That was reasonable as a one-time repair query. It is not a good per-request runtime query.

The migration-safe replacement is an alias table:

CREATE TABLE public.user_identity_alias (
    app_id uuid NOT NULL,
    alias_kind text NOT NULL CHECK (alias_kind IN ('principal_id', 'provider_subject', 'email', 'legacy_social_id')),
    alias_value text NOT NULL,
    data_user_id uuid NOT NULL REFERENCES public.user_profiles(id) ON DELETE CASCADE,
    source text NOT NULL DEFAULT 'migration',
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (app_id, alias_kind, alias_value)
);

CREATE INDEX user_identity_alias_user_idx
    ON public.user_identity_alias (data_user_id);

Then the runtime lookup becomes deterministic:

SELECT data_user_id::text
FROM public.user_identity_alias
WHERE app_id = $1
  AND alias_kind = 'principal_id'
  AND alias_value = $2
LIMIT 1;

During migration, insert aliases for principal id, provider subject, and email. After all product FKs point to canonical principal ids, the runtime resolver can collapse to a direct membership/profile check:

SELECT p.id::text
FROM heimdall.principal p
JOIN heimdall.membership m ON m.principal_id = p.id
JOIN public.user_profiles up ON up.id = p.id
WHERE p.id = $1
  AND m.app_id = $2
  AND p.status = 'ACTIVE'
  AND m.status IN ('ACTIVE', 'INVITED')
LIMIT 1;

Rewrite Message Hot Paths

Message history is the most important read path.

Keep this index:

CREATE INDEX IF NOT EXISTS idx_messages_chat_created
    ON public.messages (chat_id, created_at DESC);

Do not rely on the checked-in (chat_id, updated_at DESC) index for chat history. The application orders by created_at DESC.

topMessages should not rank every message in every relevant chat. Use a bounded per-chat lateral read:

WITH relevant_chats AS (
    SELECT DISTINCT chat_id, type, entity_id, name, data
    FROM (
        SELECT chat_id, 'direct' AS type, friend_id AS entity_id, name, data
        FROM public.friends_view
        WHERE user_id = $1
        UNION ALL
        SELECT chat_id, 'group' AS type, id AS entity_id, name, data
        FROM public.groups_view
        WHERE user_id = $1
    ) r
)
SELECT
    m.id,
    m.chat_id,
    m.value,
    m.kind,
    m.sender_id,
    su.display_name AS sender_name,
    m.receiver_id,
    rc.type,
    rc.entity_id,
    rc.name AS entity_name,
    rc.data AS entity_data,
    m.created_at,
    m.updated_at
FROM relevant_chats rc
JOIN LATERAL (
    SELECT *
    FROM public.messages m
    WHERE m.chat_id = rc.chat_id
    ORDER BY m.created_at DESC
    LIMIT 5
) m ON TRUE
JOIN public.bestbuds_user_projection su
  ON su.app_id = $2 AND su.data_user_id = m.sender_id
ORDER BY m.created_at DESC;

This shape is bounded by relevant_chats * 5, not by total messages in those chats.

Also add this for campaign signals:

CREATE INDEX IF NOT EXISTS idx_messages_chat_sender_created
    ON public.messages (chat_id, sender_id, created_at DESC);

That index supports "joined but never messaged" and per-campaign activity checks without repeatedly walking messages by sender alone.

Campaign and Group Compatibility

campaigns_view and group_view aggregate users through users_view. Once users_view is replaced, the aggregation should join the app-scoped projection or materialize membership summaries separately.

Rules:

1. Campaign discovery must have deterministic ordering.
2. Campaign membership checks need both (campaign_id, user_id) and (user_id, campaign_id) access.
3. remainingDays should not be a JSON expression in a hot scheduler query.
4. Group/campaign response assembly should not multiply users because of memberships.

Indexes:

CREATE INDEX IF NOT EXISTS idx_campaign_users_user_campaign
    ON public.campaign_users (user_id, campaign_id);

CREATE INDEX IF NOT EXISTS idx_group_users_user_group
    ON public.group_users (user_id, group_id);

If active campaign scanning remains a recurring Worker job, add a generated or stored column instead of repeatedly casting JSON:

ALTER TABLE public.campaigns
    ADD COLUMN IF NOT EXISTS remaining_days int;

CREATE INDEX IF NOT EXISTS idx_campaigns_remaining_created
    ON public.campaigns (remaining_days, created_at DESC)
    WHERE chat_id IS NOT NULL;

The Worker can still mirror the value into data for compatibility, but the query predicate should be typed.

Refresh Rotation Must Be Atomic

The current refresh flow checks used_at in Kotlin and then updates the row. Concurrent calls can both read the token as unused before either update commits. Live data already shows families with multiple usable tokens created milliseconds apart.

Use a single atomic update:

WITH claimed AS (
    UPDATE heimdall.refresh_token
    SET used_at = now(),
        rotated_at = now(),
        updated_at = now()
    WHERE token_hash = $1
      AND used_at IS NULL
      AND revoked_at IS NULL
      AND expires_at > now()
    RETURNING *
)
SELECT *
FROM claimed;

If claimed returns no row, check whether the token exists and is already used. If it is already used, revoke the family:

UPDATE heimdall.refresh_token
SET revoked_at = now(),
    updated_at = now()
WHERE family_id = $1
  AND revoked_at IS NULL;

Then insert the successor in the same transaction. This makes the presented token a database-claimed one-time credential rather than an application-level best effort.

Add an invariant if the operational model allows it:

CREATE UNIQUE INDEX IF NOT EXISTS uq_refresh_one_live_per_family
    ON heimdall.refresh_token (family_id)
    WHERE used_at IS NULL
      AND revoked_at IS NULL;

If old sessions can legitimately keep multiple unused roots during migration, run a repair first and add the index only after the duplicates are removed.

PAT Verification Write Amplification

TokenInteractor.verifyPersonalAccessToken updates last_used_at on every successful verification. That is correct for audit freshness, but it can turn a read-heavy authentication path into a write-heavy hot row.

Use a coarse update window:

UPDATE heimdall.personal_access_token
SET last_used_at = now(),
    updated_at = now()
WHERE token_hash = $1
  AND (last_used_at IS NULL OR last_used_at < now() - interval '5 minutes');

For very high volume service tokens, write usage events to audit/telemetry and coalesce last_used_at asynchronously.

FK and Index Backlog

Add missing FK-side indexes before tables grow. The live audit found these important gaps:

For invitation or email login resolution, also add:

CREATE UNIQUE INDEX IF NOT EXISTS uq_identity_primary_email
    ON heimdall.identity (lower(primary_email))
    WHERE primary_email IS NOT NULL;

If duplicate emails exist, repair first, then add the unique index.

Sticker Schema Drift

The live/new dump sticker schema uses object-storage keys:

• sticker_packs.tray_icon_r2_key
• stickers.file_r2_key
• stickers.size_bytes

Older source and current Worker code still use URL columns:

• sticker_packs.tray_icon_url
• stickers.file_url

Do not paper over this with a broad view unless old deployed Workers still need it. Pick one of these paths:

1. Update Worker code and DTO mapping to use R2 keys and generate public/signed URLs at the edge.
2. Add temporary compatibility generated views for old callers, with explicit deprecation.

The R2-key shape is the better long-term model because it avoids storing derived public URLs in Postgres and lets the media edge own URL policy.

Grants and RLS

The current grants are too broad for a production Supabase database. The database should not rely on the fact that Workers normally use privileged connections.

Hardening order:

1. Revoke broad anon CRUD on public tables.
2. Revoke anon read access to Heimdall base tables.
3. Expose only narrow read views that are intentionally public.
4. Enable RLS on public tables that can be reached by Supabase roles.
5. Keep Worker/service-role access through explicit service credentials and server-side authorization, not end-user table grants.
6. Add a schema audit check that fails if anon regains DML on product tables or SELECT on Heimdall secrets/session/token tables.

Minimum check:

SELECT table_schema, table_name, grantee, privilege_type
FROM information_schema.table_privileges
WHERE table_schema IN ('public', 'heimdall')
  AND grantee = 'anon'
ORDER BY table_schema, table_name, privilege_type;

Compatibility Migration Plan

Use a staged rollout. Do not replace every query and every schema object in one DDL window.

Stage 0: freeze and audit

• Record live row counts and duplicate candidates.
• Dump schema and data.
• Capture all current grants.
• Verify public.user_profiles.id rows all exist as Heimdall principals.
• Identify active public FKs that still point at legacy ids.
• Identify callers that depend on users_view shape.

Stage 1: canonical projection

• Create bestbuds_user_projection.
• Backfill it from Heimdall membership plus user_profiles.
• Add one-row-per-principal and one-row-per-data-user constraints.
• Add email/social indexes.
• Keep users_view intact for old callers.

Stage 2: alias table

• Create user_identity_alias.
• Insert principal id aliases for all canonical users.
• Insert provider subject aliases where needed.
• Insert normalized email aliases only when email is verified and conflict-free.
• Rewrite resolveBestBudsDataUserId to use aliases first, then direct canonical id, with old OR query as a temporary fallback.

Stage 3: hot query rewrites

• Rewrite topMessages to the lateral top-N shape.
• Change message sender/receiver joins to bestbuds_user_projection.
• Rewrite campaign/group aggregations to app-scoped projection.
• Add deterministic ordering to discovery queries.
• Keep the old views only for low-volume compatibility routes.

Stage 4: transaction hardening

• Make refresh rotation atomic.
• Repair duplicate usable refresh families.
• Add the refresh-token live-family invariant if safe.
• Coalesce PAT last_used_at.
• Wrap multi-statement pack creation in one transaction or one SQL CTE.

Stage 5: source and live schema convergence

• Update checked-in schema.sql to match live message indexes.
• Update sticker schema/code to the R2-key model or add an explicit temporary compatibility view.
• Move all new auth/public compatibility DDL into canonical migration files.
• Add a repeatable read-only schema audit script that emits JSON and fails CI on drift.

Stage 6: security hardening

• Apply grant/RLS cleanup.
• Verify Workers still operate with service credentials.
• Verify public clients cannot read Heimdall token/session/PAT tables.
• Add an automated privilege regression check.

Suggested Query Replacements

Operational Checks

Run these before and after each migration stage.

-- users_view fanout
SELECT count(*) AS rows,
       count(DISTINCT id) AS distinct_ids,
       count(*) - count(DISTINCT id) AS duplicate_rows
FROM public.users_view;

-- duplicate active projection rows
SELECT app_id, principal_id, count(*)
FROM public.bestbuds_user_projection
GROUP BY app_id, principal_id
HAVING count(*) > 1;

-- public profile without principal
SELECT count(*)
FROM public.user_profiles up
LEFT JOIN heimdall.principal p ON p.id = up.id
WHERE p.id IS NULL;

-- messages pointing at missing users
SELECT
    count(*) FILTER (
      WHERE NOT EXISTS (
        SELECT 1 FROM public.user_profiles up WHERE up.id = messages.sender_id
      )
    ) AS orphan_sender,
    count(*) FILTER (
      WHERE receiver_id IS NOT NULL
        AND NOT EXISTS (
          SELECT 1 FROM public.user_profiles up WHERE up.id = messages.receiver_id
        )
    ) AS orphan_receiver
FROM public.messages;

-- refresh-token family safety
SELECT family_id, count(*) AS usable_tokens
FROM heimdall.refresh_token
WHERE used_at IS NULL
  AND revoked_at IS NULL
  AND expires_at > now()
GROUP BY family_id
HAVING count(*) > 1;

Performance checks:

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...

Use BUFFERS and row counts, not just milliseconds. The live database is still small enough that low timings can hide bad asymptotic behavior.

Success Criteria

This migration is complete when:

1. public.users_view is no longer used by hot messaging, social, campaign, or auth request paths.
2. There is exactly one app-facing user projection row per BestBuds principal.
3. Legacy id/social/email matching is served by indexed aliases or removed.
4. topMessages scales with number_of_chats * top_n, not total chat history.
5. Message history uses (chat_id, created_at DESC).
6. Refresh rotation cannot mint two live successors for one presented token.
7. PAT verification does not write on every request.
8. Public and live sticker schema agree.
9. Missing FK-side indexes are closed.
10. anon has no broad access to Heimdall base tables and no broad DML on product tables.
11. A read-only schema audit can be run before every deploy and produce the same invariants in JSON.

Final Position

Heimdall is a good canonical auth base. The old public schema can remain a product fact store. The unsafe part is the compatibility layer pretending to be a general user table while joining identity, membership, provider, profile, embedding, and legacy fields on every read.

Make compatibility explicit and indexed. Move legacy matching into data. Make hot queries bounded. Enforce transaction invariants in SQL. Then the schema can scale without forcing every Worker request to rediscover the migration history.