One of the first design problems I encountered while building the application was determining how bets should be validated and represented internally. Rather than allowing users to submit arbitrary bet information, each bet was tied to a specific sporting event stored within the database. At a basic level, this prevented invalid or incorrectly entered bets, such as users mistyping teams or referencing games that did not actually exist on a given date. However, the relationship quickly became much more valuable than simple validation. Associating bets with normalized event records enabled the application to support analytics and future insight systems, such as tracking which teams or sports users frequently bet on, analyzing how close to game time bets were being placed, and eventually comparing betting behavior across sports books or markets. It also introduced consistency into the relational model, since multiple bets from different users or sports books could reference the same underlying event.

Initially, the simplest approach would have been allowing the frontend to query third-party sports APIs directly whenever game information was needed. However, this introduced several architectural and operational concerns, including API rate limiting, increased frontend latency, exposure of external API keys, repeated duplicate requests across clients, and tight coupling between frontend logic and external payload formats. To address this, the application introduced an internal events table that stored normalized game data fetched from external providers. While this initially functioned as a lightweight cache to reduce API calls, it gradually evolved into a central source of truth for the application. By maintaining an internal representation of sporting events, the system gained control over normalization, validation, and consistency while insulating downstream application logic from external API changes. As the project expanded toward supporting additional leagues and potentially prediction markets in the future, this separation became increasingly important.

Once the events table became foundational infrastructure, keeping it synchronized with real-world game data became its own engineering problem. Games may be postponed, delayed, rescheduled, or updated dynamically during playoff scenarios where future matchups are not known in advance. As a result, the system required some form of scheduled ingestion pipeline capable of periodically fetching, transforming, and updating event data automatically.

Local Prototype

The initial synchronization architecture for the application followed a relatively straightforward ingestion pipeline that lived directly within the backend application itself. The flow consisted of sending a request to an external sports API to retrieve event information, normalizing the returned payload into the application’s internal schema, and inserting or updating records within the events table. Since sporting events change continuously throughout a season due to delays, postponements, updated start times, and playoff scheduling, the database required periodic synchronization in order to remain reliable as the application’s source of truth. To automate this process, I introduced a local cron job that executed the synchronization flow once every day. Cron jobs are time-based scheduling utilities commonly used in operating systems to automate repetitive background tasks, and this approach provides a simple way to keep the database updated without requiring manual intervention.

Operational Limitations

While the local cron job architecture worked well as an initial prototype, it quickly introduced several operational limitations. Since the synchronization workflow was running locally, ingestion reliability became tied directly to my personal development environment and required my machine to remain online in order for scheduled updates to execute successfully. As the project continued to grow, deployment and scaling also became more difficult because the synchronization pipeline was tightly coupled to the application runtime rather than existing as an independently managed service. Additionally, the scheduled jobs themselves were physically separated from the hosted database infrastructure, creating a less centralized architecture for secrets management, API credentials, and deployment configuration. Although the system functioned correctly, it became clear that a more production-oriented synchronization architecture was needed.

Edge Function Development and Deployment

Supabase Edge Functions

To address the operational limitations of the local synchronization architecture, I transitioned the ingestion workflow into a dedicated Supabase Edge Function. Edge functions are lightweight serverless backend functions that execute on demand in response to HTTP requests or scheduled events. Instead of maintaining a continuously running backend service solely for periodic synchronization, the ingestion logic could now execute independently within managed cloud infrastructure located near the hosted database itself. This significantly simplified the architecture by separating event synchronization responsibilities from the main application runtime. The edge function became solely responsible for fetching external API data, normalizing payloads, and updating the events table, while the frontend and backend only consumed the already-normalized internal data model.

// Edge Function Logic
// logic for each impoerted module exists in files within same function

import "jsr:@supabase/functions-js/edge-runtime.d.ts"
import { formatNBAEvents } from "./formatNBAEvents.ts"
import { getNBAEvents } from "./getNBAEvents.ts"
import { writeNBAEvents } from "./writeNBAEvents.ts"

Deno.serve(async (request) => {

  if (request.method !== "POST") {
    return new Response(
      JSON.stringify({ error: "Method not allowed. Use POST." }),
      {
        status: 405,
        headers: { "Content-Type": "application/json" },
      },
    )
  }
  try {
    console.log("sync-events edge function invoked")
    const nbaResponses = await getNBAEvents()
    const events = nbaResponses.flatMap((item) => formatNBAEvents(item.payload))
    const writtenEvents = await writeNBAEvents(events)

    return new Response(
      JSON.stringify({
        success: true,
        message: "sync-events fetched, formatted, and upserted events",
        gameDates: nbaResponses.map((event) => event.gameDate),
        totalResponses: nbaResponses.length,
        totalEvents: events.length,
        upsertedEvents: writtenEvents.length,
        events: writtenEvents,
      }),
      {
        status: 200,
        headers: { "Content-Type": "application/json" },
      },
    )
  } catch (error) {
    console.error("sync-events failed", error)

    return new Response(
      JSON.stringify({
        success: false,
        error: error instanceof Error ? error.message : "Unknown error",
      }),
      {
        status: 500,
        headers: { "Content-Type": "application/json" },
      },
    )
  }
})

This also centralized API credential and secrets management within Supabase while removing the dependency on my local machine remaining online for scheduled updates.

Local Development and Testing

Before deploying the synchronization pipeline into the hosted Supabase environment, I first wanted to validate the ingestion workflow locally against a controlled database environment. To do this, I took the following steps:

1. I created a dedicated supabase folder within my project directory by running supabase init. This initialized local configuration files, migrations, and edge function scaffolding.
2. To launch the local database services and the edge function runtime used for testing, I ran supabase start.
3. In the functions folder within the supabase directory, I created a new directory which contained the edge function using supabase functions new sync-events. The synchronization logic was stored in the index.ts file. The edge function was responsible for fetching external API data, normalizing payloads, and updating the events table.
4. Local testing requires keys and secrets to be stored on the machine. I configured environment variables and API credentials locally using an .env file or exported shell variables.
5. Since my edge function must be able to communicate with the database, I pulled the database from my remote machine in order to test if my function was able to insert the data in the format required.
6. To test the edge function, I ran the command supabase functions serve sync-events. This creates a temporary backend environment for supabase edge functions. In a separate terminal I called the functions manually using a curl request to validate the ingestion workflow end-to-end.

Remote Deployment

Once the synchronization workflow was validated locally, the edge function could be deployed into the hosted Supabase environment and integrated with remote scheduling infrastructure.

1. I first deployed the edge function to the remote machine thats hosts my Supabase project using supabase functions deploy sync-events.
2. In my local environment, my .env file held third party API keys and other secrets that my edge function uses. To store my secrets, I ran the command supabase secrets set API_KEY=... , for each variable.
3. I followed the same testing as I did with local development but I called the url endpoint of my deployed edge function instead of my local edge function.
4. To automate the calls to the edge function, I attached a cron job using the pg_cron integration on Supabase to the deployed edge functio to run the function everyday at 10 a.m.
5. I then verified that the function was actually be called by viewing the logs associated with both the integration and the edge function.

Key Takeaways

One of the biggest lessons from this project was realizing that there are many valid ways to approach synchronization and ingestion workflows. The system could have been implemented using traditional backend servers, cloud functions, external schedulers, or containerized workers. For my particular use case, Supabase Edge Functions combined with pg_cron provided a strong balance between simplicity, reliability, and operational overhead. The architecture allowed the ingestion pipeline to run independently from the main application while remaining close to the hosted database infrastructure. More importantly, the project reinforced that infrastructure decisions should evolve naturally alongside application requirements rather than starting with unnecessary complexity.

Framing the Problem

League Context

The NBA regular season spans 6 months and consists of 1230 total games (82 per team). Each team competes to snag a spot in the playoffs where they play a bracket style tournament to win the NBA championship. Given the length of the regular season, hot streaks, flukey stretches, injuries, and trades continuously prompt new story lines in the NBA regarding which teams have a real shot at the NBA championship.

Core Problem Statement

Can regular-season data identify true championship contenders?

Why It’s Hard

Championship teams rarely follow a single blueprint. Over the past several seasons, the league has produced a wide range of champions with distinct identities, some built around elite offensive firepower, others anchored by dominant defense, depth, or a transcendent superstar. No team has returned to the Finals in consecutive years in the past 7 years, underscoring how fragile sustained dominance can be in a league shaped by parity, matchup dynamics, and roster volatility.

Compounding this challenge is the nonlinear nature of an NBA season. Teams evolve. Early-season performance may be distorted by injuries, experimentation with rotations, midseason trades, or the gradual development of chemistry and identity. A contender in April may look fundamentally different from that same team in November. Any model built purely on aggregate regular-season statistics must therefore account for timing, trajectory, and context.

What is a Contender

Defining a “contender” is more nuanced than simply identifying the eventual champion. A single playoff outcome can obscure how competitive a team truly was. For instance, in the 2017–2018 season, the Warriors ultimately swept the Cavaliers in the Finals, yet were pushed to a 3–2 deficit in the Western Conference Finals against Houston. If the Rockets had converted a few late-game possessions differently, the championship narrative may have changed entirely.

Relying solely on champions or Finals appearances therefore risks collapsing meaningful competitive tiers into a binary outcome. Instead, this project treats contention as a spectrum of playoff performance rather than a single result.

To operationalize this idea, I use playoff statistics and outcomes from 2010–2025 and apply an unsupervised clustering approach to group teams based on postseason success profiles. Rather than imposing a predefined label, the model allows historical playoff data to reveal natural tiers of performance, which can then be interpreted as championship-level, near-contender, or non-contender archetypes.

Unsupervised Learning to Determine Contenders

Within the playoffs I extracted 5 features that best characterized a teams dominance without having to scale by season and accounted for strong contender losing teams losing in the semi-finals or conference finals. These 5 features were:

1. Win Percentage — what percentage of the games did the team win

2. Total Playoff Wins — more wins equals deeper playoff run

3. Average +/- per game

4. Average wins per series

5. Average +/- per series

These features were scaled and fit to a KMeans model with n_cluster=3 and init= “k-means++”. The goal was to split the teams into categories of play-in / non-contender, decent playoff team, and championship contender which these parameters did well. For visualization, I applied Principal Component Analysis (PCA) to reduce the five-dimensional feature space into two principal components, enabling a 2D representation of the clustering structure.

The rightmost cluster (yellow) captures championship-caliber teams, including archetypes such as the 2013–2014 Spurs and 2016–2017 Warriors. The central cluster (dark purple) represents strong but not dominant playoff teams, such as the 2023–2024 Los Angeles Lakers or the 2016–2017 Celtics. The leftmost cluster (pink) consists of lower-tier playoff participants and early exits.

Modeling Perspective

Although playoff performance naturally forms tiers, I ultimately frame this as a binary classification problem: contender vs. non-contender. After identifying championship-level teams through clustering, I collapse all other teams — including solid playoff teams and non-playoff teams — into a single non-contender class. This simplifies the prediction task to a clearer decision boundary: Does this regular-season profile resemble that of a historically championship-caliber team or not?

While a ranking or probability-based framework could provide more granularity, the binary setup aligns with the practical objective of identifying true title threats rather than differentiating between varying levels of non-championship outcomes. This framing also improves interpretability and stability, as it reduces noise introduced by marginal playoff differences and focuses the model on learning the structural characteristics unique to elite teams.

Hypothesized Key Factors

At a foundational level, championship-caliber teams tend to separate themselves through strong net rating, which serves as a holistic indicator of two-way performance. Teams that consistently outscore opponents at a high margin over large samples generally possess the structural balance required for postseason success.

Beyond baseline efficiency, more granular factors may further distinguish true contenders. These include lineup-level performance metrics (to capture rotational stability and adaptability), star player impact and usage concentration, clutch-time efficiency, prior playoff experience, and health continuity throughout the season and entering the postseason. Together, these variables attempt to move beyond surface-level win totals and instead capture the underlying traits that historically define championship-level teams.

Exploratory Data Analysis

Season Aggregate Stats

This section establishes a high-level baseline for team strength using season-long advanced metrics aggregated at the team level. By combining indicators such as PIE, usage rate, and availability-adjusted contributions, the goal is to capture overall roster quality beyond traditional box score totals. These features help contextualize which teams consistently generate positive on-court impact across their rotation, rather than relying on raw win totals alone. As an EDA step, this provides an initial signal of which statistical profiles tend to align with higher-tier teams. Below we see two advanced stats that allow us to visualize some rough decision boundaries to classify championship contenders.

Best Player Analysis

This analysis isolates each team’s top contributors using a custom rating metric derived from PIE scaled by minutes played, along with a season impact adjustment that incorporates games played. By combining efficiency, workload, and availability, the metric captures not just how effective a player is, but how much that effectiveness translates over a full season. The motivation behind this step is the consistent historical pattern that championship teams are anchored by at least one elite, high-impact player, such as Shai Gilgeous-Alexander during the 2024–2025 season or Nikola Jokić in 2022–2023, whose individual production meaningfully elevates team performance. Rather than analyzing depth, this feature is explicitly constructed to encode the concentration of star talent on a roster, providing a quantitative proxy for elite player impact that can later be incorporated into the classification model.

Although the table tracks the top 3 players from each team for every season, below is a visual of the top 10 most frequently occuring best players.

Clutch Performance Analysis

This analysis captures team performance in high-leverage situations using full-season clutch net rating, defined as the difference between offensive and defensive rating in games within five points during the final five minutes. Clutch net rating provides a compact measure of late-game execution, reflecting how efficiently a team scores and defends under pressure. Championship-level teams often separate themselves in close games, where possession value and decision-making are magnified. By encoding clutch net rating as a single variable, this feature serves as a quantitative proxy for composure and execution in decisive moments for use in the classification model.

The top 10 teams by best net rating in the clutch are displayed below.

Line Up Analysis

This analysis evaluates team performance at the lineup level using a composite Four Factors score rather than raw net rating. While net rating captures overall scoring margin, it can become noisy at the lineup level due to small sample sizes and short shooting swings. The Four Factors framework breaks performance into the core drivers of winning, including shooting efficiency, turnover control, offensive rebounding, and free throw rate, on both offense and defense. By aggregating the top minute weighted lineups per team, this feature encodes how consistently effective a team’s primary combinations are and provides a more stable, process oriented measure of lineup strength for the classification model.

Model Building

Pre Modeling Observations

Before training classifiers, several structural properties of the dataset were addressed. First, the target variable is highly imbalanced, with 62 contenders and 388 non-contenders, meaning a naïve model could achieve high accuracy by simply predicting the majority class; therefore evaluation is centered on F1 score to better balance precision and recall, ensuring the model meaningfully identifies true contenders rather than defaulting to the dominant class. Second, features were scaled within each season rather than across seasons, since league context shifts over time, for example an elite offensive rating in 2012–2013 may correspond numerically to a below-average rating in 2022–2023 due to pace and scoring inflation; seasonal normalization preserves relative strength within era. Finally, with 15 engineered features spanning lineup metrics, Four Factors aggregates, and player impact measures, Principal Component Analysis was explored to reduce dimensionality and mitigate multicollinearity while retaining the majority of variance, allowing for more stable and interpretable downstream modeling.

Logistic Regression

Given the class imbalance and moderately high dimensional feature space, Logistic Regression was expected to perform well due to its ability to learn a stable linear decision boundary while incorporating class weights and probability threshold tuning. Using a pipeline with PCA and grid search cross validation scored on F1, the best model retained 90% explained variance, reducing 15 features to 8 principal components. The optimal configuration used C = 0.1, a class weight ratio of 1:6.8, and a tuned prediction threshold of 0.866, producing an F1 score of 0.88, substantially outperforming other classical approaches.

Support Vector Classifier

SVC was expected to perform reasonably well given its ability to construct flexible decision boundaries in high dimensional spaces. However, despite tuning kernel and gamma parameters, performance lagged behind Logistic Regression. The best configuration used a polynomial kernel with gamma = 0.1 and PCA retaining 90% explained variance, but achieved an F1 score of only 0.44, suggesting limited separability even under nonlinear transformations.

K Nearest Neighbors

KNN was not expected to perform strongly due to the curse of dimensionality and class imbalance, which can distort distance based methods. Even after PCA retaining 95% variance and tuning neighbors, distance weighting, and power parameters, the best model achieved an F1 score of 0.50, reinforcing the expectation that local neighborhood methods struggle in this setting.

Decision Tree Classifiers

Decision Trees were expected to suffer from instability and high variance, particularly given the heterogeneous makeup of contending teams where strong star driven teams and balanced lineup driven teams may not share simple rule based splits. Using PCA at 90% explained variance, the best configuration achieved an F1 score of 0.50, indicating limited generalization and supporting the hypothesis that a single tree struggles to capture contender structure.

Inference on 2025–2026 Season

This model surfaces a few important limitations before interpreting results. First, Four Factors metrics were not directly available at the lineup level, requiring custom feature engineering to approximate lineup impact. Second, season availability data is slightly skewed since a full 82-game sample is not yet complete, which may introduce small distortions in cumulative impact metrics.

Using Logistic Regression provides a key advantage: probabilistic outputs rather than just binary labels. Based on the selected threshold, the Oklahoma City Thunder, Denver Nuggets, and Cleveland Cavaliers are classified as contenders, with the San Antonio Spurs narrowly missing the cutoff by just 0.004, effectively placing four teams at the top tier. The second tier, based on probability magnitude, includes the New York Knicks, Minnesota Timberwolves, Los Angeles Lakers, and Boston Celtics. A third tier emerges with lower but still notable probabilities, including the Charlotte Hornets, Toronto Raptors, Detroit Pistons, and Houston Rockets. These tier distinctions reflect relative contender strength rather than strict playoff predictions, offering a probabilistic view of competitive positioning across the league.

The goal of this system is to provide users with a structured way to track and analyze their betting activity across multiple sports books, while enabling deeper insights into betting behavior over time. Rather than simply storing raw bet data, the system is designed to capture relationships between users, bets, and events so that meaningful patterns that allow users to better understand their habits and make more informed, responsible decisions. This foundation supports both deterministic metrics (e.g., profit/loss, win rates) and more advanced behavioral analysis, allowing the system to surface trends like overuse of parlays or inconsistent stake sizing, ultimately helping users better understand and refine their betting decisions.

Core Entities

The system is built around four core entities that model the relationships needed to track and analyze betting activity. The User entity identifies each bettor and stores metadata such as unit size, which standardizes bet sizing and promotes responsible gambling, since the same dollar amount can represent different levels of risk depending on a user’s bankroll. The Event entity represents a game along with its start time, enabling time-based analysis and helping identify patterns in team or league preferences. The Bet entity serves as the central record, tying a wager to a user and associated events through foreign keys while storing key details such as stake, payout, and sportsbook for performance tracking. Finally, the Bet Leg entity captures granular information for each wager, including market type, line, and selection, and supports both single bets and multi-leg parlays through multiple associated records per bet.

Design Influences

The design of the schema was heavily influenced by both ingestion patterns and query requirements. On the ingestion side, repeatedly syncing external sports data required idempotent writes, which led to the use of stable business keys such as a combination game information (home team, away team, start time, league) and external event ID rather than solely internal identifiers. Time normalization also became important, as incoming data from multiple sources needed to be standardized into a single starts_at timestamp to support consistent querying. On the query side, access patterns drove the decision to store commonly filtered fields such as home_team, away_team, and starts_at, directly on the event record, avoiding the need for reconstruction or parsing at runtime. This ensured efficient lookups and simplified range-based queries, particularly when filtering by local calendar days.

The structure of bets and bet legs was shaped by the need to support flexible wagering types while maintaining clean data modeling. Since a single bet can contain multiple legs, a parent-child relationship between bets and bet_legs allows for normalization without duplicating shared data. This also influenced the write path, where creating a bet and its associated legs required an atomic, transactional approach, leading to the use of RPC functions to handle multi-table inserts. Additionally, read and analytics patterns informed selective denormalization at the bet level, with frequently queried fields like bet_type, placed_at, and result stored directly to support efficient filtering and metric computation. Finally, event resolution requirements — matching bets to real-world games using team names and timestamps — led to storing both human-readable and structured identifiers, enabling reliable mapping for both ingestion and downstream analysis.

Initial Implementation + Limitations

While building the logic for how I wanted my backend to interact with the database, I started by adding basic API endpoints that allowed me to connect to my database and write the information necessary in the correct tables but problems arose as I built out the application.

Supabase Service Key

Supabase is a Backend as a Service (BaaS), meaning my application connects to the database and related backend services through a Supabase client configured with an API key. In my initial implementation, I used the Supabase service role key for most database operations. This worked during development, but the service role key effectively has administrative access and bypasses Row Level Security, meaning database reads and writes were trusted entirely by my backend code rather than enforced by the database itself.

# Querying DB using Service Key
user = self._supabase_service.get_user_from_access_token(access_token)
user_id = user["id"]
client = self._supabase_service.get_client()

try:
    query = (
        client.table("bets")
        .select("*")
        .eq("user_id", user_id)
        .order("placed_at", desc=True)
    )
except Exception as exc:
    raise RuntimeError(f"Failed to fetch user bets: {exc}") from exc

For an application used only by myself, this was functional, but it would not be a safe design for a multi-user system. If simple operations like querying public events or retrieving a user’s bet history rely on root-level access, then a bug in the backend query logic could expose or modify data across users. A better approach is to reserve the service role key for trusted backend jobs and use the anon key with JWT-based authentication and RLS for user-facing queries.

REST API POST Requests

In the initial implementation, most database access was handled through standard REST-style operations from the backend. This worked well for simple reads and writes, but it became more fragile once a single user action required changes across multiple related tables. Creating a bet, for example, required inserting a record into the bets table and then inserting one or more related records into bet_legs. If these were handled as separate REST operations, the application would need to manually ensure that both writes succeeded together.

# API call with multiple post requests to add a bet
def create_bet_with_legs(self, access_token: str, payload: BetCreate) -> dict[str, Any]:
  client = self._supabase_service.get_user_client(access_token)
  normalized_payload = self.prepare_bet_payload(payload)
  
  payload_json = normalized_payload.model_dump(mode="json")
  legs_payload = payload_json.pop("legs", [])
  
  # 1. Insert parent bet first
  bet_response = client.table("bets").insert(payload_json).execute()
  bet_rows = getattr(bet_response, "data", None) or []
  if not bet_rows:
      raise RuntimeError("Bet insert returned no data.")
  
  bet_id = bet_rows[0]["bet_id"]
  
  # 2. Attach bet_id to each leg
  leg_rows = [
      {
          **leg,
          "bet_id": bet_id,
      }
      for leg in legs_payload
  ]
  
  # 3. Insert legs second
  legs_response = client.table("bet_legs").insert(leg_rows).execute()
  inserted_legs = getattr(legs_response, "data", None) or []

In a multi-user system, this becomes harder to reason about because a partial failure could leave the database in an inconsistent state, such as a bet being created without its legs or legs being written without the expected parent data. The fix was to move this flow into an RPC function, where Postgres handles the multi-table write as one database-side operation.

The second issue was that REST queries placed too much responsibility on the backend application to enforce ownership rules. For example, a get_bets function might query the bets table using select * and then filter by user_id based on the authenticated user from the JWT. While this works, the security depends on every query being written correctly every time. If a filter is forgotten or incorrectly implemented, the database itself would not prevent access to another user’s rows. Row Level Security fixed this by moving the ownership rule into the database through policies, allowing the application to use an authenticated Supabase client while Postgres enforces which rows the user can read, insert, update, or delete.

Improved Architecture

Supabase Anonymous Key + Row Level Security

In the improved architecture, the system shifts from application-enforced logic to database-enforced guarantees by fully leveraging features provided by Supabase. Instead of relying on a privileged service key and manual query filters, all user-facing interactions are performed through the anonymous key paired with an authenticated JWT.

# Query db with anonymous key
client = self._supabase_service.get_user_client(access_token)

try:
    query = (
        client.table("bets")
        .select("*")
        .order("placed_at", desc=True)
    )
except Exception as exc:
    raise RuntimeError(f"Failed to fetch user bets: {exc}") from exc

This allows Row Level Security (RLS) policies to govern access at the database level, ensuring that users can only read and modify data they own, while still enabling public access patterns where appropriate (such as getting information for a user’s betting history).

-- Row Level Security Policy
CREATE POLICY "Users can manage their own bets"
ON public.bets
FOR ALL
TO authenticated
USING (user_id = auth.uid())
WITH CHECK (user_id = auth.uid());

This change simplifies the application layer and centralizes authorization logic within Postgres, making it consistent and harder to bypass.

RPC Functions

RPC functions are used to encapsulate more complex operations that involve multiple tables or derived logic. Rather than issuing multiple REST calls for related writes, these functions execute server-side within a single transaction, enabling atomic writes, ensuring that creating a bet and its associated legs cannot result in partial or inconsistent data.

# Creating a bet using RPC function
def create_bet_with_legs(self, access_token: str, payload: BetCreate) -> dict[str, Any]:
  client = self._supabase_service.get_user_client(access_token)
  
  normalized_payload = self.prepare_bet_payload(payload)
  payload_json = normalized_payload.model_dump(mode="json")
  legs_payload = payload_json.pop("legs", [])
  
  rpc_params = {
      "p_bet": payload_json,
      "p_legs": legs_payload,
  }
  
  # rpc function is titled "rls_create_bet_with_legs" in Supabase
  try:
      response = client.rpc("rls_create_bet_with_legs", rpc_params).execute()
  except Exception as exc:
      raise RuntimeError(f"Failed to create bet with legs via RPC: {exc}") from exc

This pattern aligns naturally with RLS, as functions can derive the current user from auth.uid() and operate within the same security context as standard queries. Together, RLS and RPC functions form a cleaner boundary between the application and the database, where the database is responsible not just for storing data, but also for enforcing correctness and access control.

Key Takeaways

Designing this system reinforced the importance of pushing critical logic, especially authorization and data integrity, down to the database layer rather than relying on application code. Using Row Level Security with authenticated clients simplifies access control and ensures consistent enforcement across all queries. RPC functions provide a clean way to handle multi step operations through atomic writes, reducing the risk of partial or inconsistent data. More broadly, structuring the schema around real access patterns and relationships leads to a more maintainable, secure, and scalable system.