Base44 Database Best Practices: Entity Schema, Queries, and Scale

Why this matters

The data layer is where most production-readiness questions become concrete. Schema decisions you make in week one shape what's possible in year three. On Base44, the platform's data-layer opinions push you toward certain patterns and away from others. Designing with the grain produces apps that scale cleanly. Designing against the grain produces apps that hit walls and require migration to fix.

This article covers the patterns we apply on every greenfield Base44 build and the patterns we recommend when auditing existing apps that have grown into pain.

What the entity layer is

Base44 entities are a wrapped Postgres with these properties:

• Schema defined in the IDE, not as code or migrations.
• Per-entity CRUD via the SDK; no JOINs, no aggregates.
• Limited filter syntax: equality, $in, $ne, $gt, $lt, $or, $and.
• Indexing managed by the platform; not user-configurable.
• No transactions across entities.
• 5,000-record-per-list cap as of November 2025.
• Default-permissive reads; ownership and tenant filtering is your job.

Knowing this shape lets you design with it.

Pattern 1: every entity has created_by

Even if you don't think you need user ownership today, add the field. It's free if unused and essential if needed.

User entity:
  - id (string, auto)
  - email (string)
  - full_name (string)
  - role (string, default "user")
  - created_date (timestamp, auto)

Todo entity:
  - id (string, auto)
  - title (string)
  - done (boolean, default false)
  - created_by (string, indexed-by-platform)  // set automatically by the platform on create
  - created_date (timestamp, auto)

Every list query filters by created_by. Every update checks that the calling user owns the record. ESLint rules flag any query missing the filter.

This isn't optional. Base44's default behavior leaks data without it. We covered the security implications in the SDK reference and security hardening checklist.

Pattern 2: denormalize aggressively

Without JOINs, the cost of fetching related data is high. Denormalize what you display together.

// Bad: every list of todos requires a second list of projects
Todo:
  - project_id (string)

// Better: store the displayed fields on the Todo
Todo:
  - project_id (string)
  - project_name (string, denormalized)
  - project_color (string, denormalized)

When the project changes, update the denormalized fields on every todo. This is fine for apps where projects rarely change; for apps where they do, write a backend function that propagates project changes to all dependent todos.

The trade: denormalization adds write complexity and storage cost; it dramatically reduces read complexity and latency. On Base44, where reads are the slow path, denormalization usually wins.

Pattern 3: tenant_id everywhere for multi-tenant apps

For B2B SaaS:

Organization entity:
  - id
  - name
  - plan
  - owner_user_id

User entity:
  - id
  - email
  - tenant_id (string)  // foreign key to Organization
  - role

Todo entity (or any tenant-scoped entity):
  - id
  - title
  - tenant_id (string)
  - created_by

Every list call filters by both tenant_id and (where appropriate) created_by:

const myTeamTodos = await base44.entities.Todo.list({
  tenant_id: currentUser.tenant_id,
});

Test with two-account scenarios. Account A in tenant X should never see anything from tenant Y, ever. This is the bug we find in over half of multi-tenant audits.

Pattern 4: paginate every list

Since November 2025, list() caps at 5,000 records per call. Even below that cap, large unpaginated lists destroy performance.

const PAGE_SIZE = 50;
const todos = await base44.entities.Todo.list(
  { tenant_id, created_by: me.email },
  "-created_date",
  PAGE_SIZE
);

For pages that need more than the first page, use cursor-based pagination on a sortable field:

async function loadMoreTodos(cursorDate: string | null) {
  const filter: Record<string, unknown> = {
    tenant_id,
    created_by: me.email,
  };
  if (cursorDate) {
    filter.created_date = { $lt: cursorDate };
  }
  return base44.entities.Todo.list(filter, "-created_date", PAGE_SIZE);
}

Avoid offset-based pagination; the platform's list() doesn't support offset cleanly, and offset gets slow as you go deeper into the result set anyway.

Pattern 5: aggregate via pre-computation

There's no SUM, COUNT, or GROUP BY. For aggregates, pre-compute and store.

TodoStats entity (one record per user per day):
  - user_id
  - date
  - total_count
  - completed_count
  - tenant_id

A scheduled function runs nightly:

// backend/functions/computeDailyStats.ts
export default async function handler(req: Request) {
  const today = new Date().toISOString().split("T")[0];
  const users = await base44.entities.User.list();

  for (const user of users) {
    const todos = await base44.entities.Todo.list({
      created_by: user.email,
      created_date: { $gte: today + "T00:00:00Z", $lt: today + "T23:59:59Z" },
    });

    await base44.entities.TodoStats.create({
      user_id: user.id,
      date: today,
      total_count: todos.length,
      completed_count: todos.filter(t => t.done).length,
      tenant_id: user.tenant_id,
    });
  }

  return new Response("OK", { status: 200 });
}

For real-time aggregates (today's count up to this moment), increment counters on write. This is more code but avoids the daily-aggregate latency.

Pattern 6: avoid cross-entity transactions

Base44 has no transaction() primitive. A multi-entity update can fail halfway, leaving inconsistent state.

Solutions:

Idempotent operations. Design so that re-running the same update produces the same result. If the update fails halfway, you can re-run safely.

Compensating writes. If updating entity A succeeds and entity B fails, write a compensating update to A to roll back. Document this explicitly.

Single-entity sources of truth. If two entities have related state, make one canonical and derive the other. Stripe-as-source-of-truth for billing is a common example: the User entity stores a snapshot of the subscription state, but Stripe is canonical, and a reconciliation job catches drift.

Avoid the situation entirely. Often a "transaction across entities" is a sign of bad schema. If two entities always update together, they might be one entity.

Pattern 7: indexed query patterns

You can't create indexes, but the platform indexes some fields automatically. Empirically, fields that are reliably fast to filter on:

• id
• created_date
• created_by
• Any field with high cardinality that's frequently filtered on

Fields that may not be indexed:

• Boolean flags (low cardinality)
• Recently-added fields (platform may not have re-indexed)
• Fields used in $or or complex predicates

Design queries to filter on indexed-likely fields first, with low-cardinality filters as secondary refinements:

// Good: filter by user (high-cardinality, indexed) first
await base44.entities.Todo.list({
  created_by: me.email,
  done: false,  // secondary refinement
});

// Worse: filter primarily by boolean
await base44.entities.Todo.list({
  done: false,  // platform may scan more records
});

Test query latency in production. If a query is slow, restructure the filter.

Pattern 8: external Postgres mirror for production scale

For any app expected to grow past 100K records in a hot entity, mirror to Postgres:

// backend/functions/mirrorTodoChange.ts
// Triggered on every Todo create/update/delete via webhook or polling
export default async function handler(req: Request) {
  const { event, todo } = await req.json();

  // Push to Supabase
  await fetch(`${SUPABASE_URL}/rest/v1/todos`, {
    method: event === "delete" ? "DELETE" : "POST",
    headers: {
      apikey: SUPABASE_SERVICE_KEY,
      "content-type": "application/json",
      prefer: "resolution=merge-duplicates",
    },
    body: event === "delete" ? null : JSON.stringify(todo),
  });

  return new Response("OK", { status: 200 });
}

The mirror gives you:

• SQL query capability (analytics, complex reports).
• Real backup/restore.
• Disaster recovery if Base44 has an extended outage.
• Migration runway if you ever leave the platform.
• Real indexing control.

The trade: more moving parts, more cost ($25/month Supabase), more places where state can drift. Worth it past a certain scale.

Pattern 9: archive old data

Apps that retain data forever hit performance walls. Move old data out:

// backend/functions/archiveOldTodos.ts (scheduled monthly)
export default async function handler(req: Request) {
  const sixMonthsAgo = new Date(Date.now() - 180 * 24 * 60 * 60 * 1000).toISOString();

  const old = await base44.entities.Todo.list(
    { done: true, completed_at: { $lt: sixMonthsAgo } },
    "completed_at",
    500
  );

  for (const todo of old) {
    // Write to archive table (external Postgres or another entity)
    await pushToArchive(todo);
    // Delete from active entity
    await base44.entities.Todo.delete(todo.id);
  }

  return new Response(JSON.stringify({ archived: old.length }), { status: 200 });
}

Archiving keeps the active entity small enough to query fast. Archived data goes somewhere queryable for compliance/legal needs (regulatory retention, customer data export).

Pattern 10: never let the agent run schema migrations

The AI agent treats schema changes as cosmetic. It will rename a field, drop a column, or change a type without warning that the operation is destructive. Schema changes lose data.

Rule: every schema change is propose-then-apply. Have the agent suggest. You snapshot the entity. You apply manually. You verify. We covered this in detail in schema migration best practices.

Common database mistakes on Base44

Trusting Entity.list() defaults. Returns global data. Always filter.

Skipping the second-account isolation test. Multi-tenant bugs hide from solo testing.

Designing schema as if JOINs exist. They don't. Denormalize.

Synchronous N+1 reads. Batch with $in.

Unbounded list queries. Always paginate.

Trying to compute aggregates on the fly. Pre-compute and store.

Cross-entity transaction assumptions. Design idempotent operations and compensating writes.

Hot entity above 500K records. Mirror to Postgres or archive.

Letting the AI agent migrate. Always propose-then-apply.

Forgetting tenant_id on a new entity. Every multi-tenant app drifts here as new entities get added.

Database design checklist

• Every entity has created_by.
• Multi-tenant entities have tenant_id.
• Every list call has appropriate ownership and tenant filters.
• Pagination on every list with realistic limits.
• Denormalization where related data is displayed together.
• Aggregate entities for any SUM/COUNT/GROUP BY needs.
• Idempotent operations on every write path.
• No cross-entity transactional assumptions.
• External Postgres mirror for entities approaching 100K.
• Archive job for old data on entities approaching scale limits.
• Schema changes proposed by agent, applied manually with snapshot.
• Second-account test as part of every release.

Want us to audit your database design?

Our $497 audit reviews every entity for ownership filters, tenant isolation, query patterns, and scale readiness. Most apps have 4–10 fixable issues. For larger redesigns or external-Postgres migration, we run a dedicated database engagement. Order an audit or book a free 15-minute call.

Related reading

• Base44 Schema Migration Best Practices — the playbook for safely changing schemas after design.
• Base44 SDK Reference — the API surface that compounds with these design patterns.
• Base44 Performance Optimization Guide — query optimization that follows from the schema patterns above.