• Book: Decoupled PHP — Clean and Hexagonal Architecture for Applications That Outlive the Framework
• Also by me: Hexagonal Architecture in Go
• My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
• Me: xgabriel.com | GitHub

You’ve seen the picture. Four concentric rings, the words Entities, Use Cases, Interface Adapters, Frameworks & Drivers arranged like a target, arrows pointing inward. It’s on the cover of Robert C. Martin’s Clean Architecture, in every architecture talk on YouTube, and pinned in someone’s Slack right now.

The picture is beautiful. The problem is the picture doesn’t tell you where to put PlaceOrderController.php. It doesn’t tell you whether AppDomainOrder should hold the Doctrine attributes or not. It doesn’t tell you what to do when Laravel’s Request object shows up in a method signature.

This post does the translation. Four rings on the whiteboard, mapped to PHP namespaces and a directory tree you can paste into a src/ folder today.

The four rings, restated for PHP people

The rings aren’t folder names. They’re a rule about which way the arrows point: source-code dependencies cross ring boundaries inward, never outward. The outer rings know about the inner rings. The inner rings have never heard of the outer rings.

Reading from the center outward:

Ring 1 — Entities. Things the business cares about. Order, Customer, Invoice, Money. They encode rules of the business itself, not rules of this particular application. An Order refuses to transition from shipped back to pending. That’s true whether the system is a website, a CLI, or a queue worker. Plain PHP. No Doctrine, no Eloquent, no framework imports.

Ring 2 — Use Cases. What this application does. PlaceOrder, CancelOrder, RefundPayment. One verb per class. Orchestrates entities and ports to deliver one business operation. Knows nothing about HTTP, CLI, queues, or which database is behind the repository.

Ring 3 — Interface Adapters. Translation. A controller takes an HTTP request and shapes it into the input the use case expects. A repository implementation takes a domain Order and shapes it into rows for Doctrine. The adapter ring does shape-shifting, not business logic.

Ring 4 — Frameworks and Drivers. Laravel, Symfony, Doctrine, Guzzle, PHP-FPM, the database, the message broker. The volatile, version-churning edge of the system. The things you have no control over and don’t want to.

The promise: when Doctrine ships a major version, only ring 4 changes. Moving a service from Laravel 11 to Symfony 7 leaves rings 1 and 2 untouched.

Three namespaces, four rings

The four rings collapse cleanly into three folders on disk. Most PHP teams will end up here:

src/
  Domain/
    Order/
      Order.php
      OrderId.php
      OrderStatus.php
      OrderRepository.php          // port (interface)
      OrderAlreadyShipped.php      // domain exception
    Customer/
      Customer.php
      CustomerId.php
    Shared/
      Money.php
  Application/
    Order/
      PlaceOrder.php               // use case
      PlaceOrderInput.php          // input DTO
      PlaceOrderOutput.php         // output DTO
      CancelOrder.php
    Port/
      Clock.php                    // port (interface)
  Infrastructure/
    Http/
      Controller/
        PlaceOrderController.php
    Cli/
      PlaceOrderCommand.php
    Persistence/
      Doctrine/
        DoctrineOrderRepository.php
    Clock/
      SystemClock.php

AppDomain is ring 1, plus the repository port interfaces written in the domain’s own language. AppApplication is ring 2: use cases, DTOs, and application-level ports. AppInfrastructure collapses rings 3 and 4 because in a real project the controller class and the framework that hosts it are not separable. PlaceOrderController lives in the same file as the framework’s Request and Response types. Splitting “the controller class” from “the framework’s HTTP types” into two directories produces a longer path and a fiction about purity that the IDE will not honor.

Three folders. The dependency rule still applies at the seam between rings 3 and 4 inside Infrastructure/.

Ring 1 in PHP: an entity that knows nothing

Here’s AppDomainOrderOrder. It encodes what an order is. It does not encode what the system does when an order is placed.

<?php

declare(strict_types=1);

namespace AppDomainOrder;

use AppDomainCustomerCustomerId;
use AppDomainSharedMoney;
use DateTimeImmutable;

final class Order
{
    /** @var list<OrderLine> */
    private array $lines = [];

    private OrderStatus $status = OrderStatus::Pending;

    public function __construct(
        public readonly OrderId $id,
        public readonly CustomerId $customerId,
        public readonly DateTimeImmutable $placedAt,
    ) {}

    public function addLine(OrderLine $line): void
    {
        if ($this->status !== OrderStatus::Pending) {
            throw new OrderAlreadyShipped(
                'Cannot add lines after shipment.'
            );
        }
        $this->lines[] = $line;
    }

    public function total(): Money
    {
        return array_reduce(
            $this->lines,
            fn(Money $sum, OrderLine $l) => $sum->add($l->subtotal()),
            Money::zero('EUR'),
        );
    }

    public function markShipped(): void
    {
        if ($this->status === OrderStatus::Shipped) {
            return;
        }
        $this->status = OrderStatus::Shipped;
    }
}

Notice what’s missing. No extends Model, no #[ORMEntity], no Illuminate or Symfony use statements. The only imports are other ring-1 types. Drop this file into a project whose composer.json requires only "php": "^8.3" and it parses and runs.

That’s the test. If Order.php can stand alone with PHP and nothing else, ring 1 is honest. The day someone adds use IlluminateDatabaseEloquentModel; at the top, ring 1 is leaking and the dependency rule is broken.

Ring 1, continued: the repository port

The OrderRepository interface lives in the domain too, even though every implementation will live in Infrastructure/. The reason is the dependency rule. The use case in ring 2 needs to ask for an order. It does that through an interface. That interface has to live in a ring the use case is allowed to import, which means ring 1 or ring 2, never ring 3 or 4.

<?php

declare(strict_types=1);

namespace AppDomainOrder;

interface OrderRepository
{
    public function find(OrderId $id): ?Order;

    public function save(Order $order): void;
}

The interface is named for what the domain needs, not for the storage technology behind it. OrderRepository, not DoctrineOrderTable. The domain doesn’t know there will be a database. The domain doesn’t know there could be a database. From the domain’s view, somewhere out there is something that can save and load orders. Who and how is somebody else’s problem.

Ring 2 in PHP: a use case as a single verb

A use case is one application operation. One verb. One class. One public method.

<?php

declare(strict_types=1);

namespace AppApplicationOrder;

use AppApplicationPortClock;
use AppDomainOrderOrder;
use AppDomainOrderOrderId;
use AppDomainOrderOrderRepository;

final class PlaceOrder
{
    public function __construct(
        private readonly OrderRepository $orders,
        private readonly Clock $clock,
    ) {}

    public function execute(PlaceOrderInput $in): PlaceOrderOutput
    {
        $order = new Order(
            id: OrderId::new(),
            customerId: $in->customerId,
            placedAt: $this->clock->now(),
        );

        foreach ($in->lines as $line) {
            $order->addLine($line);
        }

        $this->orders->save($order);

        return new PlaceOrderOutput(
            orderId: $order->id,
            totalCents: $order->total()->cents,
        );
    }
}

This class is testable with three fakes and zero framework boot. No php artisan, no bin/console, no Doctrine bootstrap, no in-memory SQLite. The unit test that exercises PlaceOrder runs in single-digit milliseconds.

If your use-case test needs the framework to boot, ring 2 has been contaminated and you’re paying for that contamination on every test run for the rest of the project’s life.

Ring 3 in PHP: the controller is an adapter

The controller’s job is translation. HTTP in, DTO in. DTO out, HTTP out. Business logic in between is a smell.

<?php

declare(strict_types=1);

namespace AppInfrastructureHttpController;

use AppApplicationOrderPlaceOrder;
use AppApplicationOrderPlaceOrderInput;
use AppDomainCustomerCustomerId;
use AppDomainOrderOrderLine;
use AppDomainSharedMoney;
use SymfonyComponentHttpFoundationJsonResponse;
use SymfonyComponentHttpFoundationRequest;
use SymfonyComponentRoutingAttributeRoute;

final class PlaceOrderController
{
    public function __construct(
        private readonly PlaceOrder $useCase,
    ) {}

    #[Route('/orders', methods: ['POST'])]
    public function __invoke(Request $request): JsonResponse
    {
        $body = json_decode($request->getContent(), true);

        $input = new PlaceOrderInput(
            customerId: new CustomerId($body['customer_id']),
            lines: array_map(
                fn(array $l) => new OrderLine(
                    sku: $l['sku'],
                    quantity: $l['quantity'],
                    unitPrice: Money::ofCents(
                        $l['unit_price_cents'],
                        'EUR',
                    ),
                ),
                $body['lines'],
            ),
        );

        $output = $this->useCase->execute($input);

        return new JsonResponse([
            'order_id' => (string) $output->orderId,
            'total_cents' => $output->totalCents,
        ], status: 201);
    }
}

Read it carefully. The controller parses JSON, builds an input DTO, calls the use case, and serializes the output. No business rules anywhere in the class.

Swap Symfony for Laravel and the diff is in three lines: the #[Route] attribute, the Request type-hint, and the JsonResponse return type. The use case underneath does not change. The domain underneath that does not change either.

Rings 3 and 4 in PHP: where the framework finally shows up

Out at the edge, a single class straddles rings 3 and 4: Doctrine (ring 4) reached through an adapter (ring 3) that implements the OrderRepository port from ring 1.

<?php

declare(strict_types=1);

namespace AppInfrastructurePersistenceDoctrine;

use AppDomainOrderOrder;
use AppDomainOrderOrderId;
use AppDomainOrderOrderRepository;
use DoctrineDBALConnection;

final class DoctrineOrderRepository implements OrderRepository
{
    public function __construct(
        private readonly Connection $db,
    ) {}

    public function find(OrderId $id): ?Order
    {
        $row = $this->db->fetchAssociative(
            'SELECT id, customer_id, placed_at FROM orders WHERE id = ?',
            [(string) $id],
        );
        if ($row === false) {
            return null;
        }
        return OrderMapper::toDomain($row);
    }

    public function save(Order $order): void
    {
        $this->db->executeStatement(
            'INSERT INTO orders (id, customer_id, placed_at, total_cents)
             VALUES (?, ?, ?, ?)
             ON CONFLICT (id) DO UPDATE SET total_cents = EXCLUDED.total_cents',
            [
                (string) $order->id,
                (string) $order->customerId,
                $order->placedAt->format('c'),
                $order->total()->cents,
            ],
        );
    }
}

This class imports Doctrine. The class it implements (OrderRepository) does not. The arrow points inward: Infrastructure → Domain. The domain has no idea Doctrine exists. Replace it with a PdoOrderRepository, a DynamoDbOrderRepository, or an InMemoryOrderRepository for tests, and the change is contained to one file in one folder.

That’s the payoff. The whole onion drawing exists to make this property true.

The dependency rule, mechanized

A rule that depends on every reviewer remembering it is not a rule. It’s a hope. Enforce the dependency rule with Deptrac or PHPArkitect in CI, with a config like this:

# deptrac.yaml
deptrac:
  paths:
    - ./src
  layers:
    - name: Domain
      collectors:
        - { type: classLike, value: ^App\Domain\.* }
    - name: Application
      collectors:
        - { type: classLike, value: ^App\Application\.* }
    - name: Infrastructure
      collectors:
        - { type: classLike, value: ^App\Infrastructure\.* }
  ruleset:
    Domain: []
    Application:
      - Domain
    Infrastructure:
      - Domain
      - Application

Domain depends on nothing. Application may depend on Domain. Infrastructure may depend on both. Anything else is a build failure. Push a controller import into your domain and CI fails before review.

A test on your current codebase

Open a feature folder in your current project. Sort every file into three buckets.

Bucket A — pure PHP. Loadable by Composer with "require": { "php": "^8.3" } and nothing else. Imports are PHP built-ins plus your own namespaces. Open each file. Read the use statements. Verify.

Bucket B — application files contaminated by the framework. A use case that imports IlluminateSupportFacadesDB. A “service” that calls Cache::remember(...). A class extending SymfonyComponentConsoleCommandCommand but containing business logic inside execute() instead of delegating to a use case class.

Bucket C — framework glue. Routes, controllers that do nothing but call other code, command classes, event listeners registered by attribute, service provider configuration.

Write the three counts down. A healthy application has bucket A as the largest: domain plus use cases, in pure PHP, no framework imports below the controller layer. Bucket C is smaller and lives entirely in Infrastructure/. Bucket B should be small or empty. Every file in bucket B is a refactor candidate: pull the business logic into a use case in bucket A, leave a thin adapter in bucket C.

If bucket B is the largest, the framework is the application.

What the picture is, and isn’t

The four rings are a thinking tool. The dependency rule is the law. The directories are tactics that follow from both.

You don’t need four folders. You probably don’t even need three if the application is small enough. What you need is the property that swapping Doctrine for PDO, or Laravel for Symfony, or your HTTP framework for a Slack-bot adapter, doesn’t ripple all the way into the class that knows what an order is.

If that property holds, the picture has done its job. Without it, no amount of AppInterfaceAdaptersPresentersOrderPlacementSuccessPresenterFactory will save you.

If this was useful

The full PHP application, with every ring built out, every port and adapter wired, and a strangler-pattern migration from a legacy Laravel service into this shape, is the spine of Decoupled PHP. It walks the four rings from a single-file CRUD up to a production service with HTTP, CLI, queue workers, Doctrine, an external payment gateway, and a test suite that runs without booting the framework.

Available on Kindle, Paperback, and Hardcover. English, German, and Japanese editions out now — Portuguese and Spanish coming soon.

**

I Threw Away the Database and Let Hindsight Remember Everything

**

The first thing most developers do when they start a new project is spin up a database. Postgres, SQLite, whatever — you create a schema, define your tables, and build your data model before you write a single line of business logic. I’ve done it a hundred times.

For SalesMemory, we didn’t. Not because we were being clever. Because once we understood what we were actually building, a relational database was the wrong tool for the job.

Here’s what we built: a persistent, agent memory layer for sales reps. Before a call, the rep types a prospect name and gets a structured brief — objections raised, budget signals, competitor mentions, deal health score — synthesised from every past interaction. After the call, they log 2–3 sentences. The agent stores it permanently. Every future brief gets smarter because of it.

The technology making this possible is Hindsight, an agent memory system built by Vectorize that handles retain, recall, and reflect across sessions. Without it, the agent forgets everything the moment a session ends. With it, the agent compounds intelligence over time.

But the more interesting story isn’t “we used a memory layer.” It’s what happens when you commit to that memory layer as your only persistence layer — and what you give up and gain when you do.

**

The Problem We Were Actually Solving

**

Sales reps talk to the same prospects across weeks and months. The context from those conversations — objections raised, budget signals, competitor comparisons, personal details — lives in CRM notes nobody reads, or only in the rep’s head.

Every call without memory is a wasted opportunity. The rep re-introduces themselves. They miss that the prospect already said budget is frozen until Q3. They forget that onboarding speed was the deal-breaker last time.

Our product fixes that with one lookup before the call and one note after.

The thing is: this problem doesn’t fit neatly into a relational schema. Prospect data isn’t structured. A rep logs “she seemed unsure about the onboarding” — not {objection_type: "onboarding", sentiment: "negative", confidence: 0.7}. You can’t schema-design your way to capturing what a sales conversation actually contains.

**

The Architecture: One Persistence Layer

**

Most developers would immediately reach for Postgres. We deliberately didn’t.

Hindsight is the only persistence layer in SalesMemory. There is no database. Every call log goes into Hindsight via client.retain(). Every pre-call brief is generated by feeding recalled Hindsight memory into the LLM. The entire interaction history lives in semantic memory, not rows.

Here’s what storing an interaction looks like:

python
def retain_interaction(prospect_name: str, summary: str, outcome: str, timestamp: str):
    content = f"""
Prospect: {prospect_name}
Date: {timestamp}
Outcome: {outcome}
Summary: {summary}
"""
    client.retain(
        pipeline_id=PIPELINE_ID,
        content=content,
        metadata={
            "prospect": prospect_name,
            "outcome": outcome,
            "timestamp": timestamp,
            "type": "call_log"
        }
    )

And recalling it before a call:

python
def recall_prospect(prospect_name: str) -> str:
    results = client.recall(
        pipeline_id=PIPELINE_ID,
        query=f"prospect interactions with {prospect_name}",
        top_k=10
    )
    return results

That’s the entire data layer. No ORM. No migrations. No schema versioning. No connection pooling. No ALTER TABLE when the product changes.

The tradeoff is real: you can’t do SELECT COUNT(*) FROM deals WHERE stage = 'closing'. You can’t run SQL aggregations across your prospect base. If you need that kind of reporting, you need a database.

What you gain is something SQL can never give you: semantic retrieval. When we query Hindsight with "prospect interactions with Priya Sharma", it returns contextually relevant memories — not just exact-match rows. A rep who logged “she pushed back on timing” and another who wrote “onboarding timeline is a concern” both surface for the same query. Hindsight understands meaning, not keywords.

**

The Deal Health Score: Computed, Not Stored

**

Here’s the design decision I find most interesting.

Every pre-call brief includes a Deal Health Score from 0–100. It’s one of the features users notice immediately — they want to know if a deal is improving or stalling before they pick up the phone.

We don’t store this score anywhere.

There is no deals table with a health_score column. No score recalculation job. No stale data problem. The score is computed fresh on every brief request by feeding all recalled memory into the LLM with a specific scoring rubric:

python
BRIEF_SYSTEM_PROMPT = """
You are a sales intelligence assistant. You read raw memory from past prospect
interactions and return a structured JSON pre-call brief for a sales rep.

Deal health scoring guide:
- 0-20: Cold. No engagement, no signals, or long silence.
- 21-40: Warming up. Early interest but objections unresolved.
- 41-60: Engaged. Active conversations, some positive signals.
- 61-80: Hot. Strong signals, near decision stage.
- 81-100: Closing. Verbal commitment or trial agreed.

Return ONLY valid JSON. No explanation. No markdown. No code fences.

The LLM returns a structured object: score, label (Cold / Warming up / Engaged / Hot / At risk), momentum (improving / stalling / declining), risk (single biggest risk in plain English), recommended_action, and confidence (based on how much memory exists).

Critically: the score automatically improves as more memory accumulates. After one interaction, confidence is low and the score reflects limited data. After four interactions, the LLM has seen objections, resolutions, competitor mentions, and deal progression — and the score reflects that. No developer had to build a recalculation system. It just works because the memory compounds.

Here’s a real example. Priya Sharma, VP Sales at Rentokil, after four logged interactions:

Score: 70/100
Label: Engaged
Momentum: ↑ Improving
Risk: "Data migration concerns may stall the deal"
Recommended action: "Provide a detailed data migration plan and timeline"
Confidence: Medium

Compare that to what the brief looks like for a prospect with zero interactions: “No prior context found. Approach as a first contact.” Same system, same code — the intelligence difference is entirely a function of accumulated memory.

The Weekly Digest: One Prompt, Five Prospects

The digest endpoint does something non-obvious.

Instead of making five separate recall calls and then trying to merge and compare the results, we pull memory for all prospects in a single pass, build one large prompt containing all contexts, and ask the LLM to categorise and prioritise everything simultaneously:

python
async def generate_digest() -> dict:
    all_prospects = get_all_prospects()
    prospect_contexts = []

    for name in all_prospects:
        recalled = recall_prospect(name)
        if recalled:
            prospect_contexts.append({"name": name, "context": recalled})

    prompt = build_digest_prompt(prospect_contexts)
    response = groq_client.chat.completions.create(
        model="llama-3.3-70b-versatile",
        messages=[
            {"role": "system", "content": DIGEST_SYSTEM_PROMPT},
            {"role": "user", "content": prompt}
        ],
        temperature=0.3,
        max_tokens=1200
    )
    return json.loads(response.choices[0].message.content.strip())

The LLM returns a prioritised action plan sorted into three buckets: 🔴 Needs attention now, 🟡 Follow up this week, 🟢 On track.

Why one call instead of five? The LLM reasons better when it can compare prospects it can see simultaneously. James Okafor hasn’t responded in seven days after we sent him a security report — that surfaces as “needs attention now” because the LLM can see both that there’s been recent positive momentum and that the silence is abnormal relative to his prior cadence. Five separate calls wouldn’t give it that context.

It also cuts latency by roughly 4x and eliminates a merge step that would require its own logic to get right.

What This Taught Us

Semantic memory is not keyword search. The difference matters when reps log notes informally. “She pushed back on timing” and “onboarding timeline is a concern” both surface for the same recall query because Hindsight retrieves by meaning, not string match. This makes the system usable in the way humans actually communicate.

The prompt is the product. The quality of the deal health score is entirely determined by the scoring rubric in the system prompt. Vague instructions (“rate this deal”) produce vague, inconsistent scores. Specific rubrics with deduction/addition logic produce scores that match human intuition. We spent more time on the scoring prompt than on any other single piece of code.

Metadata is what makes retrieval trustworthy. Storing {"prospect": name, "outcome": outcome, "timestamp": timestamp} alongside the content means the memory timeline filters by prospect reliably. Without metadata, you’re doing full semantic search on everything and hoping the right things surface. The structure you add at storage time is what you get back at retrieval time.

One LLM call beats five. We initially prototyped the digest as five separate calls with a merge step. The output was coherent per-prospect but missed cross-prospect comparisons the LLM could make when it saw everything at once. Collapsing to one call improved output quality, cut latency, and simplified the code.

The “no CRM update required” framing is what makes it sell. Every salesperson has been told to keep the CRM updated. Nobody does. Our system works with informal, unstructured notes because Hindsight stores and recalls semantic meaning, not structured fields. “She seemed unsure about the onboarding” is valid input. The bar for logging is low enough that reps actually use it.

**

What We’d Do Differently

**

The no-database choice works for the workflows we built. It would break immediately if we needed aggregate reporting — total deal value by stage, close rate by rep, pipeline velocity. For a production system serving a real sales team, you’d probably want Hindsight as the semantic memory layer alongside a lightweight analytics store for structured metrics. They’re not mutually exclusive.

The scoring prompt also needs human calibration at scale. Our rubric produces sensible scores for the five prospects we tested it against. Whether it holds across fifty different industries, deal sizes, and sales styles is an open question.

The thing that surprised me most building this: the memory layer isn’t a feature. It’s the entire product. Without persistent memory across sessions, every brief is generic and every digest is meaningless. With it, the agent genuinely gets smarter over time in the way a good sales manager does — by remembering what happened and reasoning about what it means.

That’s the gap SalesMemory fills. Not smarter AI. Just AI that remembers.

Check out the Hindsight documentation if you want to build something similar — the Python SDK is straightforward and the retain/recall pattern maps cleanly to most agent use cases.

• Book: Decoupled PHP — Clean and Hexagonal Architecture for Applications That Outlive the Framework
• Also by me: Database Playbook: Choosing the Right Store for Every System You Build
• My project: Hermes IDE | GitHub — an IDE for developers who ship with Claude Code and other AI coding tools
• Me: xgabriel.com | GitHub

Open any long-running Laravel or Symfony codebase. Search for the word Repository. Pick the first hit your editor opens. Odds are good that the file has a method called getUsersAsArray(), a parameter typed array $filters, and a return type of array populated with stdClass rows. It lives in AppRepositories, is registered in the container, and gets called “the user repository” in code review.

It is not a repository. It is a DAO wearing a Repository sticker.

That mislabel is the single most common architectural lie in PHP. It looks harmless because the class works — rows come out, rows go in, tests pass against a fixture. The damage shows up later. You try to swap a database. A use case has to import three vendor types to ask a business question. A new hire reads findUsersBy(['status' => 'active', 'order_by' => 'created_at DESC', 'limit' => 10]) and asks where the domain went. The answer is that it never got written down. The schema is the domain.

This post draws the line in PHP 8.3 terms: a real Repository, a DAO, and the test that separates them.

A DAO that calls itself a Repository

Here is the file you have already seen, in some form, in every project you have inherited.

<?php

declare(strict_types=1);

namespace AppRepositories;

use IlluminateSupportFacadesDB;

final class UserRepository
{
    /** @return array<int, stdClass> */
    public function findAll(array $filters = [], int $limit = 50, int $offset = 0): array
    {
        $query = DB::table('users');

        if (isset($filters['status'])) {
            $query->where('status', $filters['status']);
        }
        if (isset($filters['email_like'])) {
            $query->where('email', 'like', '%' . $filters['email_like'] . '%');
        }

        return $query
            ->orderBy('created_at', 'desc')
            ->limit($limit)
            ->offset($offset)
            ->get()
            ->all();
    }

    public function findById(int $id): ?stdClass
    {
        return DB::table('users')->where('id', $id)->first();
    }

    public function insert(array $row): int
    {
        return (int) DB::table('users')->insertGetId($row);
    }

    public function updateById(int $id, array $changes): int
    {
        return DB::table('users')->where('id', $id)->update($changes);
    }

    public function deleteById(int $id): int
    {
        return DB::table('users')->where('id', $id)->delete();
    }
}

Look at the signatures. The return type is array<int, stdClass>. The parameters are array $filters and a pair of pagination integers. The method names are findAll, insert, updateById, deleteById — the four CRUD verbs the database already knows. The $filters map keys are column names. If you renamed the status column tomorrow, every caller would break. The breakage would be invisible to the type checker, because the contract is “an array, maybe with these keys.”

Two things tell you this is a DAO:

1. It returns rows, not objects. A stdClass from DB::table('users')->first() is a row with a created_at field that is a string, an email_verified_at that might be null or a Carbon instance depending on which day Laravel won that argument, and no behavior. The caller has to know the column names. The caller now depends on the schema.
2. Its method names are storage verbs. insert, update, delete are SQL statements. They describe what the database does, not what the business asks for. There is no register, no verifyEmail, no suspend, no reactivateAfterPayment. The class has no opinion about the business because it doesn’t speak the business’s language.

This is fine as a layer. DAOs are useful. They are thin, predictable wrappers around SQL, and they have a place in real systems. The problem is the sticker. Once you call this class UserRepository and register it in the container under the same name, the team stops looking for the actual repository. There isn’t one. The use cases reach into this DAO directly, the controllers reach into it through a service that wraps two calls. The test suite mocks it with vendor types and column-name strings, so the tests end up coupled to the schema too.

A useful sanity check: imagine deleting every line of Eloquent, every DB::table call, every Doctrine type from the project. Does the interface you defined still describe something the business cares about? If yes, you have a port. If the file collapses into nonsense because its signatures are full of array, stdClass, and column names, you have a DAO that answered a database question instead of a domain one.

The same class, rewritten as a Repository

Now the version that earns the name. The starting point is a domain object that has nothing to do with persistence.

<?php

declare(strict_types=1);

namespace AppDomainUser;

final readonly class UserId
{
    public function __construct(public string $value) {}
}

final class User
{
    public function __construct(
        public readonly UserId $id,
        public readonly EmailAddress $email,
        private UserStatus $status,
        public readonly DateTimeImmutable $registeredAt,
    ) {}

    public function status(): UserStatus
    {
        return $this->status;
    }

    public function suspend(SuspensionReason $reason): void
    {
        if ($this->status === UserStatus::Suspended) {
            throw new DomainException('User already suspended');
        }
        $this->status = UserStatus::Suspended;
    }

    public function reactivate(): void
    {
        if ($this->status !== UserStatus::Suspended) {
            throw new DomainException('Only suspended users can reactivate');
        }
        $this->status = UserStatus::Active;
    }
}

A User knows how to be suspended and how to be reactivated. It does not know how to be saved. That separation is the whole point.

The port lives next to the entity, in the same namespace.

<?php

declare(strict_types=1);

namespace AppDomainUser;

interface UserRepository
{
    public function find(UserId $id): ?User;

    public function save(User $user): void;

    public function ofEmail(EmailAddress $email): ?User;

    /** @return list<User> */
    public function suspendedSince(DateTimeImmutable $cutoff): array;
}

Four methods. Each one is a sentence a product manager would say out loud. Find this user. Save this user. Get me the user with this email. Give me the users who have been suspended since this date. No SQL hides in the signatures, no pagination knobs that have not yet earned their way in, no filter map standing in for the schema. The return types are User, ?User, list<User>: domain objects, not rows.

find() returns ?User and not a thrown exception. The caller, which is a use case, is in a better position to decide what missing means. It can throw UserNotFound, return a 404 from a controller, or take a different branch. The repository reports; the use case interprets.

The Doctrine adapter is where SQL is finally allowed to exist.

<?php

declare(strict_types=1);

namespace AppInfrastructurePersistenceDoctrine;

use AppDomainUserEmailAddress;
use AppDomainUserUser;
use AppDomainUserUserId;
use AppDomainUserUserRepository;
use DoctrineDBALConnection;

final class DoctrineUserRepository implements UserRepository
{
    public function __construct(private readonly Connection $db) {}

    public function find(UserId $id): ?User
    {
        $row = $this->db->fetchAssociative(
            'SELECT id, email, status, registered_at
             FROM users WHERE id = :id',
            ['id' => $id->value],
        );
        return $row === false ? null : UserMapper::fromRow($row);
    }

    public function save(User $user): void
    {
        $this->db->executeStatement(
            'INSERT INTO users (id, email, status, registered_at)
             VALUES (:id, :email, :status, :registered_at)
             ON CONFLICT (id) DO UPDATE SET
                 email = EXCLUDED.email,
                 status = EXCLUDED.status',
            UserMapper::toRow($user),
        );
    }

    public function ofEmail(EmailAddress $email): ?User
    {
        $row = $this->db->fetchAssociative(
            'SELECT id, email, status, registered_at
             FROM users WHERE email = :email',
            ['email' => $email->value],
        );
        return $row === false ? null : UserMapper::fromRow($row);
    }

    public function suspendedSince(DateTimeImmutable $cutoff): array
    {
        $rows = $this->db->fetchAllAssociative(
            'SELECT id, email, status, registered_at
             FROM users
             WHERE status = :status AND suspended_at >= :cutoff',
            ['status' => 'suspended', 'cutoff' => $cutoff->format('c')],
        );
        return array_map(UserMapper::fromRow(...), $rows);
    }
}

The class implements the port. It owns the SQL, owns the mapping from rows to User, and is the only file that imports a Doctrine type. Swap PostgreSQL for DynamoDB and only this file changes. The domain doesn’t move.

UserMapper is the boring half of the contract — it takes a row and returns a User, or takes a User and returns an array shaped like the table. It is the one place in the system that knows column names. Every other consumer of User talks to the domain object.

What changes for the caller

The use case that was previously calling UserRepository::insert(['email' => ..., 'status' => 'active']) is now writing PHP, not SQL.

<?php

declare(strict_types=1);

namespace AppApplicationUser;

use AppDomainUserEmailAddress;
use AppDomainUserSuspensionReason;
use AppDomainUserUserNotFound;
use AppDomainUserUserRepository;

final readonly class SuspendUser
{
    public function __construct(private UserRepository $users) {}

    public function execute(SuspendUserInput $input): SuspendUserOutput
    {
        $user = $this->users->ofEmail(new EmailAddress($input->email))
            ?? throw new UserNotFound($input->email);

        $user->suspend(new SuspensionReason($input->reason));
        $this->users->save($user);

        return new SuspendUserOutput($user->id->value, $user->status()->value);
    }
}

Notice what is not here. No DB::transaction(function () use (...) { ... }). No $user->update(['status' => 'suspended']). No mention of a column. The use case asks the domain to suspend itself, asks the repository to save the result, and returns a DTO. Next year’s storage migration leaves this file untouched.

The test for this use case is also smaller, because the fake repository is an in-memory array-backed class that implements the port. No mock framework. No expects($this->once())->method('update'). No fixture rows with twelve columns. The fake speaks domain, so the test speaks domain.

<?php

final class InMemoryUserRepository implements UserRepository
{
    /** @var array<string, User> */
    private array $byId = [];

    public function find(UserId $id): ?User
    {
        return $this->byId[$id->value] ?? null;
    }

    public function save(User $user): void
    {
        $this->byId[$user->id->value] = $user;
    }

    public function ofEmail(EmailAddress $email): ?User
    {
        foreach ($this->byId as $u) {
            if ($u->email->equals($email)) {
                return $u;
            }
        }
        return null;
    }

    public function suspendedSince(DateTimeImmutable $cutoff): array
    {
        return array_values(array_filter(
            $this->byId,
            fn (User $u) => $u->status() === UserStatus::Suspended,
        ));
    }
}

Twenty-three lines. No container, no migrations, no RefreshDatabase trait, no SQLite-in-memory. The use case is exercised against a real repository with fake storage, which is the actual point of the abstraction.

The checklist for telling them apart

When you open a class labelled Repository, run through this list. If three or more apply, the file is a DAO and the label is a lie.

• The method signatures contain array, stdClass, or a vendor type (Builder, EntityManager, Model, Connection, Collection).
• The parameters include a $filters map keyed by column names.
• The method names are CRUD verbs: insert, update, delete, findAll, findBy<ColumnName>.
• Pagination is in the port’s signature (int $limit, int $offset) rather than in a dedicated read-side projection.
• The docblock describes columns rather than business concepts.
• Deleting every Eloquent or Doctrine import from the file leaves it nonsensical.
• The file lives under AppRepositories and the project has zero files under AppDomain.

DAOs earn their keep in admin tooling, batch jobs, internal reports, and ad-hoc queries where the cost of a domain object is genuine overhead. Keep them, name them honestly (UserDao, UserQueries, UserDbGateway), and stop pretending they sit inside the hexagon. A Repository is a port; a DAO is an adapter that exposes itself directly. Putting one where the other belongs is how teams convince themselves they have Hexagonal Architecture when what they have is database/sql with a coat of paint.

Where to draw the line

The shortest version of the rule: a Repository returns and accepts domain objects, lives next to the entity it serves, and has method names a product manager would recognize. Everything else is a DAO, a query object, or a read-side projection. Each has its own name and home.

When you find a class that fails the checklist, you have three reasonable moves. Rename it to UserDao and live with the honesty. Wrap it in a real UserRepository port that returns domain objects, and let the use cases stop importing Eloquent. Or, if the system is small enough that a domain object is overkill, leave the DAO where it is and accept that this slice of the app is CRUD, not architecture. All three are fine. Mislabelling the DAO as a Repository is the one move that costs you something every week and gives back nothing.

If this was useful

The Repository-versus-DAO line is one of the four anti-patterns Decoupled PHP tears down chapter by chapter — alongside service-layer dumping grounds, ActiveRecord-as-domain, and anemic-domains-with-fat-services. The book walks the same shape from a one-port checkout up to a full production application with Doctrine, Symfony Messenger, and Guzzle all kept behind ports they don’t get to define. If you want the storage-strategy side of this argument with less PHP and more decision-making, Database Playbook is the companion.

Available on Kindle, Paperback, and Hardcover. English, German, and Japanese editions out now — Portuguese and Spanish coming soon.

By Simeon Griggs

Multi-tenancy is a term used across various kinds of technical infrastructure, including application hosting, compute, databases, and more.

For example, you may purchase cloud services from a provider, but your account is one of many that draws from a common pool of resources. Your account is one “tenant” in a multi-tenant infrastructure.

In this article, we’re focusing on using a single Postgres database cluster to serve an application with many tenants—you are our customer, and your customers are tenants in that cluster.

Note: PlanetScale databases are, by default, multi-tenant within our infrastructure. Single-tenant resources are available on Enterprise. See the deployment options documentation for more details.

Given the many approaches to multi-tenancy within a Postgres database, it is worth clarifying the recommended best practices and the data models you should avoid. These recommendations are informed by years of seeing multi-tenant applications, both good and bad, succeed and fail at scale.

Definitions

The term “database” is overloaded and can refer to different things:

• A Database Cluster refers to the entire database server instance – the running Postgres process, its storage and any replicas.
• A Logical Database is an isolated namespace within a database cluster that contains its own schemas, tables, and data.

When you generate credentials to connect to a database, you’re connecting to the database cluster. The queries you perform will target a single logical database within it. On PlanetScale Postgres, the default logical database name is postgres.

In short: one database cluster can contain many logical databases.

When modeling data in a relational database:

• A Tenant refers to a single entity that accesses their own subset of data in your application.
• Single-tenancy refers to giving each tenant their own isolated schema, logical database, or database cluster.
• Multi-tenancy refers to using a consistent schema (set of tables and relationships) for all of the users of your application within a single database cluster.

Three approaches to tenant isolation

There are three common approaches to separating tenant data within a single database cluster:

1. Row-level isolation where each tenant’s data is isolated by a column value such as tenant_id
2. Schema-per-tenant where each tenant has its own schema and tables
3. Database-per-tenant where each tenant has its own logical database, schema, and tables

Of the three approaches, row-level isolation is the most common and is our recommended approach.

Row-level isolation is also the only true method of “multi-tenancy” in a relational database. Schema-per-tenant and database-per-tenant within the same database cluster do not share tables, but they do share resources.

Finally, you may already be running a database using one schema-per-tenant. You may be able to migrate to a recommended approach to improve the performance of your application and workloads. See Migrating to row-level multi-tenancy.

Good examples for multi-tenancy

Good examples of multi-tenancy include SaaS applications that need to isolate data for each customer but have so many customers that it would be impractical to assign each customer to an individual database cluster. Or multi-national applications that need to isolate data for each country, market, or region.

These are good use cases for multi-tenancy because only the data is different between tenants. The schema, tables, relationships, application code and access patterns are uniform across all tenants.

With any multi-tenancy approach, your goal should be for data belonging to each tenant to be consumed by the same applications, with care to ensure that one tenant cannot query another tenant’s data nor that their behavior in your application could jeopardize the experience of another tenant.

Note: These recommendations assume all tenants share the same schema. If tenants genuinely need different schema structures, schema-per-tenant or database-per-tenant is the better fit.

Row-level isolation

Recommended. This is the most common, general-purpose method for combining tenants in a single database.

• All data is stored in a single database cluster
• All tenants share the same schema and tables
• Each tenant’s data is isolated with a column such as tenant_id

With row-level isolation, each tenant shares the same schema and tables, but has its own data.

This is the simplest model conceptually and the most scalable approach to multi-tenancy.

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    customer_name TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    total NUMERIC
);

-- tenant_id should lead most indexes
CREATE INDEX idx_orders_tenant_created ON orders(tenant_id, created_at DESC);

-- Insert data for different tenants into the same table
INSERT INTO orders (tenant_id, customer_name, total) VALUES (1, 'Alice', 49.99);
INSERT INTO orders (tenant_id, customer_name, total) VALUES (2, 'Hans', 59.99);

-- Every query must filter by tenant
SELECT * FROM orders WHERE tenant_id = 1;

Depending on the size of your tables, row-level isolation can easily scale to many thousands of tenants. Migrations and schema changes need only be applied to a single table to update all tenants. Querying across tenants is simple and efficient.

Modeling tenants

In most multi-tenant applications, tenants have metadata beyond just an ID — a name, a region, etc. A dedicated tenants table gives you a place to store this and lets the tenant_id column across your schema remain a compact, performant BIGINT foreign key.

CREATE TABLE tenants (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    code VARCHAR(2) UNIQUE NOT NULL, -- 'uk', 'de'
    name TEXT NOT NULL               -- 'United Kingdom', 'Germany'
);

Using a BIGINT for tenant_id is preferred over text-based identifiers. A BIGINT is faster to compare than a string and is a stable identifier that won’t need to change if a tenant rebrands or a region code is restructured.

The column name tenant_id is a common one, but not a required naming convention. For example, a social media application may use the column user_id for the same purpose.

Enforcing tenant filtering

The inherent risk of row-level isolation is that every query must include WHERE tenant_id = ?. Rather than relying on each query to add this manually, use ORM global scopes, middleware, or a shared data access layer to inject the tenant filter automatically.

Postgres also offers Row-Level Security (RLS) as an optional, additional layer of defense. RLS automatically appends a filter to every query on a table based on a session variable. In the example below, RLS ensures that queries are scoped to the current tenant without relying on the application to include the filter.

-- Create a non-superuser role for the application
CREATE ROLE app_user LOGIN PASSWORD 'secret';
GRANT SELECT, INSERT, UPDATE, DELETE ON orders TO app_user;

-- Enable RLS and define the policy
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
    USING (tenant_id = current_setting('app.current_tenant')::BIGINT);

-- At runtime, your app sets the tenant context per request
BEGIN;
SET LOCAL app.current_tenant = '1';
-- SET LOCAL ensures the setting is scoped to this transaction
-- which is important when using connection pooling.
-- Only returns orders for tenant_id = 1

SELECT * FROM orders;
COMMIT;

We generally don’t recommend relying on RLS. It shifts security logic into the database, where policy misconfiguration, silent failures, and connection pooling interactions are difficult to debug. Keep tenant isolation enforced in your application code.

Partitioning

With all data stored in a single table, as your database scales and your tenant count grows, row-level isolation can be further optimized by partitioning the table. The tenant_id column, which is used to partition the data, is an ideal partition key.

Partitioning is a Postgres feature that splits a single logical table into multiple sub-tables based on a column value. Your application queries don’t need to target a specific partition, as Postgres will automatically route the query to the correct one.

In practice, you would only partition tables that grow large enough to benefit from it. A messages table with billions of rows is a strong candidate for partitioning by tenant, but not a small reference table like office_locations with only thousands of rows.

Note that Postgres requires the partition key to be part of the primary key on partitioned tables.

-- Create a partitioned table
CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    tenant_id BIGINT NOT NULL,
    customer_name TEXT,
    total NUMERIC,
    PRIMARY KEY (tenant_id, id)
) PARTITION BY LIST (tenant_id);

-- Create a partition for each tenant
-- All rows with tenant_id=1 (UK) go into 'orders_tenant_1'
CREATE TABLE orders_tenant_1 PARTITION OF orders FOR VALUES IN (1);
-- All rows with tenant_id=2 (DE) go into 'orders_tenant_2'
CREATE TABLE orders_tenant_2 PARTITION OF orders FOR VALUES IN (2);

-- Your application doesn't know or care about partitions
INSERT INTO orders (tenant_id, customer_name, total) VALUES (1, 'Alice', 49.99);
-- Postgres automatically routes this to orders_tenant_1

SELECT * FROM orders WHERE tenant_id = 1;
-- Postgres only scans orders_tenant_1 (partition pruning)

Partitioning can greatly improve performance and scalability by reducing the amount of data that needs to be scanned and the size of indexes. Internal processes such as vacuuming and index maintenance are also performed on a per-partition basis.

This adds to operational overhead, as you will need to create a new partition for each tenant.

Row-level isolation with partitioning offers some of the benefits of database-per-tenant multi-tenancy with lower operational overhead.

Tenant data lifecycle

With partitioning, onboarding each new tenant requires creating a new partition.

Partitioning simplifies offboarding tenants: you can drop the partition, and all data for that tenant is deleted.

-- Wrap in a transaction in case the DROP fails
BEGIN;
ALTER TABLE orders DETACH PARTITION orders_tenant_1;
DROP TABLE orders_tenant_1;
COMMIT;

Without partitioning, a new tenant’s data can be inserted into a table with no schema changes or migrations.

However, removing tenants requires doing table-level delete operations, which can generate a significant number of dead tuples and increase vacuum pressure.

DELETE FROM orders WHERE tenant_id = 1;

Schema-per-tenant

Generally not recommended. Schema-per-tenant has a few benefits but does not work well at scale.

• All data is stored in a single database cluster
• Each tenant has its own schema and tables
• Each tenant’s schema and data are isolated by the schema name as a prefix to the table name

The appeal of this approach is greater isolation, since your queries do not need to filter on a specified tenant_id column. Instead, your application can reuse the same queries but with a different search_path to target the correct tenant’s data.

-- Create the schemas
CREATE SCHEMA uk;
CREATE SCHEMA de;

-- Each gets identical tables
CREATE TABLE uk.orders (id BIGINT PRIMARY KEY, customer_name TEXT, total NUMERIC);
CREATE TABLE de.orders (id BIGINT PRIMARY KEY, customer_name TEXT, total NUMERIC);

-- At runtime, your app sets the search path per request
BEGIN;
SET LOCAL search_path TO uk;
SELECT * FROM orders;  -- returns uk.orders data
COMMIT;

BEGIN;
SET LOCAL search_path TO de;
SELECT * FROM orders;  -- now returns de.orders data
COMMIT;

There are performance benefits to using a schema-per-tenant. With each table containing fewer rows, indexes are smaller and more likely to fit in the buffer cache. One tenant’s update/delete churn will not increase another tenant’s bloat or vacuum workload.

However, the operational overhead of maintaining a schema-per-tenant outweighs the performance benefits. It increases schema migration complexity because they need to be applied to each tenant’s schema. Should you need to query across tenants, complex cross-schema joins will be required.

While this approach works, it likely won’t scale beyond a few hundred tenants. Every table, index, constraint, and sequence across all schemas lives in shared system catalogs. With hundreds of schemas, each containing even a modest number of tables and their indexes, these catalogs grow into millions of rows. This slows the query planner as it consults the catalog on every query. Migrations slow down as the catalog size increases.

Safety concerns of SET search_path

There is no database-level enforcement of preventing access to the wrong schema. Schema-per-tenant feels like greater separation of data, but it does not meaningfully impact data isolation from a security perspective. You may also need to create a separate database user and set up precise schema-level permissions for better security.

Tenant data lifecycle

Onboarding new tenants requires creating a new schema for the tenant and performing a migration.

Removing tenants from a schema-per-tenant configuration may be one of the few operational advantages of this approach to multi-tenancy, as it is a single, simple operation.

DROP SCHEMA uk CASCADE;

Database-per-tenant

Generally not recommended. Database-per-tenant has a few benefits but is at odds with the connection model of Postgres.

• All data is stored in a single database cluster
• Each tenant has its own logical database, schema, and tables
• Each tenant’s data is isolated by the logical database name

Within a PlanetScale Postgres database, you have the option to run CREATE DATABASE to create many logical databases within a single database cluster.

The appeal of using logical databases per tenant is increased isolation: you do not need to filter by a column or modify search_path; instead, you can modify the connection string to connect to the correct database. This makes working with the data and schema of an individual tenant much simpler.

-- Create separate databases
CREATE DATABASE uk_store;
CREATE DATABASE de_store;

-- Connect to the UK database and create tables there
c uk_store
CREATE TABLE orders (id BIGINT PRIMARY KEY, customer_name TEXT, total NUMERIC);

-- Connect to the German database and do the same
c de_store
CREATE TABLE orders (id BIGINT PRIMARY KEY, customer_name TEXT, total NUMERIC);

There are notable performance benefits to using a database-per-tenant. With each table in each database containing fewer rows, indexes are smaller and more likely to fit in the buffer cache. One tenant’s update/delete churn will not increase another tenant’s bloat or vacuum workload.

Database-per-tenant is better for performance than a schema-per-tenant, as each database contains its own catalog of tables, indexes, constraints, and sequences.

However, these performance benefits are still outweighed by the drawbacks of increased operational complexity. Critically, connection pooling becomes a problem immediately, as PgBouncer pools are calculated per-database and will quickly exceed your max_connections limit. Connection limits are the primary issue with database-per-tenant multi-tenancy.

Additionally, each CREATE DATABASE copies Postgres’s template database, consuming roughly 8 MB. Unlike schema-per-tenant, where all schemas share a single set of system catalogs, every logical database carries its own, multiplying storage and catalog maintenance overhead with each new tenant.

While all the isolation and performance benefits of a database-per-tenant are compelling, it conflicts with Postgres’s connection model.

Additionally, if you need to query across tenants, there is no way to do so in Postgres. You would need to use an external data warehouse or a custom application layer to join the data together.

While this approach works, it likely won’t scale beyond a few hundred tenants.

Security considerations

Of all the multi-tenancy approaches, the database-per-tenant approach is the most isolated from a security perspective. Each tenant has its own logical database, schema, and tables. Each tenant’s data can be accessed only by a user with privileges to that database and schema.

Even so, the limitations on connectivity and the operational complexity of this model make it difficult to recommend.

Tenant data lifecycle

Every new tenant requires a new logical database to be created and a migration to set up its tables.

Removing tenants from a database-per-tenant configuration may be one of the few operational advantages of this approach to multi-tenancy, as it is a single, simple operation with no side effects.

DROP DATABASE uk_store;

Protecting tenants from each other

In all three approaches to multi-tenancy, tenants must be protected from one another, both in terms of data access and resource contention.

Our recommended approach, row-level isolation, is the most exposed because tables and indexes are shared. Care must be taken here to keep things safely isolated. Schema- and database-per-tenant approaches are more isolated at the relation level, but all three compete for CPU, memory, disk I/O, and connections.

One tenant running an expensive query degrades performance for all other tenants, commonly referred to as a “noisy neighbor” problem. Within your database, you can add some protection by setting statement_timeout and idle_in_transaction_session_timeout appropriately. Your application should also be aware of potential rate limits, which could allow one tenant to disrupt another tenant’s experience.

PlanetScale Query Insights can help you identify and troubleshoot performance issues within your database, which you can debug manually or with an Agent using the PlanetScale MCP server.

Migrating to row-level multi-tenancy

Should your application already be configured for schema, database, or some other kind of multi-tenancy, you may be able to migrate to row-level multi-tenancy by adding a tenant_id column to your tables and updating your application to filter by this column.

If you are not yet on PlanetScale, we have successfully migrated large, multi-tenant workloads that were experiencing operational, performance, or scaling issues. We offer hands-on assistance on a case-by-case basis.

Reach out to discuss your current situation.

Other examples for multi-tenancy

For needs that are less than mission critical, such as internal applications and side projects, you may diverge from the recommendations in this post. For example, you might like to run distinct applications from a single database cluster, as it seems cheaper or operationally advantageous.

If your multiple “tenants” are actually different applications with unique data structures running from a single database, we simply ask you to exercise caution.

If you can’t behave, be careful.

From Concept to Code

In Part 1 of this series, I explained that everything on Solana is an account with five fields: lamports, data, owner, executable, and rent_epoch.

That’s the theory. But theory only sticks when you run it against real data.

So I built a command-line account explorer that queries any Solana address and prints all five fields in a readable format. In this post, I’ll walk you through how I built it, what I learned from the output, and the three things that genuinely surprised me when I started querying real accounts.

By the end, you’ll have a tool you can run against any address on the network — and a much sharper intuition for what’s actually living on-chain.

What We’re Building

A CLI tool that takes any Solana address and returns:

• Balance in both SOL and lamports
• Owner program (with friendly names for known programs)
• Whether it’s executable or a data account
• Data size and a hex preview of the first bytes
• Rent epoch

Here’s what the output looks like against three different account types:

A wallet account:

The Token Program:

The System Program itself:

Same five fields. Three completely different accounts. Let’s build it.

Setup

mkdir solana-account-explorer
cd solana-account-explorer
npm init -y
npm install @solana/kit

Create explorer.mjs — we use .mjs for ES module syntax with top-level await.

The Full Code

import { createSolanaRpc } from "@solana/kit";

// Known program addresses mapped to friendly names
const KNOWN_PROGRAMS = {
  "11111111111111111111111111111111": "System Program",
  "TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA": "Token Program",
  "TokenzQdsByhw9vBi3wq3kQtj78oYCa9PzPgcGW8K3": "Token Program (SPL-2022)",
  "ATokenGPvbdGVqstVQmcLsNZAqeEoctVrGMMXuLYDJ": "Associated Token Program",
  "9xQeWvG816bUx9EPjHmaT23yfBYP5ihLeLSKcsFqrXa": "Raydium Program",
  "JUP4Fb2cqiRUcaTHdrPC8h2gNsszFildan3xnjnZALn": "Jupiter Aggregator",
  "DezXAZ8z7PnrnRJjz3wXBoRgixVpdZuvXAfqJ7DKPs1": "Magic Eden",
  "BPFLoaderUpgradeab1e11111111111111111111111": "BPF Loader (Program Upgrades)",
  "BPFLoader2111111111111111111111111111111111": "BPF Loader",
};

// Get address from command line argument
const address = process.argv[2];

if (!address) {
  console.error("❌ Error: No address provided!");
  console.error("Usage: node explorer.mjs <solana-address>");
  process.exit(1);
}

// Connect to devnet
const rpc = createSolanaRpc("https://api.devnet.solana.com");

async function exploreAccount() {
  try {
    console.log("n🔍 Solana Account Explorern");
    console.log("━".repeat(60));

    // Fetch balance and account info in parallel
    const [balance, accountInfo] = await Promise.all([
      rpc.getBalance(address).send(),
      rpc.getAccountInfo(address).send(),
    ]);

    // Convert lamports to SOL
    const solBalance = (Number(balance.value) / 1e9).toFixed(9);
    console.log(`n📍 Address:n   ${address}n`);
    console.log(`💰 Balance:n   ${solBalance} SOL (${balance.value} lamports)n`);

    if (!accountInfo.value) {
      console.log("ℹ️  This address does not exist on the blockchain yet.n");
      console.log("━".repeat(60) + "n");
      return;
    }

    // Resolve owner to friendly name if known
    const owner = accountInfo.value.owner;
    const ownerName = KNOWN_PROGRAMS[owner] || owner;
    console.log(`👤 Owner:n   ${ownerName}n`);

    // Executable flag
    const isExecutable = accountInfo.value.executable;
    console.log(
      `⚙️  Executable:n   ${isExecutable ? "Yes (Program Account)" : "No (Data/Wallet Account)"}n`
    );

    // Rent epoch
    console.log(`📅 Rent Epoch:n   ${accountInfo.value.rentEpoch}n`);

    // Data size and hex preview
    const dataLength = accountInfo.value.data.length;
    console.log(`📦 Data:n   Size: ${dataLength} bytesn`);

    if (dataLength > 0) {
      const displaySize = Math.min(64, dataLength);
      const dataSample = Buffer.from(accountInfo.value.data)
        .slice(0, displaySize)
        .toString("hex");
      console.log(`   First ${displaySize} bytes (hex):`);
      console.log(`   ${dataSample}`);
      if (dataLength > displaySize) {
        console.log(`   ... (${dataLength - displaySize} more bytes omitted)`);
      }
    } else {
      console.log(`   (empty)n`);
    }

    console.log("n" + "━".repeat(60) + "n");
  } catch (error) {
    console.error("❌ Error exploring account:");
    console.error(error.message);
    process.exit(1);
  }
}

exploreAccount();

Run it against any address:

# Query the System Program
node explorer.mjs 11111111111111111111111111111111

# Query the Token Program
node explorer.mjs TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA

# Query your own wallet
node explorer.mjs <your-wallet-address>

What the Output Taught Me

The System Program owns itself — sort of

When I queried 11111111111111111111111111111111, I expected to see the System Program as its own owner. Instead I got NativeLoader.

That threw me until I understood: NativeLoader is the Solana runtime’s built-in loader. It owns the System Program the same way a kernel owns its own system calls. The System Program is baked into the runtime at a level below the normal account ownership model. It’s not a program that was deployed — it was compiled into the validator itself.

Key difference from Token Program: The Token Program is owned by BPF Loader (Program Upgrades), which means it was deployed like any other program and can be upgraded. The System Program cannot — it’s part of the protocol.

The Token Program is only 48 bytes — but controls everything

Look at the Token Program output: 48 bytes of data. That’s it. 48 bytes of what turns out to be a pointer — not the actual bytecode.

This is because the Token Program uses the upgradeable BPF loader pattern. The 48 bytes in the main account point to a separate program data account that holds the actual bytecode. The split exists so the program can be upgraded without changing its address.

This was my first real encounter with indirection on Solana — the account you call isn’t always where the code lives.

A wallet with 0 bytes of data is a complete account

The wallet output shows Size: 0 bytes and (empty) for data. In Web2 terms that looks like a broken record — a row with no content. But on Solana, a wallet doesn’t need data. Its entire purpose is to hold lamports. The owner field (System Program) and the lamports field are all it needs to function.

This is the account model in action: the structure is always the same, but some fields are intentionally empty depending on the account’s purpose.

Three Things Worth Noticing in Your Own Queries

1. Compare executable accounts vs data accounts

Run the explorer on a program address and a wallet address back to back. Notice how executable: Yes vs executable: No changes everything about what that account does — even though both have identical field structures.

2. Watch the owner field

Every account you’ll ever encounter is owned by exactly one program. Query five random addresses and look at what owns them. You’ll start recognizing patterns: wallets owned by System Program, tokens owned by Token Program, NFT metadata owned by Metaplex’s program.

3. The data field is where all the interesting stuff hides

The hex bytes in the data field aren’t random — they’re structured binary data that each program knows how to decode. That wall of hex is actually a token supply, or a price oracle, or a governance vote. Decoding that raw data is exactly what Post 3 covers.

The KNOWN_PROGRAMS Map — Why It Matters

One design choice worth explaining: the KNOWN_PROGRAMS lookup table at the top of the file.

Without it, the owner field just shows a raw base58 address like BPFLoaderUpgradeab1e11111111111111111111111. With it, you see BPF Loader (Program Upgrades) — immediately meaningful.

This is the same pattern Solana Explorer uses under the hood. As you build on Solana, you’ll develop your own mental map of which program addresses do what. This table is a starter version of that map. Add to it as you encounter new programs.

Try It Yourself

# Copy the code above into explorer.mjs, then:
npm install @solana/kit

# Try these three to see the contrast clearly:
node explorer.mjs 11111111111111111111111111111111
node explorer.mjs TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA
node explorer.mjs SysvarC1ock11111111111111111111111111111111

Each one will show you the same five fields, completely different values. That contrast is worth seeing with your own eyes.

What’s Next

This explorer shows you the raw bytes in the data field — but doesn’t tell you what they mean. A token mint’s data field contains supply, decimals, and mint authority. A governance account’s data field contains vote counts and proposal state. All of it is encoded in binary.

Post 3 covers exactly that: three methods for decoding raw Solana account data, from manual buffer parsing to using Anchor’s IDL to using pre-built deserializers. That’s where the hex bytes stop being noise and start being information.

Series: The Account Model Foundation

• Part 1: The Account Model Explained
• Part 2: Building an Account Explorer (this post)
• Part 3: Decoding Account Data (coming May 20)
• Part 4: System Program Deep Dive (coming May 22)

What address did you query first? Drop it in the comments — I’d love to see what people are exploring.

Part of the 100 Days of Solana Epoch 1 Writing Challenge.

Most organizations have a data governance policy. It lives in a Confluence page. It defines who owns what data, what terms mean, and who should have access. And almost nobody follows it, because it’s not enforced where queries actually run.

A semantic layer changes that. It moves governance from a document into the query path, where every rule is applied automatically, for every user, through every tool.

Governance on Paper vs. Governance in Practice

Data governance fails when it depends on people doing the right thing manually. A policy says “Revenue means completed orders minus refunds.” An analyst writes a slightly different formula. A dashboard uses the wrong table. An AI agent invents its own definition. The governance policy exists. Nobody follows it. And the organization makes decisions on inconsistent data.

The root cause isn’t that people are careless. It’s that governance is separated from the systems people actually use to query data. Enforcement happens in a side channel — documentation, review processes, audit logs — not in the query itself.

Centralized Definitions Eliminate Conflicting Metrics

A semantic layer solves the definition problem by making the governance policy code.

Revenue isn’t a paragraph in a wiki. It’s a SQL view:

CREATE VIEW business.revenue AS
SELECT
    OrderDate,
    Region,
    SUM(OrderTotal) AS Revenue
FROM silver.orders_enriched
WHERE Status = 'completed' AND Refunded = FALSE
GROUP BY OrderDate, Region;

Every dashboard, notebook, and AI agent that needs Revenue queries this view. There’s no alternative formula to use. The semantic layer IS the governance for this metric.

When the definition changes (say, a new refund category is added), the view is updated once, and every consumer gets the new logic automatically. No rollout. No migration. No “did everyone update their dashboard?”

Access Policies Enforced at Query Time

The second governance gap: access control. Most organizations enforce security at the BI tool level. Tableau restricts who sees which dashboard. Power BI applies row-level filters. But if someone opens a SQL client and queries the underlying table directly, those filters don’t apply.

A semantic layer enforces policies at a lower level. When access control exists in the semantic layer, it applies to every query path:

Dremio implements this through Fine-Grained Access Control (FGAC): policies defined as UDFs that filter rows and mask columns based on the querying user’s role. These policies are applied at the virtual dataset (view) level. A regional manager queries business.revenue and sees only their region. A data engineer sees all regions. Same view, same SQL, different results based on identity.

This approach eliminates the “security gap” that appears when users bypass BI tools. Every route to the data flows through the semantic layer. Every route inherits the policies.

Lineage and Accountability Through Views

The layered view architecture (Bronze → Silver → Gold) that a semantic layer uses is inherently traceable. Every Gold metric traces back to its Silver business logic, which traces back to the Bronze source mapping, which traces back to raw data.

This traceability matters for compliance. When an auditor asks “Where does your Revenue number come from?”, you don’t search through dashboards and notebooks. You follow the view chain:

• gold.monthly_revenue_by_region → references silver.orders_enriched
• silver.orders_enriched → joins bronze.orders_raw with bronze.customers_raw
• bronze.orders_raw → maps to production.public.orders in PostgreSQL

Every step is documented. Every transformation is visible. The lineage isn’t reconstructed after the fact — it’s structural.

Documentation as a Governance Tool

Governance is also about discoverability. Can someone find the right dataset without messaging five people? Can they tell whether a view is production-ready or experimental?

Two mechanisms handle this in a semantic layer:

Wikis attach human-readable (and AI-readable) descriptions to tables, columns, and views. They explain what data represents, where it comes from, and any caveats. A column named cltv gets a description: “Customer Lifetime Value, calculated as total revenue from first purchase to current date, excluding refunds.”

Labels categorize data for governance workflows. A label like “PII” triggers automatic column masking. A label like “Certified” indicates the view has been reviewed and approved for production use. A label like “Deprecated” warns consumers to migrate to the replacement.

For organizations with thousands of datasets, manual documentation is impractical. Dremio’s generative AI auto-generates Wiki descriptions by sampling table data and suggests Labels based on column content. This bootstraps documentation to 70% coverage automatically. The data team fills in what the AI misses.

Certification and Change Management

Not all views are equal. A semantic layer should distinguish between views that are experimental, under review, and production-ready.

A practical certification workflow:

1. Draft: New view created by an analyst. Not yet reviewed. Labeled “Draft.”
2. Reviewed: View reviewed by the data team. Business logic validated. Documentation complete.
3. Certified: View approved for production use. Labeled “Certified.” Available in production dashboards and to AI agents.

Each Certified view should have a documented owner — the person accountable for its accuracy and freshness. When business requirements change, the owner updates the view and documentation together. Changes are reviewed before the “Certified” label is reapplied.

This workflow doesn’t require advanced tooling. Labels, Wikis, and a team agreement on the process are sufficient. What matters is that governance is visible inside the semantic layer, not tracked in a separate system.

What to Do Next

Audit your top 10 business metrics. For each one, ask three questions: Is the formula defined in one place? Is access control enforced at the query level (not just the BI tool)? Can you trace the number back to its raw source in under 60 seconds? Every “no” is a governance gap that a semantic layer closes.

Try Dremio Cloud free for 30 days

1. The Problem It Solves

Imagine you have a dataset with 100 features, for example, pixel values from 10×10 grayscale images, or 100 different sensor readings from a factory machine. Visualising the data is impossible, many features might be redundant (e.g., two sensors that always move together), and training a model on all 100 features can be slow and prone to overfitting. PCA solves the dimensionality reduction problem: it finds a small number of new features (called principal components) that capture the most important patterns in the data. Real‑world applications include compressing images, visualising high‑dimensional data in 2D/3D, speeding up other algorithms, and removing noise.

2. The Core Idea (Intuition First)

Think of a flat, elongated cloud of points on a piece of paper. If you had to draw a single line through that cloud that “explains” as much of the shape as possible, you would draw the line along the longest direction of the cloud that’s the first principal component. Then, if you had to draw a second line, perpendicular to the first, that explains the next most variation, that’s the second principal component. PCA finds these “lines of maximum variance” in your data. By keeping only the top few such directions, you can represent each data point with just two or three numbers instead of hundreds, while losing as little information as possible.

Technically, PCA looks for orthogonal directions (principal components) that maximise the variance of the projected data. It turns out these directions are the eigenvectors of the data’s covariance matrix, sorted by their eigenvalues.

3. How It Works (The Math + Logic)

Let’s walk through PCA step by step.

Step 1 – Standardise the data

PCA is sensitive to scale. We first centre (subtract mean) and often scale (divide by standard deviation) each feature.

For a data matrix

$X$

of shape

(nsamples,nfeatures)(n_{text{samples}}, n_{text{features}})(nsamples​,nfeatures​)

, we compute the mean of each feature and subtract it:

Step 2 – Compute the covariance matrix

The covariance matrix

$C$

(size

nfeatures×nfeaturesn_{text{features}} times n_{text{features}}nfeatures​×nfeatures​

) tells us how each pair of features varies together. Entry

CijC_{ij}Cij​

is the covariance between feature

$i$

and feature

$j$

.

Step 3 – Compute eigenvalues and eigenvectors

We find eigenvectors

$\mathbf{v}$

and eigenvalues

$\lambda$

that satisfy:

• Each eigenvector v is a principal component (a direction in the original feature space).
• Its corresponding eigenvalue λ is the amount of variance explained by that component.

We sort the eigenvectors by decreasing eigenvalues. The first eigenvector points in the direction of highest variance, the second is orthogonal and points in the next highest, and so on.

Step 4 – Choose the top k components

We keep only the first k eigenvectors. The value k is often chosen by looking at the “explained variance ratio”: sum of the top k eigenvalues divided by the sum of all eigenvalues. A common rule is to keep enough components to explain 95% of the total variance.

Step 5 – Project the data

Finally, we transform the original data into the new lower‑dimensional space:

where

WkW_kWk​

is a matrix whose columns are the top

$k$

eigenvectors. The new data has shape

(nsamples,k)(n_{text{samples}}, k)(nsamples​,k)

.

4. When to Use It

Use PCA when:

• You need to visualise high‑dimensional data in 2D or 3D (e.g., scatter plot of customers after PCA).
• You want to speed up another algorithm (e.g., reduce 1000 features to 50 before training a k‑NN or SVM).
• You suspect multicollinearity – many features are highly correlated. PCA decorrelates them.
• You want to remove noise – small variance components often capture noise.

Assumptions:

• PCA is linear, it only finds linear combinations of original features. If the underlying structure is highly non‑linear, autoencoders or t‑SNE may work better.
• It assumes that directions of maximum variance are “important” for your downstream task. This is often true but not guaranteed (e.g., the smallest variance direction might separate classes better).

When it fails:

• When features are not linearly related to the underlying structure (e.g., a spiral or circle pattern).
• When you need interpretability, the principal components are linear mixes of all original features, so they are not as interpretable as the original features themselves.

5. My Implementation

We’ll use the classic Iris dataset (4 features) and reduce it to 2D for visualisation. Then we’ll see how much variance is preserved.

import numpy as np
import matplotlib.pyplot as plt
from sklearn.datasets import load_iris
from sklearn.decomposition import PCA
from sklearn.preprocessing import StandardScaler

# Load data
iris = load_iris()
X, y = iris.data, iris.target
feature_names = iris.feature_names
target_names = iris.target_names

# Standardise (important for PCA)
scaler = StandardScaler()
X_scaled = scaler.fit_transform(X)

# Apply PCA
pca = PCA(n_components=2)  # reduce to 2 dimensions
X_pca = pca.fit_transform(X_scaled)

# Explained variance ratio
print(f"Explained variance ratio: {pca.explained_variance_ratio_}")
print(f"Total variance preserved: {sum(pca.explained_variance_ratio_):.2%}")
print(f"Principal components (directions in original feature space):")
for i, comp in enumerate(pca.components_):
    print(f"  PC{i+1}: {', '.join([f'{name}: {val:.2f}' for name, val in zip(feature_names, comp)])}")

# Visualise
plt.figure(figsize=(8,6))
colors = ['red', 'green', 'blue']
for target, color, label in zip(np.unique(y), colors, target_names):
    plt.scatter(X_pca[y == target, 0], X_pca[y == target, 1], 
                c=color, label=label, alpha=0.7)
plt.xlabel('First Principal Component')
plt.ylabel('Second Principal Component')
plt.title('Iris dataset after PCA (2D projection)')
plt.legend()
plt.grid(alpha=0.3)
plt.show()

Output (numerical values may vary slightly):

Explained variance ratio: [0.72962445 0.22850762]
Total variance preserved: 95.81%
Principal components (directions in original feature space):
  PC1: sepal length (cm): 0.52, sepal width (cm): -0.27, petal length (cm): 0.58, petal width (cm): 0.56
  PC2: sepal length (cm): 0.38, sepal width (cm): 0.92, petal length (cm): 0.02, petal width (cm): 0.07

The first two principal components capture 95.8% of the total variance – meaning we lose very little information by going from 4 dimensions down to 2. The scatter plot clearly separates the three species, showing PCA preserved the meaningful structure.

6. Key Takeaways

1. PCA is a linear dimensionality reduction technique that finds directions of maximum variance using eigenvectors of the covariance matrix. It is fast, deterministic, and works well for many real‑world datasets.

2. Always standardise your data before PCA otherwise features with large scales will dominate the principal components, and the results will be misleading.

3. Use the explained variance ratio to decide how many components to keep. A common choice is to keep enough to explain 90–95% of the variance. For visualisation, use 2 or 3 components and accept some information loss.