ICT:DBML - version 4.1

// =====================================================
// Costasano Heritage Database Model — v4.1
//
// Adds identifier/numbering infrastructure:
// - short codes on Chapters, Organisations, Places
// - automatic identifiers for DigitalAssets
//
// Purpose:
// supports automatic page names and file names
// =====================================================



// ============================
// CORE ENTITIES
// ============================

Table HeritageObjects {
  id string [pk]
  parent_id string [ref: > HeritageObjects.id]

  title string
  type_id string [ref: > HeritageObjectTypes.id]
  subtype string

  place_id string [ref: > Places.id]

  date_from datetime
  date_to datetime

  description text
  language string
  notes text
}



// ============================
// Digital Assets
// ============================

Table DigitalAssets {
  id string [pk]

  // ---- numbering / identification (NEW v4.1) ----
  chapter_id string [ref: > ResearchChapters.id]
  context_code string // organisation.code OR place.code
  sequence_number int // numeric counter within chapter+context
  identifier string // formatted ID (e.g. CH03-ROM-0007)

  // ---- storage ----
  file_id string [ref: > MediaWikiFiles.id]

  // ---- recursion ----
  parent_id string [ref: > DigitalAssets.id]

  // ---- classification ----
  asset_source_type_id string [ref: > AssetSourceTypes.id]
  asset_type_id string [ref: > AssetTypes.id]

  // ---- spatial context ----
  place_id string [ref: > Places.id]

  asset_source_reference string
  language string
  description text
  notes text
  ai_processed boolean

  // ---- citation / publication ----
  citation_text text
  permalink string
  repository string
  rights string
  is_publishable boolean
}



// ============================
// EXTERNAL (MediaWiki)
// ============================

Table MediaWikiFiles {
  id string [pk]
  title string
  timestamp datetime
  user string
  comment text
  size int
  sha1 string
  mime string
  url string
}



// ============================
// Research Entities
// ============================

Table Persons {
  id string [pk]
  firstname string
  lastname string
  birth_date datetime
  death_date datetime
  biography text
  contact_information text
  notes text
}



Table Organisations {
  id string [pk]

  name string
  code string // NEW v4.1 — short identifier (ARCH, CONG, etc.)

  description text
  place_id string [ref: > Places.id]

  organisations_ref string
  contact_information text
  notes text
}



// ============================
// Places (spatial context)
// ============================

Table Places {
  id string [pk]
  parent_id string [ref: > Places.id]

  name string
  code string // NEW v4.1 — short identifier (ROM, FLO, etc.)

  type string // city, site, region, building
  latitude float
  longitude float

  notes text
}



// ============================
// Narrative
// ============================

Table ResearchChapters {
  id string [pk]
  parent_id string [ref: > ResearchChapters.id]

  title string
  code string // NEW v4.1 — short identifier (CH01, CH02)

  description text
  sequence_number int

  start_datetime datetime
  end_datetime datetime
  notes text
}



// ============================
// LOOKUPS
// ============================

Table HeritageObjectTypes {
  id string [pk]
  type_name string
  description text
}

Table AssetSourceTypes {
  id string [pk]
  source_type_name string
  description text
}

Table AssetTypes {
  id string [pk]
  asset_type_name string
  description text
}

Table DigitalAssetRoles {
  id string [pk]
  role_name string
  description text
}

Table PersonRoles {
  id string [pk]
  role_name string
  description text
}

Table OrganisationRoles {
  id string [pk]
  role_name string
  description text
}

Table Keywords {
  id string [pk]
  keyword string
  description text
}



// ============================
// RELATIONSHIPS
// ============================

Table HeritageObjectDigitalAssets {
  id string [pk]
  heritageobject_id string [ref: > HeritageObjects.id]
  digitalasset_id string [ref: > DigitalAssets.id]
  role_id string [ref: > DigitalAssetRoles.id]
  is_preferred boolean
}

Table PersonDigitalAssets {
  id string [pk]
  person_id string [ref: > Persons.id]
  digitalasset_id string [ref: > DigitalAssets.id]
}

Table OrganisationDigitalAssets {
  id string [pk]
  organisation_id string [ref: > Organisations.id]
  digitalasset_id string [ref: > DigitalAssets.id]
}



Table HeritageObjectChapters {
  id string [pk]
  heritageobject_id string [ref: > HeritageObjects.id]
  researchchapter_id string [ref: > ResearchChapters.id]
}



Table HeritageObjectPersons {
  id string [pk]
  heritageobject_id string [ref: > HeritageObjects.id]
  person_id string [ref: > Persons.id]
  role_id string [ref: > PersonRoles.id]
}

Table HeritageObjectOrganisations {
  id string [pk]
  heritageobject_id string [ref: > HeritageObjects.id]
  organisation_id string [ref: > Organisations.id]
  role_id string [ref: > OrganisationRoles.id]
}

Table PersonOrganisationRoles {
  id string [pk]
  person_id string [ref: > Persons.id]
  organisation_id string [ref: > Organisations.id]
  role_id string [ref: > OrganisationRoles.id]
}



Table HeritageObjectKeywords {
  id string [pk]
  heritageobject_id string [ref: > HeritageObjects.id]
  keyword_id string [ref: > Keywords.id]
}



Table HeritageObjectHolders {
  id string [pk]
  heritageobject_id string [ref: > HeritageObjects.id]
  person_id string [ref: > Persons.id]
  organisation_id string [ref: > Organisations.id]
}