Best Practices — Colocated Permissions Index

This guide covers best practices for integrating with the FGA Colocated Permissions Index (Search With Permissions). It is organized into five sections:

0. Security
1. Idempotency and Deduplication Patterns
2. Leveraging Freshness for Authorization Decisions
3. Migrating Between Authorization Models
4. Querying Permissions inside Managed Data Platforms

0. Security

When you use a Colocated Permissions Index, you are storing authorization data in your own environment. That means the local permissions_index table, cache, warehouse table, or derived dataset should be treated as sensitive application data and secured accordingly.

In particular, avoid using PII in your IDs. Values such as subject_id, object_id, or related business identifiers are often copied into analytics systems, logs, exports, dashboards, and debugging workflows. If those IDs contain email addresses, employee numbers, customer names, or other directly identifying data, you are spreading PII into every system that consumes the index.

Prefer opaque, stable identifiers such as internal UUIDs or synthetic IDs, and keep any mapping back to user-facing or sensitive data in a separate, securely protected system.

At minimum, secure your permissions table with the same care you apply to other sensitive operational data:

• restrict read and write access using least-privilege roles,
• encrypt data at rest and in transit,
• avoid broad analyst or developer access unless it is required,
• audit access to the table and any downstream derived datasets,
• apply retention and deletion policies appropriate for your environment,
• review logs, exports, and BI tooling to ensure that permission data is not unintentionally exposed.

If you version by index_id or copy permissions into multiple destinations, apply the same controls everywhere the data is replicated. A colocated index improves query performance, but it also creates additional copies of authorization data that you are responsible for protecting.

1. Idempotency and Deduplication Patterns

Delivery Guarantees

The Permissions Index Read Expansions API streams events at-least-once. Every tuple write that produces a permission change produces an expansion event, but delivery is not exactly-once: you may see the same event more than once. This is expected behavior.

Why Duplicates Happen

Reconnecting by itself doesn't cause duplicates — only resuming from a from token that's behind your actual progress does. That gap usually comes from a crash: the consumer applies an event, then crashes before saving the corresponding token. On restart, it resumes from the older token and re-receives events it already handled.

Timeline:
  ┌─ Event A (from: x1) ──── consumer processes, saves token x1
  ├─ Event B (from: x2) ──── consumer processes, but crashes before saving x2
  ├─ Event C (from: x3) ──── never received
  │
  └─ Consumer reconnects with the earlier ?from=x1
       ├─ Event B (from: x2) ──── DUPLICATE (already processed)
       └─ Event C (from: x3) ──── new event

Continuation Token Management

The from continuation token is your resume point for the Read Expansions stream. Proper token management determines where a consumer restarts after disconnects, crashes, or deploys.

Safe Replay

When the permissions table and continuation token share a database, save the from continuation token in the same transaction as the expansion update. If your process crashes after receiving an event but before persisting its effects, restarting from the last committed token causes that event to be delivered again. Because your consumer is idempotent, this is what makes replay safe.

An intermediary that publishes to a separate stream or broker usually cannot include that publish and its FGA token update in one database transaction. Unless your broker supports transactions across both operations, publish the event first and save the FGA token only after the broker confirms the publish. A crash between those steps may cause the event to be published again, but it will not be skipped. Use event.id or idempotent downstream writes to handle that duplicate safely.

The inverse is dangerous: if you save a newer token before the corresponding event is durably processed, a crash can cause that event to be skipped permanently on restart.

Safe pattern:
    receive event (from=x2)
    apply event durably (idempotent upsert)
    save last_continuation_token = x2

Unsafe pattern:
    receive event (from=x2)
    save last_continuation_token = x2
    crash before applying event

If you process events in batches, save the from continuation token from the last successfully committed event in the batch.

On Restart

On restart, read the continuation token and pass it as the ?from= parameter:

GET /stores/{store_id}/indexes/{index_id}/expansions?authorization_model_id={authorization_model_id}&from=token_xyz

When the Stream Closes

After about 5 minutes of streaming, your consumer may receive a close event like this:

{"result":{"closed":{"reason":"STREAM_CLOSED_REASON_CONNECTION_LIFETIME_EXCEEDED"}}}

This is expected behavior, not an error. It means the current stream connection reached its maximum lifetime and should be replaced with a new connection.

When this happens:

1. Treat the close event as a normal reconnect signal.
2. Read the most recent durably saved from continuation token.
3. Open a new Read Expansions request using that token.
4. Continue processing events as normal.

on_closed(event):
    if event.reason == "STREAM_CLOSED_REASON_CONNECTION_LIFETIME_EXCEEDED":
        token = load_last_continuation_token()
        reconnect_with(token)

Do not clear local state, rebuild the index, or treat this as data loss. If you have saved the from token with the corresponding event update, the new connection continues forward from the right point. Duplicates only happen if the saved token is behind what the consumer already processed.

If reconnecting fails, retry with backoff. But the normal path should be: close event → reconnect with the saved from continuation token → continue streaming.

What Happens if You Lose the Continuation Token?

If you lose or do not have a continuation token, omit the ?from= parameter. The stream will replay all expansion events from the beginning of the index. This is safe because your consumer should be idempotent — replaying all events again will rebuild the same final state.

This is effectively a "full reindex" operation. For large indexes, it may take time, but the result will be correct.

Designing an Idempotent Consumer

Because delivery is at-least-once, your consumer must be prepared to handle duplicate events safely.

The Goal: replaying the same expansion event twice should produce the same result as applying it once.

Consumer Topologies

Two responsibilities have to land somewhere: whatever reads a stream must own a durable resume position for it, and whatever stores flattened permissions must write them idempotently. Your topology decides where.

Reading directly from FGA:

FGA Expansions stream
    │  (at-least-once — duplicates can happen when resuming from an older token)
    ▼
Downstream consumer         ← owns: 'from' continuation token + idempotent writes to the permissions table

Reading through an intermediary:

FGA Expansions stream
       │  (at-least-once — duplicates can happen when resuming from an older token)
       ▼
Intermediary consumer        ← owns: 'from' continuation token, saved only after the publish is confirmed
    │  (internal stream/broker — exposes its own offset or cursor)
       ▼
Downstream consumer(s)       ← owns: intermediary stream offset or cursor + idempotent writes to the permissions table

Duplicates can arise at either boundary, so each of these rules applies on its own:

• An intermediary must tolerate duplicate input, but it does not have to remove duplicates before publishing. It may forward them or deduplicate them using event.id.
• Every consumer that stores flattened permissions must apply events idempotently, whether or not an intermediary deduplicated them first.
• If you run multiple consumers directly against the Expansions stream, each one maintains its own from continuation token.
• Downstream consumers cannot resume from the FGA from token — it only resumes the intermediary's connection to FGA. The internal stream or broker must expose its own durable resume position, such as a Kafka offset, queue cursor, or sequence number. If the transport does not provide one, the system exposing the stream has to create one.
• A downstream consumer saves its position together with its state update when both can participate in one transaction. Otherwise, apply the state update before checkpointing and rely on idempotent writes if the event is redelivered.

Note: Always advance the from continuation token, even when the event is a duplicate and you choose not to republish it. Skipping the token update can cause the stream to replay a large range of events on the next restart.

Deduplication ID

Each expansion event carries a unique event.id that stays the same across replays.

• Use event.id to detect duplicate events.
• Do not use event.id as an order or sort key.
• Do not use event.id to identify a stored flattened permission.

Idempotent Database Writes

An INSERT event and a later DELETE event for the same permission are separate logical events and should not be assumed to have the same event.id.

Instead, identify a flattened permission by its natural tuple key:

(subject_type, subject_id, subject_relation, relation, object_type, object_id)

Do not include event.id, operation, or as_fresh_as in this key. Including them would prevent a DELETE from matching the row created by an earlier INSERT.

Then apply each operation against that key:

• EXPANSION_OPERATION_INSERT: UPSERT the permission. Repeating the INSERT overwrites the row with the same data.
• EXPANSION_OPERATION_DELETE: DELETE the matching permission. Repeating the DELETE affects zero rows and does not produce an error.

This makes writes to the local permissions index idempotent.

Database Pattern: UPSERT on the Natural Key

Define a permissions_index table with the permission fields as its primary key. Normalize a missing subject_relation to an empty string so direct subjects can participate in the key consistently:

CREATE TABLE permissions_index (
    subject_type      TEXT      NOT NULL,   -- e.g., 'user'
    subject_id        TEXT      NOT NULL,   -- e.g., 'alice'
    subject_relation  TEXT      NOT NULL DEFAULT '', -- e.g., 'member' (empty for direct users)
    relation          TEXT      NOT NULL,   -- e.g., 'can_view'
    object_type       TEXT      NOT NULL,   -- e.g., 'document'
    object_id         TEXT      NOT NULL,   -- e.g., '3-1'
    updated_at        TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (subject_type, subject_id, subject_relation, relation, object_type, object_id)
);

In-Memory Pattern: Map Keyed by Natural Key

If you are building an in-memory permissions index (for a caching layer, a real-time search proxy, etc.), use the same natural key for the map:

// Pseudocode
permissions = Map<PermissionKey, Permission>

on_expansion_event(event):
    key = PermissionKey(
        subject_type = event.subject_type,
        subject_id = event.subject_id,
        subject_relation = event.subject_relation or "",
        relation = event.relation,
        object_type = event.object_type,
        object_id = event.object_id
    )

    if event.operation == "EXPANSION_OPERATION_INSERT":
        permissions[key] = Permission(
            subject_type = event.subject_type,
            subject_id = event.subject_id,
            subject_relation = event.subject_relation,
            relation = event.relation,
            object_type = event.object_type,
            object_id = event.object_id
        )
    else:
        permissions.delete(key)

Assigning the same permission key overwrites the existing entry with the same data. Deleting a key that isn't present is a no-op.

Ordering Expansion Events

Sort expansion events by event.as_fresh_as. When the index processes a batch of tuples together, every expansion event produced from that batch carries the same freshness timestamp. Those events are concurrent: they represent the same computed state of the index at the same point in time, so any order among them is equally correct. Events produced from tuples processed before or after that batch have a different timestamp.

If you need a total display or processing order within a shared timestamp batch, assign a local monotonic timestamp when you receive each event. That value is only useful for local ordering; it has no authorization or deduplication meaning.

// JavaScript/TypeScript example
let lastLocalTimestamp = 0;

function getLocalMonotonicTimestamp() {
  const now = Date.now();
  const timestamp = now > lastLocalTimestamp ? now : lastLocalTimestamp + 1;
  lastLocalTimestamp = timestamp;
  return timestamp;
}

function processExpansionEvent(event) {
  const parsedEvent = {
    id: event.id,
    operation: event.operation === "EXPANSION_OPERATION_DELETE" ? "remove" : "add",
    user: `${event.subject_type}:${event.subject_id}`,
    relation: event.relation,
    object: `${event.object_type}:${event.object_id}`,
    as_fresh_as: event.as_fresh_as,
    local_monotonic_timestamp: getLocalMonotonicTimestamp(),
  };

  // Apply to your index...
}

---

2. Leveraging Freshness for Authorization Decisions

A permissions index is eventually consistent with FGA. After a tuple is written to FGA, there is a brief period (typically seconds) where your colocated permissions index has not yet received the corresponding expansion events.

This means FGA is always the source of truth. Your colocated permissions index is an optimized read replica for search and filtering use cases. You can calculate the freshness of your index at any point in time.

What is Freshness?

Freshness indicates how up-to-date your colocated permissions index is relative to FGA. It answers the question: "How fresh is my index right now?"

Freshness Formula

Each expansion event includes two timestamps:

• event.as_fresh_as indicates how up-to-date the index is. Use it for calculating index freshness.
• event.tuple_written_at is deprecated. Do not use it in new integrations or rely on it for auditing, ordering, deduplication, permission identity, or freshness calculations.

For example:

{
    "result": { 
        "event": {
            "from":"FmhrVnpxNHBsUWgyNHc3NXh0R2NIX2eEAb8whDm7Dxliq5tblJNxfn9cud3OFdTAxweqAUtHJj2udjfufHZY4SkvkzJS7kEydaOxkDfhcmd1p0P4-AlHfMksAGaGjJvrxPzV8fLPgJmzrftqn3twPKlZKjDhhQyEg230CO2CR0l_947bOngyJqe1ZLUh3XErvOwHp81GlONH1eAFQg==",
            "id":"018f0c21-a1b9-7e38-9a45-0f2bc4d5e6f7",
            "subject_type":"team",
            "subject_id":"engineering",
            "subject_relation":"member",
            "object_type":"document",
            "object_id":"3-1",
            "relation":"can_view",
            "operation":"EXPANSION_OPERATION_INSERT",
            "tuple_written_at":"2026-04-02T08:34:51Z",
            "as_fresh_as":"2026-04-03T19:57:51Z"
        }
    }
}

The stream also emits a freshness event carrying freshness.as_fresh_as timestamp. This event appears every 2 seconds of index inactivity, meaning the index has not received any new tuple writes, and thus has not produced any new expansion events up to the timestamp indicated by freshness.as_fresh_as.

For example:

{
    "result": {
        "freshness": {
            "as_fresh_as":"2026-04-15T14:12:09.029627Z"
        }
    }
}

To compute freshness, use the latest as_fresh_as timestamp from either event or freshness event type:

freshness = now() - max(event.as_fresh_as, freshness.as_fresh_as)

Your consumer should track both as_fresh_as timestamps and retain the latest non-null value from either event type. When an index is first created, event.as_fresh_as can be null; ignore null values until a non-null timestamp arrives.

Monitoring Freshness

Treat freshness as a consumer-side operational metric: apply the formula above when the metric is read or on a regular schedule, not only when an event arrives. If you recompute it only on arrival, a disconnected stream leaves the stored value frozen and a stale index looks fresh.

Evaluating Freshness on Every Request

If freshness decides whether the permissions index is usable, then every request that relies on the index should check the latest freshness state before trusting index results. Your consumer maintains that state continuously, and each request compares it to an application threshold, such as "use the index only if freshness is under 15 seconds."

With this model, the process typically looks like this:

1. Define MAX_ACCEPTABLE_FRESHNESS for your application, e.g. 300 seconds (5 minutes).
2. Receive either an event.as_fresh_as or a freshness.as_fresh_as timestamp.
3. Update permissions_index_freshness_state. This can live in-memory, in a shared key-value store, or in a single database table row.
   • latest_event_as_fresh_as=event.as_fresh_as
   • latest_freshness_as_fresh_as=freshness.as_fresh_as
   • updated_at=now()
4. On each request, read that state and apply the freshness formula.
5. Compare the result to MAX_ACCEPTABLE_FRESHNESS.
   • If freshness is acceptable, use the permissions index.
   • If freshness is above the threshold or the freshness state is unavailable, fall back to a higher-consistency path such as FGA Check/ListObjects, or fail closed.

Heartbeats vs Freshness

Under normal conditions your consumer receives expansion or freshness events and should not expect heartbeats. Heartbeats are only emitted while the server is mid-computation on an expansion taking longer than 30 seconds where it cannot report a freshness timestamp yet and has no new expansion events to send, so it sends a heartbeat instead.

A heartbeat confirms the connection is alive. But because heartbeats are rare by design, their absence is only concerning if you are not receiving expansion or freshness events, usually indicating a dead connection stream.

{
    "result": {
        "heartbeat": {}
    }
}

When to Use the Colocated Permissions Index vs. FGA

Use Case	Recommended Approach	Why
Search results filtering	Permissions Index	Filtering thousands of results requires low-latency lookups. The index is optimized for this. A brief delay (seconds) before a newly-permitted document appears in search is acceptable.
Paginated list views	Permissions Index	Same as search: you need to JOIN or filter large sets efficiently.
Analytics / reporting	Permissions Index	Batch queries over permissions data for BI dashboards benefit from colocated storage.
Single-resource access gate	FGA Check API	When a user clicks on a specific document, verify access against FGA directly. This gives you real-time correctness at the moment of access.
Sensitive operations (delete, share, export)	FGA Check API	Security-critical actions should always be authorized against the source of truth.

General rule: Use the Permissions Index for read-heavy, list-based operations where eventual consistency is acceptable within your freshness threshold. Use the FGA Check API for write/action checks where real-time correctness is required.

Combining the Permissions Index with FGA Check

A common pattern is to use the Permissions Index for initial filtering and FGA Check for confirmation:

-- Step 1: Query using the Permissions Index and business data together
results = SELECT d.* FROM documents d
                    JOIN permissions_index p ON d.id = p.object_id
                    WHERE p.subject_id = 'alice'
                        AND p.relation = 'can_view'
                        AND p.object_type = 'document'
                        AND d.title LIKE '%quarterly%'
          ORDER BY d.updated_at DESC
          LIMIT 25

// Step 2: When user clicks a result, verify with FGA Check
on_document_open(doc_id):
    allowed = fga.check(user="user:alice", relation="can_view", object="document:{doc_id}")
    if not allowed:
        show_error("Access has been revoked. Please refresh your search.")

This gives you the best of both worlds: the performance of the colocated index for search, and the real-time correctness of FGA Check for access checks.

Fallback Strategy When Freshness is Too High

If freshness exceeds your acceptable MAX_ACCEPTABLE_FRESHNESS threshold — whether due to a consumer disconnection, a large backpressure of expansions, or infrastructure issues — you need a fallback plan to ensure authorization decisions remain correct.

Before falling back, monitor three signals:

1. Event freshness: If incoming expansion events have an event.as_fresh_as that is significantly behind, the index is still catching up to the latest changes. This typically occurs after index creation or high write activity.
2. Index freshness: If the latest freshness.as_fresh_as shows high latency (e.g., > 15 seconds), the index is taking a long time to process the latest tuple writes.
3. Connection health: Freshness events arrive every 2 seconds while the stream is idle, so a gap much longer than that means either the index is processing a large batch of expansions or the consumer could be disconnected. Monitor heartbeats and connection state to detect this.

In any scenario, if freshness exceeds your acceptable MAX_ACCEPTABLE_FRESHNESS threshold, trigger your fallback plan.

Your fallback plan could be:

• Show a staleness warning to the user (e.g., "Results may not reflect recent permission changes").
• Cancel the request and surface an error if freshness exceeds a critical threshold.
• Route authorization decisions through FGA Check or ListObjects (if available).
• Run FGA BatchCheck on the top N results only, as a compromise between correctness and performance.
• Route to a backup internal authorization system with less granularity.
• Fail closed: deny access until the index is fresh again.
• Or something else that fits your application's risk tolerance and user experience.

Returning to Normal

Freshness returns to acceptable levels once the consumer catches up on historical events, the index finishes computing a large batch of expansions, or a dropped connection is re-established. At that point it is safe to resume using the colocated Permissions Index for authorization decisions.

Handling the "New Enemy" Problem

The "new enemy" problem occurs when access is revoked but the colocated index hasn't processed the revocation yet. During this window, the index incorrectly says the user still has access.

Mitigation strategies:

1. Use FGA Check for sensitive operations: Always gate sensitive actions through FGA Check. The Check API reflects revocations in near real-time.
2. Monitor freshness and alert on lag: If your index falls behind by more than your SLA (e.g., 15 seconds), alert your operations team and consider triggering the fallback strategy.
3. Accept the tradeoff for search: For search result filtering, showing non-sensitive details like Title, Date, or Owner for a recently-revoked document in results for a few extra seconds is a reasonable tradeoff. When the user clicks the document, the FGA Check will deny access.

---

3. Migrating Between Authorization Models

When a new model is created in FGA, it needs to be validated for compatibility with existing permissions indexes.

If a model is compatible with existing indexes, Auth0 FGA automatically assigns the new model to the index. Incompatible models will require a new index.

Compatible Model Changes

A model is compatible when its Indexable Path is unchanged. The existing index and local permissions_index table remain valid, no re-index is needed, and you can continue processing events normally after the model is assigned.

Requests to /expansions endpoint with a compatible model will succeed.

Incompatible Model Changes

A model is incompatible when its Indexable Path changes. It is not automatically assigned to the existing index, so creating a new index is necessary to migrate to it.

Typical examples include:

• Removing or renaming an indexed relation
• Changing the chain of relations that determines the indexed permission
• Changing the index definition so the current flattened rows no longer represent the indexed permissions correctly

Requests to /expansions with an unassigned or incompatible model ID fail.

Automatic Model Assignment

When you create a new index, you must select a model to index. But Auth0 FGA also evaluates the store's model history from newest to oldest and assigns each compatible model. It also will evaluate any new models as they are created. This background job can take several hours in a store with thousands of models. Until a model is assigned, you cannot use it with that index.

What If a permissions_index Table Already Exists?

If you already have a populated permissions_index table, the safest approach depends on whether the model change is compatible.

Option 1: Reuse the Existing Table for Compatible Changes

If the model is compatible, keep the existing table and keep the existing data. After Auth0 FGA automatically assigns the new model ID to the existing index, continue calling the /expansions endpoint but with the new model ID and streaming expansions into the same table.

This is the ideal path because:

• The stored expansions remain valid
• No table rewrite is required
• No application cutover is required

Option 2: Shadow Table for Incompatible Changes

If the model is incompatible, the best practice is usually to build a new table for the new index, for example:

• permissions_index_{old_index_id} for the old index
• permissions_index_{new_index_id} for the new index

or equivalent names based on index purpose.

This lets you:

• Keep the old application path running
• Build the new index in parallel
• Validate the new index before cutover
• Switch reads atomically later

This is usually safer than mutating one shared table in place while a re-index is underway.

Option 3: Shared Table Versioned by index_id

If you prefer a single physical table, version rows by index_id and include index_id in the primary key and query filters.

For example:

CREATE TABLE permissions_index (
    index_id           TEXT      NOT NULL,
    subject_type       TEXT      NOT NULL,
    subject_id         TEXT      NOT NULL,
    subject_relation   TEXT      NOT NULL DEFAULT '',
    relation           TEXT      NOT NULL,
    object_type        TEXT      NOT NULL,
    object_id          TEXT      NOT NULL,
    updated_at         TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (index_id, subject_type, subject_id, subject_relation, relation, object_type, object_id)
);

This can work well if your platform already expects multiple active index versions. The tradeoff is that every query and every maintenance operation must now be index-aware. For most application cutovers, separate shadow tables are easier to reason about.

Recommended Cutover Process

For incompatible changes, treat cutover as a controlled migration.

1. Create the new authorization model
2. Confirm the Indexable Path changed
3. Create a new index
4. Create a new destination table or table namespace for that index
5. Start a new consumer for the new index
6. Wait for the re-index to complete and for freshness to reach your acceptable threshold
7. Validate that application queries against the new table return the expected results
8. Switch application reads from the old index/table to the new index/table
9. Monitor for a stabilization period
10. Retire the old consumer, old index, and old table when safe

Application Cutover Patterns

There are two common cutover patterns:

• Configuration cutover: the application reads from whatever table or index ID is marked active in configuration. Once validation is complete, update that configuration in one step.
• View or alias cutover: the application always reads from a stable database view or alias, and cutover updates that view to point at the new underlying table.

Both patterns avoid changing application query logic during the migration.

Practical Rule

Use this rule of thumb:

• If the Indexable Path is unchanged, wait for Auth0 FGA to automatically assign the new model to the existing index, then call the /expansions endpoint with the new model id, and keep using the current table,
• If the Indexable Path changed, create a new index, build it separately, and cut over application reads only after validation.

Passing the wrong model to /expansions should fail, and that is on purpose. It prevents you from accidentally using expansions that would return incorrect access.

---

4. Querying Permissions inside Managed Data Platforms

Overview

A common integration pattern is to maintain a colocated permissions index table in a data warehouse (like Snowflake, Databricks, BigQuery) so that analytics queries, dashboards, reporting, or even AI agents can respect access control. The idea is simple: your consumer reads the Read Expansions stream, inserts/deletes rows in a Snowflake table, and your analysts can JOIN that table with any other Snowflake table to filter results by permission.

Joining Permissions with Business Data

This is where the colocated Permissions Index is valuable. Instead of calling the FGA Check API for every row in a query (infeasible or impractical at scale), you JOIN against the colocated permissions table.

Basic: Filter Documents by User Access

-- "Search for documents matching 'quarterly report' that alice can view"
SELECT d.id, d.title, d.author, d.updated_at
FROM documents d
JOIN permissions_index p
    ON  p.object_type  = 'document'
    AND p.object_id    = d.id
WHERE p.subject_type = 'user'
  AND p.subject_id   = 'alice'
  AND p.relation     = 'can_view'
  AND d.title ILIKE '%quarterly report%'
ORDER BY d.updated_at DESC
LIMIT 25;

This replaces what would otherwise require thousands of FGA Check calls (one per document) with a single SQL JOIN.

Analytics: Count Documents per User

-- "How many documents can each user view?" (for capacity planning / audit)
SELECT
    p.subject_id   AS user_id,
    COUNT(*)        AS accessible_document_count
FROM permissions_index p
WHERE p.subject_type = 'user'
  AND p.relation     = 'can_view'
  AND p.object_type  = 'document'
GROUP BY p.subject_id
ORDER BY accessible_document_count DESC;

Access Audit: Who Can See a Specific Document?

-- "Who has can_view access to document 'budget-2026'?"
SELECT
    p.subject_type,
    p.subject_id
FROM permissions_index p
WHERE p.object_type = 'document'
  AND p.object_id   = 'budget-2026'
  AND p.relation    = 'can_view'
ORDER BY p.subject_type, p.subject_id;

Cross-Table Analytics: Permissions + Activity

-- "Which documents that alice can view have not been accessed in 90 days?"
-- Useful for data governance and cleanup
SELECT d.id, d.title, d.last_accessed_at
FROM documents d
JOIN permissions_index p
    ON  p.object_type  = 'document'
    AND p.object_id    = d.id
WHERE p.subject_type = 'user'
  AND p.subject_id   = 'alice'
  AND p.relation     = 'can_view'
  AND d.last_accessed_at < DATEADD('day', -90, CURRENT_TIMESTAMP())
ORDER BY d.last_accessed_at ASC;

Row-Level Security with the Colocated Permissions Index

If your Snowflake environment supports row access policies, you can enforce permissions transparently so that analysts never see unauthorized data — without modifying their queries.

-- Create a row access policy that filters based on the permissions index
CREATE OR REPLACE ROW ACCESS POLICY document_access_policy
AS (object_id VARCHAR) RETURNS BOOLEAN ->
    EXISTS (
        SELECT 1 FROM permissions_index p
        WHERE p.object_type  = 'document'
          AND p.object_id    = object_id
          AND p.subject_type = 'user'
          AND p.subject_id   = CURRENT_USER()
          AND p.relation     = 'can_view'
    );

-- Apply the policy to the documents table
ALTER TABLE documents ADD ROW ACCESS POLICY document_access_policy ON (id);

Now any query against documents is automatically filtered by the user's permissions:

-- This query automatically respects access control via the row access policy
SELECT * FROM documents WHERE title ILIKE '%budget%';
-- Only returns documents the current Snowflake user has can_view access to

Note: This approach requires that Snowflake user names match the subject_id values in the permissions index. You may need a mapping table if your FGA user IDs differ from Snowflake user names.

---

FAQ

Can I run multiple consumers for the same index?

Yes. Each consumer tracks its own continuation token independently. You can have one consumer writing to PostgreSQL and another writing to Snowflake, each progressing at their own rate.

What happens if my consumer goes down for an extended period?

When the consumer reconnects with its last saved from continuation token, it resumes from that token and catches up to the current state. If the token is very old, catching up may take time depending on the volume of events that occurred during the outage.

If you have lost or do not have a continuation token, omit the ?from= parameter to replay from the beginning. Because the consumer is idempotent, this will rebuild the correct state.

Should I store the full expansion event or just the key fields?

At minimum, store the natural key fields (subject_type, subject_id, subject_relation, relation, object_type, object_id). These are sufficient for JOIN queries. Maintain the latest event.as_fresh_as and freshness.as_fresh_as separately, as consumer freshness state rather than per-row columns.

If you need a full event audit trail, store raw events in a separate permissions_index_events table.

What indexes should I create on the permissions table?

At minimum:

-- For "what can this user see?" queries
CREATE INDEX idx_perm_subject ON permissions_index
    (subject_type, subject_id, subject_relation, relation);

-- For "who can see this object?" queries
CREATE INDEX idx_perm_object ON permissions_index
    (object_type, object_id, relation);

The right indexes depend on your query patterns. If you primarily filter by user, prioritize the subject index. If you primarily audit by object, prioritize the object index.

Relation vs. Subject Relation

An expansion event has two relation fields that serve different purposes:

• relation — the permission on the object. It answers: what can the subject do to this object?
• subject_relation — the relation on the subject. It answers: through which userset was this subject resolved?

The distinction arises because FGA allows you to assign usersets — not just individual users — to objects. Consider this model:

type user

type group
  relations
    define member: [user]

type document
  relations
    define can_view: [user, group#member]

The type restriction [user, group#member] means can_view can be assigned to either a direct user or to the member userset of a group. When you write a tuple that assigns a userset:

user:   group:engineering#member
relation: can_view
object:   document:report

FGA resolves every user who is a member of group:engineering and produces flattened expansion events. If user:alice and user:bob are members, the expansions are:

subject_type	subject_id	subject_relation	relation	object_type	object_id
user	alice	member	can_view	document	report
user	bob	member	can_view	document	report

Notice:

• relation is can_view — the permission granted on the document.
• subject_relation is member — the relation on group that was used to resolve the userset.

If instead a direct tuple had been written (user:alice can_view document:report), the expansion would have no subject_relation because no userset was involved.