Nquiry Data Dictionary

Last Updated: 2026-02-10 (last reviewed 2026-04-29) Database: Amazon RDS PostgreSQL Application: Nquiry - Compliance-critical investigation management

---

Table of Contents

1. Core Tables
2. Multi-Tenant Tables
3. Investigation Tables
4. Evidence Tables
5. Analysis & Reporting Tables
6. Billing & Usage Tables
7. Template Tables
8. Audit & Compliance Tables
9. Enum Types
10. Helper Functions

---

Core Tables

user_profile

User account information, linked to Cognito auth.

Column	Type	Nullable	Default	Description
profile_id	UUID	NO	uuid_generate_v4()	Primary key
user_id	UUID	NO	-	Reference to auth.users(id)
email	TEXT	YES	-	User email address
full_name	TEXT	YES	-	Display name
role	TEXT	NO	'user'	System role: user, admin, manager
current_organization_id	UUID	YES	-	Currently selected organization
created_at	TIMESTAMPTZ	YES	NOW()	Record creation timestamp
updated_at	TIMESTAMPTZ	YES	NOW()	Last update timestamp

Constraints: UNIQUE(user_id), role IN ('user', 'admin', 'manager')

---

Multi-Tenant Tables

organization

Teams and personal workspaces. Every user has at least one.

Column	Type	Nullable	Default	Description
organization_id	UUID	NO	gen_random_uuid()	Primary key
name	TEXT	NO	-	Organization display name
slug	TEXT	NO	-	URL-friendly identifier (unique)
type	TEXT	NO	'personal'	personal or team
billing_email	TEXT	YES	-	Email for billing notifications
billing_status	TEXT	YES	'trial'	trial, active, past_due, canceled
billing_plan	TEXT	YES	'trial'	trial, professional, team, enterprise
stripe_customer_id	TEXT	YES	-	Stripe customer ID (unique)
stripe_subscription_id	TEXT	YES	-	Stripe subscription ID (unique)
settings	JSONB	YES	'{}'	Organization-specific settings
storage_used_bytes	BIGINT	NO	0	Current storage usage
storage_quota_bytes	BIGINT	NO	2147483648	Max storage (default 2GB for trial)
ai_usage_limit	INTEGER	YES	25	Max AI generations (25 total for trial)
current_period_start	DATE	YES	-	Billing period start date
current_period_end	DATE	YES	-	Billing period end date
trial_started_at	TIMESTAMPTZ	YES	-	When the trial period began
trial_ends_at	TIMESTAMPTZ	YES	-	When the trial period expires (14 days)
trial_investigation_limit	INTEGER	YES	1	Max investigations completable during trial
completed_investigation_count	INTEGER	YES	0	Number of investigations marked complete
trial_ai_used	INTEGER	YES	0	Total AI generations used during trial (not reset)
created_at	TIMESTAMPTZ	NO	now()	Record creation timestamp
updated_at	TIMESTAMPTZ	NO	now()	Last update timestamp

Constraints: type IN ('personal', 'team'), billing_status IN ('trial', 'active', 'past_due', 'canceled'), billing_plan IN ('trial', 'professional', 'team', 'enterprise')

organization_member

User membership in organizations with role-based access.

Column	Type	Nullable	Default	Description
member_id	UUID	NO	gen_random_uuid()	Primary key
organization_id	UUID	NO	-	FK to organization
user_id	UUID	NO	-	FK to auth.users
role	TEXT	NO	'member'	owner, admin, member, viewer
invited_by	UUID	YES	-	User who sent the invite
joined_at	TIMESTAMPTZ	YES	now()	When user joined
created_at	TIMESTAMPTZ	NO	now()	Record creation timestamp
updated_at	TIMESTAMPTZ	NO	now()	Last update timestamp

Constraints: UNIQUE(organization_id, user_id), role IN ('owner', 'admin', 'member', 'viewer')

Role Hierarchy: owner > admin > member > viewer

organization_invitation

Pending invitations to join an organization.

Column	Type	Nullable	Default	Description
invitation_id	UUID	NO	gen_random_uuid()	Primary key
organization_id	UUID	NO	-	FK to organization
email	TEXT	NO	-	Invited email address
role	TEXT	NO	'member'	Role to assign on acceptance
token	TEXT	NO	random hex	Unique invitation token
invited_by	UUID	NO	-	User who created invitation
expires_at	TIMESTAMPTZ	NO	now() + 7 days	Token expiration
accepted_at	TIMESTAMPTZ	YES	-	When invitation was accepted
created_at	TIMESTAMPTZ	NO	now()	Record creation timestamp

Constraints: UNIQUE(organization_id, email), role IN ('admin', 'member', 'viewer')

investigation_member

Per-investigation membership for the inquiry-centric model (NQU-115). Tracks who has access to each investigation.

Column	Type	Nullable	Default	Description
member_id	UUID	NO	gen_random_uuid()	Primary key
investigation_id	UUID	NO	-	FK to investigation
user_id	UUID	NO	-	FK to auth.users
role	TEXT	NO	'member'	owner or member
invited_by	UUID	YES	-	User who sent the invite
joined_at	TIMESTAMPTZ	YES	now()	When user joined
created_at	TIMESTAMPTZ	NO	now()	Record creation timestamp

Constraints: UNIQUE(investigation_id, user_id), role IN ('owner', 'member')

Backfill: Existing investigation creators were backfilled with role='owner' on migration.

inquiry_invitation

Pending invitations to join an investigation (NQU-115).

Column	Type	Nullable	Default	Description
invitation_id	UUID	NO	gen_random_uuid()	Primary key
investigation_id	UUID	NO	-	FK to investigation
email	TEXT	NO	-	Invited email address
role	TEXT	NO	'member'	Role to assign on acceptance
invited_by	UUID	NO	-	User who created invitation
token	TEXT	NO	random hex (32 bytes)	Unique invitation token
expires_at	TIMESTAMPTZ	NO	now() + 7 days	Token expiration
accepted_at	TIMESTAMPTZ	YES	-	When invitation was accepted
created_at	TIMESTAMPTZ	NO	now()	Record creation timestamp

Constraints: UNIQUE(investigation_id, email), role IN ('member')

---

Investigation Tables

investigation

Core project/case entity. Supports multiple work types.

Column	Type	Nullable	Default	Description
investigation_id	UUID	NO	uuid_generate_v4()	Primary key
organization_id	UUID	NO	-	FK to organization
created_by	UUID	NO	-	User who created it
title	TEXT	NO	-	Project title
focus_statement	TEXT	YES	-	Scope/purpose statement
status	TEXT	NO	'planning'	Workflow status
work_type	work_type	NO	'investigation'	Type of engagement
custom_work_type_label	TEXT	YES	-	Custom label when work_type='other'
source_template_id	UUID	YES	-	Template used to create this project
created_at	TIMESTAMPTZ	YES	NOW()	Record creation timestamp
updated_at	TIMESTAMPTZ	YES	NOW()	Last update timestamp

Constraints:

• status IN ('planning', 'collection', 'analysis', 'reporting', 'complete', 'archived')
• custom_work_type_label required when work_type = 'other'

topic

Logical groupings of questions within an investigation.

Column	Type	Nullable	Default	Description
topic_id	UUID	NO	uuid_generate_v4()	Primary key
investigation_id	UUID	NO	-	FK to investigation
title	TEXT	NO	-	Topic title
description	TEXT	YES	-	Topic description
investigator_notes	TEXT	YES	-	Internal notes
sequence_order	INTEGER	NO	0	Display order
created_at	TIMESTAMPTZ	YES	NOW()	Record creation timestamp
updated_at	TIMESTAMPTZ	YES	NOW()	Last update timestamp

question

Questions to be answered by the investigation.

Column	Type	Nullable	Default	Description
question_id	UUID	NO	uuid_generate_v4()	Primary key
investigation_id	UUID	NO	-	FK to investigation
topic_id	UUID	YES	-	FK to topic (optional grouping)
question_text	TEXT	NO	-	The question text
sequence_order	INTEGER	NO	0	Display order
finding_status	TEXT	NO	'pending'	Finding determination
finding_summary	TEXT	YES	-	Summary of finding
created_at	TIMESTAMPTZ	YES	NOW()	Record creation timestamp
updated_at	TIMESTAMPTZ	YES	NOW()	Last update timestamp

Constraints: finding_status IN ('pending', 'substantiated', 'not_substantiated', 'inconclusive')

background_document

Investigation context documents (charge letters, org charts, etc.).

Column	Type	Nullable	Default	Description
background_doc_id	UUID	NO	gen_random_uuid()	Primary key
investigation_id	UUID	NO	-	FK to investigation
title	TEXT	NO	-	Document title
description	TEXT	YES	-	Document description
document_type	background_document_type	YES	'other'	Type of document
file_path	TEXT	YES	-	S3 path
file_type	TEXT	YES	-	MIME type
file_size	BIGINT	YES	-	Size in bytes
content_text	TEXT	YES	-	Extracted text content
include_in_analysis	BOOLEAN	YES	true	Include in AI analysis
display_order	INTEGER	YES	0	Display order
created_at	TIMESTAMPTZ	YES	now()	Record creation timestamp
updated_at	TIMESTAMPTZ	YES	now()	Last update timestamp

framework_document

Evaluation frameworks, policies, standards attached to investigation.

Column	Type	Nullable	Default	Description
document_id	UUID	NO	uuid_generate_v4()	Primary key
investigation_id	UUID	NO	-	FK to investigation
title	TEXT	NO	-	Document title
file_name	TEXT	NO	-	Original filename
file_path	TEXT	NO	-	S3 storage path
file_size	INTEGER	YES	-	Size in bytes
document_type	TEXT	NO	'other'	policy, standard, framework, regulation, other
description	TEXT	YES	-	Document description
display_order	INTEGER	YES	0	Display order
include_in_analysis	BOOLEAN	YES	true	Include in AI analysis
uploaded_at	TIMESTAMPTZ	YES	NOW()	Upload timestamp
created_at	TIMESTAMPTZ	YES	NOW()	Record creation timestamp
updated_at	TIMESTAMPTZ	YES	NOW()	Last update timestamp

---

Evidence Tables

evidence

Evidence items collected for an investigation.

Column	Type	Nullable	Default	Description
evidence_id	UUID	NO	uuid_generate_v4()	Primary key
investigation_id	UUID	NO	-	FK to investigation
evidence_type	TEXT	NO	-	document, interview, website, observation, dataset, note, standard
title	TEXT	NO	-	Evidence title
description	TEXT	YES	-	Evidence description
content_text	TEXT	YES	-	Text content or transcript
source	TEXT	YES	-	Source of evidence
source_date	DATE	YES	-	Date of source material
investigator_notes	TEXT	YES	-	Internal notes
to_be_collected	BOOLEAN	NO	true	Whether evidence is pending collection (auto-toggles to false when content is added)
collected_at	TIMESTAMPTZ	YES	NOW()	When evidence was collected
created_at	TIMESTAMPTZ	YES	NOW()	Record creation timestamp
updated_at	TIMESTAMPTZ	YES	NOW()	Last update timestamp

Constraints: evidence_type IN ('document', 'interview', 'website', 'observation', 'dataset', 'note', 'standard')

evidence_attachment

File attachments for evidence items.

Column	Type	Nullable	Default	Description
attachment_id	UUID	NO	uuid_generate_v4()	Primary key
evidence_id	UUID	NO	-	FK to evidence
file_name	TEXT	NO	-	Original filename
file_path	TEXT	NO	-	S3 storage path
file_type	TEXT	NO	-	MIME type
file_size	INTEGER	YES	-	Size in bytes
extracted_text	TEXT	YES	-	OCR/extracted text
display_order	INTEGER	NO	0	Display order
uploaded_at	TIMESTAMPTZ	YES	NOW()	Upload timestamp

evidence_note

Notes/annotations on evidence items.

Column	Type	Nullable	Default	Description
note_id	UUID	NO	uuid_generate_v4()	Primary key
evidence_id	UUID	NO	-	FK to evidence
user_id	UUID	NO	-	User who created note
note_text	TEXT	NO	-	Note content
note_type	TEXT	NO	'general'	observation, analysis, follow_up, concern, general
created_at	TIMESTAMPTZ	YES	NOW()	Record creation timestamp
updated_at	TIMESTAMPTZ	YES	NOW()	Last update timestamp

evidence_question_link

Many-to-many relationship between evidence and questions.

Column	Type	Nullable	Default	Description
link_id	UUID	NO	uuid_generate_v4()	Primary key
question_id	UUID	NO	-	FK to question
evidence_id	UUID	NO	-	FK to evidence
relevance_notes	TEXT	YES	-	Why this evidence is relevant
created_at	TIMESTAMPTZ	YES	NOW()	Record creation timestamp

Constraints: UNIQUE(question_id, evidence_id)

---

Analysis & Reporting Tables

analysis

AI-generated analyses for investigations.

Column	Type	Nullable	Default	Description
analysis_id	UUID	NO	uuid_generate_v4()	Primary key
investigation_id	UUID	NO	-	FK to investigation
question_id	UUID	YES	-	FK to question (for question-specific analysis)
topic_id	UUID	YES	-	FK to topic (for topic-specific analysis)
analysis_type	TEXT	NO	-	Type of analysis
analysis_text	TEXT	NO	-	Generated analysis content
structured_output	JSONB	YES	-	Validated JSON output matching analysis schema
retrieval_stats	JSONB	YES	-	Similarity score stats from semantic search
faithfulness_score	FLOAT	YES	-	Ratio of claims grounded in evidence (0-1)
faithfulness_details	JSONB	YES	-	Detailed claim verification results
coverage_score	FLOAT	YES	-	Ratio of question elements addressed (0-1)
coverage_details	JSONB	YES	-	Elements checked and gaps identified
quality_confidence	TEXT	YES	-	Overall quality: established/probable/possible/insufficient
evidence_cited	JSONB	YES	-	Evidence references used
ai_model_version	TEXT	YES	-	Model used for generation
prompt_id	UUID	YES	-	FK to prompt_template used
prompt_version	INTEGER	YES	-	Version of prompt at generation time
validation_passed	BOOLEAN	YES	-	Whether output passed Zod schema validation
validation_errors	JSONB	YES	-	Validation error details if failed
user_feedback	TEXT	YES	-	User feedback on analysis
feedback_incorporated	BOOLEAN	YES	false	Whether feedback was applied
iteration_number	INTEGER	YES	1	Regeneration count
user_action	TEXT	YES	-	Latest user feedback action on this analysis
user_action_at	TIMESTAMPTZ	YES	-	When user_action was last set
user_action_metadata	JSONB	YES	'{}'	Additional metadata for the user action
detail_reviewed_at	TIMESTAMPTZ	YES	-	When investigator first expanded the detail section
citation_checked_at	TIMESTAMPTZ	YES	-	When investigator first opened a citation in the side panel
generation_started_at	TIMESTAMPTZ	YES	-	Set before Claude API call; enables orphan staleness detection
generation_retry_count	INTEGER	NO	0	Number of orphan recovery retries (max 2)
generation_error	TEXT	YES	-	Error message when generation_status = 'failed'
created_at	TIMESTAMPTZ	YES	NOW()	Record creation timestamp
updated_at	TIMESTAMPTZ	YES	NOW()	Last update timestamp

Constraints:

• analysis_type IN ('question_analysis', 'overall_summary', 'gap_analysis', 'error_check', 'topic_analysis')
• quality_confidence IN ('established', 'probable', 'possible', 'insufficient')
• user_action IN ('viewed', 'regenerated', 'edited', 'accepted', 'rejected', 'needs_revision')

Prompt Traceability: Every analysis records prompt_id and prompt_version to enable performance tracking and rollback analysis.

Quality Metrics: Each analysis stores comprehensive quality metrics:

• retrieval_stats: Semantic search similarity scores and distribution
• faithfulness_score/details: Verification that claims are grounded in evidence
• coverage_score/details: Verification that all question elements are addressed
• quality_confidence: Overall confidence level mapped to evidence framework

User Feedback Tracking: Each analysis tracks the latest user action (agree, disagree, needs_revision, regenerate, edit, view) for measuring AI quality outcomes. Full event history is stored in analysis_feedback.

Engagement Gates: detail_reviewed_at and citation_checked_at track investigator engagement before allowing judgment. Both gates must be unlocked (non-NULL) before the Agree/Disagree/Unsure controls become active. Gate timestamps are set once and persist across sessions.

Generation Durability (NQU-167): generation_started_at, generation_retry_count, and generation_error enable orphan recovery on server restart. If a container dies mid-generation, the orphan recovery module detects stale records via generation_started_at and marks them failed. Quality checks that were interrupted are re-triggered automatically. Max 2 retries per record.

analysis_feedback

Event history of user feedback actions on analyses. Supports tracking the full lifecycle of user interaction with AI output.

Column	Type	Nullable	Default	Description
feedback_id	UUID	NO	gen_random_uuid()	Primary key
analysis_id	UUID	NO	-	FK to analysis
user_id	UUID	NO	-	User who took the action
action	TEXT	NO	-	Feedback action: viewed, regenerated, edited, accepted, rejected, needs_revision
reason	TEXT	YES	-	Optional reason (e.g., why regenerated or rejected)
feedback_categories	TEXT[]	YES	'{}'	Multi-select rejection taxonomy categories (NQU-138)
metadata	JSONB	YES	'{}'	Additional context (edit details, etc.)
created_at	TIMESTAMPTZ	YES	NOW()	When the feedback event occurred

Constraints: action IN ('viewed', 'regenerated', 'edited', 'accepted', 'rejected', 'needs_revision')

Indexes:

• idx_analysis_feedback_analysis_id on (analysis_id)
• idx_analysis_feedback_user_id on (user_id)
• idx_analysis_feedback_action on (action)
• idx_analysis_feedback_created_at on (created_at)
• idx_analysis_feedback_categories GIN on (feedback_categories) WHERE feedback_categories != '{}'
• idx_analysis_feedback_rejected on (created_at DESC) WHERE action = 'rejected'

report

Generated reports from investigations.

Column	Type	Nullable	Default	Description
report_id	UUID	NO	uuid_generate_v4()	Primary key
investigation_id	UUID	NO	-	FK to investigation
version_number	INTEGER	NO	1	Report version
status	TEXT	NO	'draft'	draft, review, final
content_structured	JSONB	YES	-	Structured report content
content_full	TEXT	YES	-	Full text content
endnotes	JSONB	YES	-	Citations/references
generated_at	TIMESTAMPTZ	YES	NOW()	Generation timestamp
finalized_at	TIMESTAMPTZ	YES	-	When marked final

prompt_template

AI prompt templates for analysis generation.

Column	Type	Nullable	Default	Description
prompt_id	UUID	NO	uuid_generate_v4()	Primary key
prompt_type	TEXT	NO	-	Unique prompt identifier
prompt_name	TEXT	NO	-	Display name
system_prompt	TEXT	NO	-	System message template
user_prompt_template	TEXT	NO	-	User message template
is_active	BOOLEAN	YES	true	Whether currently in use
version	INTEGER	YES	1	Auto-incremented on content changes
created_at	TIMESTAMPTZ	YES	NOW()	Record creation timestamp
updated_at	TIMESTAMPTZ	YES	NOW()	Last update timestamp

Trigger: trg_archive_prompt_version - Automatically archives previous version to prompt_template_history on UPDATE.

prompt_template_history

Version history for prompt templates. Enables rollback and audit of prompt changes.

Column	Type	Nullable	Default	Description
history_id	UUID	NO	gen_random_uuid()	Primary key
prompt_id	UUID	NO	-	FK to prompt_template
prompt_type	TEXT	NO	-	Prompt type at time of archive
prompt_name	TEXT	NO	-	Name at time of archive
system_prompt	TEXT	NO	-	System prompt at time of archive
user_prompt_template	TEXT	NO	-	User prompt at time of archive
version	INTEGER	NO	-	Version number that was replaced
was_active	BOOLEAN	NO	-	Whether prompt was active
changed_by	UUID	YES	-	FK to user_profile (if tracked)
change_reason	TEXT	YES	-	Optional reason for change
archived_at	TIMESTAMPTZ	NO	NOW()	When this version was replaced
original_created_at	TIMESTAMPTZ	YES	-	Original created_at value
original_updated_at	TIMESTAMPTZ	YES	-	Original updated_at value

Helper Functions:

• get_prompt_history(prompt_type) - Returns all versions including current
• get_prompt_version(prompt_type, version) - Returns full content of a specific version
• rollback_prompt(prompt_type, version, reason) - Restores a previous version
• compare_prompt_versions(prompt_type, v1, v2) - Side-by-side comparison

---

Billing & Usage Tables

ai_usage

Tracks AI generation usage for billing quota enforcement.

Column	Type	Nullable	Default	Description
usage_id	UUID	NO	gen_random_uuid()	Primary key
organization_id	UUID	NO	-	FK to organization
user_id	UUID	NO	-	User who triggered usage
usage_type	TEXT	NO	-	Type of AI operation
tokens_input	INTEGER	YES	-	Input tokens consumed
tokens_output	INTEGER	YES	-	Output tokens generated
cost_usd	DECIMAL(10,6)	YES	-	Estimated cost in USD
billing_period_start	DATE	NO	-	Period start date
billing_period_end	DATE	NO	-	Period end date
created_at	TIMESTAMPTZ	NO	now()	Record creation timestamp

Constraints: usage_type IN ('analysis_generation', 'analysis_regeneration', 'ai_chat', 'report_section')

storage_usage

Detailed tracking of file storage per organization.

Column	Type	Nullable	Default	Description
usage_id	UUID	NO	gen_random_uuid()	Primary key
organization_id	UUID	NO	-	FK to organization
bucket_name	TEXT	NO	-	S3 bucket name
file_path	TEXT	NO	-	Full file path
file_size_bytes	BIGINT	NO	-	File size
file_type	TEXT	YES	-	MIME type
uploaded_by	UUID	YES	-	User who uploaded
created_at	TIMESTAMPTZ	NO	now()	Upload timestamp

Constraints: UNIQUE(organization_id, bucket_name, file_path)

---

Template Tables

project_template

Reusable templates for creating new projects.

Column	Type	Nullable	Default	Description
template_id	UUID	NO	gen_random_uuid()	Primary key
organization_id	UUID	NO	-	FK to organization
name	VARCHAR(200)	NO	-	Template name
description	TEXT	YES	-	Template description
work_type	VARCHAR(50)	YES	'investigation'	Default work type
created_by	UUID	NO	-	User who created template
created_at	TIMESTAMPTZ	YES	NOW()	Record creation timestamp
updated_at	TIMESTAMPTZ	YES	NOW()	Last update timestamp

template_topic

Topics within a project template.

Column	Type	Nullable	Default	Description
template_topic_id	UUID	NO	gen_random_uuid()	Primary key
template_id	UUID	NO	-	FK to project_template
title	VARCHAR(200)	NO	-	Topic title
description	TEXT	YES	-	Topic description
investigator_notes	TEXT	YES	-	Default notes
sequence_order	INTEGER	YES	0	Display order
created_at	TIMESTAMPTZ	YES	NOW()	Record creation timestamp

template_question

Questions within a project template.

Column	Type	Nullable	Default	Description
template_question_id	UUID	NO	gen_random_uuid()	Primary key
template_id	UUID	NO	-	FK to project_template
template_topic_id	UUID	YES	-	FK to template_topic
question_text	TEXT	NO	-	Question text
sequence_order	INTEGER	YES	0	Display order
created_at	TIMESTAMPTZ	YES	NOW()	Record creation timestamp

template_background_document

Background documents within a project template.

Column	Type	Nullable	Default	Description
template_bg_doc_id	UUID	NO	gen_random_uuid()	Primary key
template_id	UUID	NO	-	FK to project_template
title	VARCHAR(200)	NO	-	Document title
description	TEXT	YES	-	Document description
document_type	VARCHAR(50)	YES	-	Type of document
file_name	VARCHAR(255)	YES	-	Original filename
file_path	VARCHAR(500)	YES	-	S3 storage path
file_type	VARCHAR(100)	YES	-	MIME type
file_size	INTEGER	YES	-	Size in bytes
display_order	INTEGER	YES	0	Display order
created_at	TIMESTAMPTZ	YES	NOW()	Record creation timestamp

template_framework_document

Framework documents within a project template.

Column	Type	Nullable	Default	Description
template_fw_doc_id	UUID	NO	gen_random_uuid()	Primary key
template_id	UUID	NO	-	FK to project_template
title	VARCHAR(200)	NO	-	Document title
description	TEXT	YES	-	Document description
document_type	VARCHAR(50)	YES	-	Type of document
file_name	VARCHAR(255)	YES	-	Original filename
file_path	VARCHAR(500)	YES	-	S3 storage path
file_type	VARCHAR(100)	YES	-	MIME type
file_size	INTEGER	YES	-	Size in bytes
display_order	INTEGER	YES	0	Display order
created_at	TIMESTAMPTZ	YES	NOW()	Record creation timestamp

---

Audit & Compliance Tables

audit_log

Immutable audit trail for FedRAMP/HIPAA compliance.

Column	Type	Nullable	Default	Description
log_id	UUID	NO	gen_random_uuid()	Primary key
created_at	TIMESTAMPTZ	NO	now()	When action occurred
user_id	UUID	YES	-	User who performed action
organization_id	UUID	YES	-	Organization context
action	audit_action	NO	-	Action type (enum)
resource_type	audit_resource_type	NO	-	Resource type (enum)
resource_id	UUID	YES	-	ID of affected resource
ip_address	INET	YES	-	Client IP address
user_agent	TEXT	YES	-	Client user agent
metadata	JSONB	YES	'{}'	Additional context
success	BOOLEAN	NO	true	Whether action succeeded
error_message	TEXT	YES	-	Error details if failed
contains_phi	BOOLEAN	YES	false	HIPAA: PHI was accessed

Note: This table is append-only. No updates or deletes allowed.

---

Enum Types

work_type

Project/investigation type:

• evaluation
• review
• audit
• inspection
• investigation
• inquiry
• assessment
• case
• other

background_document_type

Context document types:

• charge_letter
• org_chart
• scope_memo
• policy
• other

audit_action

Auditable actions (see audit_logging migration for full list):

• Authentication: auth.login, auth.logout, auth.login_failed, auth.password_reset
• Investigation: investigation.create, investigation.view, investigation.update, investigation.delete
• Evidence: evidence.create, evidence.view, evidence.update, evidence.delete, evidence.download
• Analysis: analysis.generate, analysis.view
• Report: report.generate, report.view, report.download
• Organization: organization.create, organization.update, member management
• GDPR: gdpr.data_export, gdpr.account_delete

audit_resource_type

Auditable resource types:

• user, organization, investigation, evidence, topic, question, analysis, report, invitation, system, prompt

---

Helper Functions

Function	Returns	Description
user_organization_ids()	SETOF UUID	Returns all organization IDs for current user
user_has_org_access(org_id, min_role)	BOOLEAN	Checks if user has at least min_role in org
is_admin()	BOOLEAN	Checks if current user is system admin
handle_new_user()	TRIGGER	Auto-creates profile and personal org on signup
check_storage_quota(org_id, bytes)	BOOLEAN	Returns true if upload fits within quota
get_storage_stats(org_id)	TABLE	Returns storage usage breakdown
get_ai_usage_count(org_id)	INTEGER	Returns AI usage count for current period
check_ai_usage_quota(org_id)	BOOLEAN	Returns true if under AI usage limit
record_ai_usage(...)	UUID	Records an AI usage event
sync_billing_quotas(org_id)	VOID	Updates quotas based on billing status
cleanup_old_audit_logs(days)	INTEGER	Removes audit logs older than retention period
get_prompt_history(prompt_type)	TABLE	Returns all versions of a prompt
get_prompt_version(prompt_type, version)	TABLE	Returns full content of a specific version
rollback_prompt(prompt_type, version, reason)	INTEGER	Restores a previous version, returns new ver
compare_prompt_versions(type, v1, v2)	TABLE	Side-by-side comparison of two versions
archive_prompt_version()	TRIGGER	Auto-archives prompt before UPDATE
user_has_investigation_access(investigation_id)	BOOLEAN	Dual-check: investigation_member OR org membership

---

S3 Storage Buckets

Bucket	Purpose	Path Pattern
evidence	Evidence attachments	{org_id}/{investigation_id}/{evidence_id}/{filename}
framework-documents	Framework/policy docs	{org_id}/{investigation_id}/{document_id}/{filename}

---

Dropped Tables

These tables were removed and should not be used:

Table	Removed	Replaced By
evaluation_guide	2026-01-27	framework_document
investigation_guide_link	2026-01-27	N/A (was junction table)