cross-datasource-mapping
Cross-Datasource Mapping
This skill documents how the same entities map across the three datasources: Notion API, NotionArchive (MSSQL), and StonePro (Firebird).
Entity: Job / Production Order
| Field | Notion (Production DB) | NotionArchive MSSQL | StonePro Firebird |
|---|---|---|---|
| Primary key | Page UUID | notion_jobs.id (int) |
DOC_HEADER.DOC_ID |
| Notion ID | Page UUID | notion_page_id |
— |
| Job number | Job (title) |
job_number |
DOC_NUMBER |
| Job name | Job Name (rich_text) |
job_name |
(derived from CUSTOMER_NAME + SITE_ADDRESS) |
| Status | Status (status) |
status |
DOC_STATUS (int code) |
| Install date | Install Date (formula → Install relation) |
install_date (ISO string) |
DUE_DATE |
| Customer | Relation → Customers DB | customer_name (denorm) |
CUSTOMER_ID FK |
| Stone colour | Relation → Stone Colours DB | stone_colour (denorm) |
COLOUR |
| Suburb | Rich text property | suburb |
SUBURB |
| Folder path | Folder in O: Drive (rich_text) |
folder_path |
— |
Entity: Piece
| Field | Notion (Pieces DB) | NotionArchive MSSQL | StonePro Firebird |
|---|---|---|---|
| Primary key | Page UUID | notion_pieces.id |
DOC_ITEMS.ITEM_ID |
| Piece number | Piece No (title) |
piece_no |
LINE_NUMBER + DESCRIPTION |
| Parent job | Relation → Production | production_id (FK) |
DOC_ID (FK) |
| Material | Relation → Stone Colours | stone_colour |
MATERIAL_NAME |
| Length | Number property | length (float) |
LENGTH (NUMERIC) |
| Width | Number property | width (float) |
WIDTH (NUMERIC) |
| Thickness | Number property | thickness (float) |
THICKNESS (NUMERIC) |
| Area | Number property (m²) | area (float) |
AREA (NUMERIC) |
| Edge profile | Multi-select | edge_profile (CSV) |
EDGE_PROFILE |
| NC file | Rich text | nc_file |
— |
| Machine | Select | machine |
— |
Entity: Slab
| Field | Notion (Slabs DB) | NotionArchive MSSQL | StonePro Firebird |
|---|---|---|---|
| Primary key | Page UUID | notion_slabs.id |
INVENTORY.INVENTORY_ID |
| Slab name | Title property | slab_name |
SLAB_NUMBER |
| Stone colour | Relation → Stone Colours | stone_colour |
COLOUR |
| Supplier | Relation → Suppliers | supplier |
SUPPLIER_NAME |
| Bundle | Text property | bundle |
BUNDLE_NUMBER |
| Lot number | Text property | lot_number |
LOT_NUMBER |
| Dimensions | L/W/T number properties | length/width/thickness |
LENGTH/WIDTH/THICKNESS |
| Parent job | Relation → Production | production_id |
RESERVED_FOR / USED_IN |
Entity: Cutout
| Field | Notion (Cutouts DB) | NotionArchive MSSQL | StonePro Firebird |
|---|---|---|---|
| Primary key | Page UUID | notion_cutouts.id |
— (embedded in DOC_ITEMS) |
| Cutout name | Title property | cutout_name |
CUTOUT_DETAILS (text field) |
| Parent piece | Relation → Pieces | piece_id (FK) |
ITEM_ID (FK) |
| Type | Select | cutout_type |
— |
| Quantity | Number | quantity |
— |
Entity: Customer
| Field | Notion (Customers DB) | NotionArchive MSSQL | StonePro Firebird |
|---|---|---|---|
| Primary key | Page UUID | notion_customers.id |
CUSTOMERS.CUSTOMER_ID |
| Name | Title property | customer_name |
COMPANY_NAME or FIRST_NAME + LAST_NAME |
| Phone | Text property | phone |
PHONE / MOBILE |
| Text property | email |
EMAIL |
|
| Address | Text property | address |
ADDRESS + SUBURB + STATE + POSTCODE |
Entity: Stone Colour / Material
| Field | Notion (Stone Colours DB) | NotionArchive MSSQL | StonePro Firebird |
|---|---|---|---|
| Primary key | Page UUID | notion_stone_colours.id |
MATERIALS.MATERIAL_ID |
| Name | Title property | colour_name |
MATERIAL_NAME |
| Type | Select (Natural/Engineered) | material_type |
MATERIAL_TYPE |
| Supplier | Relation → Suppliers | supplier |
SUPPLIER_ID FK |
| Thickness | Number | thickness |
THICKNESS |
ID Cross-Reference
The system links records across datasources:
| Entity | Notion → MSSQL Link | MSSQL → StonePro Link |
|---|---|---|
| Job | notion_page_id (UUID) |
job_number = DOC_NUMBER |
| Piece | notion_page_id (UUID) |
Manual via job + description |
| Slab | notion_page_id (UUID) |
lot_number + bundle match |
| Customer | notion_page_id (UUID) |
customer_name fuzzy match |
| Colour | notion_page_id (UUID) |
colour_name = MATERIAL_NAME |
Cross-Reference Implementation
- Notion → MSSQL: Always via
notion_page_idcolumn (guaranteed unique per page) - MSSQL → StonePro: Via
job_number↔DOC_NUMBER(primary link), supplemented by name matching for customers and materials - StonePro → Notion: Not directly linked; requires MSSQL as intermediary
- Mapping logic lives in
stonepro_service/mapping/andnotion_sync/mappings/
More from ds-codi/project-memory-mcp
pyside6-mvc
Use this skill when building Python desktop applications using PySide6 with strict MVC architecture where all UI is defined by .ui files. Covers architecture patterns, controller/model/view separation, signal handling, and .ui file workflows.
95pyside6-qml-models-services
Use this skill when creating domain models with Qt signal support, implementing the repository pattern for data persistence, building service classes for external interactions, designing the central signal registry, or working with application state management. Covers BaseModel, model serialization, database repositories, service patterns, signal definitions, and the ApplicationState singleton.
34pyside6-qml-bridge
Use this skill when exposing Python objects to QML, creating bridge classes, defining Qt properties with NOTIFY signals, implementing invokable methods / slots, or connecting QML user actions to Python controllers. Covers the QObject bridge pattern, property decorators, type conversions, context properties, and QML type registration.
32vscode-chat-response-stream
Use this skill when building VS Code chat participant responses using ChatResponseStream. Covers markdown, command buttons, command links, file trees, progress messages, references/anchors, the generic push() method, and ChatFollowupProvider for suggested follow-ups.
3cxxqt-rust-gui
Use this skill when building Qt-based GUI applications in Rust using CxxQt. Covers project structure, QObject integration, QML bindings, signal/slot patterns, and build configuration with cxx-qt-build.
3message-broker
Use this skill when building TCP-based pub/sub message brokers for inter-process communication between Python applications and AutoHotkey scripts. Covers architecture, protocol design, client/server patterns, and message routing.
3