PHPackages pstoute/laravel-keywords-database - PHPackages - PHPackages [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register) 1. [Directory](/) 2. / 3. [Database & ORM](/categories/database) 4. / 5. pstoute/laravel-keywords-database ActiveLibrary[Database & ORM](/categories/database) pstoute/laravel-keywords-database ================================= Laravel package for centralized keyword database architecture across multiple applications. Share keyword data, SERP results, and research across your SEO tools. v1.0.0(3mo ago)0272MITPHPPHP ^8.2 Since Feb 3Pushed 3mo agoCompare [ Source](https://github.com/pstoute/laravel-keywords-database)[ Packagist](https://packagist.org/packages/pstoute/laravel-keywords-database)[ Docs](https://github.com/pstoute/laravel-keywords-database)[ RSS](/packages/pstoute-laravel-keywords-database/feed)WikiDiscussions main Synced 1mo ago READMEChangelog (1)Dependencies (6)Versions (2)Used By (0) Laravel Keywords Database ========================= [](#laravel-keywords-database) [![Latest Version on Packagist](https://camo.githubusercontent.com/d4858756b847496bdf9faa49e1bd6da03d60699679f7368e96c4973ebbdad1e1/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f762f7073746f7574652f6c61726176656c2d6b6579776f7264732d64617461626173652e7376673f7374796c653d666c61742d737175617265)](https://packagist.org/packages/pstoute/laravel-keywords-database)[![License](https://camo.githubusercontent.com/fa718073acad89064f75e419494a09b9133e36eace117263b846befb95a1dd30/68747470733a2f2f696d672e736869656c64732e696f2f7061636b61676973742f6c2f7073746f7574652f6c61726176656c2d6b6579776f7264732d64617461626173652e7376673f7374796c653d666c61742d737175617265)](https://packagist.org/packages/pstoute/laravel-keywords-database) A Laravel package for building a centralized keyword database shared across multiple applications. Perfect for SEO tools, rank trackers, and keyword research platforms that need to share data efficiently. Overview -------- [](#overview) When building multiple SEO-related applications (rank trackers, keyword research tools, content optimization platforms), you often need to store and share keyword data. This package provides: - **Centralized keyword storage** - One database for all your applications - **SERP results caching** - Reduce API costs by sharing search results - **Keyword metrics by location** - Store volume, CPC, and competition data per location - **Keyword research sessions** - Track and share research across apps - **Connection resilience** - Built-in retry logic for database operations Architecture ------------ [](#architecture) ``` ┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐ │ Rank Tracker │ │ Keyword Research│ │ SEO Dashboard │ │ Application │ │ Application │ │ Application │ └────────┬────────┘ └────────┬────────┘ └────────┬────────┘ │ │ │ └───────────────────────┼───────────────────────┘ │ ┌────────────▼────────────┐ │ Keywords Database │ │ (PostgreSQL) │ └─────────────────────────┘ ``` Requirements ------------ [](#requirements) - PHP 8.2+ - Laravel 11.x or 12.x - PostgreSQL 14+ (recommended) or MySQL 8+ Installation ------------ [](#installation) ``` composer require pstoute/laravel-keywords-database ``` The package will auto-register its service provider. Configuration ------------- [](#configuration) ### 1. Publish the configuration file [](#1-publish-the-configuration-file) ``` php artisan vendor:publish --tag=laravel-keywords-database-config ``` ### 2. Add environment variables [](#2-add-environment-variables) ``` KEYWORDS_DB_HOST=your-database-host KEYWORDS_DB_PORT=5432 KEYWORDS_DB_DATABASE=keywords_database KEYWORDS_DB_USERNAME=your-username KEYWORDS_DB_PASSWORD=your-password ``` ### 3. Run migrations [](#3-run-migrations) ``` php artisan keywords-db:migrate ``` Or publish and customize migrations: ``` php artisan vendor:publish --tag=laravel-keywords-database-migrations php artisan migrate --path=database/migrations/laravel-keywords-database ``` Usage ----- [](#usage) ### Finding or Creating Keywords [](#finding-or-creating-keywords) ``` use Pstoute\KeywordsDatabase\Models\SharedKeyword; use Pstoute\KeywordsDatabase\Facades\KeywordsDatabase; // Using the model directly $keyword = SharedKeyword::findOrCreateKeyword('best seo tools', 'us'); // Using the facade $keyword = KeywordsDatabase::findOrCreate('best seo tools', 'us'); // Bulk operations $keywords = KeywordsDatabase::bulkFindOrCreate([ 'seo software', 'keyword research tools', 'rank tracking', ], 'us'); ``` ### Working with Location Metrics [](#working-with-location-metrics) ``` use Pstoute\KeywordsDatabase\Models\KeywordLocationMetrics; // Get metrics for a keyword in a specific location $metrics = $keyword->getLocationMetrics($locationId); // Check if metrics need refresh if ($metrics->needsRefresh()) { // Fetch fresh data from your API provider } // Update metrics $metrics->update([ 'avg_monthly_searches' => 12000, 'cpc_average' => 2.50, 'competition' => 0.75, 'competition_level' => 'HIGH', 'fetched_at' => now(), ]); ``` ### Storing SERP Results [](#storing-serp-results) ``` use Pstoute\KeywordsDatabase\Models\SerpResult; // Create a SERP result $serpResult = SerpResult::create([ 'keyword_id' => $keyword->id, 'location_id' => $locationId, 'device' => 'desktop', 'status' => 'completed', 'results' => $serpData, // Array of SERP listings 'searched_at' => now(), ]); // Find position for a URL $position = $serpResult->getPositionForUrl('https://example.com'); // Check if result is still fresh if (!$serpResult->isFresh()) { // Fetch new SERP data } ``` ### Keyword Research Sessions [](#keyword-research-sessions) ``` use Pstoute\KeywordsDatabase\Models\KeywordResearchSession; use Pstoute\KeywordsDatabase\Models\KeywordResearchResult; // Create a research session $session = KeywordResearchSession::create([ 'team_id' => $teamId, 'seed_keyword' => 'seo tools', 'location_id' => $locationId, 'research_types' => ['suggestions', 'related', 'questions'], 'status' => 'pending', ]); // Mark as started $session->markStarted(); // Add results KeywordResearchResult::create([ 'session_id' => $session->id, 'seed_keyword_id' => $seedKeyword->id, 'discovered_keyword_id' => $discoveredKeyword->id, 'location_id' => $locationId, 'research_type' => 'suggestions', 'search_volume' => 5000, 'keyword_difficulty' => 45, 'search_intent' => 'commercial', ]); // Mark as completed $session->markCompleted(keywordsFound: 150, actualCost: 0.15); ``` ### Database Connection Resilience [](#database-connection-resilience) The package includes built-in retry logic for handling transient database failures: ``` use Pstoute\KeywordsDatabase\Services\DatabaseConnectionHelper; $result = DatabaseConnectionHelper::executeWithRetry(function () { return SharedKeyword::where('keyword', 'like', '%seo%')->get(); }); // Check connection status $status = DatabaseConnectionHelper::getConnectionStatus(); if (!$status['connected']) { // Handle connection failure } ``` Artisan Commands ---------------- [](#artisan-commands) ### Check connection status [](#check-connection-status) ``` php artisan keywords-db:status ``` ### View database statistics [](#view-database-statistics) ``` php artisan keywords-db:stats ``` ### Run migrations [](#run-migrations) ``` php artisan keywords-db:migrate ``` ### Cleanup expired SERP tasks [](#cleanup-expired-serp-tasks) ``` # Preview what would be cleaned up php artisan keywords-db:cleanup-expired --dry-run # Clean up tasks older than 48 hours php artisan keywords-db:cleanup-expired --hours=48 ``` Building Your Own Integrations ------------------------------ [](#building-your-own-integrations) This package provides the data layer. You'll need to implement your own: ### 1. Location Sync Command [](#1-location-sync-command) Sync locations from your SERP API provider: ``` // Example for DataForSEO class SyncLocationsCommand extends Command { public function handle() { $response = Http::withBasicAuth($login, $password) ->get('https://api.dataforseo.com/v3/serp/google/locations'); foreach ($response->json()['tasks'][0]['result'] as $location) { SerpLocation::updateOrCreate( ['criteria_id' => $location['location_code']], [ 'location_id' => $location['location_code'], 'name' => $location['location_name'], 'country_code' => $location['country_iso_code'], 'target_type' => $location['location_type'], ] ); } } } ``` ### 2. Keyword Metrics Fetcher [](#2-keyword-metrics-fetcher) Fetch keyword metrics from your provider: ``` class FetchKeywordMetrics extends Command { public function handle() { $keywords = SharedKeyword::whereDoesntHave('locationMetrics', function ($q) { $q->where('location_id', $this->locationId)->fresh(); })->limit(100)->get(); foreach ($keywords as $keyword) { $metrics = $this->apiClient->getKeywordMetrics($keyword->keyword); KeywordLocationMetrics::updateOrCreate( ['keyword_id' => $keyword->id, 'location_id' => $this->locationId], [ 'avg_monthly_searches' => $metrics['search_volume'], 'cpc_average' => $metrics['cpc'], 'competition' => $metrics['competition'], 'fetched_at' => now(), ] ); } } } ``` Compatible Data Providers ------------------------- [](#compatible-data-providers) This package is provider-agnostic. Implement the `KeywordProviderInterface` for your preferred service: - [DataForSEO](https://dataforseo.com/) - [SerpApi](https://serpapi.com/) - [SEMrush API](https://www.semrush.com/api/) - [Ahrefs API](https://ahrefs.com/api) - [Moz API](https://moz.com/products/api) Database Schema --------------- [](#database-schema) ### Tables [](#tables) TableDescription`keywords`Core keyword storage with intent probabilities`keyword_histories`Monthly historical data`serp_google_locations`Geographic locations for searches`serp_google_countries`Country data`serp_google_domains`Google domains (google.com, google.co.uk, etc.)`serp_google_languages`Language data`serp_results`Cached SERP results`keyword_location_metrics`Location-specific keyword metrics`keyword_research_sessions`Research session tracking`keyword_research_results`Discovered keywords from researchConfiguration Options --------------------- [](#configuration-options) ``` // config/laravel-keywords-database.php return [ // Database connection name 'connection' => env('KEYWORDS_DB_CONNECTION', 'keywords_database'), // Connection retry settings 'retry' => [ 'max_attempts' => 3, 'delay_ms' => 100, 'multiplier' => 2, ], // Caching settings 'cache' => [ 'enabled' => true, 'ttl' => 86400, // 24 hours 'prefix' => 'keyword:', ], // Data freshness thresholds 'freshness' => [ 'serp_results_hours' => 24, 'metrics_days' => 7, 'research_days' => 7, ], ]; ``` Testing ------- [](#testing) ``` composer test ``` Contributing ------------ [](#contributing) Contributions are welcome! Please feel free to submit a Pull Request. License ------- [](#license) The MIT License (MIT). Please see [License File](LICENSE) for more information. Credits ------- [](#credits) - [Paul Stoute](https://github.com/pstoute) ### Health Score 40 — FairBetter than 88% of packages Maintenance82 Actively maintained with recent releases Popularity15 Limited adoption so far Community6 Small or concentrated contributor base Maturity46 Maturing project, gaining track record Bus Factor1 Top contributor holds 100% of commits — single point of failure How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window. **Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores. **Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement. **Maturity (30%)** — Project age, version count, PHP version support, and release stability. ### Release Activity Cadence Unknown Total 1 Last Release 96d ago ### Community Maintainers ![](https://www.gravatar.com/avatar/f809227e1e4c6e364e83cda5f2ebe4b144dd738685da480baa2fc2e120a57125?d=identicon)[pstoute](/maintainers/pstoute) --- Top Contributors [![pstoute](https://avatars.githubusercontent.com/u/669308?v=4)](https://github.com/pstoute "pstoute (4 commits)") --- Tags laravelkeywordsseoSERPdataforseorank-trackingkeyword-research ### Code Quality TestsPHPUnit ### Embed Badge ![Health badge](/badges/pstoute-laravel-keywords-database/health.svg) ``` [![Health](https://phpackages.com/badges/pstoute-laravel-keywords-database/health.svg)](https://phpackages.com/packages/pstoute-laravel-keywords-database) ``` ### Alternatives [tucker-eric/eloquentfilter An Eloquent way to filter Eloquent Models 1.8k4.8M26](/packages/tucker-eric-eloquentfilter)[cviebrock/eloquent-sluggable Easy creation of slugs for your Eloquent models in Laravel 4.0k13.6M253](/packages/cviebrock-eloquent-sluggable)[cviebrock/eloquent-taggable Easy ability to tag your Eloquent models in Laravel. 567694.8k3](/packages/cviebrock-eloquent-taggable)[clickbar/laravel-magellan This package provides functionality for working with the postgis extension in Laravel. 423715.4k1](/packages/clickbar-laravel-magellan)[baril/bonsai An implementation of the Closure Tables pattern for Eloquent. 3593.5k](/packages/baril-bonsai)[toponepercent/baum Baum is an implementation of the Nested Set pattern for Eloquent models. 3154.7k](/packages/toponepercent-baum) PHPackages © 2026 [Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)