Enforcing Business Logic in the Database: Moving Plan Limits from the Interface to PostgreSQL

In contemporary software development, implementing tier-based resource caps—such as restricting free-tier users to ten items—is standard practice. Historically, developers have relied on frontend validations, API route checks, or application-state management to enforce these constraints. However, relying on client-side logic or stateless middle tiers often leaves systems vulnerable to race conditions, multi-tab bypasses, and direct API manipulations. To address these vulnerabilities, software engineers are increasingly returning to database-level constraints, utilizing PostgreSQL triggers, row-level security, and advisory locks to make subscription limits absolute.

The Architectural Flaw of Client-Side Constraints

For years, the prevailing architectural pattern for enforcing usage caps has resided within the user interface or application routing layer. Developers typically implement UI interventions such as disabling interaction buttons once a threshold is reached, maintaining state counts in application stores, or inserting validation logic inside API route handlers.

While these methods provide a seamless user experience under optimal conditions, they function primarily as suggestions rather than hard limits. Modern web applications operate in complex, multi-threaded environments where users frequently open multiple browser tabs, maintain persistent sessions across devices, or interact directly with backend application programming interfaces using authentication tokens extracted via developer tools. Under these conditions, client-side guards easily fail, allowing operations like an eleventh resource creation to successfully write to the underlying database table.

Recognizing these architectural limitations, a growing cohort of backend and full-stack engineers has begun shifting validation logic directly down to the persistence layer. By establishing database-native constraints, developers ensure that business rules are enforced universally across all potential client interfaces, legacy scripts, and third-party integrations.

PostgreSQL Implementation and the Role of Triggers

Implementing strict resource limitations at the database level requires a combination of PostgreSQL features, including trigger functions, row-level security (RLS), and transaction-level advisory locks. In a modern architecture utilizing direct client-to-database communication—such as setups pairing PostgreSQL with PostgREST or Supabase—the database acts as the ultimate arbiter of state.

When a user attempts to create a new resource, such as following a technology stack in a release-tracking application, the operation bypasses traditional custom route handlers. Instead, the browser executes a direct database mutation.

create function public.enforce_follow_rules()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
declare
  followed integer;
begin
  if (select retired from public.technologies where id = new.technology_id) then
    raise exception 'A retired Technology cannot be followed'
      using errcode = 'FRTRD';
  end if;

  perform pg_advisory_xact_lock(hashtextextended(new.user_id::text, 0));

  if coalesce((select plan from public.plans where user_id = new.user_id), 'free') = 'free' then
    select count(*) into followed from public.follows where user_id = new.user_id;
    if followed >= 10 then
      raise exception 'The Free Plan follows at most 10 Technologies'
        using errcode = 'FLIMT';
    end if;
  end if;

  return new;
end;
$$;

create trigger enforce_follow_rules
  before insert on public.follows
  for each row execute function public.enforce_follow_rules();

This database trigger operates on three core pillars: data integrity checks, concurrency control, and subscription tier validation. First, it verifies the state of the referenced entity, preventing interactions with deprecated or retired records. Second, it evaluates the user’s subscription tier, applying specific resource ceilings exclusively to free-tier accounts while leaving pathways open for future paid expansions. Finally, it executes prior to row insertion, ensuring that invalid transactions are rejected at the inception point.

Mitigating Concurrency Risks with Advisory Locks

A critical vulnerability in any database-level counting mechanism is the race condition introduced by concurrent transactions. If a user initiates simultaneous requests from two distinct browser tabs when their account sits at nine active items, both execution threads will independently query the database, calculate a count of nine, and successfully pass validation. Consequently, both inserts execute, resulting in eleven total records and bypassing the ten-item ceiling.

To neutralize this risk, engineers utilize PostgreSQL transaction-level advisory locks via the pg_advisory_xact_lock function. By generating a deterministic hash based on the user’s unique identifier, the database serializes evaluation decisions on a per-user basis.

When multiple requests arrive concurrently, the advisory lock forces subsequent transactions to queue until the active transaction completes its check and insertion cycle. Once the initial transaction commits or rolls back, the lock automatically releases. This mechanism ensures that even under heavy concurrent load or automated stress testing, aggregate counts remain strictly accurate.

Furthermore, executing these functions with security definer privileges ensures that internal validation rules apply uniformly, regardless of whether the calling role is an authenticated end-user or an administrative service account.

Standardizing Error Handling Through Custom SQL States

Moving validation logic to the database introduces a communication challenge: transmitting the exact cause of a rejection from the PostgreSQL server back to the client application without relying on fragile string parsing of error messages.

Standard database error messages are prone to breaking if database administrators or developers alter descriptive text strings during refactoring. To establish a robust error-handling contract between the database and the frontend, engineers assign custom SQLSTATE error codes within the XX000 custom error space provided by the SQL standard.

In practice, specific alphanumeric codes are allocated to distinct business rule violations—such as FLIMT for free plan limit breaches and FRTRD for attempts to interact with retired entities. Modern API layers and data-fetching libraries like PostgREST capture these native error objects and forward the status codes directly to the client runtime.

const BY_CODE: Record<string, FollowRejection> =  
  FLIMT: "limit", 
  FRTRD: "retired" 
;

export function followRejectionFor(code: string | undefined): FollowRejection  "unknown";

By mapping incoming error codes to deterministic UI states, client applications can gracefully handle rejections. Unrecognized errors, dropped connections, or expired sessions default safely to generalized recovery prompts, ensuring the user interface remains stable and informative rather than failing silently.

Bridging the Gap Between Database Rules and State Management

While database-level constraints secure the backend against invalid data states, frontend applications must still manage optimistic updates, asynchronous mutations, and UI synchronization. A common architectural anti-pattern involves dispersing mutation logic across individual component rows.

When individual UI components manage their own isolated mutations, a rejection triggered by the database remains trapped within the specific component that initiated the request. Sibling components—such as top-level resource counters, navigation bars, or warning banners—fail to receive the error state. This fragmentation causes the user interface to fall out of sync with the true state of the database.

To resolve this synchronization challenge, developers consolidate state management into centralized context providers. By lifting mutation handling, error catching, and count tracking into a unified provider layer, applications ensure that all interface components reflect a single source of truth.

const value: Follows = 
  count: following.size,
  atLimit: following.size >= FREE_FOLLOW_LIMIT,
  isFollowing: (technologyId) => following.has(technologyId),
  toggle: (technologyId) => mutation.mutate( 
    technologyId, 
    follow: !following.has(technologyId) 
  ),
  isPending: (technologyId) => mutation.isPending && mutation.variables?.technologyId === technologyId,
  rejectionOn: (technologyId) => (technologyId === rejected ? rejection : null),
;

This centralized approach guarantees that UI counters, interaction limits, and error banners update simultaneously. If the database rejects an insertion, the centralized mutation handler catches the custom error code, rolls back the optimistic UI update across all dependent components, and displays the appropriate contextual notification.

Accessibility and Interface Anticipation

Enforcing resource caps at the database level does not absolve frontend developers from designing proactive user interfaces. Relying solely on database rejections to inform users they have reached a limit results in a poor user experience, as users only discover restrictions after performing an action.

Best practices dictate that the interface should anticipate database constraints. When a user reaches their maximum resource allocation, unselected interaction controls should be programmatically updated using accessibility attributes such as aria-disabled="true", accompanied by clear explanatory text indicating the nature of the restriction.

Maintaining these controls within the document’s tab order ensures keyboard and screen reader accessibility. If controls were instead hidden or removed entirely using native HTML disabled attributes, assistive technologies would encounter abruptly vanishing elements without context, creating barriers for users navigating via alternative input devices.

Automated testing frameworks benefit significantly from this approach. Tools like Playwright and Cypress naturally respect accessibility markers, refusing to click elements marked with aria-disabled. Developers can write test assertions that verify the presence of the restriction attribute, using forced click commands specifically to prove that the constraint correctly blocks unauthorized actions.

Industry Implications and Long-Term Maintainability

The shift toward database-enforced business logic represents a broader movement in software engineering toward defense-in-depth architectures. As applications scale and teams distribute development across multiple microservices and client platforms, relying on application-tier validation alone becomes increasingly fragile.

Embedding rules directly into relational database schemas offers several distinct long-term advantages:

• Longevity: Database triggers outlive individual frontend frameworks, API gateways, and backend rewrites, protecting data integrity regardless of how many times the client application is replaced.
• Consistency: Automated test suites and external integrations automatically inherit the same business rules, eliminating discrepancies between web clients, mobile apps, and programmatic API access.
• Auditability: Centralizing constraints within migration scripts provides a clear, version-controlled history of product rules and tier limitations.

While maintaining duplicate constants—such as defining resource limits both in database migrations and frontend configuration files—introduces a minor maintenance overhead, it represents an optimal engineering trade-off. The database definition serves as the authoritative legal boundary, while the frontend constant enables proactive user interface rendering.

Ultimately, delegating validation to the database transforms subscription limits from aspirational software claims into undeniable operational facts. By combining PostgreSQL triggers, advisory locks, custom SQL states, and centralized frontend state management, engineering teams can build robust, tamper-proof systems that maintain absolute data integrity under any operational conditions.