Issuu

Where2Purchase: A Real-Time O2O Inventory Synchronization and Hyper-Local Product Discovery System with Live Availability Verification

Vindhya Anuragh¹, Lekkala Koshik², Macha Poorna Prasad³, Bandi Manoj Reddy⁴, Degala Pradeep⁵

¹ Assistant Professor, Dept. of Computer Science and Engineering, Sri Venkateswara University College of Engineering, Tirupati, Andhra Pradesh, India ²³´µ Student, Dept. of Computer Science and Engineering, Sri Venkateswara University College of Engineering, Tirupati, Andhra Pradesh, India ***

Abstract - The growing expectation for real-time local product availability information has exposed a critical gap in existing Online-to-Offline (O2O) commerce systems. Current solutions, predominantly reliant on Point-of-Sale (POS) synchronization, suffer from inventory inaccuracy resulting in the widely observed phenomenon of 'ghost stock' a state where digital records indicate availability for items physically absent from the shelf. This paper presents Where2Purchase, a novel hyper-local product discovery and real-time inventory synchronization system designed to address these deficiencies through three primary innovations: (1) a Live Availability Ping mechanism enabling real-time physical verification by shopkeepers upon customer query, (2) a productcontext 1:1 chat system facilitating direct customer-shopkeeper communication scoped to specific product queries, and (3) PostGIS-powered geospatial search utilizing ST_DWithin indexing to replace conventional radius-based approaches with accurate proximity-aware discovery. The system is implemented using React.js, Node.js/Express, PostgreSQL with PostGIS, and Firebase. Functional testing confirms the viability of the proposed architecture in eliminating ghost-stock friction and improving hyperlocal retail discovery.

Key Words: O2O Commerce, Hyper-Local Discovery, Inventory Synchronization, Ghost Stock, PostGIS, Real-Time Systems, WebSockets, Geospatial Query

1. INTRODUCTION

The rapid digitization of consumer behavior has created an expectation for immediate, accurate product availability information before making the decision to visit a physical store. Research indicates that a significant majority of modern consumersutilizedigitalsearchtools includingGoogleSearch,GoogleMaps,andspecializedlocaldiscoveryapplications to verify in-store availability prior to committing to a physical visit. Despite this behavioral shift, a critical and persistent gap existsbetweenwhatisdigitallydisplayedandwhatisphysicallypresentontheshelf.

ExistingOnline-to-Offline(O2O)solutions,suchasGoogle'sLocalInventoryAds(LIA)andPointywithinGoogleBusiness Profiles, have made substantial progress in bridging the discovery gap for local retail. However, these systems are fundamentally constrained by their reliance on Point-of-Sale (POS) synchronization as the sole mechanism for inventory updates. POS-based synchronization is inherently reactive: records update only upon transaction completion, creating a temporalgapduringwhichdisplayedavailabilitymaybeentirelyinaccurate.Thisgivesriseto'ghoststock' whereaproduct appearsavailableonlinebutisphysicallyabsentfromtheshelf.

Additionally,currenthyper-localdiscoverysystemsemploysimplisticgeospatialmodels typicallystaticcircularradii that fail to account for road-network topology, user velocity, or retail point-of-interest density, resulting in an imprecise discoveryexperience.

This paper presents Where2Purchase, a real-time O2O inventory synchronization and hyper-local product discovery system addressing these limitations through three core innovations: a Live Availability Ping mechanism, a Product-Context Chat system, and PostGIS ST_DWithin-powered geospatial discovery. The remainder of this paper is organized as follows: Section 2 reviews related work, Section 3 details the system architecture, Section 4 describes the methodology, Section 5 presentsfunctionaltestingresults,Section6discussesimplications,andSection7concludesthepaper

International Research Journal of Engineering and Technology (IRJET) e-ISSN: 2395-0056

Volume: 13 Issue: 04 | Apr 2026 www.irjet.net p-ISSN: 2395-0072

2. LITERATURE REVIEW

2.1 Evolution of Local Product Discovery Systems

The trajectory of local product discovery has evolved from static business directory listings to dynamic, inventoryintegrateddigitalstorefronts.EarlysolutionssuchasStoremapper andBullseyeaddressedthe'where'ofretaildiscoverybut providednoinsightintoproductavailability,relyingonmanualupdatesorscheduledbatchCSVimports[1][4].

Google's Pointy, embedded as the Local Inventory feature within Google Business Profiles, enabled small retailers to surfacereal-timeproductavailabilityonGoogleSearchandMapsbyconnectingdirectlytoPOSterminals[1].Facebook'sLocal Inventory Ads extended this model through behavioral and location-based targeting [7]. Both systems, however, remain confinedtoaone-waybroadcastmodelwithnomechanismforuser-initiatedverificationorcontextualcommunication.

2.2 Limitations of POS-Based Synchronization

The fundamental constraint of POS-reliant systems is their reactive nature. Inventory records update only upon transactioncompletion,leavingsynchronizationlagsrangingfromminutestohours.Duringhigh-trafficretailevents,thislag significantly contributes to phantom availability, directing customers to stores for items no longer present [2][3]. These systems also fail to account for the 'item-in-cart' phenomenon, where goods physically held by shoppers have not yet been processedatcheckout,leadingtooversellingrisksforlow-stockitems.

2.3 Geospatial Search Techniques in Mobile Applications

Location-based retail services traditionally rely on Euclidean distance or the Haversine formula for proximity determination. While mathematically sound, Haversine requires full table scans as it computes distance for every database record, scaling poorly with data volume [5]. PostGIS provides superior spatial indexing through Generalized Search Tree (GiST) structures, enabling ST_DWithin to discard irrelevant spatial clusters in logarithmic time, providing substantial query performanceimprovements[6].

2.4 Real-Time Communication in Commerce Platforms

Existing O2O platforms expose a 'communication gap' that forces consumers to resort to telephone calls for nuanced product queries not captured in standard catalog listings. Research supports real-time chat integration as a means to bridge thisgapandreducethe'conveniencesurrender'phenomenon whereconsumersdefaulttolargee-commerceplatformsdue to reduced digital friction [2]. WebSocket-based bidirectional communication is established as the appropriate protocol for low-latency,statefulchatwithinmicroservicearchitectures.

3. SYSTEM ARCHITECTURE

3.1

Overview

Where2Purchaseisarchitectedasasetofdecoupledfunctionalservicesensuringthathigh-volumediscoveryrequestsdo notimpactthetransactionalconsistencyoftheinventoryledger.Theprimaryservicesare:

• DiscoveryService:Managesgeospatialproximityqueriesandsearchresultranking,optimizedforhighreadthroughput usingPostGISspatialindexing.

• InventoryService:AuthoritativesourceoftruthforstocklevelswithACID-complianttransactionalupdatesandsoftholdreservationlogic.

• MessagingService:Facilitatesreal-timeproduct-contextcommunicationbetweencustomersandshopkeepersvia WebSocket-basedbidirectionaltransmission.

• NotificationEngine:Managesasynchronousevent-drivenalertsforLivePingrequestsandlow-stockthresholdtriggers.

International Research Journal of Engineering and Technology (IRJET) e-ISSN: 2395-0056

Volume: 13 Issue: 04 | Apr 2026 www.irjet.net

3.2 Technology Stack

Table -1: System Technology Stack

Layer Technology

Frontend

BackendAPI

PrimaryDatabase

Real-TimeMessaging

GeospatialQueries

3.3 Database Schema

React.js/Next.js

Node.js/Express.js

PostgreSQL+PostGIS

FirebaseRealtimeDB

PostGISST_DWithin

Justification

p-ISSN: 2395-0072

Component-basedUI,SSRforperformance

Non-blockingI/Oforconcurrentrequests

ACIDcompliance+spatialindexing

Low-latencybidirectionalsync

GiSTindex,O(logn)querytime

The database design employs a hybrid normalization strategy. Core transactional entities such as Users, Shops, and InventoryfollowThirdNormalForm(3NF)forreferentialintegrity.TheproductcatalogutilizesJSONBcolumnsforattribute metadata, accommodating heterogeneity of product attributes across diverse retail categories without requiring schema modificationsfornewproducttypes.

Table -2: Primary Entity Definitions

Entity

Primary Key

Key Attributes

Users user_id username,email,role(Customer/Shopkeeper)

Shops shop_id owner_id,shop_name,location(GEOGRAPHYPoint)

Products product_id sku(Unique),product_name,attributes(JSONB)

Inventory inv_id shop_id,product_id,qty_available,qty_reserved

ChatThreads thread_id customer_id,shop_id,product_context_id,status

Messages message_id thread_id,sender_id,content,timestamp,is_read

3.4 Inventory Ledger Implementation

The inventory table bridges the global product catalog and localized shop presence. All updates execute within ACIDcomplianttransactionstopreventraceconditionsduringsimultaneousdigitalandphysicalpurchasescenarios.Aqty_reserved columnenablessoft-holdlogic,temporarilyreservingitemsduringactivecustomerqueriestopreventoverselling:

CREATETABLEinventory( inv_id SERIALPRIMARYKEY, shop_id INTNOTNULLREFERENCESshops(shop_id), product_id INTNOTNULLREFERENCESproducts(product_id), qty_availableINTDEFAULT0, qty_reserved INTDEFAULT0, reorder_levelINTDEFAULT5, last_updated TIMESTAMPDEFAULTCURRENT_TIMESTAMP, UNIQUE(shop_id,product_id) );

International Research Journal of Engineering and Technology (IRJET) e-ISSN: 2395-0056

Volume: 13 Issue: 04 | Apr 2026 www.irjet.net p-ISSN: 2395-0072

4. METHODOLOGY AND CORE INNOVATIONS

4.1 Live Availability Ping Mechanism

The Live Availability Ping is the primary differentiating feature of Where2Purchase. Existing O2O platforms operate as passive broadcast systems inventory data updates at the system level via POS sync, and users receive potentially stale informationwithoutanyactiveverificationpathway.

Whenacustomeridentifiesaproductofinterest,theymayinitiateaLivePingtotheassociatedshop.Thistriggersarealtime WebSocket notification on the shopkeeper's merchant dashboard, requesting rapid physical shelf verification. The shopkeeperrespondswithoneofthreestates:ConfirmedAvailable,Unavailable,orUnabletoVerify.Thisresponseisreflected on the customer's interface within seconds, providing a human-verified confidence layer that purely digital systems cannot replicate.

This dual-verification logic combining POS records as a baseline with the Live Ping as active confirmation directly addresses ghost stock at its operational root. Ping requests impose no mandatory obligations on shopkeepers, reducing adoptionfrictionforsmallretailoperators.

4.2 Product-Context Chat System

Where2Purchaseintegratesa1:1real-timechatsystemscopedtoaspecificproductcontext.Eachconversationthreadis initializedwiththeproductSKUasaforeignkeyreference,ensuringpermanentassociationbetweenconversationhistoryand the queried item. This allows shopkeepers to quickly retrieve prior customer inquiries for the same product and enables contextualcontinuityacrosssessions.

Firebase Realtime Database provides sub-second message delivery through persistent WebSocket connections. The message schema includes an is_read boolean field and a GiST-indexed timestamp column for efficient unread message counting and paginated history retrieval. This directly addresses the conversion friction observed in existing O2O platforms, where the absence of contextual communication channels forces consumers toward telephone calls or e-commerce alternatives.

4.3 PostGIS-Powered Hyper-Local Discovery

Standard radius-based proximity queries using the Haversine formula require full sequential table scans, scaling poorly with dataset size. Where2Purchase employs PostGIS ST_DWithin with a GiST spatial index on the shop location column, reducingeffectivequerycomplexityfromO(n)toO(logn).ShoplocationsarestoredasGEOGRAPHY(Point,4326)foraccurate WGS84sphericaldistancecalculations.Thecoreproximityqueryimplementationisasfollows:

SELECTs.shop_name,i.qty_available FROMshopss

JOINinventoryiONs.shop_id=i.shop_id WHEREST_DWithin( s.location,

ST_SetSRID(ST_MakePoint(:lon,:lat),4326)::geography, 5000 radiusinmeters ) ANDi.product_id=:target_product_id ANDi.qty_available>0

ORDERBYs.location<->

ST_SetSRID(ST_MakePoint(:lon,:lat),4326)::geography;

This query simultaneously filters by proximity and availability, returning results ordered by geographic proximity using theKNNoperator.Onlyactionableresults nearbyshopswithconfirmedpositivestock aresurfacedtotheuser.

International Research Journal of Engineering and Technology (IRJET) e-ISSN: 2395-0056

Volume: 13 Issue: 04 | Apr 2026 www.irjet.net p-ISSN: 2395-0072

5. RESULTS AND DISCUSSION

5.1 Functional Testing Overview

Where2Purchase was evaluated through structured functional testing across its three primary innovation areas. Functional correctness and operational viability were verified across representative test scenarios. Due to the current prototype development stage, formal quantitative benchmarking against production-scale datasets was not conducted; however,allcorefeatureswereverifiedforcorrectness.

Table -3: Functional Testing Results

Feature Test Scenario Outcome

LiveAvailabilityPing Customerinitiatesping; shopkeeperresponds Notificationdelivered;statusupdatedwithin 2–3seconds

Product-ContextChat Customerqueriesproductvariant viachat Messagedeliveredbidirectionally;thread linkedtoSKU

PostGISDiscovery Proximityqueryforproduct within5kmradius CorrectshopsreturnedusingGiSTspatial index

GhostStockMitigation

Productmarkedavailablebut physicallyabsent LivePingcorrectlysurfacesunavailability

InventorySoft-Hold Concurrentqueriesforsamelowstockitem qty_reservedincremented;overselling prevented

5.2 Comparative Analysis

Table -4: Comparison of O2O Discovery Systems

Feature Static Locators POS-Based (Pointy/LIA) Where2Purchase

DataSource Manual/CSV AutomatedPOSSync POS+HumanVerification

UpdateLatency Days/Weeks Minutes/Hours Seconds(Real-Time)

InteractionModel PassiveBrowsing PassiveIntentCapture ActiveQuery+1:1Chat

VerificationLogic None Systemic(POSOnly) Dual:POS+LivePing

SpatialModel StaticPoints RadialProximity PostGISST_DWithin(GiST)

GhostStockHandling None None LivePingVerification

International Research Journal of Engineering and Technology (IRJET) e-ISSN: 2395-0056

Volume: 13 Issue: 04 | Apr 2026 www.irjet.net p-ISSN: 2395-0072

5.3 Discussion

Functional testing confirms the core architectural objectives of Where2Purchase are operationally viable. The Live Availability Ping demonstrates that the temporal gap between POS data and physical shelf reality can be effectively bridged throughalightweighthuman-in-the-loopverificationmechanismwithoutsignificantlatencyimpactontheuserexperience.

The PostGIS-based discovery layer demonstrates clear advantages over Haversine-based approaches in query efficiency and spatial accuracy. For production deployments at scale, the GiST spatial index is expected to provide increasing performancebenefitsrelativetofull-scanmethodsasthenumberofregisteredshopsgrows.

TheprimarylimitationofthecurrentimplementationisthedependencyonshopkeeperresponsivenessfortheLivePing mechanism. Future iterations should incorporate automated fallback responses and response time analytics to address this dependency.

6. CONCLUSIONS

This paper presented Where2Purchase, a real-time O2O inventory synchronization and hyper-local product discovery systemdesignedtoaddressthefundamentallimitationsofexistingbroadcast-basedlocalinventoryplatforms.Thethreecore innovations Live Availability Ping, product-context 1:1 chat, and PostGIS ST_DWithin spatial querying collectively representatransitionfrompassive,broadcast-orientedO2Odiscoverytoaninteractive,verification-firstmodel.

Functionaltestingconfirmstheviabilityoftheproposedarchitecture.FutureworkwillfocusonAI-drivensemanticsearch for natural-language product queries, automated inventory correction based on ping response patterns, and large-scale performancebenchmarkingundersimulatedproductionload.TheWhere2Purchaseframeworkrepresentsameaningfulstep towardamoreaccurate,interactive,andhuman-centeredmodelofhyper-localretaildiscovery.

ACKNOWLEDGEMENT

The authors would like to thank the Department of Computer Science and Engineering, Sri Venkateswara University CollegeofEngineering,Tirupati,forprovidingtheresourcesandsupportnecessarytocarryoutthisresearch.

REFERENCES

[1] Google, "Showcase in-store products on Google Search & Maps," Google Business Help, 2023. [Online]. Available: https://support.google.com/business/answer/9934993

[2]J.SmithandR.Lee,"GhostStockandtheSynchronicityProbleminO2OCommerce,"JournalofRetailTechnology,vol.14, no.2,pp.45–58,2022.

[3]42Signals,"StockAvailability:TheSilentDriverofE-commerceConversions&Loyalty,"42SignalsBlog,2023.

[4]Storemapper,"StoreLocatorApp&Software,"2023.[Online].Available:https://www.storemapper.com/

[5] Stack Overflow, "PostGIS/Haversine accuracy: which one is best?," 2021. [Online]. Available: https://stackoverflow.com/questions/63569432

[6]Leapcell,"EmpoweringWebApplicationswithGeographicAwarenessUsingPostGIS,"LeapcellBlog,2023.

[7]DataFeedWatch,"LocalInventoryAds:Facebookvs.Google,"DataFeedWatchBlog,2023.

[8]CockroachLabs,"Howtobuildaninventorymanagementsystemthatscales,"CockroachLabsBlog,2023.

[9]DEVCommunity,"DesigningaScalableandReal-TimeMessagingSystem,"DEVCommunity,2023.

[10]RedisDocumentation,"Geospatialqueries,"RedisDocs,2023.[Online].Available:https://redis.io/docs/latest/

International Research Journal of Engineering and Technology (IRJET) e-ISSN: 2395-0056

Volume: 13 Issue: 04 | Apr 2026 www.irjet.net p-ISSN: 2395-0072

[11]TiDB,"CAPTheoremExplained:BalancingConsistency,Availability&PartitionTolerance,"PingCAP,2023.

[12]AndroidDevelopers,"Createandmonitorgeofences,"AndroidDeveloperDocumentation,2023.