The AI-native expense management system will be designed to automate the tracking, submission, and analysis of expenses using an intelligent AI agent. This agent will be capable of understanding and processing natural language inputs, allowing users to submit expenses through a chat interface. The system will support generative AI techniques using Retrieval-Augmented Generation (RAG) to provide insights and search through past expenses effectively. It will also leverage vector embeddings to store and query expense-related data, ensuring efficient and accurate information retrieval. The system will track expenses per tenant, making tenant_id a key field in every schema. The AI agent will process receipts, categorize expenses, flag anomalies, and generate expense reports. It also provides analytics and insights on spending patterns, helping users make informed financial decisions.

​
Postgres Schemas

​
1. expenses

Tracks individual expenses submitted by users.

CREATE TABLE expenses (
  expense_id uuid,
  tenant_id uuid,
  user_id uuid,
  amount DECIMAL(10, 2),
  category VARCHAR(255),
  description TEXT,
  expense_date DATE,
  expense_embedding vector(256),
  PRIMARY KEY(tenant_id, expense_id),
  FOREIGN KEY (tenant_id) REFERENCES tenants(id)
);

​
2. receipts

Stores receipt images and links them to expenses.

CREATE TABLE receipts (
  receipt_id uuid,
  tenant_id uuid,
  expense_id uuid,
  receipt_image BYTEA,
  receipts_embedding vector(256),
  PRIMARY KEY (tenant_id, receipt_id),
  FOREIGN KEY (tenant_id, expense_id) REFERENCES expenses(tenant_id, expense_id)
);

​
3. expense_reports

Aggregates expenses into reports for review and approval.

CREATE TABLE expense_reports (
  report_id uuid,
  tenant_id uuid,
  user_id uuid,
  report_name VARCHAR(255),
  start_date DATE,
  end_date DATE,
  status VARCHAR(50),
  PRIMARY KEY (tenant_id, report_id),
  FOREIGN KEY (tenant_id, user_id) REFERENCES users.tenant_users(tenant_id, user_id)
);

​
4. expense_report_details

Links expenses to expense reports.

CREATE TABLE expense_report_details (
  report_detail_id uuid,
  report_id uuid,
  expense_id uuid,
  tenant_id uuid,
  PRIMARY KEY (tenant_id, report_detail_id),
  FOREIGN KEY (tenant_id, expense_id) REFERENCES expenses(tenant_id, expense_id),
  FOREIGN KEY (tenant_id, report_id) REFERENCES expense_reports(tenant_id, report_id)
);

​
5. user_preferences

Stores user preferences for personalized AI responses.

CREATE TABLE user_preferences (
  preference_id uuid,
  tenant_id uuid,
  user_id uuid,
  preference_data JSONB,
  PRIMARY KEY (tenant_id, preference_id),
  FOREIGN KEY (tenant_id, user_id) REFERENCES users.tenant_users(tenant_id, user_id)
);

​
6. expense_analytics

Stores analytical data and insights generated by the AI.

CREATE TABLE expense_analytics (
  analytics_id uuid,
  tenant_id uuid,
  user_id uuid,
  analysis_date DATE,
  insights TEXT,
  PRIMARY KEY (tenant_id, analytics_id),
  FOREIGN KEY (tenant_id, user_id) REFERENCES users.tenant_users(tenant_id, user_id)
);

​
Full Script

CREATE TABLE expenses (
  expense_id uuid,
  tenant_id uuid,
  user_id uuid,
  amount DECIMAL(10, 2),
  category VARCHAR(255),
  description TEXT,
  expense_date DATE,
  expense_embedding vector(256),
  PRIMARY KEY(tenant_id, expense_id),
  FOREIGN KEY (tenant_id) REFERENCES tenants(id)
);

CREATE TABLE receipts (
  receipt_id uuid,
  tenant_id uuid,
  expense_id uuid,
  receipt_image BYTEA,
  receipts_embedding vector(256),
  PRIMARY KEY (tenant_id, receipt_id),
  FOREIGN KEY (tenant_id, expense_id) REFERENCES expenses(tenant_id, expense_id)
);

CREATE TABLE expense_reports (
  report_id uuid,
  tenant_id uuid,
  user_id uuid,
  report_name VARCHAR(255),
  start_date DATE,
  end_date DATE,
  status VARCHAR(50),
  PRIMARY KEY (tenant_id, report_id),
  FOREIGN KEY (tenant_id, user_id) REFERENCES users.tenant_users(tenant_id, user_id)
);

CREATE TABLE expense_report_details (
  report_detail_id uuid,
  report_id uuid,
  expense_id uuid,
  tenant_id uuid,
  PRIMARY KEY (tenant_id, report_detail_id),
  FOREIGN KEY (tenant_id, expense_id) REFERENCES expenses(tenant_id, expense_id),
  FOREIGN KEY (tenant_id, report_id) REFERENCES expense_reports(tenant_id, report_id)
);

CREATE TABLE user_preferences (
  preference_id uuid,
  tenant_id uuid,
  user_id uuid,
  preference_data JSONB,
  PRIMARY KEY (tenant_id, preference_id),
  FOREIGN KEY (tenant_id, user_id) REFERENCES users.tenant_users(tenant_id, user_id)
);

CREATE TABLE expense_analytics (
  analytics_id uuid,
  tenant_id uuid,
  user_id uuid,
  analysis_date DATE,
  insights TEXT,
  PRIMARY KEY (tenant_id, analytics_id),
  FOREIGN KEY (tenant_id, user_id) REFERENCES users.tenant_users(tenant_id, user_id)
);

Was this page helpful?

SmartBooks AIFeedback360 AI