> ## Documentation Index
> Fetch the complete documentation index at: https://thenile.dev/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# SmartSpend AI - Set teams free from manual expenses

<img src="https://mintcdn.com/nile/PVqSPvIIF-xsKfJe/images/expensesnapshot.png?fit=max&auto=format&n=PVqSPvIIF-xsKfJe&q=85&s=2e3714919c7ce9fd3dd9827848fc4723" alt="expensesnapshot" width="1331" height="913" data-path="images/expensesnapshot.png" />

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

<img src="https://mintcdn.com/nile/PVqSPvIIF-xsKfJe/images/expenseschema.png?fit=max&auto=format&n=PVqSPvIIF-xsKfJe&q=85&s=b9ef344c4ce77c80946464336ed1f95b" alt="expenseschema" width="1384" height="1005" data-path="images/expenseschema.png" />

### 1. expenses

Tracks individual expenses submitted by users.

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
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.

```sql theme={null}
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

```sql theme={null}
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)
);
```