> ## Documentation Index > Fetch the complete documentation index at: https://docs.fga.dev/llms.txt > Use this file to discover all available pages before exploring further. # Best Practices — Colocated Permissions Index > Learn the best practices for using the Colocated Permissions Index effectively. This guide covers best practices for integrating with the FGA Colocated Permissions Index (Search With Permissions). It is organized into five sections: 0. [Security](#0-security) 1. [Idempotency and Deduplication Patterns](#1-idempotency-and-deduplication-patterns) 2. [Leveraging Freshness for Authorization Decisions](#2-leveraging-freshness-for-authorization-decisions) 3. [Migrating Between Authorization Models](#3-migrating-between-authorization-models) 4. [Querying Permissions inside Managed Data Platforms](#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](https://docs.fga.dev/api/service/permissions-index/read-expansions.md) 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. ```text theme={null} 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: ```json theme={null} {"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. ```text theme={null} 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](#designing-an-idempotent-consumer) — 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: ```sql theme={null} 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 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 theme={null} // 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](https://docs.fga.dev/permissions-index/terminology.md#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: ```json theme={null} { "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: ```json theme={null} { "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. ```json theme={null} { "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: ```text theme={null} -- 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 ``` ```text theme={null} // 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](https://docs.fga.dev/permissions-index/terminology.md#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](https://docs.fga.dev/permissions-index/terminology.md#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: ```sql theme={null} 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](https://docs.fga.dev/permissions-index/terminology.md#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](https://docs.fga.dev/permissions-index/terminology.md#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](https://docs.fga.dev/permissions-index/terminology.md#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 ```sql theme={null} -- "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 ```sql theme={null} -- "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? ```sql theme={null} -- "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 ```sql theme={null} -- "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. ```sql theme={null} -- 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: ```sql theme={null} -- 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: ```sql theme={null} -- 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: FGA model · DSL 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.