Managing entitlements based on subscriptions in your Next.js Supabase app

Learn how to effectively manage access and entitlements based on subscriptions in your Next.js Supabase app.

As your SaaS grows, managing user entitlements becomes increasingly complex. In this tutorial, we'll explore how to build a flexible and performant entitlements system using Makerkit, Supabase, and PostgreSQL.

This approach leverages PostgreSQL functions and Supabase RPCs to create a system that's both powerful and adaptable to various billing scenarios.

The Challenge of Managing User Entitlements

Managing user entitlements in a SaaS application involves several challenges:

Flexibility: The system should handle various entitlement types (flat access, usage quotas, etc.).
Performance: Checking entitlements should be fast and efficient.
Consistency: Entitlement logic should be consistent across the application.
Security: Entitlement checks should be enforceable at the database level.

Why does Makerkit not have a built-in entitlements system?

Makerkit is designed to be flexible and extensible, allowing you to build custom features on top of the core functionality. Entitlements are a complex topic that can vary significantly between applications, so we've chosen to provide a foundation for building entitlements systems rather than a one-size-fits-all solution.

This tutorial will give you an implementation that you can customize to fit your specific requirements. We'll cover the core concepts and provide a working example that you can adapt to your needs.

Leveraging Supabase and PostgreSQL

We'll create a system that uses PostgreSQL functions to handle entitlement logic. These functions will be callable via Supabase RPCs from our application code and usable in Row Level Security (RLS) policies.

Step 1: Creating PostgreSQL Tables

We'll start by creating two main tables in our database:

-- Table to store plan entitlements
CREATE TABLE public.plan_entitlements (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  variant_id VARCHAR(255) NOT NULL,
  feature VARCHAR(255) NOT NULL,
  entitlement JSONB NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE (variant_id, feature)
);
revoke all on public.plan_entitlements from public;
alter table public.plan_entitlements enable row level security;
grant select on public.plan_entitlements to authenticated;
create policy select_plan_entitlements
    on public.plan_entitlements
    for select
    to authenticated
    using (true);

Next, we'll create a table to store feature usage data:

-- Table to store feature usage
CREATE TABLE public.feature_usage (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id UUID NOT NULL REFERENCES public.accounts(id) ON DELETE CASCADE,
  feature VARCHAR(255) NOT NULL,
  usage JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT now(),
  updated_at TIMESTAMPTZ DEFAULT now(),
  UNIQUE (account_id, feature)
);
revoke all on public.feature_usage from public;
alter table public.feature_usage enable row level security;
create policy select_feature_usage
    on public.feature_usage
    for select
    to authenticated
    using (
        public.has_role_on_account(account_id) or (select auth.uid()) = account_id
    );
-- Index for faster lookups
CREATE INDEX idx_feature_usage_account_id ON public.feature_usage(account_id, feature);

These tables will store entitlements for each plan variant and track feature usage for each account. The plan_entitlements table will define the entitlements for each feature, while the feature_usage table will track usage data.

Creating a row when an account is created

To ensure that each account has a corresponding row in the feature_usage table when it's created, you can use a trigger:

CREATE OR REPLACE FUNCTION public.create_feature_usage_row()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO public.feature_usage (account_id, feature)
  VALUES (NEW.id, '');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER create_feature_usage_row
AFTER INSERT ON public.accounts
FOR EACH ROW
EXECUTE FUNCTION public.create_feature_usage_row();

In this way, the trigger will automatically create a row in the feature_usage table when a new account is created.

Explanation of the Tables

Let's break down the tables we've created:

plan_entitlements: This table stores entitlements for each feature in each plan variant. The entitlement column contains a JSON object that defines the entitlement type and any additional parameters (e.g., usage limits).
feature_usage: This table tracks feature usage for each account. The usage column contains a JSON object that stores usage data for each feature. For quota-based entitlements, this object will store the usage count. For flat entitlements, it can be an empty object, since you may only need to check for the existence of the entitlement.

Step 2: Creating PostgreSQL Functions

Next, we'll create PostgreSQL functions to handle entitlement checks and feature usage:

-- Function to check if an account can use a feature
CREATE OR REPLACE FUNCTION public.can_use_feature(p_account_id UUID, p_feature VARCHAR(255))
RETURNS BOOLEAN AS $$
DECLARE
  v_subscription_id UUID;
  v_variant_id TEXT;
  v_entitlement JSONB;
BEGIN
  -- Get the subscription ID and the variant ID for the account's subscription
  SELECT sub.id, sub_item.variant_id INTO v_subscription_id, v_variant_id
  FROM public.subscriptions sub
  JOIN public.subscription_items sub_item ON sub.id = sub_item.subscription_id
  WHERE account_id = p_account_id AND active = true
  LIMIT 1;
  IF v_subscription_id IS NULL THEN
    RETURN FALSE;
  END IF;
  -- Get the entitlement for the feature
  SELECT entitlement INTO v_entitlement
  FROM public.plan_entitlements
  WHERE variant_id = v_variant_id AND feature = p_feature;
  IF v_entitlement IS NULL THEN
    RETURN FALSE;
  END IF;
  -- For flat entitlements, just check if it exists
  IF v_entitlement->>'type' = 'flat' THEN
    RETURN TRUE;
  END IF;
  -- For quota-based entitlements, check against usage
  IF v_entitlement->>'type' = 'quota' THEN
    DECLARE
      v_usage JSONB;
      v_limit INTEGER;
    BEGIN
      SELECT usage INTO v_usage
      FROM public.feature_usage
      WHERE account_id = p_account_id AND feature = p_feature;
      v_limit := (v_entitlement->>'limit')::INTEGER;
      IF v_usage IS NULL OR (v_usage->>'count')::INTEGER < v_limit THEN
        RETURN TRUE;
      ELSE
        RETURN FALSE;
      END IF;
    END;
  END IF;
  -- Add more entitlement types here as needed
  RETURN FALSE;
END;
$$ LANGUAGE plpgsql;
-- Function to get entitlement details
CREATE OR REPLACE FUNCTION public.get_entitlement(p_account_id UUID, p_feature VARCHAR(255))
RETURNS JSONB AS $$
DECLARE
  v_subscription_id UUID;
  v_variant_id TEXT;
  v_entitlement JSONB;
  v_usage JSONB;
BEGIN
  -- Get the subscription for the account
  SELECT id, variant_id INTO v_subscription_id, v_variant_id
  FROM public.subscriptions
  WHERE account_id = p_account_id AND active = true
  LIMIT 1;
  IF v_subscription_id IS NULL THEN
    RETURN NULL;
  END IF;
  -- Get the entitlement for the feature
  SELECT entitlement INTO v_entitlement
  FROM public.plan_entitlements
  WHERE variant_id = v_variant_id AND feature = p_feature;
  IF v_entitlement IS NULL THEN
    RETURN NULL;
  END IF;
  -- Get current usage
  SELECT usage INTO v_usage
  FROM public.feature_usage
  WHERE account_id = p_account_id AND feature = p_feature;
  -- Combine entitlement and usage data
  RETURN jsonb_build_object(
    'entitlement', v_entitlement,
    'usage', COALESCE(v_usage, '{}'::JSONB)
  );
END;
$$ LANGUAGE plpgsql;
-- Function to update feature usage
CREATE OR REPLACE FUNCTION public.update_feature_usage(p_account_id UUID, p_feature VARCHAR(255), p_usage JSONB)
RETURNS VOID AS $$
BEGIN
  INSERT INTO public.feature_usage (account_id, feature, usage)
  VALUES (p_account_id, p_feature, p_usage)
  ON CONFLICT (account_id, feature)
  DO UPDATE SET usage = feature_usage.usage || p_usage, updated_at = NOW();
END;
$$ LANGUAGE plpgsql;
-- Grant execute permissions
GRANT EXECUTE ON FUNCTION public.can_use_feature(UUID, VARCHAR) TO authenticated;
GRANT EXECUTE ON FUNCTION public.get_entitlement(UUID, VARCHAR) TO authenticated;
GRANT EXECUTE ON FUNCTION public.update_feature_usage(UUID, VARCHAR, JSONB) TO authenticated;

These functions handle the core logic of our entitlements system. They check if a feature is available, get entitlement details, and update feature usage.

Let's see some examples of how these functions work:

`can_use_feature` - Check if an Account Can Use a Feature

This function takes an account ID and a feature name as input and returns a boolean value indicating whether the account can use the feature. It checks the account's subscription, the entitlement for the feature in the plan variant, and the feature usage to determine if the account has access to the feature.

SELECT can_use_feature('account_id', 'feature_name');

`get_entitlement` - Get Entitlement Details for a Feature

This function takes an account ID and a feature name as input and returns a JSON object containing the entitlement details and the current usage data for the feature. It combines the entitlement information from the plan variant with the usage data to provide a comprehensive view of the feature's status for the account.

SELECT get_entitlement('account_id', 'feature_name');

`update_feature_usage` - Update Feature Usage Data

This function takes an account ID, a feature name, and a JSON object representing the usage data as input. It inserts or updates the feature usage data for the account and feature, allowing you to track and manage usage limits for quota-based entitlements.

SELECT update_feature_usage('account_id', 'feature_name', '{"count": 1}');

These functions provide the foundation for building a flexible and scalable entitlements system in your Supabase project. You can extend and customize them to suit your specific requirements and use cases.

NB: these functions can be called using the Supabase Client using the rpc method. For example:

const { data, error } = await supabase.rpc('can_use_feature', {
  p_account_id: 'account_id',
  p_feature: 'feature_name'
});

Knowing this, we can now create a TypeScript service that interacts with these functions to provide a high-level API for managing entitlements in our application. This is useful for encapsulating the logic and making it easier to use in our codebase, for example, if you want to restrict access to a page or feature based on entitlements.

Step 3: Creating the EntitlementsService

Now, let's create a TypeScript service that uses these functions via Supabase RPCs:

apps/web/lib/server/entitlements.service.ts

import type { SupabaseClient } from '@supabase/supabase-js';
import type { Database } from '~/lib/database.types';
export function createEntitlementsService(
 client: SupabaseClient<Database>,
 accountId: string
) {
 return new EntitlementsService(client, accountId);
}
class EntitlementsService {
 constructor(
   private readonly client: SupabaseClient<Database>,
   private readonly accountId: string
 ) {}
 async canUseFeature(feature: string) {
   const { data, error } = await this.client.rpc('can_use_feature', {
     p_account_id: this.accountId,
     p_feature: feature
   });
   if (error) throw error;
   return data;
 }
 async getEntitlement(feature: string) {
   const { data, error } = await this.client.rpc('get_entitlement', {
     p_account_id: this.accountId,
     p_feature: feature
   });
   if (error) throw error;
   return data;
 }
 async updateFeatureUsage(feature: string, usage: Record<string, unknown>) {
   const { error } = await this.client.rpc('update_feature_usage', {
     p_account_id: this.accountId,
     p_feature: feature,
     p_usage: usage
   });
   if (error) throw error;
 }
}

This service provides a clean interface for checking entitlements in your application code.

Step 4: Using the EntitlementsService

Here's how you might use the EntitlementsService in your application:

export async function handleApiRequest(accountId: string, endpoint: string) {
  const client = getSupabaseServerClient();
  const entitlementsService = createEntitlementsService(client, accountId);
  const canUseAPI = await entitlementsService.canUseFeature('api_access');
  if (canUseAPI) {
    const entitlement = await entitlementsService.getEntitlement('api_calls');
    if (entitlement.entitlement.type === 'flat') {
      // Process API request for flat entitlement
      return processApiRequest(endpoint);
    } else if (entitlement.entitlement.type === 'quota') {
      const currentUsage = entitlement.usage.count || 0;
      const limit = entitlement.entitlement.limit;
      if (currentUsage < limit) {
        // Update usage
        const count = currentUsage + 1;
        await entitlementsService.updateFeatureUsage('api_calls', { count });
        // Process API request
        return processApiRequest(endpoint);
      } else {
        throw new Error('API call quota exceeded');
      }
    }
    // Handle other entitlement types as needed
  }
  throw new Error('No access to API');
}

Step 5: Using in Row Level Security

One of the major benefits of this approach is that we can use these functions directly in our RLS policies:

CREATE POLICY "Users can only access features they're entitled to" ON public.some_table
  FOR SELECT
  TO authenticated
  USING (
    public.can_use_feature(auth.uid(), 'some_feature')
  );

This ensures that our entitlement checks are enforced at the database level, providing an additional layer of security.

Handling credits fill up

Generally speaking, you want to fill up credits when the user pays for them. This is usualy done using the invoice paid webhook from a payment gateway.

Makerkit makes it very easy to handle this sort of situations, as it listens to the invoice.paid event. To handle the event, we can update the webhook handler to fill up the credits for the user.

apps/web/app/api/billing/webhook/route.ts

// ... existing imports removed for brevity
import { createEntitlementsService } from '~/lib/server/entitlements.service';
export const POST = enhanceRouteHandler(
 async ({ request }) => {
   const provider = billingConfig.provider;
   const logger = await getLogger();
   const ctx = {
     name: 'billing.webhook',
     provider,
   };
   logger.info(ctx, `Received billing webhook. Processing...`);
   const supabaseClientProvider = () => getSupabaseServerAdminClient();
   const service = await getBillingEventHandlerService(
     supabaseClientProvider,
     provider,
     billingConfig,
   );
   try {
     await service.handleWebhookEvent(request, {
       onInvoicePaid: async (data) => {
         // update AI_CALLS entitlement when an invoice gets paid
         await handleInvoicePaidForAPICalls(data.account_id);
       }
     });
     logger.info(ctx, `Successfully processed billing webhook`);
     return new Response('OK', { status: 200 });
   } catch (error) {
     logger.error({ ...ctx, error }, `Failed to process billing webhook`);
     return new Response('Failed to process billing webhook', {
       status: 500,
     });
   }
 },
 {
   auth: false,
 },
);
async function handleInvoicePaidForAPICalls(accountId: string) {
 const logger = await getLogger();
 // create entitlements service for the account
 const entitlementsService = await createEntitlementsService(
   supabaseClientProvider(),
   accountId,
 );
 const entitlement = await entitlementsService.getEntitlement(
   'api_calls',
 );
 // if no entitlement found, log an error and return
 if (!entitlement) {
   logger.error(ctx, `No entitlement found for "api_calls". You may need to create one in the DB`);
   return;
 }
 logger.info(ctx, `Updating usage for api_calls`);
 // fill up the credits for the account based on the entitlement
 // of their existing plan
 const count = entitlement?.data?.limit ?? 0;
 if (!count) {
   logger.error(ctx, `No limit found for "api_calls" entitlement. You may need to set a limit in the DB`);
   return;
 }
 const { error } = await entitlementsService.updateFeatureUsage('api_calls', { count });
 if (error) {
   logger.error(ctx, `Failed to update usage for "api_calls"`, error);
   return;
 }
 logger.info(ctx, `Successfully processed invoice paid event`);
}

In the code above, we listen to the invoice.paid event and fill up the credits for the user based on their entitlements. This is done by fetching the entitlement for the user and updating the usage for the api_calls feature.

This is just an example of how you can handle the invoice.paid event. You can customize this code to fit your specific use case.

Benefits of This Approach

This approach offers several benefits:

Flexibility: The system can handle various types of entitlements, from simple feature flags to complex usage-based limits.
Performance: By moving the entitlement logic to the database, we reduce the number of round trips between the application and the database.
Consistency: The same logic is used whether we're checking entitlements in our application code or in RLS policies.
Security: Entitlements can be enforced at the database level through RLS, providing robust security.
Maintainability: Centralizing the entitlement logic in the database makes it easier to maintain and update.

Customizing for Different Billing Scenarios

This system provides a solid foundation that can be customized for various billing scenarios - for example, tiered pricing, usage-based billing, or feature-based billing. You can extend the functions and services to handle these scenarios by adding additional logic and data structures.

Conclusion

Building a robust entitlements system is crucial for any SaaS application. By leveraging PostgreSQL functions and Supabase RPCs, we've created a system that's performant, consistent, flexible, and secure.

This approach provides a solid foundation that can grow with your application, handling increasingly complex entitlement scenarios as your product evolves. Remember, while this system provides a great starting point, you may need to adapt it to your specific requirements and billing scenarios.