Assets Schema

Assets covers catalog structure, inventory objects, and movement or repair tracking.

Namespace

These tables should move into the assets PostgreSQL schema.

• assets.asset_manufacturers
• assets.asset_types
• assets.asset_models
• assets.assets
• assets.asset_assignments
• assets.asset_circulations
• assets.asset_repairs

Catalog Tables

assets.asset_manufacturers

• Primary key: id
• Required fields: tenant_id, name
• Unique key: (tenant_id, name)
• Foreign key: tenant_id -> platform.tenants.id

assets.asset_types

• Primary key: id
• Required fields: tenant_id, name
• Optional field: end_of_support_label
• Unique key: (tenant_id, name)
• Foreign key: tenant_id -> platform.tenants.id

assets.asset_models

• Primary key: id
• Required fields: tenant_id, name, tracking_type, manufacturer_id, asset_type_id
• Optional field: end_of_support
• tracking_type check: serialized or countable
• Foreign keys:
  • tenant_id -> platform.tenants.id
  • manufacturer_id -> assets.asset_manufacturers.id
  • asset_type_id -> assets.asset_types.id

Inventory Tables

assets.assets

• Primary key: id
• Required fields: tenant_id, model_id, name, quantity
• Optional fields: project_id, serial_number
• Check: quantity > 0
• Foreign keys:
  • tenant_id -> platform.tenants.id
  • model_id -> assets.asset_models.id
  • project_id -> public.projects.id (ON DELETE SET NULL)

assets.asset_assignments

• Primary key: id
• Required fields: tenant_id, asset_id, start_date
• Optional fields: person_id, location_id, end_date
• Check: exactly one of person_id or location_id must be set
• Check: end_date is null or end_date >= start_date
• Foreign keys:
  • tenant_id -> platform.tenants.id
  • asset_id -> assets.assets.id
  • person_id -> census.persons.id
  • location_id -> locations.locations.id

assets.asset_circulations

• Primary key: id
• Required fields: tenant_id, asset_id, circulation_type, occurred_at
• Optional fields: person_id, location_id, notes
• Foreign keys:
  • tenant_id -> platform.tenants.id
  • asset_id -> assets.assets.id
  • person_id -> census.persons.id
  • location_id -> locations.locations.id
• Allowed circulation_type values:
  • issued_to_person
  • returned_from_person
  • moved_to_location
  • received_at_location
  • location_confirmation

Repair Table

assets.asset_repairs

• Primary key: id
• Required fields: tenant_id, asset_id, issue_id, title, status, priority, reported_at
• Optional fields: description, location_id, resolved_at
• Check: non-empty title
• Check: valid status and priority enums
• Check: resolved_at is null or resolved_at >= reported_at
• Foreign keys:
  • tenant_id -> platform.tenants.id
  • asset_id -> assets.assets.id
  • issue_id -> public.issues.id
  • location_id -> locations.locations.id

Relationship Notes

• Assets belong to models, and models belong to manufacturer + type.
• Assets are owned by a tenant and can optionally be grouped by a project.
• Assignments and circulations are separate: assignment stores ownership period; circulation stores event timeline.
• Repairs are linked across schemas to parent service issues using assets.asset_repairs.issue_id.