model saas {
  version "1.0"
  description "Multi-tenant application: tenants, authentication, billing."
}

naming {
  entity {
    case snake
  }
  column {
    case snake
  }
  foreign_key {
    prefix "fk_"
  }
}

lint {
  unindexed-foreign-key warn
}

property owner {
  type text
  description "Team accountable for the entity."
  applies entity
}

property retention_days {
  type integer
  description "Days the rows are kept before archival."
  default 365
  applies entity, table
}

classification pii {
  description "Personally identifiable information."
  level "none"
  level "indirect"
  level "direct"
  inherit
}

domain tenant_ref {
  type bigint
}

enum subscription_status {
  value "trialing"
  value "active"
  value "past_due"
  value "cancelled"
}

term Tenant {
  definition "An isolated customer organisation sharing the application."
}

entity plan {
  description "A price plan."
  attribute id : bigint not null
  attribute code : text(40) not null
  attribute monthly_price : decimal(10, 2) not null
  key primary pk_plan (id)
  key unique uq_plan_code (code)
}

entity tenant {
  description "An isolated customer organisation."
  classify pii "none"
  prop owner "platform"
  attribute id : bigint not null
  attribute slug : text(63) not null
  attribute name : text(200) not null
  attribute created_at : timestamptz not null default expr "now()"
  key primary pk_tenant (id)
  key unique uq_tenant_slug (slug)
}

target lite : sqlite {
  schema main {
    table api_key {
      entity api_key
      column id integer not null identity by_default attribute id
      column tenant_id integer not null attribute tenant_id
      column name text not null attribute name
      column key_hash blob not null attribute key_hash
      column created_by integer attribute created_by
      column revoked_at text attribute revoked_at
      primary key pk_api_key (id)
      unique uq_api_key_hash (key_hash)
      foreign key fk_api_key_created_by (created_by) references user (id) on delete set_null relationship api_key_created_by
      foreign key fk_api_key_tenant (tenant_id) references tenant (id) on delete cascade relationship api_key_tenant
      index ix_api_key_created_by (created_by)
      index ix_api_key_tenant (tenant_id)
    }
    table invoice {
      entity invoice
      column id integer not null identity by_default attribute id
      column tenant_id integer not null attribute tenant_id
      column subscription_id integer not null attribute subscription_id
      column number text not null attribute number
      column issued_on text not null attribute issued_on
      column total numeric not null default 0 attribute total
      primary key pk_invoice (id)
      unique uq_invoice_tenant_number (tenant_id, number)
      foreign key fk_invoice_subscription (subscription_id) references subscription (id) relationship invoice_subscription
      foreign key fk_invoice_tenant (tenant_id) references tenant (id) on delete cascade relationship invoice_tenant
      index ix_invoice_subscription (subscription_id)
    }
    table invoice_line {
      entity invoice_line
      column invoice_id integer not null attribute invoice_id
      column line_no integer not null attribute line_no
      column tenant_id integer not null attribute tenant_id
      column description text not null attribute description
      column amount numeric not null attribute amount
      primary key pk_invoice_line (invoice_id, line_no)
      foreign key fk_invoice_line_invoice (invoice_id) references invoice (id) on delete cascade relationship invoice_line_invoice
      foreign key fk_invoice_line_tenant (tenant_id) references tenant (id) on delete cascade relationship invoice_line_tenant
      index ix_invoice_line_tenant (tenant_id)
    }
    table membership {
      entity membership
      column tenant_id integer not null attribute tenant_id
      column user_id integer not null attribute user_id
      column "role" text not null default "member" attribute "role"
      primary key pk_membership (tenant_id, user_id)
      foreign key fk_membership_tenant (tenant_id) references tenant (id) on delete cascade relationship membership_tenant
      foreign key fk_membership_user (user_id) references user (id) on delete cascade relationship membership_user
      index ix_membership_user (user_id)
    }
    table plan {
      entity plan
      column id integer not null identity by_default attribute id
      column code text not null attribute code
      column monthly_price numeric not null attribute monthly_price
      primary key pk_plan (id)
      unique uq_plan_code (code)
    }
    table subscription {
      entity subscription
      column id integer not null identity by_default attribute id
      column tenant_id integer not null attribute tenant_id
      column plan_id integer not null attribute plan_id
      column status text not null default "trialing" attribute status
      column started_on text not null attribute started_on
      column ends_on text attribute ends_on
      primary key pk_subscription (id)
      check ck_subscription_status "status IN ('trialing', 'active', 'past_due', 'cancelled')"
      foreign key fk_subscription_plan (plan_id) references plan (id) on delete restrict relationship subscription_plan
      foreign key fk_subscription_tenant (tenant_id) references tenant (id) on delete cascade relationship subscription_tenant
      index ix_subscription_plan (plan_id)
      index ix_subscription_tenant (tenant_id)
    }
    table tenant {
      entity tenant
      column id integer not null identity by_default attribute id
      column slug text not null attribute slug
      column name text not null attribute name
      column created_at text not null default expr "CURRENT_TIMESTAMP" attribute created_at
      primary key pk_tenant (id)
      unique uq_tenant_slug (slug)
    }
    table user {
      entity user
      column id integer not null identity by_default attribute id
      column tenant_id integer not null attribute tenant_id
      column email text not null attribute email
      column display_name text attribute display_name
      column password_hash blob attribute password_hash
      column last_login_at text attribute last_login_at
      primary key pk_user (id)
      unique uq_user_tenant_email (tenant_id, email)
      foreign key fk_user_tenant (tenant_id) references tenant (id) on delete cascade relationship user_tenant
    }
  }
}

target pg : postgresql {
  schema app {
    enum_type subscription_status ("trialing", "active", "past_due", "cancelled")
    table api_key {
      entity api_key
      column id bigint not null identity always attribute id
      column tenant_id bigint not null attribute tenant_id
      column name varchar(100) not null attribute name
      column key_hash bytea not null attribute key_hash
      column created_by bigint attribute created_by
      column revoked_at "timestamp with time zone" attribute revoked_at
      primary key pk_api_key (id)
      unique uq_api_key_hash (key_hash)
      foreign key fk_api_key_created_by (created_by) references user (id) on delete set_null relationship api_key_created_by
      foreign key fk_api_key_tenant (tenant_id) references tenant (id) on delete cascade relationship api_key_tenant
      index ix_api_key_created_by (created_by)
      index ix_api_key_tenant (tenant_id)
    }
    table invoice {
      entity invoice
      column id bigint not null identity always attribute id
      column tenant_id bigint not null attribute tenant_id
      column subscription_id bigint not null attribute subscription_id
      column number varchar(30) not null attribute number
      column issued_on date not null attribute issued_on
      column total numeric(12, 2) not null default 0 attribute total
      primary key pk_invoice (id)
      unique uq_invoice_tenant_number (tenant_id, number)
      foreign key fk_invoice_subscription (subscription_id) references subscription (id) relationship invoice_subscription
      foreign key fk_invoice_tenant (tenant_id) references tenant (id) on delete cascade relationship invoice_tenant
      index ix_invoice_subscription (subscription_id)
    }
    table invoice_line {
      entity invoice_line
      column invoice_id bigint not null attribute invoice_id
      column line_no integer not null attribute line_no
      column tenant_id bigint not null attribute tenant_id
      column description varchar(200) not null attribute description
      column amount numeric(12, 2) not null attribute amount
      primary key pk_invoice_line (invoice_id, line_no)
      foreign key fk_invoice_line_invoice (invoice_id) references invoice (id) on delete cascade relationship invoice_line_invoice
      foreign key fk_invoice_line_tenant (tenant_id) references tenant (id) on delete cascade relationship invoice_line_tenant
      index ix_invoice_line_tenant (tenant_id)
    }
    table membership {
      entity membership
      column tenant_id bigint not null attribute tenant_id
      column user_id bigint not null attribute user_id
      column "role" varchar(20) not null default "member" attribute "role"
      primary key pk_membership (tenant_id, user_id)
      foreign key fk_membership_tenant (tenant_id) references tenant (id) on delete cascade relationship membership_tenant
      foreign key fk_membership_user (user_id) references user (id) on delete cascade relationship membership_user
      index ix_membership_user (user_id)
    }
    table plan {
      entity plan
      comment "A price plan."
      column id bigint not null identity always attribute id
      column code varchar(40) not null attribute code
      column monthly_price numeric(10, 2) not null attribute monthly_price
      primary key pk_plan (id)
      unique uq_plan_code (code)
    }
    table subscription {
      entity subscription
      column id bigint not null identity always attribute id
      column tenant_id bigint not null attribute tenant_id
      column plan_id bigint not null attribute plan_id
      column status subscription_status not null default "trialing" attribute status
      column started_on date not null attribute started_on
      column ends_on date attribute ends_on
      primary key pk_subscription (id)
      foreign key fk_subscription_plan (plan_id) references plan (id) on delete restrict relationship subscription_plan
      foreign key fk_subscription_tenant (tenant_id) references tenant (id) on delete cascade relationship subscription_tenant
      index ix_subscription_plan (plan_id)
      index ix_subscription_tenant (tenant_id)
    }
    table tenant {
      entity tenant
      comment "An isolated customer organisation."
      column id bigint not null identity always attribute id
      column slug varchar(63) not null attribute slug
      column name varchar(200) not null attribute name
      column created_at "timestamp with time zone" not null default expr "now()" attribute created_at
      primary key pk_tenant (id)
      unique uq_tenant_slug (slug)
    }
    table user {
      entity user
      column id bigint not null identity always attribute id
      column tenant_id bigint not null attribute tenant_id
      column email varchar(320) not null attribute email
      column display_name varchar(200) attribute display_name
      column password_hash bytea attribute password_hash
      column last_login_at "timestamp with time zone" attribute last_login_at
      primary key pk_user (id)
      unique uq_user_tenant_email (tenant_id, email)
      foreign key fk_user_tenant (tenant_id) references tenant (id) on delete cascade relationship user_tenant
    }
  }
}