Introduction

The previous article defined the high-level system architecture for the AI Tender Opportunity Scanner.

We established that the product will use:

• React for the frontend
• FastAPI for the backend API
• PostgreSQL for persistent storage
• SQLAlchemy for database access
• Alembic for schema migrations
• pgvector for semantic matching
• OpenAI services for summaries and embeddings

The next step is designing the database schema that connects these components.

The database will become the operational foundation of the application. It must store tender notices, preserve raw procurement data, track ingestion runs, hold AI-generated summaries, maintain business profiles, calculate user-specific matches, and record actions such as saving or dismissing an opportunity.

A weak schema can create serious problems later:

• Duplicate tenders
• Inconsistent source data
• Difficult migrations
• Slow dashboard queries
• Unclear data ownership
• Untraceable AI output
• Expensive reprocessing
• Broken user isolation

In this article, we will design a practical relational schema for the MVP while preserving a clear path toward authentication, subscriptions, multiple tender sources, and team accounts.

Development Progress

Project Scope and Roadmap        ████████████████████ 100%
MVP Definition                   ████████████████████ 100%
Technology Stack                 ████████████████████ 100%
System Architecture              ████████████████████ 100%
Database Design                  ████████████████████ 100%
Backend Foundation               ░░░░░░░░░░░░░░░░░░░░   0%
Tender Ingestion                 ░░░░░░░░░░░░░░░░░░░░   0%
AI Analysis                      ░░░░░░░░░░░░░░░░░░░░   0%
Matching Engine                  ░░░░░░░░░░░░░░░░░░░░   0%
Frontend Dashboard               ░░░░░░░░░░░░░░░░░░░░   0%

Database Design Goals

Before defining tables, we need to establish the main design principles.

1. Separate Shared Data from User-Owned Data

Tender notices are global.

A tender published by a procurement authority should be stored once and reused across all users.

Business profiles, match scores, saved tenders, dismissals, and notes are user-specific.

This distinction prevents the same tender from being duplicated for every account.

2. Preserve Raw Source Payloads

Normalized tender fields will power the dashboard and matching engine.

However, normalization may discard source-specific fields or interpret values incorrectly.

The original source payload must therefore be retained for:

• Auditing
• Reprocessing
• Debugging
• Source-format changes
• New field extraction
• Compliance investigations

3. Make Processing Traceable

The database should make it possible to determine:

• When a tender was collected
• Which source produced it
• Which ingestion run imported it
• Which model summarized it
• Which embedding model created its vector
• Which scoring version generated a match
• Why a processing attempt failed

This traceability is essential for a data and AI product.

4. Enforce Data Integrity

The database should prevent invalid states where possible.

Examples include:

• Duplicate source records
• Match scores outside the valid range
• Saved tenders without users
• Matches without profiles
• Tender summaries without tenders
• Negative contract values
• Invalid date relationships

Application validation is useful, but database constraints provide the final protection.

5. Support Reprocessing

A tender may need to be:

• Renormalized
• Resummarized
• Re-embedded
• Rematched
• Updated after a source change

The schema should allow current results to be replaced or versioned without recreating the tender.

6. Optimize for MVP Queries

The initial dashboard will frequently query:

• Tenders ordered by match score
• Active tenders before the deadline
• Tenders within a country
• Tenders within a value range
• Saved tenders
• Dismissed tenders
• New recommendations
• Failed processing records

Indexes should support these workflows.

Core Entity Overview

The initial schema will contain the following major entities:

User
BusinessProfile
TenderSource
IngestionRun
RawTenderRecord
Tender
TenderSummary
TenderEmbedding
BusinessProfileEmbedding
TenderMatch
SavedTender
DismissedTender
ProcessingError

A later version may add:

Organization
OrganizationMember
Subscription
AlertPreference
AlertDelivery
TenderDocument
TenderStatusHistory
AuditLog

These later entities do not need to block the MVP.

High-Level Relationship Diagram

User
 |
 | one-to-one or one-to-many
 v
BusinessProfile
 |
 | one-to-many
 v
TenderMatch ----------------------+
 |                                |
 | many-to-one                    | many-to-one
 v                                v
Tender                         BusinessProfile
 |
 +-------------------+---------------------+
 |                   |                     |
 v                   v                     v
TenderSummary   TenderEmbedding      SavedTender
 |
 v
DismissedTender
TenderSource
 |
 | one-to-many
 v
RawTenderRecord
 |
 | many-to-one
 v
Tender
TenderSource
 |
 | one-to-many
 v
IngestionRun
 |
 | one-to-many
 v
RawTenderRecord
Tender
 |
 | one-to-many
 v
ProcessingError

This diagram is simplified.

Some relationships, such as saved and dismissed records, also reference users directly.

Entity 1: Users

The users table represents application accounts.

Authentication may be introduced after the first local prototype, but including a user entity now prevents user ownership from being retrofitted later.

Proposed Fields

id
email
hashed_password
full_name
is_active
is_admin
created_at
updated_at

Example Structure

users
------------------------------------------
id                  UUID primary key
email               VARCHAR unique not null
hashed_password     VARCHAR nullable initially
full_name           VARCHAR nullable
is_active           BOOLEAN default true
is_admin            BOOLEAN default false
created_at          TIMESTAMP WITH TIME ZONE
updated_at          TIMESTAMP WITH TIME ZONE

The hashed_password field may remain nullable during early development if the application uses a seeded local user.

Before public deployment, authentication requirements will be tightened.

Important Constraints

• Email must be unique.
• Email should be normalized before storage.
• Passwords must never be stored as plain text.
• User deletion must be handled carefully because user-owned records may need retention or anonymization.

Entity 2: Business Profiles

The business_profiles table stores the information used to match tenders to a business.

The MVP will initially support one active profile per user.

The schema can still use a one-to-many relationship so that multiple profiles can be introduced later.

Proposed Fields

id
user_id
name
company_name
company_description
services
target_industries
preferred_countries
preferred_regions
included_keywords
excluded_keywords
minimum_contract_value
maximum_contract_value
preferred_currency
certifications
preferred_languages
is_active
created_at
updated_at

Example Structure

business_profiles
------------------------------------------------
id                          UUID primary key
user_id                     UUID foreign key
name                        VARCHAR not null
company_name                VARCHAR nullable
company_description         TEXT not null
services                    JSONB not null
target_industries           JSONB nullable
preferred_countries         JSONB nullable
preferred_regions           JSONB nullable
included_keywords           JSONB nullable
excluded_keywords           JSONB nullable
minimum_contract_value      NUMERIC nullable
maximum_contract_value      NUMERIC nullable
preferred_currency          CHAR(3) nullable
certifications              JSONB nullable
preferred_languages         JSONB nullable
is_active                   BOOLEAN default true
created_at                  TIMESTAMP WITH TIME ZONE
updated_at                  TIMESTAMP WITH TIME ZONE

Why Use JSONB for Lists?

Fields such as services, industries, keywords, and certifications are naturally list-based.

For the MVP, JSONB provides a practical balance between structure and flexibility.

Example:

[
  "cloud migration",
  "Microsoft 365",
  "managed IT services",
  "cybersecurity consulting"
]

A later version could normalize some of these values into separate tables if advanced analytics, taxonomies, or shared classification systems become important.

Business Profile Value Constraints

The following validation rules should apply:

minimum_contract_value >= 0
maximum_contract_value >= 0
minimum_contract_value <= maximum_contract_value

These rules should be enforced both in the API and through database constraints where practical.

Entity 3: Tender Sources

The tender_sources table describes procurement platforms, APIs, feeds, or portals.

Examples might include:

• A European tender feed
• A national procurement API
• A regional government portal
• A manually imported dataset

Proposed Fields

id
name
slug
source_type
base_url
country_code
is_active
configuration
created_at
updated_at

Example Structure

tender_sources
-----------------------------------------------
id                  UUID primary key
name                VARCHAR not null
slug                VARCHAR unique not null
source_type         VARCHAR not null
base_url            TEXT nullable
country_code        CHAR(2) nullable
is_active           BOOLEAN default true
configuration       JSONB nullable
created_at          TIMESTAMP WITH TIME ZONE
updated_at          TIMESTAMP WITH TIME ZONE

Source Type Examples

api
json_feed
xml_feed
csv_import
html
manual

The configuration field can store non-secret source settings such as:

{
  "page_size": 100,
  "default_language": "en",
  "supports_updates": true
}

Secret credentials should remain in environment variables or a secret manager rather than inside this table.

Entity 4: Ingestion Runs

The ingestion_runs table records each execution of a tender import.

This provides operational visibility and allows developers or administrators to inspect historical ingestion behavior.

Proposed Fields

id
source_id
status
trigger_type
started_at
completed_at
records_retrieved
records_created
records_updated
records_skipped
records_failed
cursor_value
error_summary
created_at

Example Structure

ingestion_runs
------------------------------------------------
id                      UUID primary key
source_id               UUID foreign key
status                  VARCHAR not null
trigger_type            VARCHAR not null
started_at              TIMESTAMP WITH TIME ZONE
completed_at            TIMESTAMP WITH TIME ZONE nullable
records_retrieved       INTEGER default 0
records_created         INTEGER default 0
records_updated         INTEGER default 0
records_skipped         INTEGER default 0
records_failed          INTEGER default 0
cursor_value            TEXT nullable
error_summary           TEXT nullable
created_at              TIMESTAMP WITH TIME ZONE

Ingestion Status Values

pending
running
completed
completed_with_errors
failed
cancelled

Trigger Type Values

manual
scheduled
retry
backfill

Why Store a Cursor?

Some procurement APIs support incremental collection through:

• Last-modified timestamps
• Pagination tokens
• Publication dates
• Sequence numbers

The cursor_value field allows the ingestion process to resume or continue from a known point.

Entity 5: Raw Tender Records

The raw_tender_records table preserves the original data received from a source.

A raw record represents the source-level notice before normalization.

Proposed Fields

id
source_id
ingestion_run_id
external_id
source_url
payload
payload_hash
retrieved_at
processing_status
processing_attempts
last_error
created_at
updated_at

Example Structure

raw_tender_records
------------------------------------------------
id                      UUID primary key
source_id               UUID foreign key
ingestion_run_id        UUID foreign key nullable
external_id             VARCHAR not null
source_url              TEXT nullable
payload                 JSONB not null
payload_hash            VARCHAR nullable
retrieved_at            TIMESTAMP WITH TIME ZONE
processing_status       VARCHAR not null
processing_attempts     INTEGER default 0
last_error              TEXT nullable
created_at              TIMESTAMP WITH TIME ZONE
updated_at              TIMESTAMP WITH TIME ZONE

Unique Constraint

The first important uniqueness rule is:

UNIQUE(source_id, external_id)

This prevents the same source record from being imported repeatedly as a new row.

Payload Hash

A checksum of the raw payload can help detect whether an existing source record has changed.

For example:

Existing external ID + same hash:
No material update.
Existing external ID + different hash:
Source record has changed and may require reprocessing.

Raw Processing Status Values

retrieved
normalization_pending
normalized
failed
ignored

Entity 6: Tenders

The tenders table stores normalized tender data.

This is the primary business entity used by the API, dashboard, AI analysis, and matching engine.

Proposed Fields

id
source_id
raw_record_id
external_id
reference_number
title
description
contracting_authority
buyer_type
country_code
region
city
publication_date
submission_deadline
contract_start_date
contract_end_date
estimated_value
currency
procurement_category
cpv_codes
language_code
source_url
notice_status
processing_status
created_at
updated_at

Example Structure

tenders
------------------------------------------------
id                          UUID primary key
source_id                   UUID foreign key
raw_record_id               UUID foreign key nullable
external_id                 VARCHAR not null
reference_number            VARCHAR nullable
title                       TEXT not null
description                 TEXT nullable
contracting_authority       TEXT nullable
buyer_type                  VARCHAR nullable
country_code                CHAR(2) nullable
region                      VARCHAR nullable
city                        VARCHAR nullable
publication_date            DATE nullable
submission_deadline         TIMESTAMP WITH TIME ZONE nullable
contract_start_date         DATE nullable
contract_end_date           DATE nullable
estimated_value             NUMERIC nullable
currency                    CHAR(3) nullable
procurement_category        VARCHAR nullable
cpv_codes                   JSONB nullable
language_code               VARCHAR nullable
source_url                  TEXT nullable
notice_status               VARCHAR not null
processing_status           VARCHAR not null
created_at                  TIMESTAMP WITH TIME ZONE
updated_at                  TIMESTAMP WITH TIME ZONE

Tender Uniqueness

The normalized tender should also enforce:

UNIQUE(source_id, external_id)

This provides a second level of duplicate protection.

Notice Status Values

active
expired
awarded
cancelled
withdrawn
unknown

Processing Status Values

normalization_complete
analysis_pending
analysis_complete
embedding_pending
embedding_complete
matching_pending
processed
failed

The status list may be simplified later.

It is often better to use separate status fields for separate pipelines if one general status becomes ambiguous.

Contract Value Type

Contract values should use a decimal-compatible database type such as NUMERIC.

Floating-point values should not be used for monetary amounts.

Example:

NUMERIC(18, 2)

This supports large contracts while preserving currency precision.

Optional Fields

Tender sources frequently omit values such as contract amount, region, contract dates, or category.

These columns should remain nullable.

Missing data should be represented as unknown rather than replaced with misleading defaults.

Entity 7: Tender Summaries

The tender_summaries table stores AI-generated structured analysis.

Keeping AI output separate from the tender table has several advantages:

• AI results can be regenerated
• Prompt versions can be tracked
• Model versions can be compared
• Failures can be isolated
• Tender records remain source-focused

Proposed Fields

id
tender_id
overview
deliverables
requirements
certifications
language_requirements
risks
contract_duration
missing_information
model_name
prompt_version
input_hash
token_input
token_output
status
generated_at
created_at
updated_at

Example Structure

tender_summaries
------------------------------------------------
id                      UUID primary key
tender_id               UUID foreign key
overview                TEXT nullable
deliverables            JSONB nullable
requirements            JSONB nullable
certifications          JSONB nullable
language_requirements   JSONB nullable
risks                    JSONB nullable
contract_duration       VARCHAR nullable
missing_information     JSONB nullable
model_name              VARCHAR not null
prompt_version          VARCHAR not null
input_hash              VARCHAR nullable
token_input             INTEGER nullable
token_output            INTEGER nullable
status                  VARCHAR not null
generated_at            TIMESTAMP WITH TIME ZONE nullable
created_at              TIMESTAMP WITH TIME ZONE
updated_at              TIMESTAMP WITH TIME ZONE

One Current Summary or Multiple Versions?

Two approaches are possible.

Option A: One Summary per Tender

Enforce:

UNIQUE(tender_id)

When a summary is regenerated, update the existing row.

This is simpler for the MVP.

Option B: Versioned Summaries

Allow multiple summaries for the same tender and mark one as current.

This provides a complete historical record but creates more complexity.

For the initial build, one current summary per tender is sufficient.

Version history can later be added through an audit or revision table.

Input Hash

An input_hash can represent the tender text used to generate the summary.

If the tender content has not changed, the system can avoid regenerating the summary unnecessarily.

Entity 8: Tender Embeddings

The tender_embeddings table stores the vector representation of each tender.

Proposed Fields

id
tender_id
embedding
model_name
embedding_dimensions
content_hash
created_at
updated_at

Example Structure

tender_embeddings
------------------------------------------------
id                      UUID primary key
tender_id               UUID foreign key unique
embedding               VECTOR not null
model_name              VARCHAR not null
embedding_dimensions    INTEGER not null
content_hash            VARCHAR nullable
created_at              TIMESTAMP WITH TIME ZONE
updated_at              TIMESTAMP WITH TIME ZONE

The exact vector dimension depends on the selected embedding model.

Why a Separate Table?

Embedding data can be stored directly on the tender table, but a separate table provides:

• Cleaner tender records
• Easier model migration
• Optional embedding generation
• Better isolation of vector-specific indexes
• Easier replacement when models change

Entity 9: Business Profile Embeddings

The business_profile_embeddings table stores semantic vectors for user profiles.

Proposed Fields

id
business_profile_id
embedding
model_name
embedding_dimensions
content_hash
created_at
updated_at

Example Structure

business_profile_embeddings
------------------------------------------------
id                      UUID primary key
business_profile_id     UUID foreign key unique
embedding               VECTOR not null
model_name              VARCHAR not null
embedding_dimensions    INTEGER not null
content_hash            VARCHAR nullable
created_at              TIMESTAMP WITH TIME ZONE
updated_at              TIMESTAMP WITH TIME ZONE

The embedding should be regenerated whenever the profile’s matching content changes.

Entity 10: Tender Matches

The tender_matches table connects a business profile to a tender.

This is one of the most important tables in the application.

A tender is global, but its relevance is profile-specific.

Proposed Fields

id
business_profile_id
tender_id
overall_score
semantic_score
keyword_score
service_score
industry_score
geography_score
value_score
certification_score
deadline_score
exclusion_penalty
strong_matches
potential_gaps
score_version
status
calculated_at
created_at
updated_at

Example Structure

tender_matches
------------------------------------------------
id                          UUID primary key
business_profile_id         UUID foreign key
tender_id                   UUID foreign key
overall_score               NUMERIC not null
semantic_score              NUMERIC nullable
keyword_score               NUMERIC nullable
service_score               NUMERIC nullable
industry_score              NUMERIC nullable
geography_score             NUMERIC nullable
value_score                 NUMERIC nullable
certification_score         NUMERIC nullable
deadline_score              NUMERIC nullable
exclusion_penalty           NUMERIC default 0
strong_matches              JSONB nullable
potential_gaps              JSONB nullable
score_version               VARCHAR not null
status                      VARCHAR not null
calculated_at               TIMESTAMP WITH TIME ZONE
created_at                  TIMESTAMP WITH TIME ZONE
updated_at                  TIMESTAMP WITH TIME ZONE

Unique Constraint

A profile should have one current match record per tender:

UNIQUE(business_profile_id, tender_id)

If the match is recalculated, the record can be updated.

Score Constraints

The database should enforce valid score ranges.

For example:

overall_score >= 0
overall_score <= 100

The same rule can apply to individual components if they use a zero-to-one-hundred scale.

Alternatively, component scores may use zero-to-one internally.

The selected convention must remain consistent.

Why Store Score Components?

Only storing the final score would make debugging difficult.

Separate components allow us to answer:

• Was the semantic score too high?
• Did the geography rule fail?
• Was the tender rejected due to value?
• Did an exclusion keyword reduce the score?
• Which scoring version produced the result?

This is critical for match-quality improvement.

Match Status Values

new
viewed
saved
dismissed
expired

There is an important design question here.

Should status live in tender_matches, or should save and dismiss actions be stored separately?

For the MVP, saved and dismissed actions will remain separate tables because they represent explicit user actions and may require metadata.

The match status can therefore focus on system-level state such as:

active
below_threshold
expired
failed

Entity 11: Saved Tenders

The saved_tenders table records tenders that a user wants to review further.

Proposed Fields

id
user_id
tender_id
business_profile_id
note
saved_at
created_at
updated_at

Example Structure

saved_tenders
------------------------------------------------
id                      UUID primary key
user_id                 UUID foreign key
tender_id               UUID foreign key
business_profile_id     UUID foreign key nullable
note                    TEXT nullable
saved_at                TIMESTAMP WITH TIME ZONE
created_at              TIMESTAMP WITH TIME ZONE
updated_at              TIMESTAMP WITH TIME ZONE

Unique Constraint

A user should not save the same tender repeatedly:

UNIQUE(user_id, tender_id)

The business_profile_id field can preserve the profile context under which the tender was saved.

Entity 12: Dismissed Tenders

The dismissed_tenders table stores opportunities that a user does not want to see in normal recommendations.

Proposed Fields

id
user_id
tender_id
business_profile_id
reason_code
reason_text
dismissed_at
created_at

Example Structure

dismissed_tenders
------------------------------------------------
id                      UUID primary key
user_id                 UUID foreign key
tender_id               UUID foreign key
business_profile_id     UUID foreign key nullable
reason_code             VARCHAR nullable
reason_text             TEXT nullable
dismissed_at            TIMESTAMP WITH TIME ZONE
created_at              TIMESTAMP WITH TIME ZONE

Example Reason Codes

not_relevant
wrong_country
contract_too_large
contract_too_small
missing_certification
deadline_too_close
wrong_industry
already_known
other

These reasons may later be used to improve the matching engine.

Unique Constraint

UNIQUE(user_id, tender_id)

A user should have only one current dismissal record for a tender.

Removing a dismissal can delete the row.

Entity 13: Processing Errors

The processing_errors table stores failures from ingestion, normalization, AI analysis, embeddings, and matching.

Proposed Fields

id
stage
source_id
ingestion_run_id
raw_tender_record_id
tender_id
business_profile_id
error_type
error_message
error_details
retryable
retry_count
resolved
occurred_at
resolved_at
created_at

Example Structure

processing_errors
------------------------------------------------
id                      UUID primary key
stage                   VARCHAR not null
source_id               UUID foreign key nullable
ingestion_run_id        UUID foreign key nullable
raw_tender_record_id    UUID foreign key nullable
tender_id               UUID foreign key nullable
business_profile_id     UUID foreign key nullable
error_type              VARCHAR not null
error_message           TEXT not null
error_details           JSONB nullable
retryable               BOOLEAN default false
retry_count             INTEGER default 0
resolved                BOOLEAN default false
occurred_at             TIMESTAMP WITH TIME ZONE
resolved_at             TIMESTAMP WITH TIME ZONE nullable
created_at              TIMESTAMP WITH TIME ZONE

Processing Stage Values

source_fetch
raw_storage
normalization
ai_summary
embedding
matching
notification

Why Store Errors in the Database?

Application logs are useful but may be temporary or difficult to query.

Database error records allow administrators to:

• Filter unresolved failures
• Retry specific tenders
• Inspect error patterns
• Identify problematic sources
• Track repeated AI failures
• Measure processing reliability

Logs and database error records serve different purposes and should complement one another.

Optional Entity: Tender Documents

Tender documents are excluded from the first strict MVP, but the schema may later include a tender_documents table.

Possible Fields

id
tender_id
external_document_id
filename
document_type
source_url
storage_path
mime_type
file_size
checksum
download_status
extraction_status
created_at
updated_at

This table should not be implemented until document downloading and analysis become part of the product scope.

Optional Entity: Alert Preferences

Daily email alerts are also postponed, but the later schema may include:

alert_preferences
--------------------------------
id
user_id
business_profile_id
frequency
minimum_score
preferred_time
timezone
is_enabled
created_at
updated_at

This supports daily or weekly tender digests.

Shared Data Versus User-Specific Data

The distinction between global and user-owned tables should now be explicit.

Shared Tables

tender_sources
ingestion_runs
raw_tender_records
tenders
tender_summaries
tender_embeddings
processing_errors

These records describe procurement data and system processing.

User-Specific Tables

users
business_profiles
business_profile_embeddings
tender_matches
saved_tenders
dismissed_tenders

These records describe how an individual user or business relates to global tender data.

Recommended Foreign-Key Relationships

User Relationships

users.id
  -> business_profiles.user_id
  -> saved_tenders.user_id
  -> dismissed_tenders.user_id

Source Relationships

tender_sources.id
  -> ingestion_runs.source_id
  -> raw_tender_records.source_id
  -> tenders.source_id

Tender Relationships

tenders.id
  -> tender_summaries.tender_id
  -> tender_embeddings.tender_id
  -> tender_matches.tender_id
  -> saved_tenders.tender_id
  -> dismissed_tenders.tender_id
  -> processing_errors.tender_id

Business Profile Relationships

business_profiles.id
  -> business_profile_embeddings.business_profile_id
  -> tender_matches.business_profile_id
  -> saved_tenders.business_profile_id
  -> dismissed_tenders.business_profile_id
  -> processing_errors.business_profile_id

Deletion Strategy

Foreign-key deletion behavior must be selected carefully.

Deleting a User

Deleting a user may cascade to:

• Business profiles
• Profile embeddings
• Tender matches
• Saved tenders
• Dismissed tenders

Shared tenders must remain intact.

In a production SaaS environment, account deletion may require anonymization or delayed deletion rather than immediate cascading removal.

Deleting a Tender

Tenders should rarely be physically deleted.

If a tender is cancelled, expired, or withdrawn, its status should change.

Physical deletion could remove:

• Historical match results
• User actions
• Processing history
• Audit evidence

A soft-retention approach is safer.

Deleting a Source

A source should normally be deactivated rather than deleted.

Existing tenders and ingestion history should remain available.

Timestamp Strategy

Most tables should include:

created_at
updated_at

Operational tables may also include more specific timestamps:

retrieved_at
started_at
completed_at
generated_at
calculated_at
saved_at
dismissed_at
occurred_at
resolved_at

All timestamps should use timezone-aware values.

The application should store timestamps in UTC and convert them for display in the user interface.

Primary Key Strategy

UUIDs are suitable for the application because they:

• Avoid exposing sequential internal IDs
• Work well across future distributed components
• Can be generated outside the database
• Reduce identifier collisions during data merging

A typical PostgreSQL UUID primary key can use:

UUID

The application may generate UUID version 4 or another suitable UUID format.

Naming Conventions

The database should use consistent naming.

Recommended conventions:

Table names: plural snake_case
Column names: snake_case
Primary key: id
Foreign key: singular_entity_id
Timestamps: created_at, updated_at
Boolean fields: is_active, is_admin, resolved

Examples:

business_profiles
tender_sources
submission_deadline
business_profile_id

Consistency makes models, migrations, and queries easier to understand.

Indexing Strategy

Indexes should reflect real query patterns.

Tender Indexes

Recommended indexes include:

(source_id, external_id) unique
submission_deadline
publication_date
country_code
notice_status
processing_status
estimated_value
contracting_authority

A composite dashboard index may later include:

notice_status + submission_deadline

Match Indexes

Recommended indexes include:

(business_profile_id, tender_id) unique
business_profile_id
overall_score
calculated_at

A particularly useful composite index may be:

business_profile_id + overall_score DESC

This supports ranked tender recommendations.

Save and Dismiss Indexes

(user_id, tender_id) unique
user_id
saved_at
dismissed_at

Ingestion Indexes

source_id
status
started_at
external_id
processing_status

Vector Indexes

pgvector can support approximate nearest-neighbor indexes when the dataset becomes large enough.

For a small MVP dataset, exact similarity search may initially be sufficient.

Vector indexes should only be added after:

• The embedding model is fixed
• The vector dimension is known
• Query performance has been measured

Full-Text Search Design

PostgreSQL full-text search can later support searches across:

• Tender title
• Tender description
• Contracting authority
• AI overview
• Deliverables
• Requirements

A generated search-vector column may eventually combine important text fields.

Conceptually:

title
+ description
+ contracting authority
+ AI summary

This should be introduced when the dashboard search feature is implemented.

It does not need to complicate the first database migration.

Database Constraints

The schema should include constraints that prevent obviously invalid data.

Tender Constraints

estimated_value >= 0
submission_deadline can be null
currency length = 3 when present
country_code length = 2 when present

Match Constraints

overall_score >= 0
overall_score <= 100
exclusion_penalty >= 0

Ingestion Constraints

records_retrieved >= 0
records_created >= 0
records_updated >= 0
records_skipped >= 0
records_failed >= 0

Profile Constraints

minimum_contract_value >= 0
maximum_contract_value >= 0
minimum_contract_value <= maximum_contract_value

Enumeration Strategy

Statuses can be represented using:

• Native PostgreSQL enums
• String columns with check constraints
• Application-level enums

For the MVP, string columns with application enums and database check constraints provide flexibility.

Native database enums can be more difficult to change through migrations.

Example:

status VARCHAR NOT NULL
CHECK (status IN ('pending', 'running', 'completed', 'failed'))

Database Schema Diagram

The main MVP relationships can be visualized as follows:

+------------------+
| users            |
+------------------+
| id PK            |
| email            |
| full_name        |
+--------+---------+
         |
         | 1:N
         v
+--------------------------+
| business_profiles        |
+--------------------------+
| id PK                    |
| user_id FK               |
| company_description      |
| services                 |
| preferred_countries      |
| value range              |
+------------+-------------+
             |
             | 1:1
             v
+------------------------------+
| business_profile_embeddings  |
+------------------------------+
| id PK                        |
| business_profile_id FK       |
| embedding                    |
| model_name                   |
+------------------------------+
+------------------+
| tender_sources   |
+------------------+
| id PK            |
| name             |
| slug             |
| source_type      |
+--------+---------+
         |
         | 1:N
         v
+------------------+
| ingestion_runs   |
+------------------+
| id PK            |
| source_id FK     |
| status           |
| counters         |
+--------+---------+
         |
         | 1:N
         v
+-----------------------+
| raw_tender_records    |
+-----------------------+
| id PK                 |
| source_id FK          |
| ingestion_run_id FK   |
| external_id           |
| payload               |
+-----------+-----------+
            |
            | 1:1 or N:1
            v
+--------------------------+
| tenders                  |
+--------------------------+
| id PK                    |
| source_id FK             |
| raw_record_id FK         |
| title                    |
| description              |
| deadline                 |
| estimated_value          |
+------+-------------+-----+
       |             |
       | 1:1         | 1:1
       v             v
+---------------+  +-------------------+
| summaries     |  | tender_embeddings |
+---------------+  +-------------------+
| tender_id FK  |  | tender_id FK      |
| overview      |  | embedding         |
| requirements  |  | model_name        |
+---------------+  +-------------------+
business_profiles
       |
       | N:M through tender_matches
       v
+------------------------+
| tender_matches         |
+------------------------+
| business_profile_id FK |
| tender_id FK           |
| overall_score          |
| score components       |
| strong_matches         |
| potential_gaps         |
+------------------------+
users + tenders
       |
       +---------------------+
       |                     |
       v                     v
+---------------+    +-------------------+
| saved_tenders |    | dismissed_tenders |
+---------------+    +-------------------+
| user_id FK    |    | user_id FK        |
| tender_id FK  |    | tender_id FK      |
| note          |    | reason_code       |
+---------------+    +-------------------+

Example End-to-End Data Creation

Consider a new tender retrieved from a procurement API.

Step 1: Create an Ingestion Run

A row is added to:

ingestion_runs

with status:

running

Step 2: Store the Raw Record

The original source payload is stored in:

raw_tender_records

Step 3: Normalize the Record

A structured row is inserted into:

tenders

Step 4: Generate the AI Summary

A validated result is stored in:

tender_summaries

Step 5: Generate the Tender Embedding

The vector is stored in:

tender_embeddings

Step 6: Match Against Business Profiles

For every active relevant profile, a record is created or updated in:

tender_matches

Step 7: Display to the User

The dashboard joins:

tender_matches
tenders
tender_summaries
saved_tenders
dismissed_tenders

to produce the ranked opportunity list.

Example Dashboard Query Logic

The dashboard needs results that are:

• Matched to the active profile
• Above a minimum score
• Not dismissed
• Not expired
• Ordered by relevance

Conceptually:

SELECT
    t.id,
    t.title,
    t.contracting_authority,
    t.country_code,
    t.submission_deadline,
    t.estimated_value,
    t.currency,
    tm.overall_score
FROM tender_matches tm
JOIN tenders t
    ON t.id = tm.tender_id
LEFT JOIN dismissed_tenders dt
    ON dt.tender_id = t.id
    AND dt.user_id = :user_id
WHERE tm.business_profile_id = :profile_id
  AND tm.overall_score >= :minimum_score
  AND dt.id IS NULL
  AND t.notice_status = 'active'
ORDER BY tm.overall_score DESC;

The final implementation will use SQLAlchemy rather than embedding this exact SQL directly.

SQLAlchemy Model Boundaries

The upcoming backend implementation will define SQLAlchemy models for each table.

A likely organization is:

app/models/user.py
app/models/business_profile.py
app/models/tender_source.py
app/models/ingestion.py
app/models/tender.py
app/models/tender_summary.py
app/models/embedding.py
app/models/tender_match.py
app/models/user_tender_action.py
app/models/processing_error.py

A central models package can expose the metadata required by Alembic.

Migration Strategy

The schema should not be created manually in production databases.

Alembic migrations will define each structural change.

A practical migration sequence could be:

1. Create users and business profiles
2. Create tender sources and ingestion runs
3. Create raw tender records and tenders
4. Create summaries and processing errors
5. Enable pgvector
6. Create embedding tables
7. Create tender matches
8. Create saved and dismissed tenders
9. Add indexes and constraints

The first implementation may combine several of these into one initial migration.

Smaller migrations become preferable after the foundation is stable.

Data Versioning

Several parts of the system require version awareness.

Prompt Version

Stored in:

tender_summaries.prompt_version

Embedding Model Version

Stored in:

tender_embeddings.model_name
business_profile_embeddings.model_name

Matching Version

Stored in:

tender_matches.score_version

These fields allow the application to determine whether old records need to be regenerated.

Cost Tracking Considerations

AI cost tracking is not part of the earliest schema, but the summary table already includes token fields.

A later generic ai_usage_events table may contain:

id
user_id
tender_id
operation
provider
model_name
input_tokens
output_tokens
estimated_cost
created_at

This would support:

• Usage analytics
• Cost monitoring
• Subscription limits
• Model comparisons

For the MVP, summary-level token tracking is sufficient.

Multi-Tenant Upgrade Path

The current schema links business profiles directly to users.

A later team-oriented version may introduce:

organizations
organization_members

The ownership model would become:

Organization
   |
   v
BusinessProfile

Users would belong to organizations through memberships.

Because tenders remain globally shared, this change would mainly affect user-owned tables.

We do not need to implement organizations now.

Database Design Decisions

The MVP schema now includes the following decisions.

Decision 1: Store Tenders Globally

A tender is stored once regardless of how many users match it.

Decision 2: Store Matches Per Profile

Relevance is specific to a business profile.

Decision 3: Preserve Raw Payloads

Every imported notice retains its original source data.

Decision 4: Separate AI Output

Summaries and embeddings are stored outside the main tender table.

Decision 5: Track Processing Runs

Every ingestion execution receives an operational record.

Decision 6: Store Match Components

The final relevance score remains explainable.

Decision 7: Separate Saved and Dismissed Actions

Explicit user actions receive their own tables.

Decision 8: Use UUID Primary Keys

Identifiers are not tied to database sequences.

Decision 9: Use JSONB Selectively

Flexible lists and source payloads use JSONB, while core searchable fields use normal columns.

Decision 10: Design for Reprocessing

Hashes, model names, prompt versions, and score versions support controlled regeneration.

Final MVP Table List

The recommended initial database tables are:

users
business_profiles
tender_sources
ingestion_runs
raw_tender_records
tenders
tender_summaries
tender_embeddings
business_profile_embeddings
tender_matches
saved_tenders
dismissed_tenders
processing_errors

This schema supports the complete MVP workflow without introducing billing, document processing, team collaboration, or alert-delivery complexity.

Development Principle

The normalized database should contain the information the application understands.

The raw-data layer should contain the information the source actually provided.

These two layers must remain connected but distinct.

This principle allows the system to evolve without losing traceability.

What We Will Build Next

The database schema is now defined conceptually.

The next step is generating the actual backend project structure that will contain:

• FastAPI application setup
• Configuration management
• Database connection code
• API routers
• SQLAlchemy models
• Pydantic schemas
• Repositories
• Services
• Source adapters
• AI integrations
• Matching logic
• Tests

The next article in the development series is:

Generating the FastAPI Project Structure for the AI Tender Opportunity Scanner.

Conclusion

The database schema for the AI Tender Opportunity Scanner has been designed around the core MVP workflow.

Tender data is stored globally.

User-specific relevance is represented through business profiles and tender matches.

Raw source payloads are preserved for traceability.

Normalized tenders power the dashboard and API.

AI summaries and embeddings remain separately versioned.

Saved and dismissed tenders record explicit user actions.

Ingestion runs and processing errors provide operational visibility.

This design gives the application a strong relational foundation without overcomplicating the first release.

With the data model established, we can now begin creating the FastAPI project structure that will turn the architecture and schema into executable code.