Skip to main content

Tenants Table Design

Schema Specification​

CREATE TABLE IF NOT EXISTS tenants (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL UNIQUE,
  created_at timestamptz DEFAULT now()
);

Design Decisions​

Column Specifications​

  1. id (uuid, PRIMARY KEY)

    • Type: UUID
    • Default: gen_random_uuid()
    • Rationale: Matches existing table patterns in the codebase (all tables use UUID primary keys)

  2. name (text, NOT NULL, UNIQUE)

    • Type: TEXT
    • Constraints: NOT NULL, UNIQUE
    • Rationale:
      • Human-readable tenant identifier
      • Unique constraint prevents duplicate tenant names
      • Used for tenant identification in queries and migrations

  3. created_at (timestamptz)

    • Type: TIMESTAMPTZ
    • Default: now()
    • Rationale: Audit timestamp for tracking when tenants were created

Omitted Fields​

  1. updated_at

    • Rationale: Tenant names are immutable. If a name change is needed, a new tenant should be created and data migrated.

  2. deleted_at (soft delete)

    • Rationale: Using hard deletes for simplicity. Can be added in future if soft delete functionality is needed.

  3. Additional metadata fields

    • Rationale: Keep the schema minimal for Phase 0. Additional fields (e.g., settings, subscription_tier) can be added later if needed.

Indexes​

No additional indexes are needed beyond the PRIMARY KEY on id and the UNIQUE constraint on name (which creates an index automatically).

Security​

  • RLS (Row Level Security) will be enabled in Phase 3
  • Initial policies will allow authenticated users to read their own tenant only
  • Admin policies will be tenant-scoped

Migration Notes​

  • This table will be created in Phase 1 as the first migration step
  • The default tenant (Autoch.at) will be inserted in Phase 2
  • All existing production data will be associated with this default tenant