Skip to content

Table Reference

claude edited this page Nov 18, 2026 · 1 revision

Table reference

Every table, view, column and rule, in the database's own words (schema_guide). See Data-Model for how they connect and Rules-and-Invariants for what is enforced.

Object Kind What it holds
audit table Append-only history of every insert and update, written by triggers. Cannot be changed.
bullet_variants table Resume lines: wordings of an item, one per version (column track), focus (column angle) and length.
education table Degrees, courses, certifications and training.
employers table Companies or organizations the person worked for. Positions belong to an employer, so a promotion is a second position under the same employer.
experience view Positions that are not Deprecated, with their employer name, in resume order.
images table Headshots. At most one Active per person: retire the old one first.
item_metrics table The numbers that prove an item, one row per number; an item can have several.
item_originals table Attachments: which source documents (originals) back up which items. One document can back many items and one item many documents.
items table Everything done or received: projects, responsibilities, achievements, awards, publications, volunteer work, talks, recommendations, reviews and kudos. Usually under a position; an item with no position is the person's own (an award or publication outside a job). Resume lines are worded from items.
last_change view Who last changed each row (user, ai or app) and when. Read only; the MCP list tool reads it.
links table Web links shown in the resume contact line.
originals table Index of unedited source documents (past resumes, reviews, job descriptions) kept in the originals/ folders. The files are never changed.
people table The person this career repository is about. One Active person per install.
positions table Titles held: one row per title at an employer, from start_date to end_date. The Experience section of a resume.
profile_json view One row: everything about the Active person as one JSON document (person, employers, positions with their employer, items with their metrics, attachments (original_ids) and their position as employer and job_title, skills, education). Start here to answer interview questions or write a profile or section.
qa table Interview question bank with the person's answers. Ships with 10 common questions; add the ones actually met.
resume_json view One row per version (column track): the whole resume of the Active person as one JSON document (person, links, employers each with their positions, each position with ranked Short and Long lines and items_without_lines (with metrics), Verified skills, education, items that are not responsibilities or achievements (awards, projects, publications ...), and warnings: printed rows still Needs review and skills hidden because not Verified). Start here to write a resume.
resume_lines view Every resume line joined to its item (kind, title, detail), person and position, including Deprecated ones.
resume_lines_ranked view Printable resume lines (line and item not Deprecated, not Story) with their item; line_rank 1 is the best line for that position and version (Verified first, then Short, then Long).
schema_guide table This guide: one plain-language row per table, view, column and rule. Read-only.
search_index table Full-text index (SQLite FTS5) of employers, positions, items, metrics, resume lines, skills and Q&A. Kept current by triggers; retired rows drop out. Search it with the search tool, which ranks best match first.
settings table App settings. Only value can be changed.
skill_links table Ties a skill to the position, item or education where it was used.
skills table Skills, systems, methods and standards. Only Verified ones print on a resume.
tracks table Versions: named sets of resume lines aimed at one kind of job, e.g. Original, Leadership. Every resume line belongs to one version (column bullet_variants.track).

audit

table. Append-only history of every insert and update, written by triggers. Cannot be changed.

Column Type Required Meaning Example
at TEXT yes When the write happened (UTC). 2026-11-01T18:00:00.000Z
table_name TEXT yes Table that was written. skills
row_id INTEGER yes id of the row written. 3
op TEXT yes insert or update. update
old_json TEXT The row before the write (JSON); empty for inserts.
new_json TEXT yes The row after the write (JSON).
actor TEXT yes Who wrote it: user, ai or app. ai
id INTEGER key Row number; other tables point to it.

bullet_variants

table. Resume lines: wordings of an item, one per version (column track), focus (column angle) and length.

Column Type Required Meaning Example
item_id INTEGER yes The item this line is worded from (items.id). 2
wording TEXT yes The line exactly as printed on a resume. Merged 14 inventory spreadsheets into one shared tracker, reducing weekly reconciliation from 6 hours to 90 minutes.
angle TEXT What the line emphasizes. Efficiency
length TEXT Short (one line), Long (two lines) or Story (a paragraph for cover letters and interviews; never printed on a resume). Short
track TEXT yes Version name (tracks.name); must exist first. Original
source TEXT Where the wording came from. Past resume 2022
status TEXT yes Verified, Needs review or Deprecated. Verified
id INTEGER key Row number; other tables point to it.
updated_by TEXT Who is writing: user, ai or app. MCP sets this for you; db.py users must set it. Cleared after it is recorded in audit. ai
updated_at TEXT When the row last changed (UTC). Pass it back as expected_updated_at to avoid overwriting newer edits. 2026-11-01T18:00:00.000Z

education

table. Degrees, courses, certifications and training.

Column Type Required Meaning Example
kind TEXT yes Degree, Course, Certification, Training or Self-study. Degree
name TEXT yes Name of the degree or credential. B.A. in Business Administration
institution TEXT School or provider. Fairmont Valley College
location TEXT City and state. Fairmont, Ohio
year TEXT Year completed, shown on the resume. 2016
start_date TEXT Start month, YYYY-MM.
end_date TEXT End month, YYYY-MM.
expires TEXT When a certification expires. 2027-06
credential_url TEXT Link that verifies the credential.
status TEXT yes Verified, Needs review or Deprecated. Verified
id INTEGER key Row number; other tables point to it.
person_id INTEGER yes The person this row belongs to (people.id).
updated_by TEXT Who is writing: user, ai or app. MCP sets this for you; db.py users must set it. Cleared after it is recorded in audit. ai
updated_at TEXT When the row last changed (UTC). Pass it back as expected_updated_at to avoid overwriting newer edits. 2026-11-01T18:00:00.000Z

employers

table. Companies or organizations the person worked for. Positions belong to an employer, so a promotion is a second position under the same employer.

Column Type Required Meaning Example
name TEXT yes Employer name, unique for the person ignoring case. Brightwater Appliance Co.
location TEXT City or region. Dayton, OH
website TEXT Employer website. https://brightwater.example
notes TEXT Private notes, not printed.
status TEXT yes Verified, Needs review or Deprecated. Verified
id INTEGER key Row number; other tables point to it.
person_id INTEGER yes The person this row belongs to (people.id).
updated_by TEXT Who is writing: user, ai or app. MCP sets this for you; db.py users must set it. Cleared after it is recorded in audit. ai
updated_at TEXT When the row last changed (UTC). Pass it back as expected_updated_at to avoid overwriting newer edits. 2026-11-01T18:00:00.000Z

experience

view. Positions that are not Deprecated, with their employer name, in resume order.

images

table. Headshots. At most one Active per person: retire the old one first.

Column Type Required Meaning Example
caption TEXT Short description of the picture. Headshot 2025
mime TEXT yes image/png, image/jpeg, image/webp or image/gif. image/jpeg
bytes INTEGER yes File size in bytes. 48211
sha256 TEXT yes Fingerprint of the picture; stops duplicates.
image BLOB yes The picture itself (binary). Not copied into the audit log.
source TEXT Where the picture came from. Photographer, March 2025
status TEXT yes Active or Deprecated. Active
id INTEGER key Row number; other tables point to it.
person_id INTEGER yes The person this row belongs to (people.id).
updated_by TEXT Who is writing: user, ai or app. MCP sets this for you; db.py users must set it. Cleared after it is recorded in audit. ai
updated_at TEXT When the row last changed (UTC). Pass it back as expected_updated_at to avoid overwriting newer edits. 2026-11-01T18:00:00.000Z

item_metrics

table. The numbers that prove an item, one row per number; an item can have several.

Column Type Required Meaning Example
item_id INTEGER yes The item (items.id). 2
label TEXT What is measured. weekly reconciliation time
value TEXT yes The number as it should be quoted (text, not computed). 6 h to 1.5 h
unit TEXT Unit, if not already in value. hours
as_of TEXT When it was measured, YYYY-MM or YYYY-MM-DD. 2024-06
sort INTEGER yes Display order, low first. 100
status TEXT yes Verified, Needs review or Deprecated. Verified
id INTEGER key Row number; other tables point to it.
updated_by TEXT Who is writing: user, ai or app. MCP sets this for you; db.py users must set it. Cleared after it is recorded in audit. ai
updated_at TEXT When the row last changed (UTC). Pass it back as expected_updated_at to avoid overwriting newer edits. 2026-11-01T18:00:00.000Z

item_originals

table. Attachments: which source documents (originals) back up which items. One document can back many items and one item many documents.

Column Type Required Meaning Example
item_id INTEGER key The item (items.id). 2
original_id INTEGER key The document (originals.id). 1
note TEXT Where in the document, in words. page 3, manager comment
status TEXT yes Active or Deprecated. Active
updated_by TEXT Who is writing: user, ai or app. MCP sets this for you; db.py users must set it. Cleared after it is recorded in audit. ai
updated_at TEXT When the row last changed (UTC). Pass it back as expected_updated_at to avoid overwriting newer edits. 2026-11-01T18:00:00.000Z

items

table. Everything done or received: projects, responsibilities, achievements, awards, publications, volunteer work, talks, recommendations, reviews and kudos. Usually under a position; an item with no position is the person's own (an award or publication outside a job). Resume lines are worded from items.

Column Type Required Meaning Example
position_id INTEGER The position it belongs to (positions.id); empty = not tied to a job. 2
parent_item_id INTEGER The item it sits inside (items.id), e.g. an achievement inside a project; empty = top level.
kind TEXT yes Project, Responsibility, Achievement, Award, Publication, Volunteer, Speaking, Recommendation, Review or Kudos. Achievement
title TEXT Short name: the project, award, paper or talk. ERP migration
detail TEXT What was done, plainly, no spin (or what the review or recommendation says). Title or detail is required. Merged 14 inventory spreadsheets into one shared tracker.
outputs TEXT Products or deliverables produced. Shared inventory tracker; training guide
organization TEXT Who issued it or where it happened (awarding body, publisher, venue, volunteer group). Red Cross
from_whom TEXT Person who gave the review, recommendation or kudos. Priya Halvorsen
start_date TEXT Start Month, YYYY-MM (or a year, YYYY). Not tied to the position's dates (rule:item_dates). 2024-01
end_date TEXT End Month, YYYY-MM (or a year, YYYY). A one-off award or review has only one date. 2024-12
source TEXT Where the fact comes from, in words. 2024 review
notes TEXT Conflicting numbers or sources.
status TEXT yes Verified, Needs review or Deprecated. Verified
id INTEGER key Row number; other tables point to it.
person_id INTEGER yes The person this row belongs to (people.id).
updated_by TEXT Who is writing: user, ai or app. MCP sets this for you; db.py users must set it. Cleared after it is recorded in audit. ai
updated_at TEXT When the row last changed (UTC). Pass it back as expected_updated_at to avoid overwriting newer edits. 2026-11-01T18:00:00.000Z

last_change

view. Who last changed each row (user, ai or app) and when. Read only; the MCP list tool reads it.

links

table. Web links shown in the resume contact line.

Column Type Required Meaning Example
kind TEXT yes LinkedIn, GitHub, Website, Portfolio or Other. Website
label TEXT Text shown for the link; the URL is shown if empty. Personal site
url TEXT yes The address. https://odalys.example.com
sort INTEGER yes Display order, low first. 1
status TEXT yes Active or Deprecated. Active
id INTEGER key Row number; other tables point to it.
person_id INTEGER yes The person this row belongs to (people.id).
updated_by TEXT Who is writing: user, ai or app. MCP sets this for you; db.py users must set it. Cleared after it is recorded in audit. ai
updated_at TEXT When the row last changed (UTC). Pass it back as expected_updated_at to avoid overwriting newer edits. 2026-11-01T18:00:00.000Z

originals

table. Index of unedited source documents (past resumes, reviews, job descriptions) kept in the originals/ folders. The files are never changed.

Column Type Required Meaning Example
kind TEXT yes Resume, Job description, Review, Recommendation, Other, or AI output (a file the AI wrote, e.g. a draft it returns). Review
path TEXT yes Relative path of the file; its extracted text sits beside it as .txt. originals/reviews/2024_review.pdf
title TEXT Human title of the document. 2024 annual performance review
doc_date TEXT Date of the document itself, YYYY-MM-DD. 2024-12-15
weight INTEGER yes Trust, 1-3: 3 official (review, signed job description), 2 own (past resume), 1 loose notes. 3
position_id INTEGER The position this document is about (positions.id). 1
sha256 TEXT yes Fingerprint of the file; detects edits or duplicates.
bytes INTEGER yes File size in bytes. 20480
extracted TEXT yes How far facts have been pulled into the tables. Not yet = no text layer; Text read = text extracted automatically, facts not read yet; Partly or Done = set by the AI after it reads the text. Waiting for the AI = anything but Done. Partly
notes TEXT Notes, e.g. "no text layer" for a scanned PDF.
id INTEGER key Row number; other tables point to it.
person_id INTEGER The person this row belongs to (people.id).
updated_by TEXT Who is writing: user, ai or app. MCP sets this for you; db.py users must set it. Cleared after it is recorded in audit. ai
updated_at TEXT When the row last changed (UTC). Pass it back as expected_updated_at to avoid overwriting newer edits. 2026-11-01T18:00:00.000Z

people

table. The person this career repository is about. One Active person per install.

Column Type Required Meaning Example
name TEXT yes Short name used in the app. Odalys Brannigan-Voss
full_name TEXT Name as printed on a resume. Odalys Brannigan-Voss
other_names TEXT Former or alternate names (for matching old documents). Odalys Voss
headline TEXT One-line professional headline under the name. Operations Coordinator - scheduling, inventory and vendor logistics
location TEXT City and state shown on the resume. Springfield Heights, Ohio
phone TEXT Contact phone. 555-0142
email TEXT Contact email. odalys@example.com
linkedin TEXT LinkedIn profile URL. https://example.com/in/odalys
summary TEXT Professional summary paragraph for the top of a resume.
work_authorization TEXT Work authorization statement. Authorized to work in the United States
status TEXT yes Active or Archived. Archive instead of deleting. Active
id INTEGER key Row number; other tables point to it.
updated_by TEXT Who is writing: user, ai or app. MCP sets this for you; db.py users must set it. Cleared after it is recorded in audit. ai
updated_at TEXT When the row last changed (UTC). Pass it back as expected_updated_at to avoid overwriting newer edits. 2026-11-01T18:00:00.000Z

positions

table. Titles held: one row per title at an employer, from start_date to end_date. The Experience section of a resume.

Column Type Required Meaning Example
employer_id INTEGER yes The employer (employers.id). 1
title TEXT yes Job title. Operations Coordinator
department TEXT Department or team. Distribution
start_date TEXT Start Month, YYYY-MM (or a year, YYYY). 2023-03
end_date TEXT End Month, YYYY-MM (or a year, YYYY). Empty means current.
sort_order INTEGER Resume order, 1 = first shown; empty sorts last, then newest start first. 1
notes TEXT Private notes, not printed. Current role.
status TEXT yes Verified, Needs review or Deprecated. Verified
id INTEGER key Row number; other tables point to it.
person_id INTEGER yes The person this row belongs to (people.id).
updated_by TEXT Who is writing: user, ai or app. MCP sets this for you; db.py users must set it. Cleared after it is recorded in audit. ai
updated_at TEXT When the row last changed (UTC). Pass it back as expected_updated_at to avoid overwriting newer edits. 2026-11-01T18:00:00.000Z

profile_json

view. One row: everything about the Active person as one JSON document (person, employers, positions with their employer, items with their metrics, attachments (original_ids) and their position as employer and job_title, skills, education). Start here to answer interview questions or write a profile or section.

qa

table. Interview question bank with the person's answers. Ships with 10 common questions; add the ones actually met.

Column Type Required Meaning Example
question TEXT yes The question. Tell me about yourself / Walk me through your resume.
category TEXT About you, Behavioral, Strengths, Motivation, Situational, Logistics or Other. Behavioral
common_wordings TEXT Other ways the question gets asked. Would you relocate?
answer TEXT The person's answer; empty until answered.
status TEXT yes Needs answer, Needs review, Verified or Deprecated. Needs answer
sort INTEGER Seed order 1-10; empty for added questions. 1
seeded INTEGER yes 1 if it shipped with the app, 0 if added. 0
notes TEXT Notes, e.g. how to tailor the answer per company.
id INTEGER key Row number; other tables point to it.
person_id INTEGER The person this row belongs to (people.id).
updated_by TEXT Who is writing: user, ai or app. MCP sets this for you; db.py users must set it. Cleared after it is recorded in audit. ai
updated_at TEXT When the row last changed (UTC). Pass it back as expected_updated_at to avoid overwriting newer edits. 2026-11-01T18:00:00.000Z

resume_json

view. One row per version (column track): the whole resume of the Active person as one JSON document (person, links, employers each with their positions, each position with ranked Short and Long lines and items_without_lines (with metrics), Verified skills, education, items that are not responsibilities or achievements (awards, projects, publications ...), and warnings: printed rows still Needs review and skills hidden because not Verified). Start here to write a resume.

resume_lines

view. Every resume line joined to its item (kind, title, detail), person and position, including Deprecated ones.

resume_lines_ranked

view. Printable resume lines (line and item not Deprecated, not Story) with their item; line_rank 1 is the best line for that position and version (Verified first, then Short, then Long).

schema_guide

table. This guide: one plain-language row per table, view, column and rule. Read-only.

Column Type Required Meaning Example
object TEXT key Table or view name, or rule name for rules. skills
column_name TEXT key Column described; empty for a table, view or rule row. category
kind TEXT yes table, view, column or rule. column
description TEXT yes What it means, in plain words.
example TEXT A sample value or statement.

search_index

table. Full-text index (SQLite FTS5) of employers, positions, items, metrics, resume lines, skills and Q&A. Kept current by triggers; retired rows drop out. Search it with the search tool, which ranks best match first.

Column Type Required Meaning Example
source_table Table the text came from. bullet_variants
source_id id of the row in source_table. 3
text The searchable words (English word stems match: "negotiated" finds "negotiate").

settings

table. App settings. Only value can be changed.

Column Type Required Meaning Example
key TEXT yes Setting name used by the app. recent_years
label TEXT yes Name shown to the person. Recent years
value TEXT yes The setting value, as text. 10
note TEXT What the setting does.
id INTEGER key Row number; other tables point to it.
updated_by TEXT Who is writing: user, ai or app. MCP sets this for you; db.py users must set it. Cleared after it is recorded in audit. ai
updated_at TEXT When the row last changed (UTC). Pass it back as expected_updated_at to avoid overwriting newer edits. 2026-11-01T18:00:00.000Z

skill_links

table. Ties a skill to the position, item or education where it was used.

Column Type Required Meaning Example
skill_id INTEGER key The skill (skills.id). 3
item_type TEXT key position, item or education. position
item_id INTEGER key id of the row in the item_type table. 1
status TEXT yes Active or Deprecated. Active
updated_by TEXT Who is writing: user, ai or app. MCP sets this for you; db.py users must set it. Cleared after it is recorded in audit. ai
updated_at TEXT When the row last changed (UTC). Pass it back as expected_updated_at to avoid overwriting newer edits. 2026-11-01T18:00:00.000Z

skills

table. Skills, systems, methods and standards. Only Verified ones print on a resume.

Column Type Required Meaning Example
name TEXT yes Skill name, unique per person. Inventory control
category TEXT Skill, System, Method or Standard. Method
status TEXT yes Verified, Needs review or Deprecated. Verified
id INTEGER key Row number; other tables point to it.
person_id INTEGER yes The person this row belongs to (people.id).
updated_by TEXT Who is writing: user, ai or app. MCP sets this for you; db.py users must set it. Cleared after it is recorded in audit. ai
updated_at TEXT When the row last changed (UTC). Pass it back as expected_updated_at to avoid overwriting newer edits. 2026-11-01T18:00:00.000Z

tracks

table. Versions: named sets of resume lines aimed at one kind of job, e.g. Original, Leadership. Every resume line belongs to one version (column bullet_variants.track).

Column Type Required Meaning Example
name TEXT yes Version name, unique ignoring case. Renaming carries every line with it. Leadership
color TEXT Display color in the viewer. #2563eb
sort INTEGER yes Display order, low first. 20
status TEXT yes Active or Retired. Active
id INTEGER key Row number; other tables point to it.
updated_by TEXT Who is writing: user, ai or app. MCP sets this for you; db.py users must set it. Cleared after it is recorded in audit. ai
updated_at TEXT When the row last changed (UTC). Pass it back as expected_updated_at to avoid overwriting newer edits. 2026-11-01T18:00:00.000Z

Clone this wiki locally