Subversion Repositories SmartDukaan

-- =====================================================================

-- Third-party external API: client auth tables + delta-sync timestamps

-- =====================================================================

-- MUST be applied BEFORE deploying the build that maps update_timestamp

-- (Hibernate SELECTs the new columns).

--

-- Pre-flight sizing (decide plain ALTER vs pt-online-schema-change):

--   SELECT table_schema, table_name, table_rows, ROUND(data_length/1024/1024) mb

--   FROM information_schema.tables

--   WHERE (table_schema='catalog' AND table_name IN ('tag_listing','item','catalog'))

--      OR (table_schema='fofo' AND table_name='current_inventory_snapshot');

-- MySQL 5.7: ADD COLUMN is ALGORITHM=INPLACE (online DML) but a full table

-- rebuild. catalog.* tables are small; if current_inventory_snapshot is

-- multi-million rows / >~1GB use pt-osc off-peak.

-- 1. API client auth ---------------------------------------------------

CREATE TABLE fofo.api_client (

  id INT UNSIGNED NOT NULL AUTO_INCREMENT,

  name VARCHAR(100) NOT NULL,

  api_key_hash CHAR(64) NOT NULL COMMENT 'lowercase hex SHA-256 of raw key',

  active TINYINT(1) NOT NULL DEFAULT 1,

  create_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  update_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id),

  UNIQUE KEY uq_api_client_key_hash (api_key_hash),

  UNIQUE KEY uq_api_client_name (name)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE fofo.api_client_store (

  id INT UNSIGNED NOT NULL AUTO_INCREMENT,

  api_client_id INT UNSIGNED NOT NULL,

  fofo_id INT NOT NULL,

  create_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (id),

  UNIQUE KEY uq_client_store (api_client_id, fofo_id),

  KEY idx_acs_fofo (fofo_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. Delta-sync update timestamps -------------------------------------

-- Existing rows get the ALTER-time value; acceptable because a partner's

-- first sync is always full (no updatedSince).

-- ON UPDATE CURRENT_TIMESTAMP fires only when a column value actually

-- changes; no-op UPDATEs do not bump it (correct for delta sync).

-- Hard DELETEs remain invisible to deltas -> partners advised to run a

-- weekly full sync.

ALTER TABLE catalog.tag_listing

  ADD COLUMN update_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  ADD KEY idx_tl_update_ts (update_timestamp);

ALTER TABLE catalog.item

  ADD COLUMN update_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  ADD KEY idx_item_update_ts (update_timestamp);

ALTER TABLE catalog.catalog

  ADD COLUMN update_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

ALTER TABLE fofo.current_inventory_snapshot

  ADD COLUMN update_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  ADD KEY idx_cis_update_ts (update_timestamp);

-- 3. Mongo one-time backfill (run in mongo shell, CONTENT db, AFTER the

--    build that stamps lastModified in Mongo.insertOrUpdateById is live).

--    Docs missing lastModified are treated as always-included in deltas,

--    so deploy/backfill ordering is safe either way.

--

--   db.siteContent.updateMany(

--     { lastModified: { $exists: false } },

--     { $set: { lastModified: NumberLong(Date.now()) } }

--   )

-- 4. Client seeding template -------------------------------------------

-- Generate a raw key offline (e.g. openssl rand -hex 32), give it to the

-- partner once, store only the hash:

--

--   INSERT INTO fofo.api_client (name, api_key_hash)

--   VALUES ('<PARTNER_NAME>', SHA2('<raw-key>', 256));

--

--   INSERT INTO fofo.api_client_store (api_client_id, fofo_id)

--   SELECT ac.id, fs.id

--   FROM fofo.api_client ac

--   JOIN fofo.fofo_store fs ON fs.id IN (<fofo ids to expose>)

--   WHERE ac.name = '<PARTNER_NAME>';