Data Platform¶
Purpose¶
Architecture-level view of D1 and R2. Operational commands (querying, backup, troubleshooting) live under Cloudflare → D1/R2 and Operations - this page is about shape, not commands.
D1: akshaya-group-global¶
One database, binding name DB everywhere it appears. It is not exclusive to the central API
Worker. corporate-web, software-web, admin-web, commerce-web, and logistics-web each bind
the same database where their own Worker needs direct, scoped access.
commerce-web uses D1 for public catalog reads and the authenticated customer's own order history;
it does not write orders directly. Order pricing/creation remains in the protected central API.
logistics-web uses D1 for public-safe tracking/network reads and Admin-authenticated operational
workflows (shipments, routes, hubs, partners, drivers, vehicles and billing). It does not expose the
central OPERATIONS_API_TOKEN to browser code.
corporate-web uses D1 for customer identity/profile, marketplace activity and Customer Account
features. Saved addresses introduced in 0012 are customer-owned convenience records; they do not
replace the immutable order_address snapshot captured during Commerce checkout.
admin-web uses the shared identity/session tables plus admin_access (0013) and the opt-in MFA
state introduced in 0014. users.role='Admin' answers whether an identity is an administrator;
admin_access.access_level answers which business areas that Admin may operate.
embroidery-web and inactive investors-web do not need a D1 binding in their static app config.
Migrations (14, additive-only)¶
| # | File | What it adds |
|---|---|---|
| 0001 | 0001_initial.sql |
contacts, shipments, orders, legacy investor enquiry table |
| 0002 | 0002_freelancer_network.sql |
freelancer_applications |
| 0003 | 0003_customer_portal.sql |
customer_profiles |
| 0004 | 0004_unified_auth.sql |
users, sessions/audit/activation and user_id links into customer/freelancer/contact/order/shipment records |
| 0005 | 0005_freelancer_marketplace.sql |
Extends freelancer_applications - freelancer marketplace foundation |
| 0006 | 0006_freelancer_structured_skills.sql |
Structured freelancer skills |
| 0007 | 0007_customer_freelancer_shortlist.sql |
Customer freelancer shortlist |
| 0008 | 0008_customer_requirements.sql |
Customer requirement briefs |
| 0009 | 0009_freelancer_requirement_invitations.sql |
Customer-to-freelancer invitations |
| 0010 | 0010_commerce_catalog.sql |
Commerce categories/products/images, order_items, order_address, inventory_movements |
| 0011 | 0011_logistics_operations.sql |
Evolves shipments; adds routes, hubs, partners, drivers, vehicles, shipment history and logistics invoices |
| 0012 | 0012_customer_account.sql |
Customer saved delivery addresses with one active default per user |
| 0013 | 0013_admin_rbac.sql |
Per-Admin access level and safe existing-Admin SuperAdmin backfill |
| 0014 | 0014_admin_mfa.sql |
Encrypted Admin TOTP state, hashed one-time recovery codes and short-lived MFA challenges |
Production deployment applies the ordered files in workers/api/migrations/ before application
Workers are deployed. Migrations follow an additive discipline: new tables/columns/indexes/triggers,
never destructive drops or renames in the normal forward path.
Commerce catalog schema (0010)¶
| Table | Purpose | Key columns |
|---|---|---|
commerce_categories |
Storefront categories | slug (unique), status (Active/Inactive), sort_order |
commerce_products |
Catalog products | sku/slug (unique), category_id (FK), price, compare_at_price, stock_quantity, status, featured |
commerce_product_images |
Product photos | product_id (FK, ON DELETE CASCADE), object_key (R2 key), sort_order |
order_items |
Order line snapshot | order_id, product_id, sku, name, quantity, unit_price, line_total |
order_address |
Immutable checkout delivery snapshot | one row per order: recipient/mobile/address/city/state/PIN/country |
inventory_movements |
Auditable stock ledger | product_id, optional order_id, change_qty, reason |
Categories/products are deactivated rather than hard-deleted because historical order items may reference them. See Applications → Commerce.
Logistics operations schema (0011)¶
0011_logistics_operations.sql preserves the original shipments table and adds operational
columns for complete delivery address metadata, district/landmark, route/hub/partner assignment,
driver/vehicle assignment, estimated/delivered timestamps, last tracking event and delivery notes.
| Table | Purpose | Important fields |
|---|---|---|
logistics_routes |
Rural delivery cluster/service route | code, cluster, district, service_days, capacity, estimated_cost, status |
logistics_hubs |
City/district/mandal hub | name, type, district, address, contact, status |
logistics_partners |
Pickup/delivery/collection partner | village, district, type, commission_rate, status |
logistics_drivers |
Delivery workforce | name, mobile, license_number, status |
logistics_vehicles |
Fleet master | unique registration_number, vehicle_type, capacity_kg, status |
shipment_events |
Shipment tracking history | shipment_id, status, location, note, created_by, created_at |
logistics_invoices |
Simple logistics account ledger | account_name, shipment_count, amount, due_date, status |
Two D1 triggers keep cross-entry-point behavior consistent: every inserted shipment receives an
initial tracking event, and Commerce-created shipments are hydrated from the immutable
order_address checkout snapshot.
The existing shipments.user_id remains the customer-ownership link, so Corporate Customer Account
queries can show both an order and its shipment.
Customer Account schema (0012)¶
customer_addresses stores customer-owned delivery address templates:
| Field | Purpose |
|---|---|
user_id |
Ownership; every read/write is scoped to the authenticated Customer |
label |
User-facing name such as Home or Office |
| recipient/mobile/address/city/state/PIN/country | Reusable delivery fields |
is_default |
One active default per customer, enforced by a partial unique index |
status |
Active or Archived; removing a saved address does not affect past orders |
The table intentionally has no foreign key to orders or order_address. Commerce order delivery
data remains a point-in-time snapshot, while saved addresses remain editable convenience data.
Admin RBAC schema (0013)¶
admin_access is one row per Admin identity:
| Field | Purpose |
|---|---|
user_id |
Admin identity, FK to shared users |
access_level |
SuperAdmin, Support, Marketplace, Commerce, or Operations |
created_by |
Admin that originally assigned the access row when applicable |
| timestamps | Audit-friendly assignment timing |
Existing Admin accounts are backfilled as SuperAdmin during the migration. A new identity with
users.role='Admin' but no admin_access row is not implicitly privileged. The Admin Worker
loads the assignment from D1 on protected requests, so access changes take effect without issuing a
new session token.
Admin MFA schema (0014)¶
MFA remains optional per Admin. No existing account is enrolled automatically.
| Table | Purpose | Sensitive-data rule |
|---|---|---|
admin_mfa |
TOTP enrollment state and last accepted time step | secret_ciphertext is AES-GCM ciphertext only; plaintext secret never persists in D1 |
admin_mfa_recovery_codes |
One-time fallback second factors | only SHA-256 code hashes persist; plaintext codes are returned once during setup |
admin_mfa_challenges |
Five-minute post-password login challenge | stores only an opaque challenge-token hash |
last_used_step prevents replay of an already accepted TOTP time-step code. Recovery-code rows mark
used_at atomically so each fallback code works once. All three tables cascade with the Admin user.
The AES-GCM key comes from the Admin Worker-only ADMIN_MFA_ENCRYPTION_KEY secret and is not stored
in D1.
R2: akshaya-group-global-files¶
One bucket, binding name FILES where needed. workers/api writes private embroidery references,
software-web manages freelancer avatars, and admin-web manages Commerce product images.
Public download remains an explicit allowlist in the central API. Product images and freelancer avatars are public-safe categories; private embroidery references are not.
logistics-web does not bind R2 in this phase because proof-of-delivery photos/signatures are not
implemented yet.
Email: Cloudflare Email Workers (send_email binding)¶
The central API NOTIFY_EMAIL binding sends configured application notifications. Logistics
operational state itself does not depend on email delivery.