Data model

ContentsVision stores everything in a single SQLite database (storage/inventory.db) through SQLAlchemy 2's async ORM. The models live in backend/app/models/. This page is the reference for every table and relationship.

Entity relationships

Foreign keys vs implicit links

The parent-child links inside a claim (Room.claim_id, ProcessingJob.room_id, InventoryItem.job_id, ItemPhoto.item_id, StagedPhoto.job_id) are real SQLAlchemy ForeignKey columns. The ownership links team_id (on users and claims) and Claim.created_by_user_id are plain indexed strings, not database-level foreign keys. Team scoping is enforced in the application layer, not by the database.

Tables

teams

The organization. Auto-created on first login from the SSO token.

Column	Type	Notes
id	string (UUID)	Primary key.
created_at	datetime
external_id	int, nullable	The AdjustSquare team id.
name	string	Team display name.
team_token	string, indexed	The team's Adjust Square API token, used as the bearer token when submitting claims.
credit_rate	float, nullable	Dollars of revenue per credit, for the media-processing Slack notification. NULL (the default) means use the global DEFAULT_TEAM_CREDIT_RATE.

users

A person. Auto-provisioned on first login.

Column	Type	Notes
id	string (UUID)	Primary key.
created_at	datetime
external_id	int, nullable, unique, indexed	The AdjustSquare user id; becomes the adjuster id on submission.
email	string, unique, indexed
name	string
team_id	string, indexed	The owning team's id.
is_active	bool
last_seen_at	datetime, nullable, indexed	When the user last made an authenticated request. Stamped by get_current_user (at most once a minute per user); powers the admin "Active Users (live)" stat.
timezone	string	IANA zone picked on Settings > Profile ("" = browser local). Display preference only; storage stays UTC.

component_visibility

Admin-controlled show/hide overrides for UI components (currently training). One row per override; resolution is team override > global override (team_id="") > the registry default in services/component_visibility.py (training defaults to HIDDEN). See GET /api/admin/components and the admin Teams tab.

Column	Type	Notes
id	string (UUID)	Primary key.
updated_at	datetime
component	string, indexed	Key from the component registry, e.g. training.
team_id	string, indexed	"" = the global row; otherwise the team this override applies to.
visible	bool

claims

The top-level unit of work.

Column	Type	Notes
id	string (UUID)	Primary key.
created_at / updated_at	datetime	created_at indexed.
claim_number	string, indexed
date_of_loss	date
zip_code	string	The loss site's US ZIP (10001) or Canadian postal code (M5V 3L9). One field for both: the two formats cannot be confused, so app/core/postal.py tells them apart, validates, and stores the canonical form. Sent onward as zip either way.
policy_limit / policy_deductible	float
insured_name / adjuster_name	string
loss_type / coverage_type / claim_type	string	Human-readable; mapped to Adjust Square codes on submit.
team_id	string, indexed	Owning team.
is_training	bool, indexed	Training sandbox flag: uploads complete instantly with fixture items (no AI, no credits) and submission is a dry run. One per user, auto-created by the Training hub. See training.
created_by_user_id	string, indexed	The users.id of the member who created the claim. That user's external_id is the Adjust Square user id.
adjust_square_claim_key	string	Set after the first submission.
adjust_square_claim_id	int	The integer claim id on Adjust Square.
submitted_at	datetime, nullable
is_archived	bool, indexed	Soft-hide flag. Archived claims drop out of the default GET /api/claims list. Nothing is ever deleted by hand; archiving is the only removal path. A claim that has been submitted (submitted_at set) is locked and cannot be archived.
archived_at	datetime, nullable	When the claim was archived (null when active). The daily purge permanently deletes anything archived more than 30 days ago; restoring clears this.

rooms

A labeled bucket inside a claim.

Column	Type	Notes
id	string (UUID)	Primary key.
claim_id	string, FK to claims.id, indexed
created_at / updated_at	datetime
name	string	e.g. "Master Bedroom".
is_archived	bool, indexed	Soft-hide flag. Archived rooms drop out of the default room list and their items stop counting toward the claim. A room that contains any submitted item is locked and cannot be archived.
archived_at	datetime, nullable	When the room was archived (null when active). Drives the 30-day purge.

processing_jobs

One upload and its processing run.

Column	Type	Notes
id	string (UUID)	Primary key.
room_id	string, FK to rooms.id, indexed
source_type	string	video, photo, or audio.
filename / file_path	string	The original file (or photos dir).
file_size_bytes	int
duration_seconds	float	Drives video/audio billing.
loss_type / claim_number	string	Copied from the claim at creation.
uploaded_by	string	Who put this here, as a label to show: an adjuster's User.name, or the homeowner's email address when it arrived through their own link. Free text, because the two sources have nothing in common to key on. Blank on anything predating the column.
origin	string, indexed	"" (the adjuster's own upload), insured_list (a list the homeowner TYPED: no file, already complete, no cost), or insured_media. The UI branches on this rather than guessing from a filename.
status	string	pending, importing_pdf, awaiting_grouping, awaiting_confirmation, extracting, transcribing, segmenting, analyzing, synthesizing, complete, error.
progress_pct	int	0-100, streamed to the UI.
current_stage	string	Human-readable current step.
error_message	text	Set on failure.
total_frames / rooms_detected / items_found	int	Processing metadata.
transcript / transcript_segments / transcript_language	text / JSON / string	Transcription output.
room_segments	JSON	Time-chunk ranges.
completed_at	datetime, nullable
billed_cost_usd	float	Credits charged (name kept for backward compatibility).
billed_minutes	float	Whole minutes billed (video/audio).
photo_request_count	int	AI calls made (photo jobs).
billing_charged	bool	Whether credits were spent up front against the external billing service (only when ENABLE_BILLING). false otherwise.
billing_txn_id	string	The billing service's transaction id for the up-front spend, when one was made.
billing_amount	float	Credits spent up front, so a refund can return the exact amount.
billing_refunded	bool	true once the up-front charge was refunded because processing failed.
recovery_attempts	int	How many times the self-healing sweep requeued this job after an interruption (restart, crash, hang). One retry is allowed; a second interruption fails and refunds the job. See troubleshooting.
ai_cost_usd	float	Our internal OpenRouter spend in dollars (token counts priced at the model's per-million rates), copied from the in-memory observability counters when the job finishes. Separate from customer credit billing. Jobs predating this stay at 0.
ai_input_tokens / ai_output_tokens / ai_api_calls	int	The token counts and AI call count behind ai_cost_usd.
pdf_template_label	string	The recognised PDF layout, e.g. Encircle contents list. Not shown to the user; kept for the pipeline log and the support email when an import goes wrong. Blank for non-PDF jobs.
photo_truncated_groups	JSON	Photo groups whose answer ran out of room, so items in them were never read. One entry per group: {"group_index", "passes", "found": [names]}. passes counts the AI calls that group has had (capped at PHOTO_MAX_PASSES); found is what has been read out of it, sent back on the next pass so only what is missing comes back. Empty on nearly every job. See the AI pipeline.

job_log_archives

The durable copy of a job's pipeline log lines. While a job runs its logs live in the in-memory ring buffer (see observability); when the job reaches complete or error the buffer is copied here so the detail survives restarts and stays readable on the Logs page. One row per job, upserted.

Column	Type	Notes
job_id	string, FK to processing_jobs.id	Primary key.
created_at	datetime
logs	JSON	The full list of structured log entries (ts, level, msg, stage, elapsed).

submissions

One "Send for Estimation" event for a claim (a claim can be submitted several times; later submissions add the new items to the same Adjust Square claim).

Column	Type	Notes
id	string (UUID)	Primary key; shown as the submission id in the UI.
claim_id	string, indexed
created_at	datetime
items_count / photos_count	int	What the submission carried.
adjust_square_claim_key	string	The portal claim it landed on.
is_resubmit	bool	False for the claim-creating first submission.
is_dry_run	bool	Training claims: recorded, sent nowhere.
status	string	processing while background item/photo uploads run, then complete.
processing_seconds	float	End to end: request start to background finish.

api_request_logs

One outbound AI API call (OpenRouter), for the admin "API Requests" inspector on the Logs page. The request JSON is stored with base64 images redacted to size placeholders; the response is stored in full.

Column	Type	Notes
id	string (UUID)	Primary key.
created_at	datetime, indexed
job_id	string, indexed	Derived from the per-job pipeline logger.
service / method / url / model	string	openrouter, POST, the endpoint, and the model id.
status_code / duration_ms / attempts	int / float / int	Final outcome and total time across retries.
input_tokens / output_tokens / cost_usd	int / int / float	From the response usage block (OpenRouter reports real cost).
request_json / response_json	JSON	Redacted request; full response.
error	text	Set when the call ultimately failed.

inventory_items

A single line of the inventory.

Column	Type	Notes
id	string (UUID)	Primary key.
job_id	string, FK to processing_jobs.id, indexed
source_type	string	Copied from the job.
uploaded_by	string	Copied from the job for the same reason source_type is. An item an adjuster types by hand carries THEIR name, even when the job around it came from the homeowner.
item_name	string	Searchable retail-style name.
quantity	int	Identical items are grouped.
room	string	Room name (denormalized for convenience).
condition	string	New / Above Average / Average / Below Average.
age_years / age_months	int	Set by the AI only from a spoken age (video narration, audio). Always 0 from photos, and never estimated from appearance.
notes	text
brand / model_number / serial_number / sku	string
damage_type	string	smoke / water / heat / impact / mold.
estimated_value	float
confidence	float	0.0-1.0.
audio_evidence / visual_evidence	text	What was said / what was seen.
audio_start_seconds / audio_end_seconds	float	The recording slice (audio items).
photo_path / crop_photo_path / photo_context_path / source_photo_path	string	Legacy photo columns, kept as a safety net.
photo_frame_time	float	Timestamp in the video.
photo_frames	text (JSON)	All analyzed frame URLs.
reviewed / flagged / approved	bool	Human review state.
is_archived	bool, indexed	Soft-hide flag. Archived items drop out of the inventory lists, counts, exports, and Adjust Square submission, but keep their item_number (no renumbering on archive) so unarchiving restores the exact slot. Submitted items cannot be archived. Archiving is the only way to remove an item; there is no manual delete.
archived_at	datetime, nullable	When the item was archived (null when active). Drives the 30-day purge.
sort_order	int
item_number	int, indexed	Unique per claim.
submitted	bool, indexed	Sent to Adjust Square.
adjust_square_item_id	int	Remote item id after submission.
photos	relationship	The item_photos rows (source of truth for images).

item_photos

Photos attached to a finished item. The source of truth for item imagery (the legacy photo_path columns on the item are kept for compatibility).

Column	Type	Notes
id	string (UUID)	Primary key.
item_id	string, FK to inventory_items.id, CASCADE delete, indexed
path / crop_path / context_path	string
is_primary	bool	The primary thumbnail. Setting it via PUT /api/inventory/item/{id}/photos/{photo_id} demotes every other photo on the item AND copies the photo onto InventoryItem.photo_path, which is what the inventory table and the export actually read. ItemPhotosModal is the user-facing way to set it.
photo_order	int	"order" is reserved in SQL, hence the name.

staged_photos

Raw uploaded photos waiting to be grouped before AI runs.

Column	Type	Notes
id	string (UUID)	Primary key.
job_id	string, FK to processing_jobs.id, CASCADE delete, indexed
filename	string	File on disk under <storage>/<job_id>/photos/ (a synthetic orig_####.jpg sequence, or pdf_####.jpg for a photo pulled out of a PDF, so the two can never overwrite each other).
original_filename	string	The photo's real upload name (e.g. IMG_1234.jpg). The on-disk filename is synthetic, so this is the only record of the real name; grouping's "Sort by name" re-orders on it. Blank for manual segments and for rows created before the column existed.
captured_at	string	When the photo was taken (YYYY-MM-DDTHH:MM:SS), read from EXIF in the browser at upload (the re-encode strips EXIF, so it must be captured before then); falls back to the file's modified time. Powers grouping's "Sort by date". Blank when unknown and for manual segments.
group_index	int, indexed	Photos sharing a group become one item. Manual segments use a high range (>= 1_000_000) so they never collide with photo groups.
photo_order	int
deleted	bool, indexed	Soft-delete; file removed on Process.
is_segment	bool	True for a crop the user drew from a parent photo during grouping (a known item the AI never re-splits).
quantity	int	User-set quantity for a described or segment item (e.g. 4 for four boxed rolls of tape). 1 by default.
manual_name	string	User-typed name for the item (a whole photo / merged group, or a segment). When set, the AI is skipped for it (no request, no credits). Blank means the AI names it.
source_filename	string	The whole photo a segment was cropped from, so "clear segments" can restore it.
bbox	string	JSON [x, y, w, h] (normalized) of the box on the source photo, kept for re-editing.
source_description	text	For a photo imported from a PDF: the note printed beside it, lifted verbatim off the page (e.g. Charred barber products). Kept exactly as printed, however poor, because it is the source record. It is never the item's name: it is passed to the AI as a hint when the photo is analyzed, and saved onto the finished item's notes. Blank otherwise.
source_item_key	string	The vendor's own label on the page (Item 42). Photos printed under ONE label are one item, so they are staged sharing a group_index and arrive already merged. Blank otherwise.

pdf_templates

One learned recipe for reading a vendor's PDF page layout, so the AI is asked once per FORMAT rather than once per file. Deliberately global, not team-scoped: these rows hold layout rules only (coordinates, regexes), never claim content, so the first upload of a new format teaches the whole app. signature_json keeps the page furniture lines the fingerprint was built from, so a vendor that prints the insured's name in that furniture (which changes the hash per claim) is still recognised by a near match rather than re-learned. See PDF import.

Column	Type	Notes
id	string (UUID)	Primary key.
fingerprint	string, unique, indexed	Hash of the layout signature: the lines repeating on most pages with digits masked, plus page size and producing application. Stable across two exports from the same tool, distinct between vendors.
label	string	The model's human name for the format, e.g. Encircle contents list. Shown to the user.
recipe_json	text	The recipe. Always re-validated through Recipe.from_dict() before use: a stored recipe is data, never code, and a bad one degrades rather than raising.
source	string	ai when the model wrote it, builtin for one we ship.
created_by_team_id	string	The team whose upload first taught us this format. Kept for support; not used to scope lookups.
use_count / last_photo_count / last_used_at	int / int / datetime	How often the recipe has been reused and how well it did last time. A recipe that keeps finding nothing is the signal to re-derive it.

activity_log

One row per meaningful action, powering the Settings activity log and its Restore button. Covered in observability.

Column	Type	Notes
id	string (UUID)	Primary key.
created_at	datetime, indexed
user_id / user_name	string	Who did it.
action_type	string	Machine code the restore logic dispatches on.
action_label / target	string	Human-readable text.
entity_type / entity_id	string	What was acted on.
snapshot	JSON, nullable	Everything needed to undo the action.
restorable / restored / restored_at	bool / bool / datetime	Restore state.

training_progress

One row per user per training mission; the source of truth for points and badges (mission complete = badge earned). See training.

Column	Type	Notes
id	string (UUID)	Primary key.
created_at / updated_at	datetime
user_id / team_id	string, indexed	Who is training, and their team (for the leaderboard).
mission_key	string, indexed	Curriculum mission key, e.g. messy-claim.
status	string	in_progress or complete.
quiz_score / quiz_total / quiz_attempts	int	Best quiz result and attempt count.
checks_passed	JSON	Check keys that passed on the latest grading run.
check_attempts	int
points	int	Banked once at completion: mission base + quiz bonus.
completed_at	datetime, nullable

training_certificates

Issued certifications, publicly verifiable by code. See training.

Column	Type	Notes
id	string (UUID)	Primary key.
code	string, unique, indexed	Shareable verification code, e.g. CV-3F9A-C24D-71BE.
user_id / team_id	string, indexed
holder_name / team_name	string	Snapshotted at issue time so the public page stays stable.
level	string	Certified Contents Specialist.
score_pct / points	int	Overall quiz accuracy and total points at issue time.
issued_at	datetime
revoked	bool	Invalidates the certificate while keeping the code resolvable.

insured_links

One invitation sent to the INSURED (the homeowner), letting them build their own inventory for exactly one claim through a public link. See Insured inventory link.

Column	Type	Notes
id	string (UUID)	Primary key.
claim_id	string, FK to claims.id, CASCADE delete, indexed	The one claim this link can reach. Checked on every public request.
team_id / created_by_user_id	string, indexed	Denormalised from the claim so the adjuster-side ownership check is one query.
token_hash	string, unique, indexed	SHA-256 of the raw token. The raw token is never stored, so a database leak yields no working links. It exists only in the emailed URL.
insured_email	string, indexed	The address the adjuster sent it to.
insured_name	string	What the person typed on arrival. Neither this nor the email is a gate: whoever holds the link may use it. Both exist so the record shows who entered the inventory.
expires_at	datetime, indexed	INSURED_LINK_TTL_DAYS from creation (30 by default).
revoked_at	datetime, nullable	Manual kill switch. Bites on the holder's next request.
email_status	string	Furthest state Mailgun reported: "", not_configured, failed, sent, delivered, opened, clicked.
email_message_id	string, indexed	Mailgun's message id.
email_error	text	Why a send or delivery failed, shown to the adjuster.
email_sent_at / email_delivered_at / email_opened_at / email_clicked_at	datetime, nullable	From the Mailgun webhook. An email "open" fires on a tracking pixel that Apple Mail loads regardless of whether a human looked, so treat it as weak evidence.
send_count	int	How many times it was sent. A resend rotates token_hash, so the earlier email's URL stops working.
link_first_opened_at / link_last_opened_at / open_count	datetime, int	Measured on our own server when the page is fetched. This is the signal worth trusting, unlike an email open.
started_at	datetime, nullable	First time they entered anything.
submitted_at	datetime, nullable	When they pressed Submit. A submitted link becomes read-only.
submitted_item_count / submitted_photo_count / submitted_video_count	int	What the submit produced.
uploaded_bytes	int	Lifetime total across every request, enforced against INSURED_LINK_MAX_TOTAL_MB. Deleting an upload does not credit it back, so upload-then-delete cannot push unlimited bytes.

status is a derived property (revoked / submitted / expired / in_progress / opened / sent), not a column.

insured_draft_rooms

A room the HOMEOWNER added, before submit. The adjuster sets up the rooms they know about; the homeowner knows their own home and can add the loft, the shed, the spare room nobody mentioned.

These are drafts rather than real rooms because adding one is a single tap and abandoning it costs nothing, so creating a Room on the spot would fill the adjuster's claim with empty rooms somebody typed and thought better of. A drafted room reaches the claim at the same moment its contents do: on submit, and only if it has contents to bring.

Column	Type	Notes
id	string (UUID)	Primary key. Draft rows point at this in their room_id until submit.
link_id	string, FK to insured_links.id, CASCADE delete, indexed
name	string	Matched case-insensitively against the claim's rooms at submit, so a "Garage" the adjuster made meanwhile is adopted rather than duplicated.
position	int	Order the homeowner added them in.
room_id	string, indexed	The rooms.id it became. Blank while still a draft. A materialised draft room is never listed again: the real room is on the claim by then.

insured_draft_items

One typed row in the homeowner's table, before submit. Every column maps onto an InventoryItem field, which is where the row lands.

Column	Type	Notes
id	string (UUID)	Primary key.
link_id	string, FK to insured_links.id, CASCADE delete, indexed
room_id	string, indexed	Either a real rooms.id (a room the adjuster set up) or an insured_draft_rooms.id (one the homeowner added). Submit resolves the second into the first and rewrites this column.
position	int	Order within the room; drives the "Item No." the homeowner sees. The real claim-unique item_number is assigned at submit.
item_name	string	Title. The only required text field, and only at submit.
quantity	int	Positive integer, minimum 1.
brand / model_number / notes	string / text	Optional.
estimated_value	float	Non-negative.
age_years / age_months	int	Non-negative. Months of 12 or more are folded into years (18 months becomes 1 year 6 months).

insured_draft_files

One photo or video the homeowner uploaded, before submit. The bytes are written to <storage>/insured/<link_id>/ on arrival, so a flaky connection never costs a re-upload; only the row that becomes a StagedPhoto waits for submit.

Column	Type	Notes
id	string (UUID)	Primary key.
link_id	string, FK to insured_links.id, CASCADE delete, indexed
room_id	string, indexed
draft_item_id	string, indexed	Set only for kind = "item_photo": the typed row it belongs to.
kind	string	room_photo, room_video, or item_photo.
filename	string	Synthetic name on disk. Photos are re-encoded to JPEG right after the upload replies (a background task; see batched photo upload), which strips EXIF (so a phone photo never carries the homeowner's GPS location onto the claim) after baking the EXIF orientation into the pixels (upright_rgb in services/image_encode.py), so a portrait phone photo stays upright without the tag.
original_filename	string	What their phone called it.
content_type / size_bytes	string / int
note	text	The homeowner's note about this one file ("the TV is behind the boxes on the left"). At submit it becomes StagedPhoto.source_description and reaches the AI through the same path as a PDF caption. For a video it becomes ProcessingJob.notes instead.
position	int

How the schema is created and migrated

There is no migration tool (no Alembic). On startup, core/database.py's init_db():

1. Calls Base.metadata.create_all to create any missing tables.
2. Runs a list of idempotent ALTER TABLE ... ADD COLUMN statements, each wrapped in a try/except so an already-existing column is silently skipped. This is how new columns are added to existing databases.
3. Runs a few one-time backfills (also guarded): assigning sequential item_numbers to old items per claim, repairing crop_photo_path for old photo items, and seeding item_photos rows for items that predate the multi-photo model.

Adding a column

To add a column: add it to the model and append an ALTER TABLE ... ADD COLUMN line in init_db(). create_all only creates whole new tables; it never alters an existing one, so without the ALTER line the column exists in the model but not in an already-deployed database.