Skip to content
Open Scriptorium

About

Schema reference

Introspected live from the running app. The architecture overview explains how these tables fit together.

Notable columns: works.traditions is a string array (GIN-indexed) using the χ-prefix tradition taxonomy. pericopes.tradition enables tradition-aware section-heading filtering. canonical_refs.ord is guaranteed monotonic with hierarchy ordering within each canonical work. See the architecture overview for details.

Currently in the corpus

28

works

160

canonical works

148,947

canonical refs

672,946

work units

2,239,608

words

36,076

lemmas

727,546

cross references

411,427

alignment groups

3,061

pericopes

7,513

versification mappings

Work Language License Units
Babylonian Talmud (Gemara) arc CC BY-SA 3.0 81,793
Berean Standard Bible en Public Domain 31,086
Bible Crampon 1904 fr Public Domain 34,675
Brenton's English Septuagint en Public Domain 28,689
CNTR Statistical Restoration el-koine Creative Commons Attribution 4.0 7,912
Chokhmat Shlomo arc Public Domain 11,158
Clementine Vulgate la Public Domain 33,950
Ein Mishpat / Ner Mitzvah arc Public Domain 3,927
Gilyon HaShas arc Public Domain 3,714
Hagahot Ya'avetz arc Public Domain 5,412
Jewish Publication Society 1917 en Public Domain 23,206
King James Version (1769 Blayney revision, with Apocrypha) en Public Domain 36,819
Louis Segond 1910 fr Public Domain 31,084
Lutherbibel 1912 de Public Domain 31,173
Maharsha Chidushei Agadot arc Public Domain 7,079
Maharsha Chidushei Halachot arc Public Domain 12,662
Mesoret HaShas arc Public Domain 3,846
Nestle 1904 Greek New Testament el-koine Public Domain 7,957
Patriarchal Greek New Testament (1904) el-koine Public Domain 7,955
Piskei Tosafot arc Public Domain 6,015
Rahlfs Septuagint (1935) el-koine Public Domain 30,555
Rashash on Talmud Bavli arc Public Domain 20,106
Rashi on Talmud Bavli arc Public Domain 114,398
Robinson-Pierpont Byzantine Textform el-koine Public Domain 7,953
Soncino English Talmud en Public Domain 5,345
Tosafot on Talmud Bavli arc Public Domain 25,167
Westminster Leningrad Codex he Creative Commons Attribution 4.0 23,145
World English Bible British Edition en Public Domain 36,165

Canonical addressing

VersificationScheme · versification_schemes

Columns

  • id : bigint ·NOT NULL
  • code : character varying ·NOT NULL
  • display_name : character varying ·NOT NULL
  • description : text
  • source_url : character varying
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL

Associations

  • has_many :canonical_works
  • has_many :works
  • has_many :outgoing_mappings → VersificationMapping
  • has_many :incoming_mappings → VersificationMapping

CanonicalWork · canonical_works

Columns

  • id : bigint ·NOT NULL
  • slug : character varying ·NOT NULL
  • title : character varying ·NOT NULL
  • native_title : character varying
  • versification_scheme_id : bigint
  • parent_canonical_work_id : bigint
  • position : integer
  • testament : character varying
  • osis_book_id : character varying
  • description : text
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL
  • sbl_abbreviation : character varying
  • citation_format : character varying
  • book_key : character varying ·NOT NULL

Associations

  • has_many :versions → PaperTrail::Version
  • belongs_to :versification_scheme
  • belongs_to :parent_canonical_work → CanonicalWork
  • has_many :sub_works → CanonicalWork
  • has_many :canonical_refs
  • has_many :work_units
  • has_many :pericopes
  • has_many :annotations
  • has_many :personal_notes
  • has_many :bookmarks
  • has_many :source_cross_references → CrossReference
  • has_many :target_cross_references → CrossReference

CanonicalRef · canonical_refs

Columns

  • id : bigint ·NOT NULL
  • canonical_work_id : bigint ·NOT NULL
  • ord : integer ·NOT NULL
  • display_label : character varying ·NOT NULL
  • osis_id : character varying
  • hierarchy : integer ·NOT NULL
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL
  • import_run_id : bigint
  • sbl_citation : character varying

Associations

  • belongs_to :canonical_work
  • belongs_to :import_run
  • has_many :work_units
  • has_many :variant_units
  • has_many :alignment_groups
  • has_many :bookmarks

VersificationMapping · versification_mappings

Columns

  • id : bigint ·NOT NULL
  • from_scheme_id : bigint ·NOT NULL
  • from_canonical_ref_id : bigint ·NOT NULL
  • to_scheme_id : bigint ·NOT NULL
  • to_canonical_ref_id : bigint ·NOT NULL
  • mapping_type : character varying ·NOT NULL
  • notes : text
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL

Associations

  • has_many :versions → PaperTrail::Version
  • belongs_to :from_scheme → VersificationScheme
  • belongs_to :from_canonical_ref → CanonicalRef
  • belongs_to :to_scheme → VersificationScheme
  • belongs_to :to_canonical_ref → CanonicalRef

Pericope · pericopes

Columns

  • id : bigint ·NOT NULL
  • canonical_work_id : bigint ·NOT NULL
  • source_dataset : character varying ·NOT NULL
  • name : character varying ·NOT NULL
  • native_name : character varying
  • pericope_type : character varying
  • sequence_number : integer
  • description : text
  • import_run_id : bigint
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL
  • canonical_range : int4range ·NOT NULL
  • tradition : character varying

Associations

  • has_many :versions → PaperTrail::Version
  • belongs_to :canonical_work
  • belongs_to :import_run

Works

License · licenses

Columns

  • id : bigint ·NOT NULL
  • name : character varying ·NOT NULL
  • spdx_id : character varying
  • url : character varying
  • attribution_required : boolean ·NOT NULL
  • attribution_template : character varying
  • description : text
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL

Associations

  • has_many :works

Author · authors

Columns

  • id : bigint ·NOT NULL
  • slug : character varying ·NOT NULL
  • name : character varying ·NOT NULL
  • sort_name : character varying
  • latin_name : character varying
  • native_name : character varying
  • birth_year : integer
  • death_year : integer
  • era : character varying
  • tradition : character varying
  • bio : text
  • wikipedia_url : character varying
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL

Associations

  • has_many :versions → PaperTrail::Version
  • has_many :work_authorships
  • has_many :works

Work · works

Columns

  • id : bigint ·NOT NULL
  • slug : character varying ·NOT NULL
  • title : character varying ·NOT NULL
  • native_title : character varying
  • work_type : character varying ·NOT NULL
  • language : character varying
  • era : character varying
  • genre : character varying
  • license_id : bigint ·NOT NULL
  • default_versification_scheme_id : bigint
  • source_url : character varying
  • import_notes : text
  • publication_year : integer
  • description : text
  • has_word_data : boolean ·NOT NULL
  • has_apparatus : boolean ·NOT NULL
  • imported_at : timestamp(6) without time zone
  • last_imported_at : timestamp(6) without time zone
  • source_revision : character varying
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL
  • import_run_id : bigint
  • pericope_traditions : character varying
  • traditions : character varying
  • cached_feature_flags : character varying

Associations

  • has_many :versions → PaperTrail::Version
  • belongs_to :license
  • belongs_to :default_versification_scheme → VersificationScheme
  • belongs_to :import_run
  • has_many :work_authorships
  • has_many :authors
  • has_many :work_units
  • has_many :words
  • has_many :source_alignment_groups → AlignmentGroup
  • has_many :target_alignment_groups → AlignmentGroup

WorkAuthorship · work_authorships

Columns

  • id : bigint ·NOT NULL
  • work_id : bigint ·NOT NULL
  • author_id : bigint ·NOT NULL
  • role : character varying ·NOT NULL
  • position : integer
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL

Associations

  • belongs_to :work
  • belongs_to :author

WorkUnit · work_units

Columns

  • id : bigint ·NOT NULL
  • work_id : bigint ·NOT NULL
  • ord : integer ·NOT NULL
  • parent_id : bigint
  • position : integer
  • label : character varying
  • hierarchy_path : ltree
  • canonical_ref_id : bigint
  • canonical_alignment_type : character varying
  • canonical_alignment_confidence : numeric(4,3)
  • body : text
  • markup : jsonb ·NOT NULL
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL
  • import_run_id : bigint

Associations

  • has_many :versions → PaperTrail::Version
  • belongs_to :work
  • belongs_to :parent → WorkUnit
  • belongs_to :canonical_ref
  • belongs_to :import_run
  • has_many :children → WorkUnit
  • has_many :words
  • has_many :work_unit_notes

WorkUnitNote · work_unit_notes

Columns

  • id : bigint ·NOT NULL
  • work_unit_id : bigint ·NOT NULL
  • import_run_id : bigint
  • position_in_unit : integer ·NOT NULL
  • note_type : character varying ·NOT NULL
  • body : text ·NOT NULL
  • anchor_text : text
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL

Associations

  • has_many :versions → PaperTrail::Version
  • belongs_to :work_unit
  • belongs_to :import_run

Word-level data

Lemma · lemmas

Columns

  • id : bigint ·NOT NULL
  • language : character varying ·NOT NULL
  • lemma : character varying ·NOT NULL
  • normalized : character varying ·NOT NULL
  • primary_gloss : character varying
  • part_of_speech : character varying
  • frequency : integer ·NOT NULL
  • lexicon_entry_id : bigint
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL
  • import_run_id : bigint
  • etymology : text
  • transliteration : character varying
  • senses : jsonb ·NOT NULL
  • related_lemma_ids : integer ·NOT NULL
  • frequency_by_book : jsonb ·NOT NULL
  • attested_period : character varying

Associations

  • has_many :versions → PaperTrail::Version
  • has_many :words
  • belongs_to :lexicon_entry → WorkUnit
  • belongs_to :import_run

Word · words

Columns

  • id : bigint ·NOT NULL
  • work_id : bigint ·NOT NULL
  • work_unit_id : bigint ·NOT NULL
  • position : integer ·NOT NULL
  • surface : character varying ·NOT NULL
  • normalized : character varying
  • lemma_id : bigint
  • morph_code : character varying
  • strongs_number : character varying
  • notes : jsonb ·NOT NULL
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL
  • import_run_id : bigint

Associations

  • belongs_to :work
  • belongs_to :work_unit
  • belongs_to :lemma
  • belongs_to :import_run
  • has_many :alignment_group_members
  • has_many :alignment_groups

AlignmentGroup · alignment_groups

Columns

  • id : bigint ·NOT NULL
  • canonical_ref_id : bigint ·NOT NULL
  • source_work_id : bigint ·NOT NULL
  • target_work_id : bigint ·NOT NULL
  • alignment_type : character varying ·NOT NULL
  • confidence : numeric(4,3)
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL
  • import_run_id : bigint

Associations

  • belongs_to :canonical_ref
  • belongs_to :source_work → Work
  • belongs_to :target_work → Work
  • belongs_to :import_run
  • has_many :alignment_group_members
  • has_many :source_members → AlignmentGroupMember
  • has_many :target_members → AlignmentGroupMember
  • has_many :source_words
  • has_many :target_words

AlignmentGroupMember · alignment_group_members

Columns

  • id : bigint ·NOT NULL
  • alignment_group_id : bigint ·NOT NULL
  • word_id : bigint ·NOT NULL
  • role : character varying ·NOT NULL
  • position : integer
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL
  • import_run_id : bigint

Associations

  • belongs_to :alignment_group
  • belongs_to :word
  • belongs_to :import_run

Critical apparatus

Manuscript · manuscripts

Columns

  • id : bigint ·NOT NULL
  • siglum : character varying ·NOT NULL
  • name : character varying
  • date_text : character varying
  • date_year_estimate : integer
  • family : character varying
  • language : character varying
  • location : character varying
  • description : text
  • image_url : character varying
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL
  • import_run_id : bigint
  • gregory_aland_number : character varying
  • intf_liste_number : character varying
  • material : character varying
  • format : character varying
  • extent_description : text
  • lacunae : jsonb ·NOT NULL
  • shelfmark : character varying

Associations

  • has_many :reading_witnesses
  • has_many :readings
  • belongs_to :import_run

VariantUnit · variant_units

Columns

  • id : bigint ·NOT NULL
  • canonical_ref_id : bigint ·NOT NULL
  • position_within_ref : integer ·NOT NULL
  • base_reading_id : bigint
  • notes : text
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL
  • lemma_text : text
  • context_before : text
  • context_after : text
  • variant_type : character varying
  • import_run_id : bigint

Associations

  • belongs_to :canonical_ref
  • belongs_to :base_reading → Reading
  • belongs_to :import_run
  • has_many :readings

Reading · readings

Columns

  • id : bigint ·NOT NULL
  • variant_unit_id : bigint ·NOT NULL
  • text : text ·NOT NULL
  • normalized_text : text
  • notes : text
  • is_base : boolean ·NOT NULL
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL
  • import_run_id : bigint

Associations

  • belongs_to :variant_unit
  • belongs_to :import_run
  • has_many :reading_witnesses
  • has_many :manuscripts

ReadingWitness · reading_witnesses

Columns

  • id : bigint ·NOT NULL
  • reading_id : bigint ·NOT NULL
  • manuscript_id : bigint ·NOT NULL
  • certainty : character varying
  • notes : text
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL
  • correction_layer : character varying
  • folio : character varying
  • column_number : integer
  • line_number : integer
  • is_lacuna : boolean ·NOT NULL
  • is_supplement : boolean ·NOT NULL
  • import_run_id : bigint

Associations

  • belongs_to :reading
  • belongs_to :manuscript
  • belongs_to :import_run

Cross-references

CrossReference · cross_references

Columns

  • id : bigint ·NOT NULL
  • source_canonical_work_id : bigint ·NOT NULL
  • target_canonical_work_id : bigint ·NOT NULL
  • relationship_type : character varying ·NOT NULL
  • confidence : numeric(4,3)
  • source_dataset : character varying
  • notes : text
  • created_by_user_id : bigint
  • approved_at : timestamp(6) without time zone
  • approved_by_user_id : bigint
  • status : character varying ·NOT NULL
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL
  • source_range : int4range ·NOT NULL
  • target_range : int4range ·NOT NULL
  • import_run_id : bigint

Associations

  • has_many :versions → PaperTrail::Version
  • belongs_to :source_canonical_work → CanonicalWork
  • belongs_to :target_canonical_work → CanonicalWork
  • belongs_to :created_by_user → User
  • belongs_to :approved_by_user → User
  • belongs_to :import_run

Users + contributions

User · users

Columns

  • id : bigint ·NOT NULL
  • email_address : character varying ·NOT NULL
  • password_digest : character varying ·NOT NULL
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL
  • display_name : character varying
  • bio : text
  • role : character varying ·NOT NULL
  • invited_by_user_id : bigint
  • invited_at : timestamp(6) without time zone
  • email_confirmed_at : timestamp(6) without time zone
  • preferences : jsonb ·NOT NULL

Associations

  • has_many :versions → PaperTrail::Version
  • has_many :sessions
  • belongs_to :invited_by_user → User
  • has_many :invitations → Invitation
  • has_many :annotations
  • has_many :personal_notes
  • has_many :bookmarks
  • has_many :reading_plans → ReadingPlan
  • has_many :reading_plan_progress → ReadingPlanProgress
  • has_many :submitted_cross_references → CrossReference

Invitation · invitations

Columns

  • id : bigint ·NOT NULL
  • code : character varying ·NOT NULL
  • created_by_user_id : bigint ·NOT NULL
  • used_by_user_id : bigint
  • used_at : timestamp(6) without time zone
  • expires_at : timestamp(6) without time zone
  • notes : text
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL

Associations

  • belongs_to :created_by_user → User
  • belongs_to :used_by_user → User

Annotation · annotations

Columns

  • id : bigint ·NOT NULL
  • user_id : bigint ·NOT NULL
  • canonical_work_id : bigint ·NOT NULL
  • body_markdown : text ·NOT NULL
  • body_html : text
  • status : character varying ·NOT NULL
  • reviewed_by_user_id : bigint
  • reviewed_at : timestamp(6) without time zone
  • review_notes : text
  • visibility : character varying ·NOT NULL
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL
  • canonical_range : int4range ·NOT NULL

Associations

  • has_many :versions → PaperTrail::Version
  • belongs_to :user
  • belongs_to :canonical_work
  • belongs_to :reviewed_by_user → User

PersonalNote · personal_notes

Columns

  • id : bigint ·NOT NULL
  • user_id : bigint ·NOT NULL
  • canonical_work_id : bigint ·NOT NULL
  • body_markdown : text ·NOT NULL
  • body_html : text
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL
  • canonical_range : int4range ·NOT NULL

Associations

  • belongs_to :user
  • belongs_to :canonical_work

Bookmark · bookmarks

Columns

  • id : bigint ·NOT NULL
  • user_id : bigint ·NOT NULL
  • canonical_work_id : bigint ·NOT NULL
  • canonical_ref_id : bigint ·NOT NULL
  • label : character varying
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL

Associations

  • belongs_to :user
  • belongs_to :canonical_work
  • belongs_to :canonical_ref

ReadingPlan · reading_plans

Columns

  • id : bigint ·NOT NULL
  • slug : character varying ·NOT NULL
  • title : character varying ·NOT NULL
  • description : text
  • created_by_user_id : bigint ·NOT NULL
  • is_public : boolean ·NOT NULL
  • length_days : integer
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL

Associations

  • belongs_to :created_by_user → User
  • has_many :reading_plan_entries
  • has_many :reading_plan_progress

ReadingPlanEntry · reading_plan_entries

Columns

  • id : bigint ·NOT NULL
  • reading_plan_id : bigint ·NOT NULL
  • day : integer ·NOT NULL
  • position : integer ·NOT NULL
  • canonical_work_id : bigint ·NOT NULL
  • label : character varying
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL
  • canonical_range : int4range ·NOT NULL

Associations

  • belongs_to :reading_plan
  • belongs_to :canonical_work
  • has_many :reading_plan_progress

ReadingPlanProgress · reading_plan_progress

Columns

  • id : bigint ·NOT NULL
  • user_id : bigint ·NOT NULL
  • reading_plan_id : bigint ·NOT NULL
  • reading_plan_entry_id : bigint ·NOT NULL
  • completed_at : timestamp(6) without time zone ·NOT NULL
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL

Associations

  • belongs_to :user
  • belongs_to :reading_plan
  • belongs_to :reading_plan_entry

Provenance + config

ImportRun · import_runs

Columns

  • id : bigint ·NOT NULL
  • importer_class : character varying ·NOT NULL
  • source_url : character varying
  • source_revision : character varying
  • source_byte_count : bigint
  • source_format : character varying
  • started_at : timestamp(6) without time zone ·NOT NULL
  • finished_at : timestamp(6) without time zone
  • status : character varying ·NOT NULL
  • error_message : text
  • error_backtrace : text
  • stats : jsonb ·NOT NULL
  • options : jsonb ·NOT NULL
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL

Associations

  • has_many :works
  • has_many :work_units
  • has_many :canonical_refs
  • has_many :words
  • has_many :lemmas
  • has_many :alignment_groups
  • has_many :alignment_group_members
  • has_many :manuscripts
  • has_many :variant_units
  • has_many :readings
  • has_many :reading_witnesses
  • has_many :cross_references
  • has_many :work_unit_notes
  • has_many :pericopes

Setting · settings

Columns

  • id : bigint ·NOT NULL
  • key : character varying ·NOT NULL
  • value : text
  • created_at : timestamp(6) without time zone ·NOT NULL
  • updated_at : timestamp(6) without time zone ·NOT NULL

Associations